WebEnabled Decision Support Systems

1
Web-Enabled Decision Support Systems

• Relationships Linking Access Tables

Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 7.1 Introduction
• 7.2 Defining Relationships in the Relationships
  Window
• 7.3 Types of Relationships
• 7.4 Hands-On Tutorial Defining Relationships in
  Access
• 7.5 Working with Existing Relationships
• 7.6 In-Class Assignment
• 7.7 Summary

3
Introduction

• A relationship connects or relates two tables
• Categorize relationships by
• Cardinality
• One-to-one, many-to-one, and many-to-many
• Degree
• Unary, binary, and ternary
• The Relationships Window is used to define
  relationships
• Add tables
• Relate tables
• Specify relationship types
• Enforce referential integrity

4
Introduction (cont.)

• Conversion from ER Diagram to Access
  Relationships Window

Entity-Relationship Diagram
Relational Schema
Relationships Windows
5
Overview

• 7.1 Introduction
• 7.2 Defining Relationships in the Relationships
  Window
• 7.3 Types of Relationships
• 7.4 Hands-On Tutorial Defining Relationships in
  Access
• 7.5 Working with Existing Relationships
• 7.6 In-Class Assignment
• 7.7 Summary

6
The Relationships Window

• The Relationships Window serves as a powerful
  graphical tool for defining relationships

Database Tables
Relationships
The Access Relationships Window
7
Opening the Relationships Window

• How-to Open the Relationships Window
• Open the university database file for this
  chapter.
• From the active Database Window, click the
  Relationships button on the toolbar.

8
Adding Tables to Relationships Window

• How-to Add a Table
• Click the Show Table button on the toolbar to
  open the Show Table dialog box.
• Select the desired tables and click the Add
  button.
• To return to the Relationships Window, click the
  Close button.

Show Table Dialog Box
9
Exploring the Relationships Toolbar and Menu

• The Relationships Window has a number of
  frequently used features accessible through
  toolbar buttons and the Relationships menu

10
Drawing a Relationship Line

• Drawing a line between two tables defines a
  relationship
• The process of drawing a line can be divided into
  two sub-tasks
• Deciding the common field from two tables to draw
  the line between the two fields.
• Drawing the line and enforcing referential
  integrity constraints

Drawing a Relationship Line
11
Referential Integrity

• A referential integrity constraint ensures that
  foreign key values of a relation must come from
  the primary key values of the related relation
• Otherwise, the value of a foreign key must be
  NULL
• Table types
• Parent tables are tables with primary keys
• Child tables are tables with foreign keys
• An orphan record is a record for which there is
  no related parent record
• Referential integrity constraints prevent the
  creation of orphan records

12
Enforcing Referential Integrity

• How-to Enforce Referential Integrity
• Open the Show Table dialog box and add the
  department table to the Relationships Window.
• Drag the DeptID field from one table to the
  DeptID field of the other table.
• Check the Enforce Referential Integrity check box.

Enforcing Referential Integrity Using Edit
Relationships Dialog Box
13
Enforcing Referential Integrity (cont.)

• How-to Enforce Referential Integrity on an
  Existing Relationship
• Right-click on a relationship and choose the Edit
  Relationships option to open the Edit
  Relationships dialog box.
• Check the Enforce Referential Integrity check
  box.
• Observe
• The type of relationship is automatically
  detected and displayed at the bottom of the Edit
  Relationships dialog box.
• The 1 and 8 symbols indicating a relationship
  type are displayed in the Relationship Window.
• Two other options related to cascade updates and
  cascade deletes become available.

14
Conditions Before Enforcement of Referential
Integrity

• Following are the few conditions that must hold
  before the enforcement of referential integrity
• Rule 1 The common field on one side of the
  relationship must be a primary key field or must
  have a unique index.
• Rule 2 Linking fields must be of the same data
  type.
• The AutoNumber and Number data types constitute
  the only exceptions, provided that their field
  size property is set to ReplicationID.
• Rule 3 The existing data in the tables cannot
  violate the referential integrity constraint.

15
Conditions After Enforcement of Referential
Integrity

• Following are the few conditions that must hold
  after the enforcement of referential integrity
• Rule 4 We cannot delete a record from the parent
  table if a record exists in the child table with
  the same key value.
• Rule 5 Similarly, we cannot update the common
  field from the parent table if corresponding
  records exist in the child table.

16
Cascade Updates and Cascade Deletes

• Maintaining the last two conditions related to
  the referential integrity can be tedious
• Upon enforcement of the referential integrity
  constraints in the Edit Relationships dialog box,
  the Cascade Update/Delete Related Fields options
  become available

Enforcing Cascade Updates and Cascade Deletes
17
Cascade Updates and Cascade Deletes (cont.)

• When the Cascade Delete Related Fields option is
  checked
• Cascade deletes are allowed
• If we delete a record with a unique field in the
  parent table, Access automatically deletes the
  related records in the child table
• Must be very cautious whenever we delete records
• If we have not properly examined the
  relationship, we could mistakenly delete a large
  amount of useful data
• When the Cascade Update Related Fields option is
  checked
• Cascade updates are allowed
• If we update a unique field in the parent table,
  Access automatically updates the related records
  in the child table

18
Saving a Layout of Relationships Window

• Access automatically saves relationship lines
  that have been added to the Relationships Window
• Access does not automatically preserve its layout
• We must manually save it after it has been
  created or altered
• Saving the layout preserves the orientation of
  the field lists and so preserves the readable
  view of the Relationships Window
• How-to Save the Layout of Relationships Window
• Choose the File Save option from the Main menu.

19
Overview

• 7.1 Introduction
• 7.2 Defining Relationships in the Relationships
  Window
• 7.3 Types of Relationships
• 7.4 Hands-On Tutorial Defining Relationships in
  Access
• 7.5 Working with Existing Relationships
• 7.6 In-Class Assignment
• 7.7 Summary

20
Types of Relationships

• Three types of relationships can exist among
  database tables
• One-to-Many or Many-to-One
• A record in Table A can have many matching
  records in Table B, but a record in Table B has
  exactly one matching record in Table A
• Most common relationship type
• Many-to-Many
• Consists of two one-to-many relationships, one
  from Table A to Table B and one from Table B to
  Table A
• One-to-One
• A record in Table A has exactly one matching
  record in Table B, and a record in Table B has
  exactly one matching record in Table A

21
Access Relationship Assignment

• When Access automatically detects the
  relationship types, it refers to the following
  predefined rules for an assignment
• If the common field in the parent table is the
  primary key or has a unique index and the
  corresponding field in the child table is a
  foreign key
• Access establishes a One-to-Many relationship
  between these two tables
• If a common field from both the tables is the
  primary key or has a unique index
• Access establishes a One-to-One relationship
  between these two tables
• If none of the common fields is a primary key or
  has a unique index
• Access establishes an indeterminate relationship
  between these two tables
• We cannot enforce referential integrity
  constraints for such relationships

22
Overview

• 7.1 Introduction
• 7.2 Defining Relationships in the Relationships
  Window
• 7.3 Types of Relationships
• 7.4 Hands-On Tutorial Defining Relationships in
  Access
• 7.5 Working with Existing Relationships
• 7.6 In-Class Assignment
• 7.7 Summary

23
Hands-On Tutorial Defining Relationships in
Access

• How-to Define Relationships in Access
• Open the university database and open the
  Relationships Window by choosing the Tools
  Relationships option from the Main menu.
• Right-click anywhere in the window and choose the
  Show Table option to display the Show Table
  dialog box.
• Make sure that all of the tables from the
  university database are visible in the
  Relationships Window. Click the Close button to
  return to the Relationships Window.

24
Hands-On Tutorial Defining Relationships in
Access (cont.)

• Relate the tblCollege and tblDepartment tables
• Select the CollegeID field from tblCollege
  drag-and-drop it on the CollegeID field in
  tblDepartment to open the Edit Relationships
  dialog box.
• Enforce the referential integrity, cascade
  updates related fields, and cascade deletes
  related records by checking appropriate check
  boxes.
• Complete relationships for the university
  database by repeating steps 4 and 5 for the
  following pairs of tables
• tblCourse and tblSection
• tblSection and tblFaculty
• tblFaculty and tblDepartment
• tblTranscript and tblSection.

25
Overview

• 7.1 Introduction
• 7.2 Defining Relationships in the Relationships
  Window
• 7.3 Types of Relationships
• 7.4 Hands-On Tutorial Defining Relationships in
  Access
• 7.5 Working with Existing Relationships
• 7.6 In-Class Assignment
• 7.7 Summary

26
Working with Existing Relationships

• Because data and data constraints can change, we
  may also want to modify the existing
  relationships in our databases
• Adding/Removing tables
• Modifying/Removing relationships
• We use the Relationships Window to make these
  modifications
• Before opening the Relationships Window to
  institute changes, we must be sure to close any
  open tables in the Database Window
• Prevents any discrepancy between what we see in
  the table Design View and what exists in the
  Relationships Window

27
Adding, Removing, and Hiding Tables in
Relationships Window

• We add database tables to the Relationships
  Window using the Show Table dialog box
• If we would like to remove a table from the
  Relationships Window, we select the table and
  press Delete on the keyboard
• Note that removing a table from the Relationships
  Window does not remove it from the database
• It only affects the layout of the Relationships
  Window
• We can also hide tables in the Relationships
  Window
• To hide a table, select the table and choose the
  Relationships Hide Table option from the Main
  menu
• To unhide the hidden tables, choose the
  Relationships Show Table option from the Main
  menu

28
Editing Relationships

• Relationship lines are not permanent
• We can edit or remove these lines as we build our
  database
• How-to Edit a Relationship
• Open the Relationships Window.
• If necessary, unhide/add the missing tables or
  rearrange the existing tables for a clearer view.
• Locate and double-click on the relationship line
  that we would like to edit to bring up the Edit
  Relationships dialog box.
• Make the desired changes in this dialog box to
  edit the relationship.
• Save the relationships layout.

29
Deleting Relationships

• How-to Delete a Relationship
• Open the Relationships Window locate and select
  the relationship we would like to delete.
• Press Delete on the keyboard.
• Click Yes when Access seeks confirmation before
  permanently deleting the relationship.
• Save the relationships layout.

30
Overview

• 7.1 Introduction
• 7.2 Defining Relationships in the Relationships
  Window
• 7.3 Types of Relationships
• 7.4 Hands-On Tutorial Defining Relationships in
  Access
• 7.5 Working with Existing Relationships
• 7.6 In-Class Assignment
• 7.7 Summary

31
In-Class Assignment

• Open the University.mdb database from the
  Chapter7 folder on the book website
• Examine tblStudent, tblCourse, tblStudentCourse,
  tblInstructor, tblInstructorCourse, and tblRoom
  tables
• Answer the following questions
• What is the relationship between the table
  tblStudent and tblCourse? Why?
• Open the Relationships Window and show all
  tables. Create relationships between the
  tblStudent and tblCourses tables. Enforce the
  referential integrity.
• What types of relationships exist between
  tblStudent, tblCourse, and tblStudentCourse? Add
  these relationships in the Relationships Window.
• Edit relationships so that when student
  information is changed, the changes are
  automatically reflected in all related tables.

32
Overview

• 7.1 Introduction
• 7.2 Defining Relationships in the Relationships
  Window
• 7.3 Types of Relationships
• 7.4 Hands-On Tutorial Defining Relationships in
  Access
• 7.5 Working with Existing Relationships
• 7.6 In-Class Assignment
• 7.7 Summary

33
Summary

• A relationship is an association between tables.
• The graphical interface known as the
  Relationships Window allows us to create, view,
  and edit relationships.
• A Referential integrity constraint is a
  constraint on data that prevents the creation of
  orphan records in tables.
• When enabled, the Cascade Update and Delete
  options can update or delete records from the
  child table that are related to records in the
  parent table that have been updated or deleted,
  respectively.
• Relationship types are denoted by the symbols 1
  and 8.

34
Additional Links

• Review more sample Relationships Window from
  database files on the book website
  http//www.dssbooks.com.
• Refer MS Access 2003 The Complete Reference by
  Virginia Andersen for more details on
  Relationships.
• Also refer Access 2003 Bible by Cary N. Prague,
  Michael R. Irwin, and Jennifer Reardon.
• Also see http//msdn.microsoft.com/ for useful
  tips on Relationships