Databases can also implement aone-to-one relationshipand a many-to-many relationship.
A teacher can teach multiple classes, but the course would not have the same relationship with the teacher.
Therefore, for each record in a Teachers table, there could be many records in the Courses table.
Erik Isakson / Getty Images
This example illustrates a one-to-many relationship: one teacher to multiple courses.
Let’s see why.
We might design a Teachers and Courses table like this:
What if Carmen teaches two or more courses?
We have two options with this design.
It makes it difficult to search for data.
More importantly, the data could become inconsistent.
For example, what if Carmen’s name changed?
The 2NF rule achieves this by separating subsets of data into multiple tables and creating a relationship between them.
Here, we removed the Course column in the Teachers table:
And here is the Courses table.
This arrangement tells us Carmen teaches both Biology and Math and that Jorge teaches English.
