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:

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.

1. Start the computer and log in
2. Open the Monson University 1 file
3. Select and copy its contents
4. Launch Microsoft SQL Server
5. Make sure that, in the Authentication combo box, Windows Authentication is selected and click Connect
6. On the Standard toolbar, click the New Query button
7. Paste the code in it
8. On the main menu, click Query -> Execute
9. In the Object Explorer, expand Databases. If you don't see MonsonUniversity1, right-click Databases and click Refresh
10. Under MonsonUniversity1, right-click Database Diagram and click New Database Diagram
11. Read the message box and click Yes
12. In the Add Table dialog box, click Add continuously until the Tables property page is empty
13. Click Close
     
    
14. Leave the diagram opened and click the SQLQuery1 tab
15. Click inside the Query window and press Ctrl + A to select everything
16. To see the list of courses offered, type:

    SELECT * FROM Academics.Courses;
    GO

17. To see the result, on the main menu, click Query -> Execute
18. Click inside the Query window and press Ctrl + A to select everything
19. To see the records of the first 10 students, type:

    SELECT TOP 10 * FROM Admissions.Students;
    GO

20. To see the result, on the main menu, click Query -> Execute
21. Click inside the Query window and press Ctrl + A to select everything
22. To see the employees of the human resources department, type:

    SELECT * FROM Administration.Employees
    WHERE DepartmentCode = N'HRMN';
    GO

23. To see the result, on the main menu, click Query -> Execute

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:

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:

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:

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

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

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:

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:

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:

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:

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:

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:

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:

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

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

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

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

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:

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

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
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