GATE/Databases (DBMS)/ER Model & Relational Model
Sign in to track
Medium16 min readDatabases (DBMS)

ER Model & Relational Model

The Entity-Relationship model is a conceptual design tool; the relational model implements it as tables. GATE tests ER-to-relational mapping, keys, constraints, and schema design.

Key Points

  • ·Entity: real-world object; Attribute: property; Relationship: association between entities
  • ·Strong entity: has its own key; Weak entity: depends on owner entity, uses partial key
  • ·Cardinality: 1:1, 1:N, M:N; Participation: total (double line) vs partial (single line)
  • ·Superkey: any set of attributes that uniquely identifies a tuple
  • ·Candidate key: minimal superkey; Primary key: chosen candidate key
  • ·Foreign key: references primary key of another relation; referential integrity
  • ·Relational schema: R(A₁, A₂, ..., Aₙ) — relation name + attribute list
  • ·Tuple: a row; Attribute: a column; Domain: set of allowed values for an attribute
  • ·NULL: unknown or inapplicable value; treated specially in comparisons (NULL ≠ NULL)

What is a Database?

Analogy: A database is like a school's filing cabinet. Each drawer is a table. Each folder in a drawer is a row (tuple). The labels on the folders tell you what is inside (attributes).

ER Diagram — Draw Before You Code

An ER (Entity-Relationship) diagram is like a blueprint for a house — you design it before building. It shows WHAT data exists and HOW things connect.

Drawing symbols:

[Rectangle]       = Entity (a "thing" that exists, like Student, Course)
(Ellipse)         = Attribute (a property, like Name, Age)
((Double Ellipse))= Multivalued attribute (Phone has multiple numbers)
(Dashed Ellipse)  = Derived attribute (Age derived from Date of Birth)
<Diamond>         = Relationship (ENROLLS, TEACHES)
[Double Rectangle]= Weak Entity (depends on another entity to exist)

Example ER diagram (school):

[Student] ----(ENROLLS)---- [Course]
   |                           |
(RollNo)                   (CourseID)
(Name)                     (Title)
(DOB)                      (Credits)

Cardinality — How Many Connect to How Many?

Think of it like relationships between people:

1:1  — One husband, one wife (in many cultures)
       One country has one capital

1:N  — One teacher teaches MANY students
       One department has MANY employees

M:N  — Many students enroll in many courses
       Many doctors treat many patients

Total vs Partial participation:

Total (double line):  EVERY entity must participate
  "Every employee MUST work in a department"
  Employee ══════ (WORKS_IN) ━━━━━ Department

Partial (single line): participation is optional
  "Not every employee manages a department"
  Employee ━━━━━ (MANAGES) ━━━━━ Department

Strong Entity vs Weak Entity

Analogy: A strong entity has its own identity card (primary key). A weak entity is like a child who uses the parent's surname to be identified.

Strong entity: STUDENT(RollNo, Name) — RollNo alone identifies
Weak entity:   DEPENDENT(Name, DOB) of EMPLOYEE
               → Identified by: (Emp_ID + Dependent_Name)
               The weak entity "depends" on the owner entity EMPLOYEE

Keys — The Identity System

Superkey:      Any set of attributes that uniquely identifies a tuple
               {RollNo}, {RollNo, Name}, {RollNo, Name, DOB} are all superkeys

Candidate key: MINIMAL superkey (remove any attribute and it fails)
               {RollNo} is a candidate key; {RollNo, Name} is NOT minimal

Primary key:   One chosen candidate key (cannot be NULL)

Alternate key: Candidate keys not chosen as primary key

Foreign key:   Attribute(s) that reference another table's primary key

Visual example:

STUDENT table:
┌─────────┬──────────┬──────────────┬──────────┐
│ RollNo  │ Name     │ Email        │ Dept_ID  │
│ (PK)    │          │ (unique)     │ (FK)     │
├─────────┼──────────┼──────────────┼──────────┤
│ 101     │ Priya    │ p@iit.ac.in  │ CS       │
│ 102     │ Rahul    │ r@iit.ac.in  │ ECE      │
└─────────┴──────────┴──────────────┴──────────┘
                                         ↑
                              References DEPARTMENT(Dept_ID)

ER to Relational Mapping

ER Construct Mapping Rule
Strong entity One table; PK = entity key
Weak entity Table with PK = (Owner PK + Discriminator key)
1:1 relationship FK on either side (prefer total-participation side)
1:N relationship FK on the N-side
M:N relationship New table with FK from BOTH sides as composite PK
Multivalued attribute Separate table with FK + the attribute

M:N Example:

STUDENT M:N COURSE via ENROLLS
Creates: ENROLLS(Student_ID, Course_ID, Grade)
PK = (Student_ID, Course_ID)  ← composite key

Quick Check

Q1. A department has many employees, but every employee must belong to a department. What is the participation? Answer: Total participation of Employee in WORKS_IN (every employee must have a department)

Q2. In ENROLLS(Student_ID, Course_ID, Grade), what is the primary key? Answer: (Student_ID, Course_ID) — composite key from both sides of M:N

Q3. What is wrong with this: foreign key Dept_ID in EMPLOYEE references DEPARTMENT(Dept_ID), but you insert an employee with Dept_ID = 'XYZ' which does not exist in DEPARTMENT? Answer: Referential integrity violation — the FK must reference an existing PK value

Key Formulas

  • M:N table PK: PK = (FK1, FK2) — composite key from both entities
  • Weak entity PK: PK = (Owner PK, Partial/Discriminator Key)

GATE Exam Tips

  • ★Weak entity PK = (owner entity PK + discriminator) — this is the standard mapping rule.
  • ★M:N relationship ALWAYS needs a separate relation with a composite PK.
  • ★Total participation on N-side of 1:N means the FK on that side cannot be NULL.
  • ★A relation can have multiple candidate keys; exactly one is chosen as primary key.

Finished reading this topic?

Mark it complete to track your study progress.

Sign in to track