Mapping strong and weak entities, composite and multivalued attributes

0 · Recall Warm-up

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

1. What does ON DELETE CASCADE do? from Lesson 2.3.2
2. What is the key of a weak entity type? from Lesson 1.2.3
3. Why can a relation not hold {CS, EE} in one cell? from Lesson 2.1.1

1 · The Hook

You have an ER diagram; the DBMS wants tables. Converting one into the other is mechanical once you know a handful of rules, and GATE asks you to apply them quickly: "how many tables?", "what is the primary key of this table?". This lesson maps entity types and attributes; the next maps relationships.

2 · Predict First

STUDENT has key roll, a composite attribute name(first, last), a multivalued attribute phone and a derived attribute age. How many tables, and what columns, does STUDENT need?

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

3 · Core Concept

Predict First — the answer

Two tables. STUDENT(roll, first, last, dob, …): the composite is flattened into its parts and age is not stored. The multivalued phone gets its own table STUDENT_PHONE(roll, phone), because a cell can hold only one value.

Rule 1 · Strong entity type

Create a relation with all simple attributes. Choose one key as the primary key.

• Composite attribute: store only its simple components: name(first, last) → columns first, last.
• Derived attribute: usually not stored (compute when needed).

STUDENT(roll, first, last, dob, dept, cgpa)

Rule 2 · Weak entity type

Create a relation with the weak entity's simple attributes plus the owner's primary key as a foreign key. The primary key is owner's PK + partial key. The identifying relationship needs no table of its own. Use ON DELETE CASCADE on the FK (weak entities die with their owner).

ROOM(hname, room_no, capacity), hname → HOSTEL.hname

Rule 3 · Multivalued attribute

Create a new relation with the owner's primary key (foreign key) and the attribute. The primary key is both together (one student can have several phones, and two students could share a phone).

STUDENT_PHONE(roll, phone), roll → STUDENT.roll

A composite multivalued attribute {address(street, city, pin)} becomes ADDRESS(roll, street, city, pin) with a key made of roll plus enough parts to be unique (often all of them).

Count of tables so far

Every strong entity type: 1 table. Every weak entity type: 1 table. Every multivalued attribute: 1 extra table.

4 · Visual

One entity type with a multivalued attribute becomes two tables.

5 · Worked Examples

Example 1 Easy

Map COURSE(cid key, title, textbooks multivalued, credits).

Solution

COURSE(cid, title, credits) and COURSE_TEXTBOOK(cid, textbook) with cid → COURSE.cid. 2 tables.

Example 2 Medium

Map the weak entity DEPENDENT (partial key dname, attributes relation, dob) owned by EMPLOYEE(eid key, name).

Solution

EMPLOYEE(eid, name); DEPENDENT(eid, dname, relation, dob), eid → EMPLOYEE.eid ON DELETE CASCADE. The identifying relationship adds no table.

Example 3 GATE

An ER diagram has 3 strong entity types (one of which has 2 multivalued attributes), 1 weak entity type and no relationships other than the weak entity's identifying relationship. How many tables are needed at minimum?

Solution

3 (strong) + 1 (weak) + 2 (multivalued) = 6.

8 · Connect the Dots

Lesson 2.4.1 handled entities and their attributes; next lesson handles the relationships between them: when a relationship becomes a foreign key, when it becomes its own table, and when two entity tables merge into one. That decides the GATE favourite "minimum number of tables".

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Multivalued

A multivalued attribute email of STUDENT(roll) is mapped to:

1. (A) one column email in STUDENT
2. (B) nothing; it is dropped
3. (C) a table STUDENT_EMAIL(roll, email) with PK (roll, email)
4. (D) a table STUDENT_EMAIL(roll, email) with PK roll

Q2 · MCQ · 1 mark(s) — Composite

A composite attribute address(street, city) of STUDENT is mapped to:

1. (A) a single column holding both
2. (B) columns street and city in STUDENT
3. (C) a derived column
4. (D) a separate table

Q3 · MCQ · 2 mark(s) — Weak entity

SECTION (partial key sec_no, attribute room) is weak with owner COURSE(cid). Its relation is:

1. (A) SECTION(cid, sec_no, room) with PK cid
2. (B) SECTION(sec_no, room) with PK sec_no
3. (C) SECTION(cid, sec_no, room) with PK (cid, sec_no)
4. (D) SECTION(sec_no, room) and a separate HAS(cid, sec_no) table

Q4 · NAT · 2 mark(s) — Counting tables

Entity types: STUDENT (strong, 1 multivalued attribute), COURSE (strong, 2 multivalued attributes), SECTION (weak, owner COURSE). Ignoring any relationships other than SECTION's identifying relationship, how many tables are needed at minimum?

Numerical answer: ________

Q5 · MSQ · 2 mark(s) — Mapping

Which are correct?

1. (A) The identifying relationship of a weak entity needs its own table.
2. (B) Derived attributes are normally not stored.
3. (C) Each multivalued attribute adds one table.
4. (D) The FK from a weak entity to its owner is usually ON DELETE CASCADE.

11 · Concept Ledger

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

mapping strong entityflattening composite attributesmapping weak entity (owner PK + partial key)mapping multivalued attribute (separate table)