Data Models: ER & Relational

Before writing a single line of SQL, professional database designers first create a blueprint of the database. This blueprint describes what information will be stored, how different pieces of information are connected, and how the database should behave.

This blueprint is called a Data Model.

---

Learning Objectives

After completing this chapter, you will be able to:

• Explain what a Data Model is and why it’s needed.
• Describe different stages of database design.
• Understand where the ER Model fits into database development.
• Explain why companies spend weeks designing databases before writing SQL.
• Identify the advantages of good database design.
• Prepare for the complete ER Model section ahead.

---

Why Database Design Matters

A poorly designed database creates problems that become more expensive over time:

1. Slow Queries

Poor relationships force the database to scan unnecessary data. Applications become slow.

2. Duplicate Data

Without planning, the same information is stored in multiple places. Student name appears in Fees, Marks, Attendance, and Library tables. A name change requires updating four places.

3. Data Inconsistency

One table stores “Rahul Sharma,” another stores “Rahul S.,” another stores “Rahul Kumar.” Which is correct?

4. Difficult Maintenance

As the application grows, adding new features requires modifying dozens of tables. Good design makes future development much easier.

---

What is a Data Model?

A Data Model is a conceptual representation of how data is organized inside a database.

It defines:

• What data exists
• How data is connected
• Rules governing the data
• Constraints and relationships

---

Types of Data Models

Model	Purpose	Stage
ER Model	Conceptual Design	Early — captures requirements
Relational Model	Logical Design	Mid — converts concepts to tables
Object-Oriented Model	Complex Objects	Specialized use cases
Object-Relational Model	Hybrid systems	Modern databases (PostgreSQL)

The ER Model is the most important for beginners and interviews.

---

Roadmap of Database Design

Requirement Analysis
        ↓
Conceptual Design (ER Model)   ← HIGH-LEVEL BLUEPRINT
        ↓
Logical Design (Relational Tables) ← CONVERT TO TABLES
        ↓
Normalization                  ← REMOVE REDUNDANCY
        ↓
SQL Implementation             ← CREATE TABLES
        ↓
Testing & Deployment

Notice that SQL comes after database design — not before.

---

Real-Life Example: Hospital Management System

Without design, you might create one giant table with all data mixed together. With design:

Entities identified:

• Patient, Doctor, Room, Medicine

Relationships:

• Doctor treats Patient (M:N)
• Patient admitted to Room (N:1)
• Doctor prescribes Medicine (M:N)

Each entity becomes a database table. Each relationship becomes a foreign key or a junction table.

---

How This Section Is Organized

The Data Models section covers everything needed for mastering ER diagrams:

1. ER Model fundamentals (entities, attributes, relationships)
2. Cardinality and participation constraints
3. Mapping ER diagrams to relational tables
4. Extended ER features (generalization, specialization, aggregation)
5. Object-Oriented and Object-Relational models

---

Interview Deep Dive

---

Key Takeaways

• Database design happens before SQL — never skip it.
• A data model is the blueprint of a database.
• The ER Model is the conceptual stage of design.
• The Relational Model converts the blueprint into tables.
• Good design reduces redundancy, improves performance, and makes maintenance easier.
• Bad design discovered in production is 100x more expensive to fix than catching it during design.