WebEnabled Decision Support Systems Relationships Linking Access Tables

Web-Enabled Decision Support Systems Relationships: Linking Access Tables Prof. Name Position University Name name@email. com (123) 456 -7890 1

Overview v 7. 1 Introduction v 7. 2 Defining Relationships in the Relationships Window v 7. 3 Types of Relationships v 7. 4 Hands-On Tutorial: Defining Relationships in Access v 7. 5 Working with Existing Relationships v 7. 6 In-Class Assignment v 7. 7 Summary 2

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

Introduction (cont. ) v Conversion from ER Diagram to Access Relationships Window: Grade STUDENT Register COURSE Entity-Relationship Diagram Relational Schema Relationships Windows 4

Overview v 7. 1 Introduction v 7. 2 Defining Relationships in the Relationships Window v 7. 3 Types of Relationships v 7. 4 Hands-On Tutorial: Defining Relationships in Access v 7. 5 Working with Existing Relationships v 7. 6 In-Class Assignment v 7. 7 Summary 5

The Relationships Window v The Relationships Window serves as a powerful graphical tool for defining relationships Database Tables Relationships The Access Relationships Window 6

Opening the Relationships Window v How-to: Open the Relationships Window 1. Open the university database file for this chapter. 2. From the active Database Window, click the Relationships button on the toolbar. 7

Adding Tables to Relationships Window v How-to: Add a Table 1. Click the Show Table button on the toolbar to open the Show Table dialog box. 2. Select the desired tables and click the Add button. 3. To return to the Relationships Window, click the Close button. Show Table Dialog Box 8

Exploring the Relationships Toolbar and Menu v The Relationships Window has a number of frequently used features accessible through toolbar buttons and the Relationships menu 9

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

Referential Integrity v 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 v Table types: – Parent tables are tables with primary keys – Child tables are tables with foreign keys v An orphan record is a record for which there is no related parent record – Referential integrity constraints prevent the creation of orphan records 11

Enforcing Referential Integrity v How-to: Enforce Referential Integrity 1. Open the Show Table dialog box and add the department table to the Relationships Window. 2. Drag the Dept. ID field from one table to the Dept. ID field of the other table. 3. Check the Enforce Referential Integrity check box. Enforcing Referential Integrity Using Edit Relationships Dialog Box 12

Enforcing Referential Integrity (cont. ) v How-to: Enforce Referential Integrity on an Existing Relationship 1. Right-click on a relationship and choose the Edit Relationships option to open the Edit Relationships dialog box. 2. Check the Enforce Referential Integrity check box. v Observe: – The type of relationship is automatically detected and displayed at the bottom of the Edit Relationships dialog box. – The “ 1” and “∞” symbols indicating a relationship type are displayed in the Relationship Window. – Two other options related to cascade updates and cascade deletes become available. 13

Conditions Before Enforcement of Referential Integrity v 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 Auto. Number and Number data types constitute the only exceptions, provided that their field size property is set to Replication. ID. – Rule 3: The existing data in the tables cannot violate the referential integrity constraint. 14

Conditions After Enforcement of Referential Integrity v 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. 15

Cascade Updates and Cascade Deletes v 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 16

Cascade Updates and Cascade Deletes (cont. ) v 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 v 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 17

Saving a Layout of Relationships Window v 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 v How-to: Save the Layout of Relationships Window – Choose the File | Save option from the Main menu. 18

Overview v 7. 1 Introduction v 7. 2 Defining Relationships in the Relationships Window v 7. 3 Types of Relationships v 7. 4 Hands-On Tutorial: Defining Relationships in Access v 7. 5 Working with Existing Relationships v 7. 6 In-Class Assignment v 7. 7 Summary 19

Types of Relationships v 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 20

Access Relationship Assignment v 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 21

Overview v 7. 1 Introduction v 7. 2 Defining Relationships in the Relationships Window v 7. 3 Types of Relationships v 7. 4 Hands-On Tutorial: Defining Relationships in Access v 7. 5 Working with Existing Relationships v 7. 6 In-Class Assignment v 7. 7 Summary 22

Hands-On Tutorial: Defining Relationships in Access v How-to: Define Relationships in Access 1. Open the university database and open the Relationships Window by choosing the Tools | Relationships option from the Main menu. 2. Right-click anywhere in the window and choose the Show Table option to display the Show Table dialog box. 3. 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. 23

Hands-On Tutorial: Defining Relationships in Access (cont. ) 4. Relate the tbl. College and tbl. Department tables: § Select the College. ID field from tbl. College; drag-and-drop it on the College. ID field in tbl. Department to open the Edit Relationships dialog box. 5. Enforce the referential integrity, cascade updates related fields, and cascade deletes related records by checking appropriate check boxes. 6. Complete relationships for the university database by repeating steps 4 and 5 for the following pairs of tables: § § tbl. Course and tbl. Section and tbl. Faculty and tbl. Department tbl. Transcript and tbl. Section. 24

Overview v 7. 1 Introduction v 7. 2 Defining Relationships in the Relationships Window v 7. 3 Types of Relationships v 7. 4 Hands-On Tutorial: Defining Relationships in Access v 7. 5 Working with Existing Relationships v 7. 6 In-Class Assignment v 7. 7 Summary 25

Working with Existing Relationships v 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 v 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 26

Adding, Removing, and Hiding Tables in Relationships Window v We add database tables to the Relationships Window using the Show Table dialog box v 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 v 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 27

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

Deleting Relationships v How-to: Delete a Relationship 1. Open the Relationships Window; locate and select the relationship we would like to delete. 2. Press Delete on the keyboard. 3. Click Yes when Access seeks confirmation before permanently deleting the relationship. 4. Save the relationships layout. 29

Overview v 7. 1 Introduction v 7. 2 Defining Relationships in the Relationships Window v 7. 3 Types of Relationships v 7. 4 Hands-On Tutorial: Defining Relationships in Access v 7. 5 Working with Existing Relationships v 7. 6 In-Class Assignment v 7. 7 Summary 30

In-Class Assignment v v v Open the University. mdb database from the “Chapter 7” folder on the book website Examine tbl. Student, tbl. Course, tbl. Student. Course, tbl. Instructor. Course, and tbl. Room tables Answer the following questions: 1. What is the relationship between the table tbl. Student and tbl. Course? Why? 2. Open the Relationships Window and show all tables. Create relationships between the tbl. Student and tbl. Courses tables. Enforce the referential integrity. 3. What types of relationships exist between tbl. Student, tbl. Course, and tbl. Student. Course? Add these relationships in the Relationships Window. 4. Edit relationships so that when student information is changed, the changes are automatically reflected in all related tables. 31

Overview v 7. 1 Introduction v 7. 2 Defining Relationships in the Relationships Window v 7. 3 Types of Relationships v 7. 4 Hands-On Tutorial: Defining Relationships in Access v 7. 5 Working with Existing Relationships v 7. 6 In-Class Assignment v 7. 7 Summary 32

Summary v A relationship is an association between tables. v The graphical interface known as the Relationships Window allows us to create, view, and edit relationships. v A Referential integrity constraint is a constraint on data that prevents the creation of orphan records in tables. v 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. v Relationship types are denoted by the symbols 1 and ∞. 33

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