Home

Relationships and Data Integrity

 
 

Establishing a Relationship

 

Introduction

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.

Creating a Relationship

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 Ellipsis
  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:
     
    Foreign Key
  10. In the same way, you can create other relationships by clicking Add and configuring the link.
    Once you have finished, click Close

Practical LearningPractical Learning: Creating Relationships

  1. Start Microsoft Visual Basic and create a new Windows Application named Exercise5
  2. In the Solution Explorer, right-click Form1.vb and click Rename
  3. Type Exercise.vb 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:
     
    Imports System.Data.SqlClient
    
    Public Class Exercise
    
        Private Sub Exercise_Load(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles MyBase.Load
            Using conBethesdaCarRental As SqlConnection = _
             	New SqlConnection("Data Source=(local); " & _
           				  "Integrated Security='SSPI';")
    
                Dim strCreateDatabase As String = "IF EXISTS ( " & _
             		"SELECT name " & _
             		"FROM sys.databases " & _
            		"WHERE name = N'BethesdaCarRental1' " & _
             		") " & _
             		"DROP DATABASE BethesdaCarRental1; " & _
             		"CREATE DATABASE BethesdaCarRental1"
    
                Dim cmdBethesdaCarRental As SqlCommand = _
                    New SqlCommand(strCreateDatabase, conBethesdaCarRental)
    
                conBethesdaCarRental.Open()
                cmdBethesdaCarRental.ExecuteNonQuery()
    
                MsgBox("A database named N'BethesdaCarRental1 has been created")
            End Using
    
            Using conBethesdaCarRental As SqlConnection = _
             	New SqlConnection("Data Source=(local); " & _
              			  "Database='BethesdaCarRental1'; " & _
              			  "Integrated Security='SSPI';")
    
                Dim strCreateTable As String = "CREATE TABLE RentalRates( " & _
             		"RentalRateID int identity(1, 1) NOT NULL, " & _
             		"Daily smallmoney, Weekly smallmoney, " & _
             		"Monthly smallmoney, Weekend smallmoney, " & _
             		"CONSTRAINT PK_RentalRates PRIMARY KEY (RentalRateID));"
    
                Dim cmdBethesdaCarRental As SqlCommand = _
                    New SqlCommand(strCreateTable, conBethesdaCarRental)
    
                conBethesdaCarRental.Open()
                cmdBethesdaCarRental.ExecuteNonQuery()
                MsgBox("A table named RentalRates has been created")
            End Using
    
            Using conBethesdaCarRental As SqlConnection = _
         		New SqlConnection("Data Source=(local); " & _
             			  "Database='BethesdaCarRental1'; " & _
             			  "Integrated Security='SSPI';")
    
                Dim strCreateTable As String = "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));"
    
                Dim cmdBethesdaCarRental As SqlCommand = _
             	New SqlCommand(strCreateTable, conBethesdaCarRental)
    
                conBethesdaCarRental.Open()
                cmdBethesdaCarRental.ExecuteNonQuery()
                MsgBox("A table named Employees has been created")
            End Using
    
            Using conBethesdaCarRental As SqlConnection = _
             	New SqlConnection("Data Source=(local); " & _
                    		  "Database='BethesdaCarRental1'; " & _
                    		  "Integrated Security='SSPI';")
    
                Dim strCreateTable As String = "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));"
    
                Dim cmdBethesdaCarRental As SqlCommand = _
                New SqlCommand(strCreateTable, conBethesdaCarRental)
    
                conBethesdaCarRental.Open()
                cmdBethesdaCarRental.ExecuteNonQuery()
                MsgBox("A table named Customers has been created")
            End Using
    
            Using conBethesdaCarRental As SqlConnection = _
                New SqlConnection("Data Source=(local); " & _
                		"Database='BethesdaCarRental1'; " & _
                		"Integrated Security='SSPI';")
    
                Dim strCreateTable As String = "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));"
    
                Dim cmdBethesdaCarRental As SqlCommand = _
             New SqlCommand(strCreateTable, conBethesdaCarRental)
    
                conBethesdaCarRental.Open()
                cmdBethesdaCarRental.ExecuteNonQuery()
                MsgBox("A table named Cars has been created")
            End Using
    
            Using conBethesdaCarRental As SqlConnection = _
             New SqlConnection("Data Source=(local); " & _
                    "Database='BethesdaCarRental1'; " & _
                    "Integrated Security='SSPI';")
    
                Dim strCreateTable As String = "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));"
    
                Dim cmdBethesdaCarRental As SqlCommand = _
             	New SqlCommand(strCreateTable, conBethesdaCarRental)
    
                conBethesdaCarRental.Open()
                cmdBethesdaCarRental.ExecuteNonQuery()
                MsgBox("A table named RentalOrder has been created")
            End Using
        End Sub
    End Class
  6. Execute the application
     
    Bethesda Car Rental: Database Creation
    Bethesda Car Rental: Rental Rates Table Creation
    Bethesda Car Rental: Employees Table Creation
    Bethesda Car Rental: Customers Table Creation
    Bethesda Car Rental: Cars Table Creation
    Bethesda Car Rental: Rental Orders Table Creation
  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

Diagrams

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

Practical LearningPractical Learning: Creating a Diagram

  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

Referential Integrity

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.

Practical LearningPractical Learning: Managing Referential Integrity

  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:
     
    Foreign Key
  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
 

Home Copyright © 2008-2012 FunctionX