Cardinality and Relationship among tables

Question

There is a table named Department, which shows the various departments of a University.
And there is a table named Branch, which shows various branches (such as electrical, computer science, mechanical, automobile, law, literature etc.)

Conditions:

1. A Department can have any number of Branches.
2. A Branch can be associated to single Department.

It means the cardinality between Department : Branch is "one to many".

Now the problem is, in this kind of situation, if I make dept_id (an attribute of table: Department) as primary key. How would I be able to associate more than one branch_id ( an attribute of table : Branch), because if I do it so, I may violate the primary key condition of dept_id itself.

How to deal with cardinality and making foreign key both go hand in hand?

Answer 1

Add a foreign key dept_id to Branch referencing Department.dept_id.

Since dept_id is unique in Department, each row in Branch can thus obviously be linked to exactly one row in Department, and there can be multiple rows in Branch containing the same dept_id.

Thus there will be a one-to-many relationship between the two.

---

To depict a many-to-many relationship, you need to create a third table that contains:

• A foreign key dept_id referencing Department.dept_id and
• A foreign key branch_id referencing Branch.branch_id