RDBMS Module 2 — Database Design

ER Model

Basic Concepts, Entities & Attributes

Proposed by Peter Chen, 1976

ER Model = Entity-Relationship Model. A high-level conceptual data model used to visually design databases before converting to relational tables. Three core concepts: Entities, Attributes, Relationships.

Entity Terminology

Term	Definition	Example
Entity	A specific real-world object with independent existence	Student 'Arun' (Roll 101)
Entity Type	Category/class of similar entities — shown as Rectangle	STUDENT
Entity Set	All entities of a type currently in DB (like a table)	All enrolled students

Attribute Types — ER Notation

Attribute Type	Description	ER Notation	Example
Simple (Atomic)	Cannot be divided further	Plain oval	Age, Gender, Salary
Composite	Can be split into sub-parts	Oval with sub-ovals	Name → First, Last
Single-Valued	One value per entity	Plain oval	Date_of_Birth, NID
Multi-Valued	Multiple values per entity	Double oval	Phone_Numbers, Degrees
Derived	Computed from other attributes	Dashed oval	Age (from DOB)
Key	Uniquely identifies entity	Underlined oval	Student_ID, EmpID

NULL Values

NULL is NOT zero, NOT empty string '', NOT a blank space. It means missing, unknown, or not applicable.

NULL Type	Meaning	Example
Missing	Value exists but unknown	Phone number not collected
Not Applicable	Attribute irrelevant for this entity	Spouse_Name for unmarried person

ER Diagram Notation — Complete Reference

ER Concept	Notation
Entity Type	Rectangle
Relationship Type	Diamond
Simple Attribute	Oval (ellipse)
Key Attribute	Oval with underlined name
Multi-Valued Attribute	Double oval
Derived Attribute	Dashed oval
Composite Attribute	Oval with sub-ovals attached
Total Participation	Double line
Partial Participation	Single line
Weak Entity Type	Double rectangle
Identifying Relationship	Double diamond
Partial Key (Discriminator)	Dashed underline

Exam tip: Double = weak/multi-valued. Dashed = derived/partial key. Underlined = key attribute. These distinctions are frequently tested.

Relationships

Relationships, Constraints & Weak Entities

Degree of a Relationship

Degree	Description	Example
Unary (1)	Same entity type relates to itself	EMPLOYEE supervises EMPLOYEE
Binary (2)	Two entity types — most common	STUDENT enrolls in COURSE
Ternary (3)	Three entity types simultaneously	SUPPLIER supplies PART to PROJECT

Cardinality Ratios (Mapping Cardinality)

Specifies max number of relationship instances an entity can participate in.

1 : 1 (One-to-One)

Each entity in A ↔ at most one in B

e.g. EMPLOYEE manages DEPARTMENT

1 : N (One-to-Many)

One in A → many in B

e.g. DEPARTMENT has many EMPLOYEES

M : N (Many-to-Many)

Many in A ↔ many in B. Requires junction table in relational model.

e.g. STUDENT enrolls in COURSE

Participation Constraints

Constraint	Meaning	ER Notation	Example
Total (Mandatory)	Every entity MUST participate	Double line	Every EMPLOYEE works for a DEPT
Partial (Optional)	Some entities may not participate	Single line	Not every EMP manages a DEPT

Weak Entity Types

A weak entity has no primary key of its own. Its existence depends on an owner entity via an identifying relationship.

Aspect	Strong Entity	Weak Entity
Primary Key	Has its own PK	No PK — identified by Partial Key + Owner PK
Existence	Independent	Depends on owner entity
ER Notation	Single rectangle	Double rectangle
Relationship	Ordinary diamond	Double diamond (identifying)
Key	Primary key (solid underline)	Partial key/discriminator (dashed underline)
Example	EMPLOYEE (Emp_ID)	DEPENDENT (Dep_Name + Emp_ID)

Extended ER (EER) — Specialization & Generalization ▼

Concept	Direction	Description	Example
Specialization	Top-down ↓	Define subclasses of a superclass (IS-A)	EMPLOYEE → MANAGER, ENGINEER
Generalization	Bottom-up ↑	Combine entities into a superclass	CAR + TRUCK → VEHICLE
Inheritance	—	Subclass inherits all superclass attributes	MANAGER inherits all EMPLOYEE attrs

Constraint	Symbol	Rule
Disjoint	'd' in circle	Entity belongs to AT MOST one subclass
Overlapping	'o' in circle	Entity can belong to MULTIPLE subclasses
Total	Double line	Every superclass entity is in at least one subclass
Partial	Single line	Superclass entity may not be in any subclass

Exam tip: Weak entity needs TWO things: double rectangle + double diamond. Partial key uses dashed underline. M:N relationships must be split into a junction/bridge table in relational schema.

Relational Model

Domains, Attributes, Tuples, Relations & NULLs

E.F. Codd, 1970

Proposed by E.F. Codd in 1970. Data is represented as a collection of relations (tables). Foundation of all RDBMS: MySQL, Oracle, PostgreSQL.

Core Terminology

Term	Definition	SQL equivalent
Domain	Set of ALL possible atomic values for an attribute	Data type + constraints
Relation Schema	R(A1, A2…An) — name + list of attributes with domains	Table definition
Relation	A set of tuples conforming to the schema	Table (with rows)
Tuple	A single row — ordered list of values	Row / Record
Attribute	A named column in the relation	Column
Degree (Arity)	Number of attributes (columns)	Number of columns
Cardinality	Number of tuples (rows) at a given time	Row count

Properties of a Relation — Must Know

✅ No duplicate tuples — every row must be unique
✅ No tuple ordering — order of rows doesn't matter
✅ Atomic values — each cell holds ONE indivisible value (1NF requirement)
ℹ️ Attribute order matters for positional notation (not for named)
ℹ️ NULL values allowed unless constrained otherwise

NULL Values in Tuples

NULL ≠ 0 | NULL ≠ '' | NULL ≠ ' '

NULL Interpretation	Example
Unknown — value exists but not known	Phone number not provided
Not Available — value exists but not given	Salary withheld
Not Applicable — attribute irrelevant	Spouse_Name for unmarried person

Any arithmetic with NULL → NULL. Any comparison with NULL → UNKNOWN (three-valued logic: TRUE / FALSE / UNKNOWN).

Constraints

Relational Model Constraints & Database Schemas

Key Types — Hierarchy

Key Type	Definition	Properties
Superkey	Any set of attributes that uniquely identifies a tuple	Can have extra/redundant attributes
Candidate Key	Minimal superkey — no redundant attributes	Removing any attr breaks uniqueness
Primary Key	Chosen candidate key — official identifier	NOT NULL + UNIQUE. Exactly one per table.
Alternate Key	Candidate keys NOT chosen as primary	Enforced via UNIQUE constraint in SQL
Foreign Key	References PK of another relation	Enforces referential integrity

Four Main Integrity Constraints

1. Domain Constraint

Each attribute value must be atomic and from its defined domain.

Marks domain: INTEGER, 0–100. Storing 'Excellent' or 150 → rejected.

2. Key Constraint

Primary key values must be unique across all tuples.

No two students can have same Student_ID.

3. Entity Integrity Constraint

Primary key attribute(s) CANNOT be NULL. Must be able to identify every tuple.

Student_ID cannot be NULL — how would we identify the student?

4. Referential Integrity Constraint (Foreign Key)

A foreign key value must either match an existing PK value in the referenced relation, or be NULL.

STUDENT.Dept_ID must exist in DEPARTMENT.Dept_ID — no orphan records.

Operations & Possible Violations

Operation	Can Violate
INSERT	Domain, Key, Entity Integrity, Referential Integrity
DELETE	Referential Integrity (if other tables reference this row)
UPDATE	Domain, Key, Entity Integrity, Referential Integrity

Exam tip: Every PK is a Candidate Key but not vice versa. Every Candidate Key is a Superkey but not vice versa. Entity Integrity = PK cannot be NULL. Referential Integrity = FK must exist in referenced table.

Normalization

1NF, 2NF, 3NF, BCNF

Reduce redundancy, eliminate anomalies

Normalization = organizing a relational DB to reduce data redundancy and improve data integrity by applying progressive normal form rules.

Why Normalize? — The 3 Anomalies

Anomaly	Problem	Example
Update	Same data in many rows — update one, others become inconsistent	Faculty dept change must update every course row
Insert	Can't add data without also adding unrelated data	Can't add dept unless a student is enrolled
Delete	Deleting one thing accidentally removes other info	Delete last student → course info also lost

Functional Dependency (FD)

X → Y means "X determines Y" — for any two tuples with same X value, Y must also be same.

FD Type	Definition	Example
Full FD	Y depends on the ENTIRE composite key (not a subset)	Grade depends on {Student_ID, Course_ID}
Partial FD	Y depends on only a PART of a composite key	Student_Name depends only on Student_ID (part of key)
Transitive FD	X→Y and Y→Z therefore X→Z (via intermediate)	Student_ID → Dept_ID → Dept_Name

Normal Form Progression

Unnormalized (UNF) — has repeating groups

↓ apply 1NF

1NF — all values atomic

↓ remove partial deps

2NF — no partial dependencies

↓ remove transitive deps

3NF — no transitive dependencies

↓ every determinant is a superkey

BCNF — stronger than 3NF

↓ remove multi-valued deps

4NF → 5NF (next section)

1NF

First Normal Form

Rule: All attribute values must be atomic (indivisible). No multi-valued attributes, no repeating groups, no arrays in cells.

❌ Violates 1NF

Stu_ID	Phone
101	9876543, 8765432

Cell has multiple values

✅ 1NF

Stu_ID	Phone
101	9876543
101	8765432

Separate row per value

Rule of thumb: If a cell contains a list/multiple values → NOT in 1NF

2NF

Second Normal Form

Rule: In 1NF + every non-prime attribute is fully functionally dependent on the ENTIRE primary key. Applies only when PK is composite.

Problem relation: ENROLLMENT(Student_ID, Course_ID, Grade, Student_Name, Course_Name)
PK = {Student_ID, Course_ID}
Grade → depends on both ✅
Student_Name → depends only on Student_ID ❌ (partial)
Course_Name → depends only on Course_ID ❌ (partial)

→ Decompose to 2NF:

STUDENT(Student_ID, Student_Name)
COURSE(Course_ID, Course_Name)
ENROLLMENT(Student_ID, Course_ID, Grade)  ← only Grade here

3NF

Third Normal Form

Rule: In 2NF + no transitive dependencies. For every FD X→Y: either (a) X is a superkey, OR (b) Y is a prime attribute.

Problem: STUDENT(Student_ID, Name, Dept_ID, Dept_Name, HOD_Name)
Student_ID → Dept_ID → Dept_Name (transitive!) ❌

→ Decompose to 3NF:

STUDENT(Student_ID, Name, Dept_ID)
DEPARTMENT(Dept_ID, Dept_Name, HOD_Name)

BCNF

Boyce-Codd Normal Form

Rule: For every non-trivial FD X→Y, X must be a superkey. Stronger than 3NF — eliminates ALL FD anomalies.

Problem: COURSE_TEACHER(Student_ID, Subject, Teacher)
Candidate keys: {Student_ID, Subject}, {Student_ID, Teacher}
Teacher → Subject ❌ (Teacher is not a superkey)

→ Decompose to BCNF:

TEACHER_SUBJECT(Teacher, Subject)
STUDENT_TEACHER(Student_ID, Teacher)

BCNF vs 3NF: 3NF allows Y to be a prime attribute as exception. BCNF is stricter — no exceptions. Every BCNF relation is in 3NF, but NOT vice versa.

Normal Forms — Quick Comparison

Normal Form	Condition to satisfy	Eliminates
1NF	All values atomic; no repeating groups; each row unique	Multi-valued cells, repeating groups
2NF	1NF + no partial dependencies on composite PK	Partial functional dependencies
3NF	2NF + no transitive dependencies	Transitive functional dependencies
BCNF	For ALL X→Y: X is a superkey	All FD-based anomalies

Exam tip: 2NF only matters when PK is composite. If PK is a single attribute, 1NF → 2NF automatically. BCNF is stricter than 3NF. Most production DBs target 3NF or BCNF.

Advanced NF

4NF and 5NF

Multi-valued & Join dependencies

Multi-Valued Dependency (MVD)

X →→ Y ("X multi-determines Y"): for a given X, there's a set of Y values independent of other attributes in the relation.

Example: EMPLOYEE_SKILLS(Employee_ID, Skill, Language)
An employee can have multiple skills AND multiple languages — independently of each other.
Employee_ID →→ Skill AND Employee_ID →→ Language

Problem: 3 skills × 2 languages = 6 rows (redundancy explosion). Inserting a new language requires adding N rows (one per skill).

4NF

Fourth Normal Form

Rule: In BCNF + no non-trivial multi-valued dependencies, unless the left side is a superkey.

EMPLOYEE_SKILLS(Employee_ID, Skill, Language) ← violates 4NF

→ Decompose:
EMPLOYEE_SKILL(Employee_ID, Skill)
EMPLOYEE_LANGUAGE(Employee_ID, Language)  ← 4NF ✅

Result: Adding a new language now requires only 1 row, not N rows.

Join Dependency (JD)

*{R1, R2…Rn} on R means R can be reconstructed exactly by joining its projections R1, R2…Rn. Every MVD is a special case of JD (with just 2 components).

5NF

Fifth Normal Form (PJNF)

Rule: In 4NF + every join dependency is implied by the candidate keys. (Project-Join Normal Form)

Example: SUPPLY(Supplier, Part, Project) — complex 3-way dependency.
Rule: if a supplier supplies a part AND that part is used in a project AND the supplier works on that project → supplier supplies that part for that project.

SUPPLIER_PART(Supplier, Part)
PART_PROJECT(Part, Project)
SUPPLIER_PROJECT(Supplier, Project)
← Original SUPPLY = join of these three ✅

All Normal Forms — Master Summary

NF	Prerequisite	Eliminates	When to apply
1NF	—	Non-atomic values, repeating groups	Always — minimum requirement
2NF	1NF	Partial FDs on composite PK	When PK is composite
3NF	2NF	Transitive dependencies	Most production DBs stop here
BCNF	3NF	All FD anomalies (stricter 3NF)	When 3NF still has anomalies
4NF	BCNF	Non-trivial MVD redundancy	Many-to-many independent attrs
5NF	4NF	Join dependency anomalies	Complex 3-way+ relationships

⭐ Practical target: 3NF or BCNF for most real databases. 4NF/5NF for complex M:N relationships.

Exam tip: FD = one value determines another (X→Y). MVD = one value determines a SET of values independently (X→→Y). 4NF handles MVDs; 5NF handles JDs. MVD is a special case of JD.

Study Mode

Flashcards

Tap card to flip

∑

Quick Ref

Master Cheatsheet

Every Key Rule in One Table

Topic	Key Rule / Fact
ER Model by	Peter Chen, 1976
Relational Model by	E.F. Codd, 1970
Entity vs Entity Type	Entity = specific object; Entity Type = category (rectangle)
Multi-valued attribute	Double oval — e.g. Phone_Numbers
Derived attribute	Dashed oval — e.g. Age from DOB
Key attribute	Underlined oval — uniquely identifies entity
Weak entity notation	Double rectangle + Double diamond
Partial key notation	Dashed underline
Total participation	Double line — every entity MUST participate
Cardinality 1:N	One in A → many in B (e.g. Dept has many Employees)
Cardinality M:N	Requires junction table in relational schema
Degree of relation	# of attributes (columns)
Cardinality of relation	# of tuples (rows)
NULL ≠	NULL ≠ 0, NULL ≠ '', any op with NULL = NULL
Superkey vs Candidate Key	CK = minimal superkey (no redundant attrs)
Entity Integrity	PK cannot be NULL
Referential Integrity	FK must match existing PK or be NULL
1NF rule	All values atomic — no lists in cells
2NF rule	No partial FDs on composite PK
3NF rule	No transitive FDs
BCNF rule	For every X→Y, X must be superkey (no exceptions)
4NF rule	No non-trivial MVDs unless LHS is superkey
5NF rule	All JDs implied by candidate keys
FD vs MVD	FD: X→Y (one value). MVD: X→→Y (set of values independently)
Practical NF target	3NF or BCNF for most databases