Lecture 4: ERD & DB Design

1. Database Design and Its Goals

Database Design is the rigorous architectural process of structuring data both logically and physically. Poor design leads to chaotic systems, while excellent design guarantees:

• The complete elimination of data redundancy.
• Absolute data integrity.
• Maximized query performance and system scalability.
• Clarity, simplicity, and ease of future maintenance.

2. The 7-Step Database Design Process

1. Requirements Analysis: Deeply understanding the business logic and what the system must accomplish.
2. Identify Entities: Pinpointing the major real-world objects the database must track (e.g., Students, Courses, Orders).
3. Define Attributes: Outlining the specific properties belonging to each entity (e.g., StudentID, Name, Email).
4. Define Primary Keys: Assigning a completely unique, non-null, and unchangeable identifier to every single record.
5. Define Relationships: Mapping how entities interact with one another (1:1, 1:M, M:N).
6. Apply Normalization: Passing the tables through mathematical Normal Forms (1NF, 2NF, 3NF) to mathematically prove the absence of redundancy.
7. Add Constraints and Indexes: Applying system rules (NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY) and building performance indexes.

3. Normalization and Database Anomalies

Unnormalized databases suffer from severe operational flaws known as anomalies:

• Insertion Anomaly: An inability to insert data into the database because it requires the presence of other unrelated data (e.g., not being able to record a new department's location until an employee is assigned to it).
• Update Anomaly: Occurs when redundant data is present. If an address changes, you must update multiple rows. Missing even one row corrupts the database.
• Deletion Anomaly: The catastrophic loss of important data when a record is deleted (e.g., firing the only employee in a department accidentally deletes the entire department's record).

4. The Normal Forms Explained

• First Normal Form (1NF): Mandates that every single cell must hold exactly one atomic value. There can be absolutely no repeating groups or arrays embedded within a column.
• Second Normal Form (2NF): The table must fulfill 1NF. Furthermore, it must contain no Partial Dependencies. This means if a table relies on a Composite Primary Key (multiple columns acting as the key), every non-key attribute must depend on the entire key, not just a fraction of it.
• Third Normal Form (3NF): The table must fulfill 2NF. Furthermore, it must contain no Transitive Dependencies. A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than depending directly on the Primary Key.

5. Logical vs. Physical Design

• Logical Design: The conceptual blueprint. It involves drawing the Entity Relationship Diagram (ERD), defining entities, mapping relationships, and outlining business rules without writing code.
• Physical Design: The technical implementation. It involves writing the SQL syntax, creating physical tables, specifying exact byte-sized Data Types, and deploying rigid database constraints.