ASSIGMENT-2

ITC423-DATABASE SYSTEMS

 

SUBMITTED BY

JASTI AJAY KRISHNA

11559376

 

FOR

Hongen Lu

 

 

25/08/2014

 

 

 

 

 

 

 

PART-A

1.

· ABS maintains each and every record of the donor.

· Details of donor includes full name and address details, and their contact details.

· Gender, Medicare number and any other details.

· Medical condition of a donor.

· Also need to know that whether the donor had been in overseas during the last three months.

· Also need to know how long they stayed in each county, whether they contracted any disease while in the country.

· Firstly the abs staff starts with the enquiry procedure and then makes a decision whether to receive blood from the donor or not.

· If the staff member thinks that every is well and good with the donor the staff of abs need to record the details of the donor along with their staff id and abs donation center.

2.

 

 

 

 

 

 

 

 

 

3.

 

 

 

 

 

 

 

 

 

 

 

4.

 

 

 

 

 

 

 

 

 

 

 

5.

 

 

 

 

 

 

 

 

 

PART B. CREATING RDM

TASKS:

1. Attributes for each table shown in above ERD diagram is as follows:

For Table Donor

	Donor
	Donor_ID Donor_title Donor_name Donor_contact Donor_address Donor_medicare_no

 

For table Donor_Overseas

	Donor_Overseas
	Donor_overseas_country visited Donor_overseas_duration Donor_overseas_disease

 

For table Medical_condition

	Medical_condition
	Medical_condition_id Medical_condition_medication Medical_condition_surgery

 

 

 

For table ABS

	ABS
	ABS_id ABS_location ABS_staff_accept

 

For table ABS_Staff

	ABS_Staff
	ABS_Staff_id ABS_Staff_name ABS_Staff_location ABS_Staff_accept

 

2. RDM for each table in ERD

 

Table Donor (Donor_id, Donor_title, Donor_name, Donor_contact, Donor_address,

Donor_medicare_no)

Primary Key Donor_id

Foreign key Donor_id references ABS_staff

 

Table Donor_overseas (Donor_overseas_country visited, Donor_overseas_duration, Donor_overseas_disease)

Primary Key Donar_overseas_country visited

Foreign key Donor_overseas references medical_condition

 

 

 

Table Medical_condition (Medical_condition_id, Medical_condition_surgery, Medical_condition_medication)

Primary Key Medical_condition_id

Foreign key Medical_condition references Donor_overseas

 

Table ABS (ABS_id, ABS_location, ABS_staff_accept)

Primary Key ABS_id

Foreign key ABS_id references ABS_staff

 

Table ABS_Staff (ABS_Staff_id, ABS_Staff_name, ABS_Staff_location, ABS_Staff_accept)

Primary Key ABS_Staff_id

Foreign key ABS_Staff_id references ABS

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PART C.

1. Dependency diagrams for ABS database

 

 

 

 

2. Normalizing ABS tables to BCNF

Table donor

{donor}=> {donor_name, donor_contact, donor_address, donor_medicare_no}

3NF

Table donor_overseas

{donor_overseas}=>{ donor_overseas_country visited, donor_overseas_duration, donor_overseas_disease}

2NF

Table Medical_condition

{ Medical_condition}=>{ Medical_condition_id, Medical_condition_surgery, Medical_condition_medication}

1NF

Table ABS

{ABS}=>{ABS_id, ABS_location, ABS_staff_accept}

1NF

Table ABS_Staff

{ABS_Staff}=>{ ABS_Staff_id, ABS_Staff_name, ABS_Staff_location, ABS_Staff_accept}

1NF

 

 

 

 

 

 

 

3. New RDM FOR NORMALISED TABLES

For Table Donor

	Donor
	Donor_name Donor_contact Donor_address Donor_medicare_no

 

For Table Donor_Overseas

	Donor_Overseas
	Donor_overseas_country visited Donor_overseas_duration Donor_overseas_disease

 

For Table Medical_condition

	Medical_condition
	Medical_condition_id Medical_condition_surgery Medical_condition_medication

 

 

 

 

 

 

 

For Table ABS

	ABS
	ABS_id ABS_location ABS_staff_accept

 

 

For Table ABS_Staff

	ABS_Staff
	ABS_Staff_id ABS_Staff_name ABS_Staff_location ABS_Staff_accept

 

 

 

 

 

 

 

 

PART D

1. We can include the table “Donation Records” so that we can maintain a record of a person blood donation records, we can know if he had recently donated blood to someone.

2. Modifying the Design