Data models: conceptual vs logical vs physical; where ER and relational fit

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What does physical data independence protect, and from what? from Lesson 0.1.3
2. Which is harder: logical or physical data independence? from Lesson 0.1.3
3. What is metadata, and where does a DBMS keep it? from Lesson 0.1.2

1 · The Hook

Before building a house, an architect first draws a sketch the family can understand: "three bedrooms, kitchen next to the dining room". Then come engineering drawings with wall thicknesses. Finally the builders work out which bricks and pipes go where.

Database design works the same way. Different stages need different languages for describing data: one that people can discuss, one that a DBMS can implement, and one for disk details. Each such language is a data model.

2 · Predict First

The college says: "Each student belongs to one department; a department has many students." Which is the better first step: (a) decide immediately how student records will be laid out on disk, or (b) draw a picture of students, departments and the "belongs to" link? Why?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

(b). First capture what the college needs in a form the college staff can check, without any technical detail. Storage layout comes last. This picture is a conceptual model, and the standard picture language for it is the ER model (Phase 1).

Data model

A data model is a set of concepts used to describe the structure of a database: what kinds of things are stored, how they are connected, and what rules hold. Most data models also include basic operations for reading and changing data.

Three kinds of data model

Where ER and relational fit: the design process

1. Requirements collection: talk to users; write down what data and which operations they need.
2. Conceptual design: draw the ER diagram (Phase 1). The college can read and check it.
3. Logical design: convert the ER diagram to a relational schema, a set of tables (Phase 2, Lesson 2.4.x).
4. Refinement: check the tables for redundancy and fix them by normalisation (Phase 4).
5. Physical design: choose file organisations and indexes (Phase 5).

This is also the order of this course. Phases 1 to 5 follow the design process from picture to disk; Phase 3 teaches how to ask questions of the tables, and Phase 6 how many users can use them safely at the same time.

Connection to the three-schema architecture

The conceptual schema of Lesson 0.1.3 is usually written in a representational model (relational tables in practice). The ER diagram is the designer's drawing of it. The internal schema is written in physical-model terms.

4 · Visual

The design process; each step uses a lower-level data model.

5 · Worked Examples

Example 1 Easy

Name the kind of data model: (a) ER model; (b) relational model; (c) "student records stored sorted by roll number with an index on dept".

Solution

(a) conceptual; (b) representational (logical); (c) physical.

Example 2 Medium

Put in design order: normalisation, ER diagram, choosing indexes, relational tables, requirement interviews.

Solution

Requirement interviews → ER diagram → relational tables → normalisation → choosing indexes.

Example 3 GATE

"The ER model is used to specify the logical design implemented directly by the DBMS." True or false?

Solution

False. The ER model is a conceptual model for design and discussion. DBMSs implement representational models such as the relational model; the ER diagram is converted into tables before implementation.

8 · Connect the Dots

Lesson 0.1.3 gave three schema levels; this lesson gave the languages (data models) used to write them, and the design process that runs from an ER sketch to disk. Before drawing ER diagrams we need a small math toolkit; the relational model is built on sets, which come next in Chapter 0.2.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Data models

The Entity–Relationship model is a:

1. (A) physical data model
2. (B) storage format
3. (C) query language
4. (D) conceptual (high-level) data model

Q2 · MCQ · 1 mark(s) — Data models

Which model is implemented directly by most modern DBMSs?

1. (A) Relational model
2. (B) Physical model
3. (C) ER model
4. (D) Flowchart model

Q3 · MCQ · 1 mark(s) — Design process

Which step comes immediately after conceptual design?

1. (A) Normalisation
2. (B) Physical design
3. (C) Logical design (ER to tables)
4. (D) Requirements collection

Q4 · MSQ · 2 mark(s) — Data models

Which belong to the physical level?

1. (A) Choosing an index on roll number
2. (B) Deciding that each student belongs to one department
3. (C) Storing records sorted by roll number
4. (D) Drawing a "belongs to" link between STUDENT and DEPARTMENT

Q5 · NAT · 1 mark(s) — Design process

Number the steps: requirements = 1, conceptual design = 2, … What number is physical design?

Numerical answer: ________

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

data modelconceptual data modelrepresentational (logical) data modelphysical data modelER model (to be taught)relational model (to be taught)database design processnormalisation (to be taught)