Normalize the given table below and functional dependencies into 1NF, 2NF, 3NF a

Question

Normalize the given table below and functional dependencies into 1NF, 2NF, 3NF and BCNF normal forms

Technician

FD1= Technician_id, app_date, app_time => patient_id, patient_name

FD2 = Technician_id => technician_name

FD3 = patient_id => patient_name

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

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 2nd normal 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.

Technician_id

technician_name

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.