Pa. 01 Question One Leaming Outcomefs) uestion One 20 Marks Convert a conceptual

Question

Pa. 01 Question One Leaming Outcomefs) uestion One 20 Marks Convert a conceptual business process level REA model into a logical relational model. Convert the Conceptual Model into Relational Model. Enter some example data into the relations. Your job is to decide whether to establish the relationship by posting a Foreign Key into the other table or create a separate table for the relationship. Give reason for your answer. dept # faculty # name name address phone (O,N) DEPARTMENT HAS FACULTY Answer

Explanation / Answer

This conceptual relationship is a many to many relationship . The relationship describes two entities Department and Faculty . A Department can have 0 or more Faculties and Faculty can have 1 or more departments.

Below are the relations..

DEPARTMENT(dept# , name, address)

FACULTY(faculty#, name, phone)

Now for the relationship between them, it is a many to many relationship. Here we cannot keep a foreign key in any of the tables, because if we keep foreign key of Faculty in Deparment table, and as more than one faculty is mapped to department , there will be multiple rows for 1 Department Id, which will contradict the Primary key rule.

SImilarly, If we keep foreign key of Departments in Faculty table, a faclulty can have more than one department, So there might be more than 1 row for 1 faculty ID. Thus again Primary key rule is violated.

So here we'll have a saparate table with two columns containing foreign key of both the tables as composite key of the table. This will be as follows

DEPARTMENT_FACULTY_MAPPING(faculty#,dept#)

dept# name address 1 Science Building 11 2 Maths Building 22

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.