1 / 34

Web-Enabled Decision Support Systems

Web-Enabled Decision Support Systems. Relationships: Linking Access Tables. Prof. Name name@email.com Position (123) 456-7890 University Name. Overview. 7.1 Introduction 7.2 Defining Relationships in the Relationships Window

minna
Download Presentation

Web-Enabled Decision Support Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Web-Enabled Decision Support Systems Relationships: Linking Access Tables Prof. Name name@email.com Position (123) 456-7890 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.) Grade Register COURSE STUDENT • 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 • Areferential integrity constraintensures 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 “∞” 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 BeforeEnforcement 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 AfterEnforcement 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 theCascade Delete Related Fieldsoption 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 theCascade 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 TableB, but a record in TableB has exactly one matching record in TableA • 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 CascadeUpdate 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 ∞.

  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

More Related