Skip to content

5.schema_design.md

Latest commit

 

History

History
323 lines (267 loc) · 8.4 KB

5.schema_design.md

File metadata and controls

323 lines (267 loc) · 8.4 KB

Database Schema Design and Normalization

Database schema design is a crucial skill that involves structuring data efficiently while maintaining data integrity. This lesson covers key concepts of database normalization and relationships.

Database Normalization

Normalization is the process of organizing data to minimize redundancy and dependency. It involves breaking down larger tables into smaller ones and defining relationships between them.

First Normal Form (1NF)

  • Rule: Each table cell should contain a single value, and each record needs to be unique.
  • Purpose: Eliminates repeating groups and ensures atomic values.

Example - Before 1NF:

CustomerOrders Table
CustomerID | CustomerName | Orders
1         | John Smith   | Book, Pen, Pencil
2         | Jane Doe     | Notebook, Eraser

After 1NF:

CustomerOrders Table
CustomerID | CustomerName | OrderItem
1         | John Smith   | Book
1         | John Smith   | Pen
1         | John Smith   | Pencil
2         | Jane Doe     | Notebook
2         | Jane Doe     | Eraser

Second Normal Form (2NF)

  • Rule: Table must be in 1NF and all non-key attributes must depend on the entire primary key.
  • Purpose: Eliminates partial dependencies.

Example - Before 2NF:

OrderDetails Table
OrderID | ProductID | Quantity | ProductName | ProductPrice
1       | P1        | 2        | Laptop      | 1000
1       | P2        | 1        | Mouse       | 20
2       | P1        | 1        | Laptop      | 1000

After 2NF:

OrderDetails Table
OrderID | ProductID | Quantity
1       | P1        | 2
1       | P2        | 1
2       | P1        | 1

Products Table
ProductID | ProductName | ProductPrice
P1        | Laptop      | 1000
P2        | Mouse       | 20

Third Normal Form (3NF)

  • Rule: Table must be in 2NF and all non-key attributes must depend only on the primary key.
  • Purpose: Eliminates transitive dependencies.

Example - Before 3NF:

Employees Table
EmpID | EmpName | DeptID | DeptName | DeptLocation
1     | John    | D1     | IT       | New York
2     | Jane    | D1     | IT       | New York
3     | Bob     | D2     | HR       | London

After 3NF:

Employees Table
EmpID | EmpName | DeptID
1     | John    | D1
2     | Jane    | D1
3     | Bob     | D2

Departments Table
DeptID | DeptName | DeptLocation
D1     | IT       | New York
D2     | HR       | London

Primary and Foreign Keys

Primary Keys

  • Uniquely identifies each record in a table
  • Must contain UNIQUE values and cannot contain NULL values
  • Can be a single column or multiple columns (composite key)
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,  -- Single column primary key
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

CREATE TABLE CourseEnrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)  -- Composite primary key
);

Foreign Keys

  • Creates a link between two tables
  • References the primary key of another table
  • Maintains referential integrity
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Types of Relationships

1. One-to-One (1:1)

  • Each record in Table A relates to exactly one record in Table B
  • Example: Each person has one passport number
Person Table         Passport Table
╔════╗ 1          1 ╔════╗
║ ID ║──────────────║ ID ║
╚════╝              ╚════╝

CREATE TABLE Person (
    PersonID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Passport (
    PassportID INT PRIMARY KEY,
    PersonID INT UNIQUE,  -- UNIQUE constraint ensures one-to-one
    PassportNumber VARCHAR(20),
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);

2. One-to-Many (1:N)

  • One record in Table A can relate to many records in Table B
  • Most common relationship type
  • Example: One department has many employees
Department Table     Employee Table
╔════╗ 1          N ╔════╗
║ ID ║──────────────║ ID ║
╚════╝              ╚════╝

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(100),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

3. Many-to-Many (M:N)

  • Records in Table A can relate to many records in Table B and vice versa
  • Requires a junction/bridge table
  • Example: Students can enroll in many courses, and courses can have many students
Students Table      Enrollments Table       Courses Table
╔════╗ 1          N ╔════╗ N          1 ╔════╗
║ ID ║──────────────║ ID ║──────────────║ ID ║
╚════╝              ╚════╝              ╚════╝

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Practical Tips for Schema Design

  1. Start with Requirements

    • Understand the business needs
    • Identify entities and their relationships
    • Document all data requirements

  2. Apply Normalization Appropriately

    • Don't over-normalize
    • Consider performance implications
    • Balance between normalization and practical needs

  3. Use Proper Naming Conventions

    • Consistent and meaningful table names
    • Clear column names
    • Prefix foreign keys with parent table name

  4. Implement Constraints

    • Use appropriate data types
    • Add CHECK constraints for data validation
    • Implement proper foreign key constraints

Exercise 🔴

-- Create a simple library management system

-- Authors table
CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE
);

-- Books table
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    PublicationYear INT,
    ISBN VARCHAR(13) UNIQUE
);

-- Book Authors (Many-to-Many relationship)
CREATE TABLE BookAuthors (
    BookID INT,
    AuthorID INT,
    PRIMARY KEY (BookID, AuthorID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

-- Members table
CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

-- Loans table (One-to-Many with Books and Members)
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    BookID INT,
    MemberID INT,
    LoanDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);

-- Insert sample data
INSERT INTO Authors VALUES
(1, 'George', 'Orwell', '1903-06-25'),
(2, 'J.K.', 'Rowling', '1965-07-31');

INSERT INTO Books VALUES
(1, '1984', 1949, '9780451524935'),
(2, 'Harry Potter and the Philosopher\'s Stone', 1997, '9780747532699');

INSERT INTO BookAuthors VALUES
(1, 1),  -- 1984 by George Orwell
(2, 2);  -- Harry Potter by J.K. Rowling

INSERT INTO Members VALUES
(1, 'John', 'Doe', 'john@example.com'),
(2, 'Jane', 'Smith', 'jane@example.com');

INSERT INTO Loans VALUES
(1, 1, 1, '2023-01-01', '2023-01-15'),  -- John borrowed 1984
(2, 2, 2, '2023-01-02', '2023-01-16');   -- Jane borrowed Harry Potter

-- Query to show book loans with member and book details
SELECT 
    l.LoanID,
    CONCAT(m.FirstName, ' ', m.LastName) as MemberName,
    b.Title as BookTitle,
    CONCAT(a.FirstName, ' ', a.LastName) as AuthorName,
    l.LoanDate,
    l.ReturnDate
FROM Loans l
JOIN Members m ON l.MemberID = m.MemberID
JOIN Books b ON l.BookID = b.BookID
JOIN BookAuthors ba ON b.BookID = ba.BookID
JOIN Authors a ON ba.AuthorID = a.AuthorID;

This schema demonstrates:

  • One-to-Many relationship (Members to Loans)
  • Many-to-Many relationship (Books to Authors)
  • Proper use of primary and foreign keys
  • Normalization principles
  • Real-world application of database design concepts