Chapter 4: Database Design and Normalization

4.1 Entity-Relationship Diagrams (ERD)

Entity-Relationship Diagrams are visual representations of the relationships between different entities in a database.

4.1.1 Components of an ERD

1. Entities: Represented by rectangles
2. Attributes: Represented by ovals
3. Relationships: Represented by diamond shapes
4. Cardinality: Represented by symbols on the relationship lines

4.1.2 Types of Relationships

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

4.1.3 Creating an ERD

Example: Let's create a simple ERD for a library system

[Textual representation of an ERD for a library system with Books, Authors, and Borrowers entities]

4.2 Normalization Forms

Normalization is the process of organizing data to minimize redundancy and dependency.

4.2.1 First Normal Form (1NF)

• Each table cell should contain a single value
• Each record needs to be unique

Example: Before 1NF:

OrderID	Products
1	Apple, Orange
2	Banana, Grape

After 1NF:

OrderID	Product
1	Apple
1	Orange
2	Banana
2	Grape

4.2.2 Second Normal Form (2NF)

• Must be in 1NF
• All non-key attributes must depend on the entire primary key

Example: Before 2NF:

OrderID	Product	Customer	CustomerAddress
1	Apple	John	123 Main St
1	Orange	John	123 Main St

After 2NF: Table: Orders

OrderID	Customer
1	John

Table: OrderDetails

OrderID	Product
1	Apple
1	Orange

Table: Customers

Customer	CustomerAddress
John	123 Main St

4.2.3 Third Normal Form (3NF)

• Must be in 2NF
• No transitive dependencies (non-key attributes depending on other non-key attributes)

Example: Before 3NF:

EmployeeID	Department	DepartmentHead
1	Sales	John Doe
2	Sales	John Doe

After 3NF: Table: Employees

EmployeeID	Department
1	Sales
2	Sales

Table: Departments

Department	DepartmentHead
Sales	John Doe

4.3 Denormalization for Performance

While normalization reduces redundancy, it can sometimes lead to performance issues due to the need for multiple joins. Denormalization intentionally introduces redundancy for performance gains.

4.3.1 When to Denormalize

• When read performance is crucial
• When the data is relatively static
• When joins between frequently accessed tables are expensive

4.3.2 Denormalization Techniques

1. Redundant Columns
2. Redundant Tables
3. Derived Tables

Example: Normalized Tables: Table: Orders

OrderID	CustomerID	OrderDate
1	101	2023-05-15

Table: OrderDetails

OrderID	ProductID	Quantity
1	201	2
1	202	1

Denormalized Table: Table: OrdersSummary

OrderID	CustomerID	OrderDate	TotalItems	TotalAmount
1	101	2023-05-15	3	150.00

4.4 Real-life Example: Designing a Database for a Hospital Management System

Let's design a database for a hospital management system, applying the concepts we've learned.

Step 1: Identify Entities and Relationships

Main Entities:

1. Patients
2. Doctors
3. Appointments
4. Departments
5. Medications

Step 2: Create an ERD

[Textual representation of an ERD for the hospital management system]

Step 3: Normalize the Database

Example of normalization:

1NF: Ensure all attributes have atomic values 2NF: Separate patient information from appointment details 3NF: Move department information to a separate table

Step 4: Consider Denormalization

For performance reasons, we might denormalize by adding a LastAppointmentDate to the Patients table, even though it's redundant information.

Step 5: Final Database Schema

CREATE TABLE Patients (
    PatientID INT PRIMARY KEY,
    Name VARCHAR(100),
    DateOfBirth DATE,
    ContactNumber VARCHAR(15),
    LastAppointmentDate DATE
);

CREATE TABLE Doctors (
    DoctorID INT PRIMARY KEY,
    Name VARCHAR(100),
    Specialization VARCHAR(50),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50),
    HOD INT  -- Head of Department
);

CREATE TABLE Appointments (
    AppointmentID INT PRIMARY KEY,
    PatientID INT,
    DoctorID INT,
    AppointmentDate DATETIME,
    Reason VARCHAR(200),
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);

CREATE TABLE Medications (
    MedicationID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description TEXT,
    DosageForm VARCHAR(50)
);

CREATE TABLE Prescriptions (
    PrescriptionID INT PRIMARY KEY,
    AppointmentID INT,
    MedicationID INT,
    Dosage VARCHAR(50),
    Frequency VARCHAR(50),
    FOREIGN KEY (AppointmentID) REFERENCES Appointments(AppointmentID),
    FOREIGN KEY (MedicationID) REFERENCES Medications(MedicationID)
);