Database theory and practise

Database Theory and Practice

Question 1

A ) Normalise the table OPERATIONS , showing each step

B ) Clearly identify the primary , candidate and foreign keys

TABLE : PATIENT

Primary Key : patientId

Foreign Key : doctorName to DOCTOR

TABLE : OPERATION

Primary Key : operationId

Candidate Key : operationName , operationDate , patientId

Foreign Key : patientId to PATIENT

Foreign Key : surgeonName to DOCTOR

Foreign Key : anaesthetistName to DOCTOR

Foreign Key : medicalCondition to CONDITION

TABLE : DOCTOR

Primary Key : doctorName

TABLE : CONDITION

Primary Key : medicalCondition

Question 2

A ) The corresponding set of functional

dependencies for the Hospital Data Set

The functional dependencies are

Patient_Id ( Patient_Name

Test_Id ( Test-Date , Nurse-Name , Physician-Name , Physician-ID Treatment-Code , Patient_Id

Patient_Id , Test-Date ( Test_Id , Physician-ID , Treatment-Code

Treatment-Code ( Tr-Desc , Tr-Date , Tr-Time , Tr-Result

B ) Produce the corresponding 3NF relations for the Hospital Data Set

1

1

2

C ) For the 3NF relations construct the equivalent ER model

OPERATIONS TABLE

patientId

patientName

admissionDate

dischargeDate

doctorName

operationName

operationId

operationDate

surgeonName

anaesthetistName

medicalCondition

docspecialty

medspecialty

rank

jobTitle

CONDITION

medicalCondition

medspecialty

OPERATION

operationId surgeonName

operationName anaesthetistName

operationDate patientId

DOCTOR

doctorName rank

docspecialty jobTitle

Patient_Id Patient_Name

1 P1

2 P2

PATIENT

patientId admissionDate

patientName dischargeDate

doctorName

OPERATION

operationId

operationDate operationName

docspecialty medspecialty

surgeonName rank

anaesthetistName jobTitle

medicalCondition

Test_Id Patient_Id Test-Date Treat-Code Nurse-Name Physician-ID

T1 1 Aug-80 TR1 Nurse-B PH-1

TR1 PH-3

TR2 PH-1

TR2 PH-2

Sept-81 TR1 Nurse-C PH-1

TR1 PH-2

TR2 PH-1

TR2 PH-2

T2 2 Aug-80 TR1 Nurse-B PH-1

TR1 PH-2

TR2 PH-1

TR3 PH-4

HOSPITAL

Physician-ID Physician-Name

PH-1 Doctor-X

PH-2 Doctor-Z

PH-3 Doctor-Y

PH-4 Doctor-W

Patient_Id Treatment-Code Tr-Desc Tr-Date Tr-Time Tr-Result

TR1 XXXXX 1-Sep-80 06 :00 R

TR1 XXXXX 1-Sep-80 16 :00

p TR2 ZZZZZ 2-Sep-80 06 :00 R

TR2 ZZZZZ 1-Sep-80 16 :00

p TR1 XXXXX 1-Nov-80 06 :00 R

TR1 XXXXX 1-Nov-80 16 :00

p TR2 ZZZZZ 3-Nov-80 06 :00 R

TR2 ZZZZZ 3-Nov-80 19 :00

p TR1 XXXXX 1-Nov-80 06 :00 R

TR1 XXXXX 1-Nov-80 16 :00

p TR2 ZZZZZ 3-Nov-80 06 :00 R

TR3 AAAA 3-Nov-80 19 :00

p PATIENT

PHYSICIAN

TREATMENT

TEST

Physician-ID

Physician-Name

Patient_Id

Patient_Name

Test_Id , Patient_Id , Test-Date , Treat-Code , Nurse-Name , Physician-ID

Patient_Id , Treatment-Code , Tr-Desc , Tr-Date , Tr-Time , Tr-result...