1 Introduction

In my last post I showed how to set up and use a 1:1 relationship between two tables.

Now I want to show the same for a 1:m relationship.

2 The assumed Situation

Let’s say we are asked to develop a database for a school. In this school there are several teachers who are assigned to different classes with n pupils. A typical 1:m relationship between the teacher and the different students in a class.

I use the Modified Chen Notation (MC Notation) below.

3 Setting up the database and the required tables

Since I would like to have the output of SSMS always in English I use the following syntax.

SET LANGUAGE ENGLISH

How I create the database and the tables can be read in my post Create a Database.

CREATE DATABASE School_Class;


USE School_Class;

CREATE TABLE Pupils
    (Pupils_ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL)
    ;


CREATE TABLE Teacher
    (Teacher_ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL,
    Subject_ VARCHAR(100) NOT NULL)
    ;

Of course, you also have the option of storing the foreign key when creating the tables so that you don’t have to insert it afterwards, as in this example (see next step). Of course, you have to pay attention to the order in which you create the tables. I have described this in my previous post here.

4 Connecting the two tables

Since this is a 1:m relationship, the foreign key should be stored in the table where multiple matches are possible. In this case it would be the pupil table.

ALTER TABLE Pupils ADD Teacher_ID INT NOT NULL FOREIGN KEY REFERENCES Teacher(Teacher_ID);

The underlying ERD now looks like this:

Let’s also look again at the relationship of the two tables under Views in SSMS. I have described how to do this here.

Using the infinity symbol in the output, we can see that a Primary Key is referencing a Foreign Key.

If you want to see a representation via Oracle Data Modeler, check out this chapter of my post Oracle SQL Developer Data Modeler: ERDs from old Posts with Oracle Data Modeler

5 Filling the tables

To see how we can use this relationship we first fill the two tables with values.

Important!

Again, it is important to pay attention to the order of filling. The Teacher table must be filled first, as the foreign key (‘Teacher_ID’) is stored in the Pupils table and references the other table (Teacher).

Filling the Teacher-Table:

INSERT INTO Teacher (Teacher_ID, First_Name, Last_Name, Subject_) VALUES (317, 'Andrew', 'Evergreen', 'History')

Filling the Pupils-Table:

INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (1, 'Max', 'Steel', 317)
INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (2, 'Jessy', 'Williams', 317)
INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (3, 'Marc', 'Pike', 317)
INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (4, 'Emily', 'Taylor', 317)
INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (5, 'Alex', 'Smith', 317)

Have a look at the two filled tables:

SELECT * FROM Pupils
SELECT * FROM Teacher

Now we perform a simple merge of the two tables using the Primary Key and Foreign Key.

SELECT * 
    FROM Pupils JOIN Teacher 
    ON Pupils.Teacher_ID = Teacher.Teacher_ID;

Again, I would like to point out that only a simple join was made for illustrative purposes. I will report on the topic of how to execute joins correctly and sensibly in a later article.

6 Conclusion

In this post I showed how to set up a 1:m relationship between two tables and make sense of it.

In my next post I will explain the structure and use of an m:m relationship.