1.3 Database Normalization & Design Fundamentals

1.3 Database Normalization & Design Fundamentals

Designing a robust relational database requires more than just creating tables that hold data—it requires structuring relations so that facts are recorded exactly once. Database Normalization is a systematic, mathematical refinement technique introduced by Dr. Edgar F. Codd that evaluates functional dependencies between attributes to eliminate redundancy and prevent data corruption during routine operations.

For the Oracle 1Z0-071 examination, you must be capable of identifying normal forms (1NF, 2NF, 3NF, and BCNF), recognizing the three classic modification anomalies, executing step-by-step table decompositions, and articulating the real-world trade-offs between normalized transactional schemas (OLTP) and denormalized analytical reporting stores (OLAP).

---

The Purpose of Normalization

The primary objective of normalization is to ensure that every non-key attribute represents a direct, standalone fact about the primary key.

When a schema is improperly normalized, data redundancy creeps in. Redundancy is not merely a waste of storage; it causes severe operational defects known as modification anomalies.

The Three Modification Anomalies

To see why normalization is essential, examine this unnormalized employee/project spreadsheet table:

+---------------------------------------------------------------------------------------------------------+
| EMP_ID | EMP_NAME  | DEPT_ID | DEPT_NAME   | DEPT_MGR_ID | PROJ_ID | PROJ_NAME    | HOURS_WORKED        |
| ------ | --------- | ------- | ----------- | ----------- | ------- | ------------ | ------------------- |
| 101    | Alice Roy | D10     | Engineering | 105         | P01     | Cloud Portal | 20                  |
| 101    | Alice Roy | D10     | Engineering | 105         | P02     | Mobile App   | 15                  |
| 102    | Bob Chen  | D20     | Marketing   | 108         | P01     | Cloud Portal | 35                  |
| 103    | Carol Dan | D10     | Engineering | 105         | P03     | Data Pipeline| 40                  |
+---------------------------------------------------------------------------------------------------------+

---

Functional Dependency: The Engine of Normalization

Normalization is based on the concept of Functional Dependency (FD):

• Given a relation $R$, attribute $Y$ is functionally dependent on attribute $X$ (written $X \rightarrow Y$) if and only if each value of $X$ is associated with exactly one value of $Y$ at any given moment.
• Here, $X$ is called the determinant.

Example: In an organization, EMPLOYEE_ID $\rightarrow$ EMAIL means that given an employee ID, there is one and only one email address associated with it.

---

Step-by-Step Normalization Walkthrough: UNF to 3NF

Let us trace a worked normalization example from an unnormalized table to Third Normal Form.

Step 0: Unnormalized Form (UNF)

A table is in Unnormalized Form (UNF) if it contains repeating groups or non-atomic attributes (multiple values in a single cell).

UNNORMALIZED TABLE: STUDENT_COURSE_REGISTRATION
+---------+-------------+-----------------------------------+-----------------------------------+
| STUD_ID | STUD_NAME   | ADVISOR_ID / ADVISOR_OFFICE       | ENROLLED_COURSES (Repeating Group)|
+---------+-------------+-----------------------------------+-----------------------------------+
| S100    | Jane Miller | A501 (Bldg A, Rm 101)             | [CS101, Intro CS, 4 cr, Grade: A] |
|         |             |                                   | [MA201, Calc II,  4 cr, Grade: B] |
| S200    | Tom Davis   | A502 (Bldg B, Rm 204)             | [CS101, Intro CS, 4 cr, Grade: A] |
+---------+-------------+-----------------------------------+-----------------------------------+

---

Step 1: First Normal Form (1NF)

Definition & Rules:

A relation is in First Normal Form (1NF) if and only if:

1. Atomic Values: Every attribute contains only atomic (indivisible, single scalar) values—no arrays, nested records, or comma-separated lists.
2. No Repeating Groups: There are no repeating sets of similar columns (e.g., COURSE_1, COURSE_2, COURSE_3).
3. Unique Rows: A primary key is identified so that each row is unique.

Transformation to 1NF:

Flatten the repeating groups so each course enrollment occupies its own discrete row, atomicize the advisor field, and establish the composite primary key: (STUD_ID, COURSE_ID).

TABLE: 1NF_STUDENT_ENROLLMENT
PK: (STUD_ID, COURSE_ID)
+---------+-----------+------------+----------------+-----------+--------------+--------+-------+
| STUD_ID | STUD_NAME | ADVISOR_ID | ADVISOR_OFFICE | COURSE_ID | COURSE_TITLE | CREDIT | GRADE |
+---------+-----------+------------+----------------+-----------+--------------+--------+-------+
| S100    | J. Miller | A501       | Bldg A-101     | CS101     | Intro CS     | 4      | A     |
| S100    | J. Miller | A501       | Bldg A-101     | MA201     | Calc II      | 4      | B     |
| S200    | T. Davis  | A502       | Bldg B-204     | CS101     | Intro CS     | 4      | A     |
+---------+-----------+------------+----------------+-----------+--------------+--------+-------+

Lingering Flaw in 1NF:

Notice the functional dependencies in this table:

• {STUD_ID, COURSE_ID} $\rightarrow$ GRADE (Full Key Dependency)
• STUD_ID $\rightarrow$ STUD_NAME, ADVISOR_ID, ADVISOR_OFFICE (Partial Dependency!)
• COURSE_ID $\rightarrow$ COURSE_TITLE, CREDIT (Partial Dependency!)

A partial dependency exists when a non-key attribute is determined by only a part of a composite primary key rather than the entire key.

---

Step 2: Second Normal Form (2NF)

Definition & Rules:

A relation is in Second Normal Form (2NF) if and only if:

1. It is already in 1NF.
2. No Partial Dependencies: Every non-prime (non-key) attribute is fully functionally dependent on the entire primary key.

Exam Rule of Thumb: If a 1NF table has a single-column primary key, it is automatically in 2NF because a single-attribute key has no sub-components that could create partial dependencies!

Transformation to 2NF:

Remove partially dependent attributes into separate tables where the determinant becomes the sole primary key:

1. TABLE: STUDENTS (Removes partial dependency on STUD_ID)
   PK: STUD_ID
   +---------+-----------+------------+----------------+
   | STUD_ID | STUD_NAME | ADVISOR_ID | ADVISOR_OFFICE |
   +---------+-----------+------------+----------------+
   | S100    | J. Miller | A501       | Bldg A-101     |
   | S200    | T. Davis  | A502       | Bldg B-204     |
   +---------+-----------+------------+----------------+

2. TABLE: COURSES (Removes partial dependency on COURSE_ID)
   PK: COURSE_ID
   +-----------+--------------+--------+
   | COURSE_ID | COURSE_TITLE | CREDIT |
   +-----------+--------------+--------+
   | CS101     | Intro CS     | 4      |
   | MA201     | Calc II      | 4      |
   +-----------+--------------+--------+

3. TABLE: ENROLLMENTS (Retains full dependency on composite key)
   PK: (STUD_ID, COURSE_ID)
   +---------+-----------+-------+
   | STUD_ID | COURSE_ID | GRADE |
   +---------+-----------+-------+
   | S100    | CS101     | A     |
   | S100    | MA201     | B     |
   | S200    | CS101     | A     |
   +---------+-----------+-------+

Lingering Flaw in 2NF:

Examine the STUDENTS table:

• STUD_ID (PK) $\rightarrow$ ADVISOR_ID (non-key)
• ADVISOR_ID (non-key) $\rightarrow$ ADVISOR_OFFICE (non-key)

Therefore, STUD_ID $\rightarrow$ ADVISOR_OFFICE is an indirect dependency through ADVISOR_ID. This is a transitive dependency ($X \rightarrow Y$ and $Y \rightarrow Z$, where $X$ is the PK and $Y$ is not a candidate key).

---

Step 3: Third Normal Form (3NF)

Definition & Rules:

A relation is in Third Normal Form (3NF) if and only if:

1. It is already in 2NF.
2. No Transitive Dependencies: No non-prime attribute is transitively dependent on the primary key (no non-key attribute determines another non-key attribute).

Classic Normalization Axiom: "Every non-key attribute must depend on the key, the whole key, and nothing but the key, so help me Codd."

• The Key = 1NF (must have a primary key identifying atomic values)
• The Whole Key = 2NF (no partial dependencies on part of a composite key)
• Nothing But The Key = 3NF (no transitive dependencies on non-key columns)

Transformation to 3NF:

Remove the transitively dependent attribute (ADVISOR_OFFICE) and its determinant (ADVISOR_ID) into an independent ADVISORS table:

1. TABLE: ADVISORS (Eliminates transitive dependency)
   PK: ADVISOR_ID
   +------------+----------------+
   | ADVISOR_ID | ADVISOR_OFFICE |
   +------------+----------------+
   | A501       | Bldg A-101     |
   | A502       | Bldg B-204     |
   +------------+----------------+

2. TABLE: STUDENTS (3NF Compliant)
   PK: STUD_ID, FK: ADVISOR_ID referencing ADVISORS
   +---------+-----------+------------+
   | STUD_ID | STUD_NAME | ADVISOR_ID |
   +---------+-----------+------------+
   | S100    | J. Miller | A501       |
   | S200    | T. Davis  | A502       |
   +---------+-----------+------------+

3. TABLE: COURSES (3NF Compliant - PK: COURSE_ID)
4. TABLE: ENROLLMENTS (3NF Compliant - PK: STUD_ID, COURSE_ID)

All four resulting tables are now in 3NF: completely free of insertion, update, and deletion anomalies!

---

Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF created to handle rare edge cases involving relations with multiple, overlapping candidate keys.

• Rule: A relation is in BCNF if and only if every determinant is a candidate key.
• If a non-prime attribute determines any part of a candidate key, the relation is in 3NF but not in BCNF.
• In virtually all practical business systems without overlapping composite candidate keys, a table in 3NF is automatically in BCNF.

---

Normalization Summary Reference Matrix

---

Denormalization & Engineering Trade-Offs

While normalization optimizes data integrity and write performance, fully normalized schemas require queries to perform multiple table joins (JOIN). In read-heavy analytical environments, joining dozens of tables across millions of rows creates significant CPU and I/O overhead.

Denormalization is the intentional, controlled introduction of redundancy into a normalized database design to improve read query performance.

+------------------------------------+    +------------------------------------+
|    NORMALIZED DESIGN (3NF / OLTP)  |    |     DENORMALIZED DESIGN (OLAP)     |
|                                    |    |                                    |
| - Zero redundancy                  |    | - Controlled redundancy            |
| - Fast, reliable INSERT/UPDATE     |    | - Faster SELECT queries (few JOINs)|
| - Queries require multiple JOINs   |    | - Slower DML / Risk of anomalies   |
| - Ideal for Banking, E-Commerce    |    | - Ideal for Data Warehouses/BI     |
+------------------------------------+    +------------------------------------+

OLTP vs. OLAP Comparison Matrix:

Oracle Solutions for Controlled Denormalization:

Rather than physically corrupting your base 3NF transactional tables, Oracle SQL provides advanced architectural features to gain denormalization performance safely:

1. Materialized Views: Pre-compute and store complex multi-table joins on disk with automated synchronization (CREATE MATERIALIZED VIEW ... REFRESH FAST ON COMMIT).
2. Virtual Columns: Store computed expressions in the data dictionary without physical column duplication.
3. Result Cache: Cache query result sets in the SGA memory pool (/*+ RESULT_CACHE */).