Part 2: Database Design Principles

1. The Art of Database Design: Entity-Relationship (ER) Diagrams

What is an ER Diagram?

An Entity-Relationship (ER) Diagram is a visual blueprint of a database’s structure. It defines entities (objects), their attributes (properties), and relationships between entities.

Key Components

1. Entities: Represent real-world objects (e.g., Student, Course, Professor).

2. Attributes: Details describing entities (e.g., StudentID, CourseName).

3. Relationships: How entities interact (e.g., a Student enrolls in a Course).

4. Cardinality: The number of instances one entity relates to another (e.g., one-to-many).

---

Example: University Database ER Diagram

Entities and Attributes:

• Student: StudentID (primary key), Name, Email.

• Course: CourseID, Title, Credits.

• Professor: ProfessorID, Name, Department.

Relationships:

• A Student enrolls in multiple Courses (many-to-many).

• A Professor teaches one or more Courses (one-to-many).

Cardinality Symbols:

• One-to-Many (1:N ): A Professor teaches many Courses, but a Course has one Professor.

• Many-to-Many (M:N ): Students can enroll in multiple Courses, and Courses have multiple Students.

---

2. Normalization: Eliminating Redundancy and Ensuring Data Integrity

Normalization is the process of organizing data to minimize redundancy and dependency. Let’s break it down with examples:

1NF (First Normal Form)

Rule: Eliminate repeating groups and ensure atomicity (each column holds a single value).

Example: Unnormalized Student Table

Problem: The CoursesEnrolled column stores multiple values.

1NF Solution: Split into two tables.

Students Table

Enrollments Table

---

2NF (Second Normal Form)

Rule: Remove partial dependencies (all non-key attributes depend on the entire primary key).

Example: Orders Table

Problem: CustomerName depends on OrderID, not the composite key (OrderID + ProductID).

2NF Solution: Split tables.

Orders Table

OrderDetails Table

(Note: ProductName still violates 3NF—more on that next!)

---

3NF (Third Normal Form)

Rule: Eliminate transitive dependencies (non-key attributes depend only on the primary key).

Problem in OrderDetails: ProductName depends on ProductID, not OrderID.

3NF Solution: Split further.

Products Table

OrderDetails Table

---

BCNF (Boyce-Codd Normal Form)

Rule: Every determinant must be a candidate key.

Example: Course Enrollment

Problem: If a Professor teaches only one Course, Professor depends on Course (not the primary key StudentID + Course).

BCNF Solution: Split into two tables.

Courses Table

Enrollments Table

---

3. From ER Diagrams to Relational Schemas

Let’s translate the University ER Diagram into actual SQL tables:

Step 1: Map Entities to Tables

Students Table

CREATE TABLE Students (  
  StudentID INT PRIMARY KEY,  
  Name VARCHAR(50),  
  Email VARCHAR(100) UNIQUE  
);

Courses Table

CREATE TABLE Courses (  
  CourseID INT PRIMARY KEY,  
  Title VARCHAR(100),  
  Credits INT,  
  ProfessorID INT,  
  FOREIGN KEY (ProfessorID) REFERENCES Professors(ProfessorID)  
);

Step 2: Handle Many-to-Many Relationships

Enrollments Table (Junction Table)

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

---

4. Data Integrity and Constraints

Primary Keys

Uniquely identify each row:

CREATE TABLE Professors (  
  ProfessorID INT PRIMARY KEY,  
  Name VARCHAR(50),  
  Department VARCHAR(50)  
);

Foreign Keys

Enforce relationships between tables:

CREATE TABLE Courses (  
  CourseID INT PRIMARY KEY,  
  Title VARCHAR(100),  
  ProfessorID INT,  
  FOREIGN KEY (ProfessorID) REFERENCES Professors(ProfessorID)  
);

Unique Constraints

Prevent duplicate values in a column:

ALTER TABLE Students  
ADD CONSTRAINT UniqueEmail UNIQUE (Email);

Check Constraints

Ensure data meets specific conditions:

ALTER TABLE Courses  
ADD CONSTRAINT ValidCredits CHECK (Credits BETWEEN 1 AND 5);

---

5. Indexing 101: Speeding Up Queries

What is an Index?

An index is like a book’s index—it helps the database quickly locate data without scanning the entire table.

Example: Without vs. With Index

Slow Query (No Index)

SELECT * FROM Students WHERE LastName = 'Smith';

The database scans all rows.

Fast Query (With Index)

CREATE INDEX idx_lastname ON Students(LastName);  
SELECT * FROM Students WHERE LastName = 'Smith';

The database uses the index to jump directly to ‘Smith’ entries.

When to Use Indexes

• Columns frequently used in WHERE, JOIN, or ORDER BY clauses.

• Large tables (10,000+ rows).

When to Avoid Indexes

• Small tables.

• Columns with frequent write operations (indexes slow down inserts/updates).

---

Conclusion

Designing a robust database requires balancing structure (ER diagrams), efficiency (normalization), and performance (indexes). By mastering these principles, you’ll create databases that scale gracefully and handle real-world complexity.

Next Up: In Part 3: Mastering SQL, we’ll dive into writing advanced queries and optimizing them for speed!

---

FAQs

Q: How many normal forms are there?
A: There are 6+ normal forms, but 3NF or BCNF is sufficient for most applications.

Q: Can I over-normalize a database?
A: Yes! Over-normalization can lead to excessive joins, slowing down queries.

Q: Should every table have a primary key?
A: Ideally, yes. It ensures uniqueness and simplifies relationships.