EXERCISE 1
Appointment
StaffNo DentistName PatientNo PatientName SurgeryNo
Date Time
S1011 Tony Smith P100 Gillian White 12-Aug-03 10.00 S10
S1011 Tony Smith P105 Jill Bell 13-Aug-03 12.00 S15
S1024 Helen Pearson P108 Ian MacKay 12-Sept-03 10.00 S10
S1024 Helen Pearson P108 Ian MacKay 14-Sept-03 10.00 S10
S1032 Robin Plevin P105 Jill Bell 14-Oct-03 16.30 S15
S1032 Robin Plevin P110 John Walker 15-Oct-03 18.00 S13
Candidate primary keys
➢ StaffNo: not possible since the same dentist occurs in multiple rows since he
can treat several patients
➢ PatientNo: not possible since a patient may have more than one
appointment
➢ (StaffNo, PatientNo): not possible since a patient may have more than one
appointment with the same doctor
➢ (PatientNo, appointment): not possible since a patient can make more than
appointment at the same date and time with multiple doctors but finally he
comes to only one
➢ (StaffNo, Appointment): the most suitable one (unique)
2
Question 1: insertion anomalies
Appointment
StaffNo DentistName PatientNo PatientName SurgeryNo
Date Time
S1011 Tony Smith P100 Gillian White 12-Aug-03 10.00 S10
S1011 Tony Smith P105 Jill Bell 13-Aug-03 12.00 S15
S1024 Helen Pearson P108 Ian MacKay 12-Sept-03 10.00 S10
S1024 Helen Pearson P108 Ian MacKay 14-Sept-03 10.00 S10
S1032 Robin Plevin P105 Jill Bell 14-Oct-03 16.30 S15
S1032 Robin Plevin P110 John Walker 15-Oct-03 18.00 S13
NULL NULL P114 John Louis ??? ???
S1045 Sarra White NULL NULL
Violation of primary key constraint
3
Question 1: Delete anomalies
Appointment
StaffNo DentistName PatientNo PatientName SurgeryNo
Date Time
S1011 Tony Smith P100 Gillian White 12-Aug-03 10.00 S10
S1011 Tony Smith P105 Jill Bell 13-Aug-03 12.00 S15
S1024 Helen Pearson P108 Ian MacKay 12-Sept-03 10.00 S10
S1024 Helen Pearson P108 Ian MacKay 14-Sept-03 10.00 S10
S1032 Robin Plevin P105 Jill Bell 14-Oct-03 16.30 S15
S1032 Robin Plevin P110 John Walker 15-Oct-03 18.00 S13
Data loss problem
4
Question 1: Modification anomalies
Appointment
StaffNo DentistName PatientNo PatientName SurgeryNo
Date Time
S1011 Tony Smith P100 Gillian White 12-Aug-03 10.00 S10
S1011 Tony Smith P105 Jill Bell 13-Aug-03 12.00 S15
S1024 Helen Pearson P108 Ian MacKay 12-Sept-03 10.00 S10
S1024 Helen Pearson P108 Ian MacKay 14-Sept-03 10.00 S10
S1032 Robin Plevin P105 Jill Bell 14-Oct-03 16.30 S15
S1032 Robin Plevin P110 John Walker 15-Oct-03 18.00 S13
Data inconsistency problem
5
First Normal Form
Appointment → data type
• Date YYYY-MM-DD
• Small date time YYYY-MM-DD hh:mm:ss
• Date time YYYY-MM-DD hh:mm:ss[nnn]
• ……
✓ No multi-valued attributes
✓ Records are uniquely identified
Second Normal Form
3 independent partial dependencies
1- DentistName is depending on StaffNo
StaffNo DentistName
2- SurgeryNo is depending on Appointment
StaffNo Appointment SurgeryNo
SurgeryNo is depending on StaffNo
3- PatientNo, PatientName are depending on Appointment & on StaffNo
StaffNo Appointment PatientNo PatientName
StaffNo DentistName StaffNo Appointment SurgeryNo
S1011 Tony Smith S1011 12-Aug-03 10.00 S10
S1024 Helen Pearson S1011 13-Aug-03 12.00 S15
S1032 Robin Plevin S1024 12-Sept-03 10.00 S10
S1024 14-Sept-03 10.00 S10
S1032 14-Oct-03 16.30 S15
S1032 15-Oct-03 18.00 S13
StaffNo Appointment PatientNo PatientName
S1011 12-Aug-03 10.00 P100 Gillian White
S1011 13-Aug-03 12.00 P105 Jill Bell
S1024 12-Sept-03 10.00 P108 Ian MacKay
S1024 14-Sept-03 10.00 P108 Ian MacKay
S1032 14-Oct-03 16.30 P105 Jill Bell
S1032 15-Oct-03 18.00 P110 John Walker
Third Normal Form
1 transitive dependency
PatientName is depending on PatientNo which is depending on Appointment
StaffNo Appointment PatientNo
PatientNo PatientName
Patient name redundancy is then removed
Dentists Surgeries
StaffNo Appointment SurgeryNo
StaffNo DentistName
S1011 12-Aug-03 10.00 S10
S1011 Tony Smith
S1011 13-Aug-03 12.00 S15
S1024 Helen Pearson
S1024 12-Sept-03 10.00 S10
S1032 Robin Plevin
S1024 14-Sept-03 10.00 S10
S1032 14-Oct-03 16.30 S15
S1032 15-Oct-03 18.00 S13
Appointments
StaffNo Appointment PatientNo Patients PatientNo PatientName
S1011 12-Aug-03 10.00 P100
P100 Gillian White
S1011 13-Aug-03 12.00 P105
P105 Jill Bell
S1024 12-Sept-03 10.00 P108
P108 Ian MacKay
S1024 14-Sept-03 10.00 P108
P110 John Walker
S1032 14-Oct-03 16.30 P105
S1032 15-Oct-03 18.00 P110 ∄Non prime attribute → prime attribute
EXERCISE 2
Repayment (borrower_id, name, address, loan_amount, request_date,
repayment_date, repayment_amount)
First Normal Form
✓ No multi-valued attributes
✓ Unique identification of records
Repayment (borrower_id, name, address, loan_amount, request_date, repayment_date,
repayment_amount)
Possible dependencies (assumptions)
Borrower_id → name
since every borrower is identified with an unique id
Borrower_id → address
since each borrower has generally only one address
Borrower_id, Requestdate → loanamount
since more than one loan cannot be requested by a single borrower
Borrower_id, requestdate, repayment_date → repayment_amount
since a borrower can make multiple repayments on a single day, but not on a single loan
2NF
Partial Functional dependency : Borrower_id, Requestdate → loanamount
Borrower (Borrower_id, Name, Address)
Borrower_loan (Borrower_id, Requestdate, Loanamount)
Repayment (Borrower_id, Requestdate, Repayment_date, Repayment_amount)
3NF
No transitive dependency
BCNF
No more than one primary key
∄ Non prime attribute → prime attribute
