Chapter 2

Data Models

7 hours9 marks21 past questionsBack to chapters Open filtered PYQs Previous: Ch 1 Next: Ch 3

Chapter Notes

Block-based notes with markdown, diagrams, code, and math.

Chapter 2 — Data Models

2.1 Data Model Comparison

Model	Structure	Use	Example DBMS
Relational	Tables (relations)	General-purpose OLTP	MySQL, PostgreSQL
ER	Entities + relationships	Design phase	Lucidchart, draw.io
Object-oriented	Objects with methods	CAD/CAM, engineering	db4o
Hierarchical	Tree records	Legacy	IBM IMS
Network	Graph records	Legacy	IDMS
Graph	Nodes + edges	Social, recommendation	Neo4j

2.2 ER Model

Entity Types

Type	Key	Notation	Example
Strong entity	Own primary key	Single rectangle	Student (sid)
Weak entity	Partial key + owner PK	Double rectangle	OrderItem (depends on Order)

Attribute Types

Type	Notation	Example
Simple (atomic)	Ellipse	age
Composite	Ellipse with sub-ellipses	name → {first, last}
Multivalued	Double ellipse	phone_numbers
Derived	Dashed ellipse	age ← birth_date

Key Types

Key	Definition	Example
Super key	Any set of attributes that uniquely identifies a tuple	{sid}, {sid, name}, {sid, name, dept}
Candidate key	Minimal super key — no proper subset is also a super key	{sid}
Primary key	The chosen candidate key (underlined in ER diagram)	sid
Foreign key	References PK of another relation	Enrollment.sid → Student.sid
Partial key	Identifies a weak entity within its owner (dashed underline)	dep_name in Dependent

Cardinality Constraints

Notation	Meaning	ER notation
1:1	One to one	Single line both sides
1:N	One to many	Arrow on the N side
M:N	Many to many	No arrow, plain line

Participation Constraints

Total (double line) — every entity MUST participate in the relationship.
Partial (single line) — some entities may not participate.

2.3 Specialization · Generalization · Aggregation

Specialization — top-down: start with general entity, split into subtypes that inherit all supertype attributes and add their own.

Generalization — bottom-up: observe multiple entity types with common attributes; merge common attributes into a new supertype.

Aggregation — treat a relationship set as an entity so it can participate in another relationship. Used when a relationship needs its own attributes or participates in a higher-level relationship.

2.4 Relational Model Basics

A relation is a table. A tuple is a row. An attribute is a column. The domain is the set of allowed values for an attribute.

Referential Integrity

A foreign key value in relation R must either:

Match an existing primary key value in the referenced relation S, OR
Be NULL (if allowed).

On violation, the DBMS enforces a rule:

Rule	Effect
RESTRICT (NO ACTION)	Block the update/delete
CASCADE	Propagate the change to child rows
SET NULL	Set FK column to NULL
SET DEFAULT	Set FK column to its default value

2.5 ER → Relational Mapping Rules

-- Rule 1: Strong entity → own relation, PK = entity key
CREATE TABLE Department (
  dept_id   INT         PRIMARY KEY,
  dept_name VARCHAR(100) NOT NULL,
  location  VARCHAR(100)
);

-- Rule 2: 1:N relationship → FK on the N-side
CREATE TABLE Employee (
  emp_id  INT         PRIMARY KEY,
  name    VARCHAR(100) NOT NULL,
  salary  DECIMAL(10,2),
  dept_id INT REFERENCES Department(dept_id) ON DELETE SET NULL
);

-- Rule 3: M:N relationship → new relation, PK = combination of both FKs
CREATE TABLE Project (
  proj_id INT PRIMARY KEY,
  title   VARCHAR(200)
);

CREATE TABLE Works_On (
  emp_id  INT REFERENCES Employee(emp_id) ON DELETE CASCADE,
  proj_id INT REFERENCES Project(proj_id) ON DELETE CASCADE,
  hours   DECIMAL(5,2),
  PRIMARY KEY (emp_id, proj_id)
);

-- Rule 4: Weak entity → PK = owner PK + partial key
CREATE TABLE Dependent (
  emp_id       INT         REFERENCES Employee(emp_id) ON DELETE CASCADE,
  dep_name     VARCHAR(100),   -- partial key
  relationship VARCHAR(50),
  dob          DATE,
  PRIMARY KEY (emp_id, dep_name)  -- composite PK
);

-- Rule 5: Multivalued attribute → separate relation
CREATE TABLE Employee_Phone (
  emp_id INT  REFERENCES Employee(emp_id) ON DELETE CASCADE,
  phone  VARCHAR(20),
  PRIMARY KEY (emp_id, phone)
);

-- Rule 6: 1:1 relationship → FK in either table (prefer total participation side)
CREATE TABLE Spouse (
  emp_id    INT PRIMARY KEY REFERENCES Employee(emp_id),
  spouse_name VARCHAR(100),
  wedding_date DATE
);

Exam Quick-Reference

Double rectangle = weak entity; double diamond = identifying relationship; double ellipse = multivalued attribute; dashed ellipse = derived attribute.
M:N → always a new relation. Never put FK on both sides without a bridge table.
Weak entity PK = owner's PK columns + partial key columns.
Specialization (top-down) ≠ Generalization (bottom-up) — the direction matters.
FK violation options: RESTRICT · CASCADE · SET NULL · SET DEFAULT.

Full Syllabus Outline

Every topic from the syllabus data is preserved here.

2.1
Introduction to data models
DEI
Description and significance of data models. Brief description and illustrative example of each type: Entity-relationship, relational, object model, hierarchical, network, and graph data models.
2.2
E-R model
DEIDWACT
Description and illustration of various concepts and notations in ER models. Illustration with ER diagram examples. Discussion of alternate notation and conventions. Exercises with mini-cases to design ER diagrams. Group activities to study real cases and design ER models. Drawing ER diagrams using CASE tools.
- 2.2.1
  Entities and entity sets
  Definition and notation for entities and entity sets.
- 2.2.2
  Attributes and keys
  Simple, composite, multivalued, and derived attributes. Primary key, candidate key, and super key concepts.
- 2.2.3
  Strong and weak entity sets
  Distinction between strong and weak entity sets. Discriminator (partial key) for weak entities. Double-rectangle notation.
- 2.2.4
  Relationship and relationship sets (Mapping cardinalities)
  One-to-one, one-to-many, and many-to-many relationships. Total and partial participation constraints.
- 2.2.5
  Specialization, generalization and aggregation
  Top-down (specialization) and bottom-up (generalization) design approaches. Aggregation for relationships involving relationships.
2.3
Relational model
DEIDWACT
Definition and description of the relational model and key terminologies. Description and illustration of different types of keys and foreign key constraint. Notational conventions and drawing of relational schema diagrams. Converting ER diagrams to relational schemas for different cases.
- 2.3.1
  Concept of relational model, key constraints
  Relation, tuple, attribute, domain. Super key, candidate key, primary key, foreign key. Referential integrity constraint.
- 2.3.2
  Converting ER model into relational model
  Rules for converting strong entities, weak entities, 1:1, 1:N, and M:N relationships, and specialization/generalization hierarchies into relational schemas.

Solved PYQs

A few past questions with short answers.

Chapter 24 marksasked 1x2082 Kartik B

Explain different types of keys in a relational database along with example.

Solution

Key Types in the Relational Model

Key Type	Definition	Example
Super Key	Any set of attributes that uniquely identifies every tuple in a relation	{sid}, {sid, name}, {email}
Candidate Key	A minimal super key — no proper subset is also a super key	{sid}, {email}
Primary Key	The one candidate key chosen by the designer (underlined in schema)	sid
Foreign Key	An attribute in one relation that references the PK of another relation	Enrollment.sid → Student.sid
Partial Key	Attribute(s) that partially identify a weak entity when combined with the owner's PK	item_no in Order_Item (combined with order_id)

Candidate Key vs Primary Key

A relation can have multiple candidate keys (e.g., both sid and email could uniquely identify a student).
The designer selects exactly one candidate key as the primary key.
All candidate keys share the same uniqueness and minimality properties; the PK is just the chosen representative.
The PK columns must not contain NULL values; other candidate keys may optionally be declared UNIQUE NOT NULL.

Referential Integrity (Foreign Key Constraint)

A foreign key value in a child relation must either match a primary key value in the referenced (parent) relation or be NULL. This prevents orphan records. On DELETE or UPDATE of a referenced row, the DBMS can: RESTRICT (block), CASCADE (propagate), SET NULL, or SET DEFAULT.

Chapter 28 marksasked 1x2082 Kartik B

Draw an ER-diagram for the Hospital Management System case: A hospital has multiple departments. Each department has a unique department ID and name. Doctors are identified by doctor ID with attributes name, specialization, and experience. A doctor belongs to one department but may treat multiple patients. Each patient has a patient ID, name, age, gender, and medical history. Each patient is assigned a room when admitted. A room has room number, type (ICU, General, Private), and cost per day. Nurses are identified by employee ID, name, and shift timings. The hospital has a pharmacy storing medicines with medicine ID, name, and expiry date. The hospital maintains records of appointments with date and time.

Solution

Cardinality Constraints:

A Department employs many Doctors and many Nurses (1:N each).
A Doctor treats many Patients; a Patient may see many Doctors (M:N → bridge via Appointment).
A Patient is assigned to at most one Room; a Room holds at most one Patient at a time (1:1).
An Appointment is between exactly one Doctor and one Patient on a given date and time.

Chapter 24 marksasked 1x2081 Chaitra R

Explain the various elements of the relational data model along with an example.

Solution

The Relational Data Model:

The relational data model, proposed by E.F. Codd in 1970, represents data as a collection of relations (tables). It is built on the mathematical foundation of set theory and predicate logic.

Core Terminology:

Term	Definition	Example
Relation	A table with a fixed set of named columns and a set of rows	Student table
Tuple	A single row; one complete record	(1, 'Asha', 'CS', 3.8)
Attribute	A named column with a specific domain	name VARCHAR(100)
Domain	The set of allowed values for an attribute	INT, VARCHAR(100), DATE
Degree	Number of attributes (columns) in a relation	4
Cardinality	Number of tuples (rows) in a relation	500
Schema	The structural definition: relation name + attributes	Student(sid, name, dept, gpa)

Integrity Constraints:

Entity Integrity: No attribute of the primary key may be NULL.
Referential Integrity: Every non-null foreign key value must match an existing primary key value in the referenced relation.
Domain Constraint: Each attribute value must lie within its defined domain.

Key Constraints:

Primary Key: Uniquely identifies each tuple; cannot be NULL.
Candidate Key: Any minimal set of attributes that uniquely identifies tuples.
Foreign Key: References the PK of another (or the same) relation, enforcing referential integrity.

More PYQs

Additional practice from this chapter.

Explain different types of keys in a relational database along with example.
4 marksasked 1x2082 Kartik B
Draw an ER-diagram for the Hospital Management System case: A hospital has multiple departments. Each department has a unique department ID and name. Doctors are identified by doctor ID with attributes name, specialization, and experience. A doctor belongs to one department but may treat multiple patients. Each patient has a patient ID, name, age, gender, and medical history. Each patient is assigned a room when admitted. A room has room number, type (ICU, General, Private), and cost per day. Nurses are identified by employee ID, name, and shift timings. The hospital has a pharmacy storing medicines with medicine ID, name, and expiry date. The hospital maintains records of appointments with date and time.
8 marksasked 1x2082 Kartik B
Explain the various elements of the relational data model along with an example.
4 marksasked 1x2081 Chaitra R
Construct an ER diagram for a medium-sized Company delivering products to customers, employing various experts, consultants and supporting staff, with inter-disciplinary project teams selected from different departments, each project having a Project Manager. Assume necessary attributes, relationship sets and mapping constraints.
8 marksasked 2x2081 Chaitra R
Explain the relational model of a database system along with foreign key constraint with appropriate example.
4 marksasked 1x2079 Chaitra R

Data Models

Chapter 2 — Data Models

2.1 Data Model Comparison

2.2 ER Model

Entity Types

Attribute Types

Key Types

Cardinality Constraints

Participation Constraints

2.3 Specialization · Generalization · Aggregation

2.4 Relational Model Basics

Referential Integrity

2.5 ER → Relational Mapping Rules

Exam Quick-Reference

Introduction to data models

E-R model

Entities and entity sets

Attributes and keys

Strong and weak entity sets

Relationship and relationship sets (Mapping cardinalities)

Specialization, generalization and aggregation

Relational model

Concept of relational model, key constraints

Converting ER model into relational model

Key Types in the Relational Model

Candidate Key vs Primary Key

Referential Integrity (Foreign Key Constraint)