0

In this lecture they mentioned 1:1 relationship.

One can represent 1:1 relationship as follows: one lecturer teaches only one subject. It can be done either by adding a subject_id to a lecturer table or by adding a lecturer_id into a subject table.

enter image description here

I have some doubts regarding either the explanation or my understanding.

Lets take the first example with adding a subject_id to a lecturer table.

LECTURE_ID LECTURE_NAME SUBJECT_ID
4          Kolmogorov   6
5          Schmidt      6

So, in fact two lecturers can teach one subject and it is not a 1:1 relation.

Lets take the second example with adding a lecturer_id to a subject table.

SUBJECT_ID SUBJECTE_NAME LECTURER_ID
5          Math          1
6          Arts          1

One lecturer teaches two different subjects.

Is there something I am missing? Or does the foreign key have to appear only once in the table? Because on this page the foreign key (P_id) appeas twice in the "Order" table.

Anni
  • 403
  • 7
  • 19
  • Each relation is correct by itself. But nobody said that both apply at the same time. – juergen d Nov 07 '16 at 17:39
  • if it is always 1 to 1 and well indexed, what does it matter? Alternatively why not just design it so that you have intersect / junction tables for when multiple lecturers teach Math 101. [Junction Tables](http://stackoverflow.com/a/32620163) – Drew Nov 07 '16 at 17:40
  • @juergend The idea was to illustrate 1:1 relation, meaning only one lecturer can teach only one subject. This can be achieved by placing the subject_id in the lecturer table. But I have constructed an example where two different lecturers teach one subject. I think I am missing some constraints on foreign keys. – Anni Nov 07 '16 at 17:47
  • 3
    A 1:1 relationship is essentially a 1:N relationship with a unique constraint. – Allan S. Hansen Nov 07 '16 at 17:49

1 Answers1

1

Well, none of these solutions are optimal. But if you have to choose, I will say that the LECTURER_ID should be a foreign key in the SUBJECT table.

But i would recommend you yo use the Boyce Codd normal form. You would then create three tables in total.

LECTURER

LECTURE_ID(PK) LECTURE_NAME
4              Kolmogorov
5              Schmidt

SUBJECT

SUBJECT_ID(PK) SUBJECTE_NAME
5              Math
6              Arts

LECTURER_SUBJECT

LECTURE_ID(PK)(FK) SUBJECT_ID (PK)(FK)
4                  5
4                  6

This way, a lecturer can have multiple subjects, and a subject can have multiple lecturers.

H0wie12
  • 33
  • 1
  • 4
  • BCNF (Boyce-Codd Normal Form) is not related to using a join table in order to create many-to-many DB relations. BCNF requires all redundancy based on functional dependency to be removed from the schema, which might be possible to achieve with join tables. – webstackdev Jun 22 '18 at 11:49