Home

The Types of Relationships

 

Creating and Using Relationships

 

A One-to-Many Relationship: A Re-Introduction to Relationships

In a typical database, you can create, among other things, two tables that each has a primary key and one of them has a foreign key. As seen previously, the foreign key allows a child table to get records from a parent table.

Normally, each record in the child table gets 0 or only one value from the parent table and a record in the parent table can provide one of its values to many records of the child table. An example would be a list of employees where each employee belongs to a department. Obviously, each employee can belong to only one department but many employees can belong to the same department. This can be illustrated as follows:

One-to-Many Relationship

This type of relationship is referred to as one-to-many. This is the most regular type of relationship used in a relational database and that's the type we have used so far.

Practical LearningPractical Learning: Creating a One-to-Many Relationship

  1. Start the computer and log in
  2. Launch Microsoft SQL Server
  3. Make sure that, in the Authentication combo box, Windows Authentication is selected and click Connect
  4. On the Standard toolbar, click the New Query button
  5. Type the starting code of the Monson University database
  6. On the main menu, click Query -> Execute
  7. In the Object Explorer, expand Databases. If you don't see MonsonUniversity1, right-click Databases and click Refresh
  8. Under MonsonUniversity1, right-click Database Diagram and click New Database Diagram
  9. Read the message box and click Yes
  10. In the Add Table dialog box, click Add continuously until the Tables property page is empty
  11. Click Close
     
    Monson University
  12. Leave the diagram opened and click the SQLQuery1 tab
  13. Click inside the Query window and press Ctrl + A to select everything
  14. To see the list of courses offered, type:
    SELECT * FROM Academics.Courses;
    GO
  15. To see the result, on the main menu, click Query -> Execute
  16. Click inside the Query window and press Ctrl + A to select everything
  17. To see the records of the first 10 students, type:
    SELECT TOP 10 * FROM Admissions.Students;
    GO
  18. To see the result, on the main menu, click Query -> Execute
  19. Click inside the Query window and press Ctrl + A to select everything
  20. To see the employees of the human resources department, type:
    SELECT * FROM Administration.Employees
    WHERE DepartmentCode = N'HRMN';
    GO
  21. To see the result, on the main menu, click Query -> Execute

A Variance to a One-To-Many  Relationship

Mutual reference is a scenario in which each of two tables references the other. As a variant to a one-to-many relationship, some records of a table A may get their foreign value from a table B, then some records of table B may get their foreign value from a table C, and finally some records of table C would get their foreign value from table A.

Another variant is where some records of a table A would get their foreign value from a table B but also some records of the table B would get their foreign value from table A. To illustrate, once again imagine you have a table of employees and each employee is recorded as belonging to a certain department. Obviously, an employee can (should) belong to only one department. This can be illustrated as follows:

A department for one or many employees

For each department, you may want to specify who the manager is. Obviously, the manager must be an employee, from the table of employees. This can be illustrated as follows:

A manager for each department - the manager is an employee

Here is an example of creating the tables and their constraints:

CREATE DATABASE Exercise;
GO
USE Exercise;
GO
CREATE SCHEMA Management;
GO
CREATE SCHEMA Personnel;
GO
CREATE TABLE Management.Departments
(
	DepartmentCode nchar(4) not null,
	Name nvarchar(50) not null,
	EmployeeNumber nchar(6),
	CONSTRAINT PK_Departments Primary Key(DepartmentCode)
);
GO
CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(6) not null,
	FirstName nvarchar(20),
	LastName nvarchar(20) not null,
	Title nvarchar(50),
	HourlySalary money,
	DepartmentCode nchar(4)
		CONSTRAINT FK_Departments References
			Management.Departments(DepartmentCode),
	CONSTRAINT PK_Employees Primary Key(EmployeeNumber)
);
GO

If you decide to create a diagram (especially if you didn't create the primary and foreign keys), you should have a link going from each table to the other, using the appropriate fields. Here is an example:

Mutual References

When creating the records, you can proceed as done so far. Here are examples:

INSERT Management.Departments(DepartmentCode, Name)
VALUES(N'HRMN', N'Human Resources'),
	  (N'ITEC', N'Information Technology'),
	  (N'PRSN', N'Personnel');
GO
INSERT Personnel.Employees--(EmployeeNumber, FirstName, LastName, Title, HourlySalary, DepartmentCode)
VALUES(N'792702', N'Frank', N'Cassini', N'General Manager', 30.25, N'HRMN'),
      (N'249441', N'Patrick', N'Levine', N'Regional Manager', 28.46, N'HRMN'),
      (N'302484', N'Catherine', N'Borrow', N'Shift Supervisor', 25.72, N'PRSN'),
      (N'485052', N'Jerry', N'Fesman', N'Head Cashier', 18.64, N'PRSN'),
      (N'279475', N'Alex', N'Simkins', N'Intern', 12.48, N'PRSN'),
      (N'908047', N'Grace', N'McDermott', N'Cashier', 14.72, N'PRSN'),
      (N'395822', N'Craig', N'Newman', N'IT Support', 20.26, N'ITEC'),
      (N'381848', N'John', N'Hough', N'Cashier', 13.52, N'PRSN'),
      (N'300724', N'Matt', N'Kern', N'Accountant', 24.58, N'HRMN'),
      (N'974115', N'Elsa', N'Steinberg', N'Webmaster', 16.94, N'ITEC'),
      (N'974005', N'David', N'Miller', N'Intern', 10.48, N'ITEC'),
      (N'273941', N'Jessica', N'Redding', N'Cashier', 12.63, N'PRSN');
GO
UPDATE Management.Departments
SET EmployeeNumber = N'792702' WHERE DepartmentCode = N'HRMN';
GO
UPDATE Management.Departments
SET EmployeeNumber = N'249441' WHERE DepartmentCode = N'ITEC';
GO
UPDATE Management.Departments
SET EmployeeNumber = N'302484'  WHERE DepartmentCode = N'PRSN';
GO

Employees

Departments

Using joins, you can create a statement that would show the actual values of the fields. Here is an example:

SELECT Personnel.Employees.EmployeeNumber,
       Personnel.Employees.FirstName,
       Personnel.Employees.LastName,
       Personnel.Employees.Title,
       Personnel.Employees.HourlySalary,
       Management.Departments.Name
FROM   Personnel.Employees INNER JOIN Management.Departments
	ON Personnel.Employees.DepartmentCode = Management.Departments.DepartmentCode

Joins

A One-to-One Relationship: A Self-Referencing Table

Imagine you have two lists where a value from one list can provide 0 or 1 value to a record of the other list, and only one record of a child list can get its foreign value from the other list. This can be illustrated as follows:

One-to-One Relationship

This type of relationship is referred to as one-to-one. To give you an example, imagine you have a list of employees and you want to specify the supervisor or manager of each employee. This can be illustrated as follows:

An employee who is a manager of other employees

By definition, a manager is primarily an employee like any other. This means that the primary information of a manager is the same as that of any other employee. This also implies that if you had to use separate tables, one for managers and another for employees, you would have two similar tables, and there is a chance that information would be duplicated in both tables. As a result, a one-to-one relationship is usually created using only one table, in which case the table would reference itself. In other words, some records would reference (be linked to) other records of the same table. This can be illustrated as follows:

An employee who is a manager of other employees

Here is an example that implements this snenario:

USE master;
GO
CREATE DATABASE Exercise;
GO
USE Exercise;
GO
CREATE TABLE Employees
(
	EmployeeNumber nchar(10) not null,
	FirstName nvarchar(20),
	LastName nvarchar(20) not null,
	Title nvarchar(50),
	Supervisor nchar(10) null,
	HourlySalary money
);
GO
INSERT Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'792702', N'Frank', N'Cassini', N'General Manager', 30.25);
INSERT Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'249441', N'Patrick', N'Levine', N'Regional Manager', 28.46);
INSERT Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'302484', N'Catherine', N'Borrow', N'Shift Supervisor', 25.72);
INSERT Employees
VALUES(N'485052', N'Jerry', N'Fesman', N'Head Cashier', N'792702', 18.64),
      (N'279475', N'Alex',  N'Simkins', N'Intern', N'302484', 12.48),
      (N'908047', N'Grace', N'McDermott', N'Cashier', N'302484', 14.72),
      (N'395822', N'Craig', N'Newman', N'IT Support', N'249441', 20.26),
      (N'381848', N'John',  N'Hough', N'Cashier', N'302484', 13.52),
      (N'300724', N'Matt',  N'Kern', N'Accountant', N'792702', 24.58),
      (N'974115', N'Elsa',  N'Steinberg', N'Webmaster', N'302484', 16.94),
      (N'974005', N'David', N'Miller', N'Intern', N'249441', 10.48),
      (N'273941', N'Jessica', N'Redding', N'Cashier', N'302484', 12.63);
GO

The regular SELECT * statement of this table only shows the list of employees and the supervisor of each employee appears only as a number, which can make it difficult to actually identify the supervisor:

Employees

By using a join, you can create a SELECT statement where the JOIN is ON itself. When formulating the statement, you must use the table twice, in which case you should (must) create an alias for each. Here is an example:

SELECT staff.FirstName, staff.LastName, staff.Title,
       staff.HourlySalary, staff.EmployeeNumber,
       managers.LastName + N', ' + managers.FirstName AS Manager
FROM Employees staff JOIN Employees managers
ON staff.Supervisor = managers.EmployeeNumber;
GO

This would produce:

Employees

Just as done for many tables in a database, you can create a table that relates to itself. To start, the table must have a primary key. Since you are creating an actual relationship, the table must have a foreign key and that key must reference the primary key of the same table. Of course, the name of the column that represents the foreign key must have a different name than that of the primary key column. Here is an example:

CREATE DATABASE Exercise;
GO
USE Exercise;
GO
CREATE TABLE Employees
(
	EmployeeID int identity(1, 1) not null,
	EmployeeNumber nchar(10) not null,
	FirstName nvarchar(20),
	LastName nvarchar(20) not null,
	Title nvarchar(50),
	ManagerID int
		CONSTRAINT FK_Employees References Employees(EmployeeID),
	HourlySalary money,
	CONSTRAINT PK_Employees Primary Key(EmployeeID)
);
GO
INSERT Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'792702', N'Frank', N'Cassini', N'General Manager', 30.25);
INSERT Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'249441', N'Patrick', N'Levine', N'Regional Manager', 28.46);
INSERT Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'302484', N'Catherine', N'Borrow', N'Shift Supervisor', 25.72);
INSERT Employees(EmployeeNumber, FirstName, LastName, Title, ManagerID, HourlySalary)
VALUES(N'485052', N'Jerry', N'Fesman', N'Head Cashier', 1, 18.64),
      (N'279475', N'Alex', N'Simkins', N'Intern', 3, 12.48),
      (N'908047', N'Grace', N'McDermott', N'Cashier', 3, 14.72),
      (N'395822', N'Craig', N'Newman', N'IT Support', 2, 20.26),
      (N'381848', N'John', N'Hough', N'Cashier', 3, 13.52),
      (N'300724', N'Matt', N'Kern', N'Accountant', 1, 24.58),
      (N'974115', N'Elsa', N'Steinberg', N'Webmaster', 3, 16.94),
      (N'974005', N'David', N'Miller', N'Intern', 2, 10.48),
      (N'273941', N'Jessica', N'Redding', N'Cashier', 3, 12.63);
GO

If you create a diagram for the table, it would have a curb that goes from and lands on itself. Here is an example:

Diagram

As seen previously, you can then create a join that gets the records from the table. Here is an example:

SELECT staff.EmployeeID AS [Empl ID], staff.EmployeeNumber As [Empl #],
       staff.FirstName AS [First Name],
       staff.LastName AS [Last Name], staff.Title,
       staff.HourlySalary AS Salary, 
       managers.LastName + N', ' + managers.FirstName AS Manager
FROM Employees staff JOIN Employees managers
ON staff.ManagerID = managers.EmployeeID;
GO

This would produce:

Employees

A Many-To-Many Relationship: Junction Tables

Consider A database for a university with its tables of students and courses:

CREATE DATABASE MonsonUniversity1;
GO
USE MonsonUniversity1;
GO
CREATE SCHEMA Studies;
GO
CREATE SCHEMA Admissions;
GO
CREATE TABLE Studies.Courses
(
	CourseCode nchar(10) not null,
	CourseName nvarchar(100),
	Credits smallint not null,
	CourseDescription nvarchar(max),
	CONSTRAINT PK_Courses PRIMARY KEY(CourseCode)
);
GO

INSERT INTO Studies.Courses(CourseCode, CourseName, Credits)
VALUES(N'CMIS 101', N'Introduction to Problem Solving and Algorithm Design', 3),
      (N'CMIS 170', N'Introduction to XML', 3),  
      (N'CMIS 320', N'Relational Databases', 3),
      (N'CMIS 420', N'Advanced Relational Databases', 3),
      (N'CMST 306', N'Introduction to Visual Basic Programming', 3),
      (N'CMST 385', N'Internet and Web Design', 3);
GO

CREATE TABLE Admissions.Students
(
	StudentNumber nchar(20) not null,
	FirstName nvarchar(20),
	MiddleName nvarchar(20),
	LastName nvarchar(20),
	CONSTRAINT PK_Students PRIMARY KEY(StudentNumber)
);
GO
INSERT INTO Admissions.Students
VALUES(N'8130480', N'Frank', N'Daniel', N'Bigg'),
      (N'2946681', N'Marianne', NULL, N'Roberts'),
      (N'7113159', N'Angele', N'Cecilia', N'Douala'),
      (N'2049220', N'James', NULL, N'Davidson'),
      (N'7927413', N'Larry', N'Herbert', N'Bibang'),
      (N'2048800', N'Ann', NULL, N'Roberts'),
      (N'9701328', N'Celia', N'Gabriela', N'Edison'),
      (N'9720048', N'Hermine', NULL, N'Nkolo');
GO

Imagine you have a list of students who are registering for courses in a new semester:

  • A student can register for one course (a type of one-to-one relationship)
  • A student can register for many courses

Many-to-Many Relationship

  • Put it another way, many courses can have been registered by one student
  • Many students can register for the same course:

Many-to-Many Relationship

  • If many students can register for the same course, this means that one course can have been registered by many students:

Many-to-Many Relationship

  • As a result, many students can register for many courses (or many students can share many courses):

Many-to-Many Relationship

This type of relationship is referred to as many-to-many.

Most of the time, to implement a many-to-many relationship, besides the two tables that hold the normal records, you would create one more table referred to as a junction table. The job of the junction table is to get a value from one table, associate it to the desired value of another table, repeat this step as many times as necessary, and produce the necessary list. This can be illustrated as follows:

Many-to-Many Relationship

Obviously, the junction table should (must) have a foreign key for each of the concerned tables. Here is an example of such a table:

CREATE TABLE Admissions.Registrations
(
    StudentNumber nchar(20),
    CourseCode nchar(10),
);

In reality, you can add as many fields as  you judge necessary. Here is an example:

CREATE TABLE Admissions.Registrations
(
    RegistrationID int identity(1, 1) not null,
    StudentNumber nchar(20),
    CourseCode nchar(10),
    CONSTRAINT PK_Registrations PRIMARY KEY(RegistrationID)
);
GO

As mentioned already, when creating the records, you get a value from one table and another value from the other table. Here are examples:

INSERT INTO Admissions.Registrations(StudentNumber, CourseCode)
VALUES(N'8130480', N'CMIS 101'),
      (N'2946681', N'CMIS 170'),
      (N'7113159', N'CMST 385'),
      (N'2049220', N'CMIS 320'),
      (N'7927413', N'CMIS 320'),
      (N'2946681', N'CMST 306'),
      (N'2048800', N'CMIS 420'),
      (N'2049220', N'CMST 306'),
      (N'7113159', N'CMST 306'),
      (N'9701328', N'CMIS 170'),
      (N'9720048', N'CMIS 420'),
      (N'9701328', N'CMST 306');
GO

Practical LearningPractical Learning: Creating a Many-To-Many Relationship

  1. Click inside the Query window and press Ctrl + A to select everything
  2. Type:
    USE MonsonUniversity1;
    GO
    CREATE TABLE Admissions.Registrations
    (
        StudentNumber nchar(20),
        CourseCode nchar(10),
    );
    GO
    INSERT INTO Admissions.Registrations(StudentNumber, CourseCode)
    VALUES(N'24795711', N'CMIS 101'),(N'94708257', N'ACCT 220'),
          (N'20409220', N'BMGT 312'),(N'71513159', N'PSYC 306'),
          (N'94708257', N'BEHS 220'),(N'20946681', N'CMIS 170'),
          (N'29480759', N'CMIS 170'),(N'82580947', N'ENGL 240'),
          (N'71513159', N'HIST 104'),(N'20409220', N'CMIS 320'),
          (N'94708257', N'CMIS 320'),(N'92084157', N'BEHS 220'),
          (N'94708257', N'WRTG 288'),(N'71513159', N'CMST 306'),
          (N'94708257', N'CMIS 420'),(N'29480759', N'WRTG 388'),
          (N'20409220', N'CMST 306'),(N'71513159', N'CMST 306'),
          (N'82580947', N'CMIS 420'),(N'24795711', N'BEHS 220'),
          (N'92084157', N'CMST 306'),(N'94708257', N'ACCT 220');
    GO
  3. To execute, on the SQL Editor toolbar, click the Execute button Execute
  4. Click inside the Query window and press Ctrl + A to select everything
  5. Type the following:
    SELECT * FROM Admissions.Registrations;
    GO
  6. To see the result of the above statement, press F5
 
 
 

A Variance to a Many-To-Many Relationship

As a variance of a many-to-many relationship, instead of just two tables, you can create a junction table that unites three or more tables. Once again, consider the example of students registering for courses:

  • When a semester starts, a student must select a semester for the courses he wants to attend, and there are many courses available for that semester. This means that the student would select a semester and select one or more courses he wishes to attend. This can be illustrated as follows:

Many-to-Many Relationship

  • Many students can register for the same semester:

Many-to-Many Relationship

  • To help them plan their academic career, many schools allow a student to register courses for more than one semester. In this case, a student can select (an) additional semester(s) and select courses she wants to attend during each semester (in this example, we don't account for a student who is repeating (re-taking) a course):

Many-to-Many Relationship

  • As a result, over the course of academic years:
    • A student would have attended many semesters:
       
      Many-to-Many Relationship
    • Many students would have attended many semesters (or a semester can "have" many students):
       
      Many-to-Many Relationship
    • A semester would show courses that were attended by many students:
       
      Many-to-Many Relationship
    • Many semesters would show many courses that were available
    • A course can be offered in many semesters
    • Many courses can be offered in many semesters
    • A course can have many students who attended it
       
      Many-to-Many Relationship
    • A course can have many students who attended it during different semesters
    • The records of many students would show many semesters they used to attend many courses

Many-to-Many Relationship

You create the junction table the same way you do for two tables: Add a foreign key for each of the tables. During data entry:

  1. Select a value from the first table. For our example, this would be the student number of the student who is registering for the course
  2. Select a value from the second table. The value must appropriately correspond to that of the first table. For our example, this would be the semester during which the selected student wants to attend one or more courses
  3. Select a value from the third table. The value must appropriately correspond to that of the first table and that of the second table. For our example, this would be the course that the student selected in the first table wants to attend during the semester selected in the second tabe

The beauty of this variant of a many-to-many relationship would be revealed during data analysis when you want to find out

  • Whether a certain course is available for a certain semester (for one reason or another, some courses are not offered during some semesters)
  • What (the names of) students registered for what semester. This information helps with school statistics (enrollment, etc)
  • What courses a student attended during a certain semester
    • Did the student attend that course already?
    • How many courses (credits) has the student accumulated already?
    • Based on the student's major, is the course required for the major? Is it required for the minor? Or is it an elective?
  • How many students have already registered for a certain course that would start soon. That would allow you to find out whether:
    • There are still seats for the course and if so, how many
    • There are enough students or the course should be canceled

Practical LearningPractical Learning: Expanding a Many-To-Many Relationship

  1. Click inside the Query window and press Ctrl + A to select everything
  2. Type the following:
    DROP TABLE Admissions.Registrations;
    GO
    CREATE TABLE Admissions.Registrations
    (
    	RegistrationID int identity(10001, 1) not null,
    	SemesterID int not null,
    	StudentNumber nchar(8),
    	CourseCode nchar(8),
    	CONSTRAINT PK_Registrations PRIMARY KEY(RegistrationID)
    );
    GO
    INSERT INTO Admissions.Registrations(SemesterID, StudentNumber, CourseCode)
    VALUES(10001, N'24795711', N'CMIS 101'),(10003, N'94708257', N'ACCT 220'),
          (10001, N'20409220', N'BMGT 312'),(10001, N'71513159', N'PSYC 306'),
          (10002, N'94708257', N'BEHS 220'),(10002, N'20946681', N'CMIS 170'),
          (10004, N'29480759', N'CMIS 170'),(10003, N'82580947', N'ENGL 240'),
          (10001, N'71513159', N'HIST 104'),(10001, N'20409220', N'CMIS 320'),
          (10002, N'94708257', N'CMIS 320'),(10001, N'92084157', N'BEHS 220'),
          (10004, N'94708257', N'WRTG 288'),(10002, N'71513159', N'CMST 306'),
          (10001, N'94708257', N'CMIS 420'),(10004, N'29480759', N'WRTG 388'),
          (10002, N'20409220', N'CMST 306'),(10002, N'71513159', N'CMST 306'),
          (10003, N'82580947', N'CMIS 420'),(10002, N'24795711', N'BEHS 220'),
          (10002, N'92084157', N'CMST 306'),(10004, N'94708257', N'ACCT 220');
    GO
  3. To execute, press F5
  4. Click the Diagram_0 tab
  5. Right-click inside the window and click Add Table.
    If you don't see the Registrations (Admissions) table, click Refresh
  6. Click Add
  7. Click Close
  8. From the Courses (Academics) table, drag CourseCode and drop it on top of CourseCode in the Registrations (Academics) table
  9. Click OK
  10. From the Semesters (Admissions) table, drag SemesterID and drop it on top of SemesterID in the Registrations (Academics) table
  11. Click OK
  12. From the Students (Admissions) table, drag StudentNumber and drop it on top of StudentNumber in the Registrations (Admissions) table
  13. Click OK
     
    Monson University
  14. Close the diagram window
  15. When asked whether you want to save, click Yes
  16. Set the name to dgmMonsonUniversity and press Enter
  17. Click the top section of the Query window and press Ctrl + A
  18. Type:
    SELECT * FROM Admissions.Registrations;
    GO
  19. To see all registrations, press F5
 
 
   
 

Previous Copyright © 2008-2011 FunctionX.com Next