Cardinality showing the nature of relationships among tables

database-design

Lets say there's a table named Department and a table named Instructor. We've conditions that says :

  • A Department can have many instructors.
  • An instructor can be associated to only single department.

Now the point is, if seen from the Department's angle, the relationship is of type "one to many"(coz one department can accommodate many instructors ) but if seen from instructor's angle, then the relationship is of type "one to one"(coz, one instructor can join only one department).

If we implement "one to many"( from department's angle) by making a foreign key in instructor table that would refer to department table, e.g. :

Instructors table : 

create table instructors(inst_id number primary key not null, inst_name varchar2(30) not null,
dept_id number constraint dept_id_fk references department(dept_id) constraint not null);

Department Table :

create table department(dept_id number primary key not null, dept_name varchar2(15) not null);

Does these statements sufficiently satisfies both "one to one"( from instructor's table angle ) and "one to many"(from department's angle) ?

or

Do we need to change or SQL code to introduce "one to one" aspect too ?

Best Answer

The code you've presented satisfies the requirements.

If we think about querying the tables starting from each "side", we can see this. (Assume the supplied parameters are suitably valid.)

Return all instructors in a given department:

SELECT i.*
    FROM department d
    INNER JOIN instructors i ON i.dept_id = d.dept_id
    WHERE d.dept_id = @departmentId;

We specify a value for the primary key of department, which restricts rows from that table to just 1 row. When we join to instructors, we need to find all instructors with that dept_id, which could be all of them, none of them, or somewhere in between. For one department, we can find many (or, more accurately, zero-to-many) instructors. (Note: yes, the query can be simplified; I'm just trying to demonstrate a point.)

Return all departments for a given instructor:

SELECT d.*
    FROM instructors i
    INNER JOIN department d ON d.dept_id = i.dept_id
    WHERE i.inst_id = @instructorId;

Again, because we specify a unique value for the primary key, this restricts the rows returned from instructors to just 1 row. In that single row, there is exactly 1 dept_id. Because dept_id is also unique in department, when joining to that table, we can only get back a single department. For one instructor, we will find exactly one department.

If you follow this process for all the relationships you create, it's easy to figure out if the requirements/design specs are met or not. You'll start to see these more easily (without writing queries) once you get more experience under your belt.

Related Question