SCRIBD TECHNICAL REFERENCE & FIELD MANUAL ENTERPRISE ENGINEERING SERIES
Relational Database Design, Normalization &
SQL Architecture
Database Administration Manual • Relational Modeling & Schema Optimization
Author: Lead Database Architect | Category: Database Engineering | Target Length: 5,000+ Chars
1. Principles of Relational Theory & Database Integrity
Relational Database Management Systems (RDBMS) implement Edgar F. Codd's relational model. Modern database
architecture relies on strict mathematical constraints to guarantee data consistency, eliminate redundancy, and support
concurrent transactional operations.
• Entity Integrity: Every table must have a designated Primary Key (PK) that is unique and non-null.
• Referential Integrity: Foreign Keys (FK) must accurately reference existing primary key values in target parent tables,
maintaining strict parent-child relationships.
• Domain Integrity: Columns must adhere to predefined data types, value constraints, and permission definitions.
• Data Anomalies Solved by Normalization:
1. Insertion Anomaly: Inability to insert data without inserting attributes of an unrelated entity.
2. Update Anomaly: Inconsistent data state caused by updating redundant values in some rows but not all.
3. Deletion Anomaly: Unintended loss of crucial data when deleting a row containing combined entities.
2. Database Normalization Stages (1NF through BCNF & 5NF)
Database normalization systematically restructures schemas into formal normal forms to remove functional dependencies
and operational anomalies.
Normal Form Core Requirement Functional Dependency Rule Anomaly Removed
1NF Atomic Values Eliminate multi-valued attributes and repeating Unstructured collections in
groups. columns.
2NF Full Functional Must be in 1NF. Non-key columns must depend on Partial dependencies on
Dependency FULL Primary Key. composite keys.
3NF Transitive Dependency Must be in 2NF. Non-key columns must NOT depend Transitive dependencies (X -> Y,
Removal on other non-key columns. Y -> Z).
BCNF Strict Determinant Rule Strict 3NF variant. For every dependency X -> Y, X Overlapping candidate key
MUST be a Super Key. anomalies.
4NF Multivalued Dependencies Must be in BCNF. Eliminates independent Multivalued dependencies
multi-valued facts. (MVDs).
5NF Join Dependency Eliminates cases where table cannot be reconstructed Cyclic join redundancies.
Uniformity from smaller projections.
CONFIDENTIAL & PROPRIETARY — FOR EDUCATIONAL & REFERENCE USE ONLY Page 1 of 3
SCRIBD TECHNICAL REFERENCE & FIELD MANUAL ENTERPRISE ENGINEERING SERIES
3. Comprehensive Refactoring Case Study: Un-Normalized to 3NF
Consider an un-normalized flat order tracking file containing repeating user data, items, and supplier addresses. The
original schema leads to extreme redundancy and deletion anomalies.
-- UN-NORMALIZED LEGACY TABLE (FLAT DATA)
CREATE TABLE LegacyOrderDump (
order_id INT,
customer_name VARCHAR(100),
customer_email VARCHAR(100),
product_ids VARCHAR(255), -- Violates 1NF (Non-atomic array string)
product_names VARCHAR(255),
supplier_name VARCHAR(100),
supplier_city VARCHAR(100) -- Violates 3NF (Transitive dependency on supplier)
);
-- REFACTORED 3NF / BCNF RELATIONAL SCHEMA
CREATE TABLE Customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
customer_email VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE Suppliers (
supplier_id INT PRIMARY KEY AUTO_INCREMENT,
supplier_name VARCHAR(100) NOT NULL,
supplier_city VARCHAR(100) NOT NULL
);
CREATE TABLE Products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
supplier_id INT,
FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
CREATE TABLE OrderItems (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
4. OLTP Normalization vs. OLAP Denormalization Comparison
CONFIDENTIAL & PROPRIETARY — FOR EDUCATIONAL & REFERENCE USE ONLY Page 2 of 3
SCRIBD TECHNICAL REFERENCE & FIELD MANUAL ENTERPRISE ENGINEERING SERIES
Criterion Normalized Schemas (OLTP) Denormalized Schemas (OLAP / Data
Warehouse)
Primary Objective Transaction integrity, rapid writes, zero duplicate data. High-speed analytical queries, aggregations,
reporting.
Schema Structure Highly fragmented normalized tables (3NF / BCNF). Star Schema or Snowflake Schema (Fact &
Dimension tables).
Query Performance Slower complex reads requiring multi-table JOINs. Ultra-fast reads with simple pre-joined dimension
tables.
Write Performance Optimal. Updates affect single rows in single tables. Slower. Updates require updating duplicated historical
rows.
Storage Efficiency Minimal storage footprint due to zero redundancy. Higher storage footprint due to intentional data
duplication.
5. Indexing Mechanics & ACID Transaction Guarantee
• B-Tree Indexing Structure: B-Trees keep indexed key columns in balanced search trees, reducing lookup time from full
table scan O(n) to index lookup O(log n). Clustered indexes dictate physical table storage order.
• ACID Guarantees:
• Atomicity: Transactions complete entirely or roll back completely via WAL (Write-Ahead Logging).
• Consistency: Transactions transition the database only between valid states matching constraints.
• Isolation: Prevents dirty reads, non-repeatable reads, and phantom reads using isolation levels (Read Committed,
Repeatable Read, Serializable).
• Durability: Committed transactions are permanently saved in persistent storage.
CONFIDENTIAL & PROPRIETARY — FOR EDUCATIONAL & REFERENCE USE ONLY Page 3 of 3