You have entities Project(proj_id, name), Employee(emp_id, name), and a many-to-many relationship WorksOn with attribute hours. How should you design the tables?
hard📝 Application Q8 of 15
SQL - Table Relationships
You have entities Project(proj_id, name), Employee(emp_id, name), and a many-to-many relationship WorksOn with attribute hours. How should you design the tables?
ACreate Project and Employee tables only; add hours to Employee
BCreate Project and Employee tables only; add hours to Project
CCreate Project, Employee, and WorksOn(proj_id, emp_id, hours) with composite PK (proj_id, emp_id)
DMerge Project and Employee into one table with hours
Step-by-Step Solution
Solution:
Step 1: Identify many-to-many relationship with attribute
Many-to-many with attribute requires a separate table to hold relationship and attribute.
Step 2: Design junction table with composite key and attribute
WorksOn table includes proj_id and emp_id as composite primary key plus hours attribute.
Final Answer:
Create Project, Employee, and WorksOn(proj_id, emp_id, hours) with composite PK (proj_id, emp_id) -> Option C
Quick Check:
Relationship with attribute = junction table with attribute [OK]
Quick Trick:Attributes on relationships go in junction tables [OK]
Common Mistakes:
MISTAKES
Ignoring relationship attribute
Adding attribute to entity tables incorrectly
Merging unrelated entities
Master "Table Relationships" in SQL
9 interactive learning modes - each teaches the same concept differently
