Relationships and Data Integrity

A relational database is a system in which information flows from one database object to another. For example, if you create an application used to process orders for a car rental business, you can create one table for the cars and a separate table used to process customers orders. When processing an order, you would want to simply select a car in the order processing table. That way, you would avoid entering new information about a particular car every time it is rented. If you do this, you may have one order that has a car named Toyota Corola with the tag number FFG802 and another order with the car Toyoda Corolla with the tag number FFF802 when in fact both orders refer to the same car. Therefore, you should avoid any chance to type the information for the car when processing an order.

To apply the rules of relational databases, you create some types of relationships among the objects of the database.

The transactions among the various objects of a database should make sure information of one object is accessible to another object. The objects that hold information, as we have mentioned already, are the tables.

To manage the flow of information from one table (A) to another table (B), the table that holds the information, A, must make it available to other tables, such as B. There are various issues that must be dealt with:

1. You must be able to uniquely identify each record from a table (A) without any confusion. For example, if you create a list of cars on a table, you should make sure that there is a unique (no duplicate) tag number for each car because each car should have one and must have one tag number. This ensures that there are no duplicate records on the table.
2. A table (A) that holds information should make that information available to other tables (such as B)
3. Two tables must not serve the same purpose. Once you have unique information on each table, one table can make its data available to other tables that need it so that the same information should not be entered in more than one table

These problems are solved by specifying a particular column as the "key" of the table. Such a column is referred to as the primary key.

In a relational database, which is the case for most of the databases you will be creating, each table should have at least one primary key. As an example, a primary key on an car table of a car rental company can be set on a Tag Number field because each car should have a unique tag number. A table can also use more than one column to represent the primary key if you judge it necessary.

Once you have decided that a table will have a primary key, you must decide what type of data that field will hold. If you are building a table that can use a known and obvious field as unique, an example would be the shelf number of a library, you can set its data type as char or varchar and make it a primary key. In many other cases, for example if you cannot decide on a particular field that would hold unique information, an example would be customers Contact Name, you should create your own unique field and make it the Primary Key. Such a field should have an int data type.

To create a primary key in the Microsoft SQL Server Management Studio or Microsoft Visual Studio, in the table, create a column and specify its data type:

• Then, on the toolbar, click the Set Primary Key button
• You can also right-click a column and click Set Primary Key

Here is an example:

To create a primary column using SQL, the primary thing to do is, on the right side of the column definition, type PRIMARY KEY. Here is an example:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL
);

In the SQL, you can give a specific name to a primary key. To do this, you can first create the column. Then, somewhere before the closing parenthesis of the table, specify the primary key column using the following formula:

CONSTRAINT PrimaryKeyName PRIMARY KEY(ColumnName)

In this formula, the CONSTRAINT keyword and the PRIMARY KEY (case-insensitive) expression are required. In the PrimaryKeyName placeholder, enter the name you want to give to the primary key. In the parentheses of the PRIMARY KEY expression, enter the name of the column that will be used as the primary key. Here is an example:

CREATE TABLE Persons
(
    PersonID int identity(1,1) NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID)
);

By convention or tradition, the name of the primary starts with PK_ followed by the name of the table. Here is an example:

USE Exercise2;
GO

CREATE TABLE Persons
(
    PersonID int identity(1,1) NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);
GO

Continuing with our car rental database, imagine a customer comes to rent a car. We already established that it would be redundant to create new car information every time you process a new customer order. Instead, you would get the car's information from the table that holds data for the cars, and provide that information to the table used to process orders. As we described earlier, the car table should be able to provide its data to the other tables that would need that data. To make this flow of information possible from one table to another, you must create a relationship between them.

To make it possible for a table B to receive data from a table A, the table B must have a column that represents the table A. This columns acts as an "ambassador" or a link. As a pseudo-ambassador, the column in the table B almost does not belong to that table: it primarily allows both tables to communicate. For this reason, the column in the table B is called a foreign key.

A foreign key is a column on a table whose data is coming from another table.

To create a foreign key in the Table Design window, in the table that will receive the key, simply create a column with the following rules:

• The column should have the same name as the primary column of the table it represents (but this is not a requirement)
• The column must (this is required) have the same data type as the primary column of the table it represents

Here is an example of a column named GenderID that is a foreign key:

Obviously in order to have information flowing from one table to another, the table that holds the primary information must be created. You can create it before or after creating the other table, as long as you have not established any link between both tables, it does not matter what sequence you use to create them.

The table that contains a primary key and that holds the information that another table would use is called the primary table or the parent table. The table that will receive the information from the other table is called the foreign table or the child table.

To create a foreign key in a table:

1. From the Object Explorer or the Server Explorer, open the child table in Design View
2. Right-click anywhere in the table and click Relationships...
    
   
3. In the Foreign Key Relationships dialog box, click Add
4. A default name would be suggested to you. You can accept or change it. To change the name of the foreign key, on the right side, expand Identity and edit the string in the (Name) field:
    
   
5. If necessary, in the same way, you can create other foreign keys by clicking Add. To delete an existing foreign key, first select it under Selected Relationships and click Delete.
   Once you are ready, click Close

You can also create a foreign key in the SQL. The basic formula to use is:

FOREIGN KEY REFERENCES ParentTableName(ForeignKeyCcolumn) 

The FOREIGN KEY expression and the REFERENCES keyword are required. In the ParentTableName placeholder, enter the name of the primary table that holds the information that will be accessed in the current table. In the parentheses of ParentTableName, enter the name of the primary column of the parent table. Here is an example:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);

Notice that the foreign key does not have an object name as we saw for the primary key. If you do not specify a name for the foreign key, the SQL interpreter would automatically create a default name for you. Otherwise, to create a name, after creating the column, enter the CONSTRAINT keyword followed by the desired name and continue the rest as we saw above. Her is an example:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID int NULL CONSTRAINT FKGenders
                       FOREIGN KEY REFERENCES Genders(GenderID)
);

As mentioned already, a relational database is one in which information flows from one table to another. To prepare the tables for this, you create primary and foreign keys, which we have done so far. Once the tables are ready, you can link them, which is referred to as creating a relationship between two tables. If you did not create a foreign key with SQL code, you can create it when establishing a relationship between two tables.

To create a relationship between two tables

1. Open the child table in the design view
2. Right-click (anywhere in) the table and click Relationships...
   If the (necessary) foreign key does not exist, click Add and specify its name under Identity) on the right side
3. Under Selected Relationships, click the foreign key that will hold the relationship
4. On the right side, expand Tables And Columns Specification
5. Click its ellipsis button
6. In the Primary Key Table combo box, select the parent table that holds the primary data
7. Under the parent table, click and select its primary key column
8. Under Foreign Key Table, make sure the name of the current table is set.
   Under the name of the child table, click and select the name of the foreign key column. Here is an example:
    
   
9. Click OK.
   When a relationship has been created, it would show in the Tables And Column Specification section:
    
   
10. In the same way, you can create other relationships by clicking Add and configuring the link.
    Once you have finished, click Close

1. Start Microsoft Visual C# and create a new Windows Application named Exercise5
2. In the Solution Explorer, right-click Form1.cs and click Rename
3. Type Exercise.cs and press Enter twice
4. Double-click the middle of the form
5. To create a database, including the tables with their primary keys and their foreign keys, implement the Load event as follows:
    

   using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace Exercise5 { public partial class Exercise : Form { public Exercise() { InitializeComponent(); } private void Exercise_Load(object sender, EventArgs e) { using (SqlConnection conBethesdaCarRental = new SqlConnection("Data Source=(local); " + "Integrated Security='SSPI';")) { string strCreateDatabase = "IF EXISTS ( " + "SELECT name " + "FROM sys.databases " + "WHERE name = N'BethesdaCarRental1' " + ") " + "DROP DATABASE BethesdaCarRental1; " + "CREATE DATABASE BethesdaCarRental1"; SqlCommand cmdBethesdaCarRental = new SqlCommand(strCreateDatabase, conBethesdaCarRental); conBethesdaCarRental.Open(); cmdBethesdaCarRental.ExecuteNonQuery(); MessageBox.Show("A database named N'BethesdaCarRental1 has been created"); } using (SqlConnection conBethesdaCarRental = new SqlConnection("Data Source=(local); " + "Database='BethesdaCarRental1'; " + "Integrated Security='SSPI';")) { string strCreateTable = "CREATE TABLE RentalRates( " + "RentalRateID int identity(1, 1) NOT NULL, " + "Daily smallmoney, Weekly smallmoney, " + "Monthly smallmoney, Weekend smallmoney, " + "CONSTRAINT PK_RentalRates PRIMARY KEY (RentalRateID));"; SqlCommand cmdBethesdaCarRental = new SqlCommand(strCreateTable, conBethesdaCarRental); conBethesdaCarRental.Open(); cmdBethesdaCarRental.ExecuteNonQuery(); MessageBox.Show("A table named RentalRates has been created"); } using (SqlConnection conBethesdaCarRental = new SqlConnection("Data Source=(local); " + "Database='BethesdaCarRental1'; " + "Integrated Security='SSPI';")) { string strCreateTable = "CREATE TABLE Employees( " + "EmployeeID int identity(1, 1) NOT NULL, " + "EmployeeNumber nchar(5), " + "FirstName varchar(32), " + "LastName varchar(32) NOT NULL, " + "FullName AS (([LastName]+', ')+[FirstName]), " + "Title varchar(80), " + "HourlySalary smallmoney, " + "CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID));"; SqlCommand cmdBethesdaCarRental = new SqlCommand(strCreateTable, conBethesdaCarRental); conBethesdaCarRental.Open(); cmdBethesdaCarRental.ExecuteNonQuery(); MessageBox.Show("A table named Employees has been created"); } using (SqlConnection conBethesdaCarRental = new SqlConnection("Data Source=(local); " + "Database='BethesdaCarRental1'; " + "Integrated Security='SSPI';")) { string strCreateTable = "CREATE TABLE Customers( " + "CustomerID int identity(1, 1) NOT NULL, " + "DrvLicNumber varchar(50), " + "FullName varchar(80), " + "Address varchar(100) NOT NULL, " + "City varchar(50), " + "State varchar(50), " + "ZIPCode varchar(20), " + "CONSTRAINT PK_Customer PRIMARY KEY (CustomerID));"; SqlCommand cmdBethesdaCarRental = new SqlCommand(strCreateTable, conBethesdaCarRental); conBethesdaCarRental.Open(); cmdBethesdaCarRental.ExecuteNonQuery(); MessageBox.Show("A table named Customers has been created"); } using (SqlConnection conBethesdaCarRental = new SqlConnection("Data Source=(local); " + "Database='BethesdaCarRental1'; " + "Integrated Security='SSPI';")) { string strCreateTable = "CREATE TABLE Cars( " + "CarID int identity(1, 1) NOT NULL, " + "TagNumber varchar(20), " + "Make varchar(50), " + "Model varchar(50) NOT NULL, " + "CarYear smallint, " + "Category varchar(50), " + "PictureLocation varchar(200), " + "CDPlayer bit, " + "DVDPlayer bit, " + "Available bit, " + "CONSTRAINT PK_Car PRIMARY KEY (CarID));"; SqlCommand cmdBethesdaCarRental = new SqlCommand(strCreateTable, conBethesdaCarRental); conBethesdaCarRental.Open(); cmdBethesdaCarRental.ExecuteNonQuery(); MessageBox.Show("A table named Cars has been created"); } using (SqlConnection conBethesdaCarRental = new SqlConnection("Data Source=(local); " + "Database='BethesdaCarRental1'; " + "Integrated Security='SSPI';")) { string strCreateTable = "CREATE TABLE RentalOrders( " + "RentalOrderID int identity(1, 1) NOT NULL, " + "DateProcessed datetime, " + "EmployeeID int Constraint " + "FK_Employees References " + "Employees(EmployeeID) NOT NULL, " + "EmployeeName varchar(80), " + "CustomerID int Constraint " + "FK_Customers References " + "Customers(CustomerID) NOT NULL, " + "CustomerName varchar(80), " + "CustomerAddress varchar(100), " + "CustomerCity varchar(50), " + "CustomerState varchar(50), " + "CustomerZIPCode varchar(20), " + "CarID int Constraint " + "FK_Cars References Cars(CarID) NOT NULL, " + "CarMake varchar(50), " + "CarModel varchar(50), " + "CarYear smallint, " + "CarCondition varchar(50), " + "TankLevel varchar(40), " + "MileageStart int, " + "MileageEnd int, " + "RentStartDate datetime, " + "RendependDate datetime, " + "Days int, " + "RateApplied money, " + "SubTotal money, " + "TaxRate decimal(6, 2), " + "TaxAmount money, " + "OrderTotal money, " + "OrderStatus varchar(50), " + "CONSTRAINT PK_RentalOrder " + "PRIMARY KEY (RentalOrderID));"; SqlCommand cmdBethesdaCarRental = new SqlCommand(strCreateTable, conBethesdaCarRental); conBethesdaCarRental.Open(); cmdBethesdaCarRental.ExecuteNonQuery(); MessageBox.Show("A table named RentalOrder has been created"); } } } }

6. Execute the application
    
7. Close the form and return to your programming environment
8. In the source file, delete the whole content of the Load event
9. In the Server Explorer, right-click the Data Connections node and click Add Connection
10. In the Server Name combo box of the Add Connection dialog box, type (local) or select the name of the server
11. In the Select Or Enter A Database Name combo box, select BethesdaCarRental1 and click Test Connection
12. Click OK twice

A diagram is a window that visually displays the relationships among the tables of a database. To create a diagram:

1. In the Object Explorer in Microsoft SQL Server Management Studio or in the Server Explorer in Microsoft Visual Studio, in the database node, you can click Database Diagrams
2. A dialog box will inform you that this database does not have a diagram. Read the message and click Yes
3. Right-click Database Diagrams and click New Database Diagram
4. In the Add Table dialog box, click each table and click the Add.
   Alternatively, you can double-click a table to add it
5. In the Add Table dialog box, you can click Close.
   On the toolbar, you can click the Zoom button and select a larger or smaller value.
   To move a table, you can drag its title bar. Here is an example:
    
   
   
6. To establish a relationship, you can click the gray box on the left of any column from the parent table and drop it on any column in the child table. A better way is to click gray box of the primary key column from the parent table, drag that box then drop it on the foreign key column of the child table. Here is an example:
    
   
7. A Tables and Columns dialog box would come up. It would display the column that was dragged and the column on which you dropped.
   If you had selected just any column, it would show but it may not be the one you wanted to drag; that is, it may not be the actual column that is supposed to manage the relationship.
   Regardless, under Primary Key Table, you should select the parent table
8. Under the parent table, select its primary column
9. Under Foreign Table, select the foreign key column. Here is an example:
    
   
10. Once you are ready, click OK. A link would be created between the tables
     
    
11. In the same way, you can create other relationships.
    When you have finished, you can save and close the database

1. In the Server Explorer, expand the BethesdaCarRental1 node and click the + button of the Database Diagrams node
2. A dialog box will inform you that this database does not have a diagram. Read the message and click Yes
3. Right-click Database Diagrams and click Add New Diagram
4. In the Add Table dialog box, click Employees and click Add
5. Double-click Cars to add it
6. In the same way, add the Customers and the RentalOrders tables
7. On the Add Table dialog box, click Close.
   Notice that, based on how we created the database and its objects, the relationships have been created already
8. To save the diagram, on the Standard toolbar, click Save
9. Set its name to dgmBethesdaCarRental and click OK
10. Close the window

On a typical database, information comes and goes. For a car rental company, car information is created and deleted on a regular basis. When information about a car is deleted, there is concern about the rental orders related to that car. Referential integrity allows you to manage these aspects of a database. You need to make sure that when data is deleted from a parent table, the child tables are notified and their related records are deleted also. When information is changed on a parent table, the related information is changed in the child tables.

To manage referential integrity, you use the Foreign Key Relationships dialog box. You can access it from the design view of a table or from the diagram window.

1. In the Server Explorer, expand the Tables node under BethesdaCarRental1.
   Right-click RentalOrders and click Open Table Definition
2. Right-click in the table and click Relationships
3. Under Selected Relationships, click FK_Customers. In the right section, expand INSERT And UPDATE Specification
4. Click Delete Rule. In its combo box, select Cascade
5. Click Update Rule. In its combo box, select Cascade:
    
   
6. In the same way, specify the following
    

   Foreign Key	Delete Rule	Update Rule
   FK_Cars	Cascade	Cascade
   FK_Customers	Cascade	Cascade
   FK_Employees	Cascade	Cascade

7. Click Close
8. Save and close the table