Scribd
Upload
4 views4 pages

Database Normalization Steps Explained

The document outlines the normalization process of a patient checkup table through 1NF, 2NF, and 3NF. It identifies that the original table is in 1NF, but has partial dependencies that necessitate conversion to 2NF, resulting in three separate tables: PATIENT, DOCTOR, and CHECKUP. The final tables are confirmed to be in 3NF with no transitive dependencies present.

Uploaded by

humna8895
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views4 pages

Database Normalization Steps Explained

The document outlines the normalization process of a patient checkup table through 1NF, 2NF, and 3NF. It identifies that the original table is in 1NF, but has partial dependencies that necessitate conversion to 2NF, resulting in three separate tables: PATIENT, DOCTOR, and CHECKUP. The final tables are confirmed to be in 3NF with no transitive dependencies present.

Uploaded by

humna8895
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Given Table (Unnormalized / Original Table)

PatientID PatientName Age DoctorID DoctorName Dept CheckupDate Fee

P01 Ali 32 D10 Dr. Khan Cardiology 12-05-2024 1500

P01 Ali 32 D11 Dr. Sara Neurology 15-05-2024 1800

P02 Ahmed 29 D10 Dr. Khan Cardiology 16-05-2024 1500

P03 Noor 41 D12 Dr. Bilal Orthopedic 17-05-2024 2000

Step 1: First Normal Form (1NF)


Rule of 1NF

• Each field must contain atomic (single) values


• No repeating groups

✅ Check

• All values are atomic


• No multi-valued attributes

Table is already in 1NF

1NF Table
PatientID PatientName Age DoctorID DoctorName Dept CheckupDate Fee

Step 2: Identify Primary Key

A patient can visit multiple doctors on different dates.

✔ Composite Primary Key:

(PatientID, DoctorID, CheckupDate)


Step 3: Second Normal Form (2NF)
Rule of 2NF

• Table must be in 1NF


• No partial dependency
(Non-key attributes should depend on the whole primary key, not part of it)

Partial Dependencies Found


Attribute Depends On

PatientName, Age PatientID

DoctorName, Dept DoctorID

Fee DoctorID

CheckupDate (Part of PK)

Partial dependency exists → Not in 2NF

Convert to 2NF
Table 1: PATIENT
PatientID (PK) PatientName Age

P01 Ali 32

P02 Ahmed 29

P03 Noor 41

Table 2: DOCTOR
DoctorID (PK) DoctorName Dept Fee

D10 Dr. Khan Cardiology 1500

D11 Dr. Sara Neurology 1800


DoctorID (PK) DoctorName Dept Fee

D12 Dr. Bilal Orthopedic 2000

Table 3: CHECKUP
PatientID (FK) DoctorID (FK) CheckupDate

P01 D10 12-05-2024

P01 D11 15-05-2024

P02 D10 16-05-2024

P03 D12 17-05-2024

Now in 2NF

Step 4: Third Normal Form (3NF)


Rule of 3NF

• Table must be in 2NF


• No transitive dependency
(Non-key attribute should not depend on another non-key attribute)

Check for Transitive Dependency

• In DOCTOR table:
o DoctorID → DoctorName, Dept, Fee
• No non-key depends on another non-key

Already in 3NF

Final Tables in 3NF


✅ PATIENT

(PatientID, PatientName, Age)


✅ DOCTOR

(DoctorID, DoctorName, Dept, Fee)

✅ CHECKUP

(PatientID, DoctorID, CheckupDate)

Final Answer Summary (Exam Ready)

• Given table is already in 1NF


• Partial dependencies exist → converted to 2NF
• No transitive dependencies → tables are in 3NF
• Final normalized tables: PATIENT, DOCTOR, CHECKUP

You might also like