1.3 Database Normalization & Design Fundamentals

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

1.3 Database Normalization & Design Fundamentals

Key Takeaways Explain This Topic AI

Database normalization is a formal mathematical process developed by E.F. Codd to eliminate data redundancy and prevent insertion, update, and deletion anomalies.

First Normal Form (1NF) requires atomic (indivisible) attribute values, no repeating groups of columns, and a defined primary key for unique row identification.

Second Normal Form (2NF) requires 1NF compliance plus the complete elimination of partial functional dependencies (all non-key attributes must depend on the entire composite primary key). Tables with single-column primary keys in 1NF are automatically in 2NF.

Third Normal Form (3NF) requires 2NF compliance plus the elimination of transitive functional dependencies (no non-key attribute may determine another non-key attribute).

Denormalization deliberately introduces controlled redundancy into analytical/OLAP schemas to minimize expensive multi-table JOINs, balancing read query performance against storage overhead and write/DML latency.

Last updated: August 2026

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 |
+---------------------------------------------------------------------------------------------------------+

Anomaly Type Problem Description Concrete Example in Above Table

Insertion Anomaly Inability to record a legitimate business fact because unrelated required data is not yet available. We create a new department ( D30 , Research , Manager 110 ), but cannot insert it into the database because no employees or projects have been assigned yet, and the composite primary key requires an EMP_ID and PROJ_ID .

Update (Modification) Anomaly Changing a single real-world fact requires updating multiple redundant rows; if any row is missed, data becomes inconsistent. If Department D10 changes its name to "Software Engineering" , we must update multiple rows. If the update modifies Alice's first row but fails on Carol's row, the database holds contradictory truths about D10 .

Deletion Anomaly Deleting a specific record to remove one fact inadvertently destroys completely unrelated, valuable business facts. If Bob Chen leaves the company and we delete his row, we accidentally erase the only record in the entire database showing that Department D20 exists and that its manager is 108 .

Functional Dependency: The Engine of Normalization

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

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

Here, X X 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:

Atomic Values: Every attribute contains only atomic (indivisible, single scalar) values—no arrays, nested records, or comma-separated lists.

No Repeating Groups: There are no repeating sets of similar columns (e.g., COURSE_1 , COURSE_2 , COURSE_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:

It is already in 1NF .

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 → Y X \rightarrow Y X → Y and Y → Z Y \rightarrow Z Y → Z , where X X X is the PK and Y Y 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:

It is already in 2NF .

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

Normal Form Prerequisite Violation Removed Key Identification Phrase
1NF None (UNF) Non-atomic attributes & repeating groups "Atomic attributes & unique PK"
2NF 1NF Partial dependencies on composite keys "Depends on the whole key"
3NF 2NF Transitive dependencies (non-key determining non-key) "Depends on nothing but the key"
BCNF 3NF Any determinant that is not a candidate key "Every determinant is a candidate key"
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:

Architectural Attribute Normalized (OLTP) Denormalized (OLAP / Data Warehouse)

Primary Workload High-volume concurrent DML ( INSERT , UPDATE , DELETE ) High-volume analytical reads ( SELECT , Aggregations)

Design Target 3NF / BCNF Star Schema / Snowflake Schema / Flat Datamarts
JOIN Overhead High (frequent joins required) Low (pre-joined summary tables)
DML Speed Very Fast (updates single table row) Slower (updates multiple redundant rows)
Storage Footprint Minimal (no duplicate columns) Higher (duplicate columns & pre-computed aggregates)
Data Integrity Risk Zero (enforced by schema constraints) High (requires application/ETL sync mechanisms)

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:

Materialized Views: Pre-compute and store complex multi-table joins on disk with automated synchronization ( CREATE MATERIALIZED VIEW ... REFRESH FAST ON COMMIT ).

Virtual Columns: Store computed expressions in the data dictionary without physical column duplication.

Result Cache: Cache query result sets in the SGA memory pool ( /*+ RESULT_CACHE */ ).