Gyanrush
Start freeOpen Gyanrush
Start freeOpen Gyanrush
  1. LPU MCA Semester 1
  2. CAP570
  3. Unit 2

CAP570 · Unit 2: E-R modeling

Conversion of ER models to relational schemas

10 practice questions · in the MTE and ETE syllabus

Practise this topicPractise this topic

Key points

  • Foreign key goes on the many side of 1:N
  • M:N needs a junction table
  • Multivalued attributes get their own table
  • Weak entity key = owner key + partial key

Follow these rules to turn an ER diagram into tables.

  • Strong entity set: one table, its key attribute becomes the primary key.
  • Composite attribute: keep one column for each simple component.
  • Multivalued attribute: a separate table holding the owner's key and one value per row.
  • 1:N relationship: put the owner key of the "one" side into the "many" side table as a foreign key.
  • M:N relationship: create a junction table with the keys of both entities as foreign keys, usually forming a composite primary key, plus any relationship attributes.
  • 1:1 relationship: place a foreign key on either side, preferably the side with total participation.
  • Weak entity: a table whose primary key combines the owner's key and the partial key.
  • Derived attributes are not stored.

Example: Student, Course and an Enrols relationship with Grade become Student, Course and Enrolment(StudentID, CourseID, Grade).

Exam trap: storing several values in a single column breaks first normal form.

Try two questions

Question 1 · Tier 2, Understand

A multivalued attribute such as PhoneNumber is converted to relational form how?

  1. Into one column storing all numbers separated by commas
  2. Into a separate table with the owner key and one number per row
  3. Into a derived column calculated from the key
  4. Into extra columns named Phone1 to Phone10 inside the same table
Show the answer

B. A multivalued attribute becomes its own table with a foreign key back to the owner.

Why not A: A comma list breaks first normal form and makes searching hard.

Why not C: A phone number is stored, not derived.

Why not D: Fixed repeating columns waste space and limit the count.

Question 2 · Tier 2, Understand

In a one-to-many relationship between Department and Employee, where is the foreign key placed?

  1. In the Employee table, on the many side
  2. In the Department table, on the one side
  3. In both tables at the same time
  4. In a third table created for every one-to-many link
Show the answer

A. The foreign key goes on the many side so each employee row points to one department.

Why not B: The one side would need many values in a single cell.

Why not C: A single foreign key on the many side is enough.

Why not D: A separate table is needed for many-to-many, not here.