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) ?
Do we need to change or SQL code to introduce "one to one" aspect too ?