Normalize the given table below and functional dependencies into 1NF, 2NF, 3NF a
ID: 3698824 • Letter: N
Question
Normalize the given table below and functional dependencies into 1NF, 2NF, 3NF and BCNF normal forms
Technician ID
Technician
Name
Appointment Date
Appointment Time
Patient ID
Patient Name
192115
Mousa Ali
12 Aug 2017
09:00 am
21018
Omar Hassan
192115
Mousa Ali
15 Aug 2017
02:00 pm
21020
Kamal Ali
192119
Abdullah Haji
01 Sep 2017
10:00 am
21119
Khalid Ahmed
192119
Abdullah Haji
05 Sep 2017
01:00 pm
21018
Omar Hassan
FD1= Technician_id, app_date, app_time ---> patient_id, patient_name
FD2 = Technician_id ---> technician_name
FD3 = patient_id ---> patient_name
NOTE: PLEASE Writing is not recommended by hand
Technician ID
Technician
Name
Appointment Date
Appointment Time
Patient ID
Patient Name
192115
Mousa Ali
12 Aug 2017
09:00 am
21018
Omar Hassan
192115
Mousa Ali
15 Aug 2017
02:00 pm
21020
Kamal Ali
192119
Abdullah Haji
01 Sep 2017
10:00 am
21119
Khalid Ahmed
192119
Abdullah Haji
05 Sep 2017
01:00 pm
21018
Omar Hassan
Explanation / Answer
Please find my answer:
Candidate key of the table is, (Technician_id, app_date, app_time)
Functional Dependencies of the table are,
Technician_id, app_date, app_time => patient_id, patient_name
Technician_id => technician_name
patient_id => patient_name
Prime attribute : The attributes which are the members of candidate key.
Non-prime attribute : The attributes which are not the members of candidate key.
Prime attributes : Technician_id, app_date, app_time
1st Normal Form
The table is already in 1st normal form because all the attributes in the relation have atomic values only.
2nd Normal Form
Rule : the table must be in 1st normal form and there should not be any partial dependency between attributes. That is every non-prime attribute must be fully dependent on candidate key.
In the given functional dependency, a partial dependency is there.
Technician_id => technician_name
Candidate key is (Technician_id, app_date, app_time). But in this dependency technician_name is partially dependent on the candidate key. It is not acceptable in 2nd normal form. Therefore convert this into 2ndnormal form. So you will get the following table,
Technician_id
technician_name
Now candidate key of this table is Technician_id. Since the dependency Technician_id =>technician_name is satisfied in 2nd normal form. technician_name is fully dependent on candidate key.
These are the tables in 2nd normal form,
Technician_id
app_date
app_time
patient_id
patient_name
FD of this table is ====
Technician_id, app_date, app_time => patient_id, patient_name
patient_id => patient_name
Technician_id
technician_name
FD of this table is ====== Technician_id => technician_name
3rd Normal Form
Rule : the table must be 2nd normal form and for a non-trivial functional dependency X -> Y , it should satisfy either of the following two conditions,
patient_id
patient_name
Candidate key of this table is patient_id and FD of this table is patient_id => patient_name
These are the tables in 2nd normal form,
Technician_id
app_date
app_time
patient_id
patient_name
Technician_id
technician_name
patient_id
patient_name
BCNF
Rule : For any non-trivial functional dependency, X ? A, X must be a super-key.
Please rate my answer if it helped you!!
Technician_id
technician_name
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.