Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

Notes › DBMS

ER Model and Relational Schema Design

Before creating tables, designers draw an Entity–Relationship (ER) diagram: a picture of the real-world things the database stores and how they relate. It is then converted into tables.

Building blocks

Concept Meaning Drawn as Example
Entity a real-world thing rectangle Student, Course
Entity set all entities of one type rectangle all students
Attribute a property oval name, email
Relationship an association between entities diamond Student enrolls in Course
Weak entity can't be identified by its own attributes double rectangle Dependent of an Employee

Types of attributes

Type Meaning Example Drawn as
Simple can't be divided age oval
Composite made of parts name → first, last oval with sub-ovals
Single-valued one value date of birth oval
Multi-valued many values phone numbers double oval
Derived computed from others age from date of birth dashed oval
Key uniquely identifies the entity roll number underlined

Cardinality (mapping constraints)

How many entities on one side can relate to how many on the other:

Cardinality Example
1 : 1 Person — has — Passport
1 : N Department — has — Employees
M : N Student — enrolls in — Course

Participation

Weak entities

A weak entity has no primary key of its own; it is identified by its owner (strong) entity + a partial key (discriminator), through an identifying relationship (double diamond). It always has total participation.

Example: Dependent(name, age) of an Employee — two employees can both have a child named "Riya", so the key is (emp_id, dependent_name).

Extended ER features

Converting ER → tables (the exam favourite)

ER element Becomes
Strong entity a table; key attribute = primary key
Composite attribute one column per part (first_name, last_name)
Multi-valued attribute a separate table (entity_key, value)
Derived attribute usually not stored (compute it)
Weak entity table with owner's key + partial key as primary key; FK to owner (ON DELETE CASCADE)
1 : 1 relationship FK on either side (prefer the side with total participation), or merge tables
1 : N relationship FK on the N side (Employee gets dept_id)
M : N relationship a new junction table with both keys as a composite primary key + relationship attributes
Specialization one table per subclass with the superclass key, or one table with a type column

Worked example — a college

Student(roll_no, name, phone*)       * = multi-valued
Course(course_id, title, credits)
Department(dept_id, name)
Student —M:N— Course   (attribute: grade)
Department —1:N— Course

Tables:

CREATE TABLE department (dept_id INT PRIMARY KEY, name VARCHAR(50));

CREATE TABLE course (
  course_id INT PRIMARY KEY,
  title     VARCHAR(100),
  credits   INT,
  dept_id   INT REFERENCES department(dept_id)       -- 1:N → FK on the N side
);

CREATE TABLE student (roll_no INT PRIMARY KEY, name VARCHAR(50));

CREATE TABLE student_phone (                         -- multi-valued attribute
  roll_no INT REFERENCES student(roll_no),
  phone   VARCHAR(15),
  PRIMARY KEY (roll_no, phone)
);

CREATE TABLE enrollment (                            -- M:N → junction table
  roll_no   INT REFERENCES student(roll_no),
  course_id INT REFERENCES course(course_id),
  grade     CHAR(2),
  PRIMARY KEY (roll_no, course_id)
);

Minimum number of tables (GATE-style)

Common design mistakes

Interview one-liners

More DBMS notes