Decomposing an unnormalized Patient_Visits table (containing Patient, Doctor, Clinic, Diagnosis, and Billing details) into BCNF tables (Patients, Doctors, Clinics, Visits, Invoices). This ensures changing a clinic address occurs in exactly one row while preserving Lossless Join decomposition for audit compliance.
-- Decomposing an unnormalized visit record into 3NF / BCNF normalized tables -- 1. Patients Entity (Primary Key: patient_id) CREATE TABLE patients ( patient_id INT PRIMARY KEY, patient_name VARCHAR(100) NOT NULL, dob DATE NOT NULL, phone VARCHAR(20) NOT NULL ); -- 2. Clinics Entity (Primary Key: clinic_id) CREATE TABLE clinics ( clinic_id INT PRIMARY KEY, clinic_name VARCHAR(100) NOT NULL, address VARCHAR(200) NOT NULL, city VARCHAR(50) NOT NULL ); -- 3. Doctors Entity (Primary Key: doctor_id, FK to clinic) CREATE TABLE doctors ( doctor_id INT PRIMARY KEY, doctor_name VARCHAR(100) NOT NULL, specialization VARCHAR(50) NOT NULL, clinic_id INT NOT NULL, FOREIGN KEY (clinic_id) REFERENCES clinics(clinic_id) ); -- 4. Visits Relation (Decomposed BCNF Junction) CREATE TABLE visits ( visit_id BIGINT PRIMARY KEY, patient_id INT NOT NULL, doctor_id INT NOT NULL, visit_timestamp TIMESTAMPTZ NOT NULL, diagnosis VARCHAR(255), FOREIGN KEY (patient_id) REFERENCES patients(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) );
Visual representation of control loops, memory layout, and execution flow for Functional Dependencies & Normalization (1NF to BCNF).
A Functional Dependency X -> Y holds on relation R if whenever two tuples agree on X, they must agree on Y. Armstrong’s Axioms: (1) Reflexivity: If Y ⊆ X, then X -> Y. (2) Augmentation: If X -> Y, then XZ -> YZ. (3) Transitivity: If X -> Y and Y -> Z, then X -> Z. Secondary rules: Union, Decomposition, Pseudo-transitivity. Attribute Closure (X+): The set of all attributes functionally determined by X given a set of FDs F. If X+ contains all attributes of R, X is a Super Key; if no proper subset of X determines all attributes, X is a Candidate Key.
Prime Attribute: An attribute that is part of ANY candidate key. Non-Prime Attribute: An attribute that does not belong to any candidate key. 1NF: All attribute values must be atomic; no multi-valued arrays or nested tables. 2NF: Relation must be in 1NF, and NO non-prime attribute may be partially functionally dependent on a proper subset of ANY candidate key (Eliminates Partial Dependencies: Proper Subset of Candidate Key -> Non-Prime Attribute). Note: If all candidate keys are single attributes, a 1NF table is automatically in 2NF!
3NF: Must be in 2NF. For every non-trivial FD X -> Y, EITHER (1) X is a Super Key, OR (2) Y is a Prime Attribute (member of a candidate key). 3NF eliminates transitive dependencies (Candidate Key -> Non-Prime -> Non-Prime). BCNF (Boyce-Codd Normal Form): Stricter than 3NF. For EVERY non-trivial FD X -> Y, X MUST strictly be a Super Key (no exception for prime attributes on the right side).
When decomposing relation R into R1 and R2: 1. Lossless Join Property (MANDATORY): R1 ⋈ R2 = R. A decomposition is lossless if and only if R1 ∩ R2 -> R1 OR R1 ∩ R2 -> R2 (the common attribute must be a super key in at least one decomposed relation). 2. Dependency Preservation (DESIRABLE): (F1 ∪ F2)+ = F+. Every FD in original F can be checked within a single decomposed relation without performing a join. Note: Any relation can always be decomposed into 3NF with both Lossless Join AND Dependency Preservation; however, BCNF guarantees Lossless Join but MAY NOT preserve all dependencies.
| Feature / Dimension | Third Normal Form (3NF) | Boyce-Codd Normal Form (BCNF) |
|---|---|---|
| Condition for X -> Y | X is a Super Key OR Y is a Prime Attribute (part of a candidate key) | X MUST strictly be a Super Key (no exceptions permitted) |
| Dependency Preservation | Always achievable alongside Lossless Join decomposition | May NOT be achievable; sometimes achieving BCNF sacrifices dependency preservation |
| Redundancy | Allows minor redundancy when Y is a prime attribute determined by non-superkey X | Completely eliminates all functional dependency-based redundancies |
Detailed answers, interviewer pro tips, key takeaway summaries, and code examples formulated for technical rounds.
✅ Correction: A 2NF violation REQUIRES a Partial Dependency (proper subset of Candidate Key -> Non-Prime). If all candidate keys consist of exactly 1 attribute, no proper subset exists, so the table is automatically in 2NF!
✅ Correction: Decomposition R into R1 and R2 is lossless if and only if (R1 ∩ R2 -> R1) OR (R1 ∩ R2 -> R2) in F+.
Systematic mathematical schema decomposition to eliminate redundancy and anomalies.