1 / 26

Many-to-Many Relationships: A More Complex System(Wk8)

Explore the implementation of many-to-many relationships, referential integrity, and cascaded deletion in Microsoft Office 2003.

kennyd
Download Presentation

Many-to-Many Relationships: A More Complex System(Wk8)

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. Many-to-Many Relationships: A More Complex System(Wk8) Exploring Office 2003 - Grauer and Barber

  2. Objectives • Use AutoNumber as the primary key • Explain what field type can relate to an AutoNumber primary key field • Implement a many-to-many relationship • Use Cascade Update and Cascade Delete Exploring Office 2003 - Grauer and Barber

  3. Objectives (continued) • Create a main and a subform based on a query • Create a parameter query • Use aggregate functions to perform calculations on groups of records • Use Get External Data to add tables from another database Exploring Office 2003 - Grauer and Barber

  4. Case Study: University Placement Center Students create a database that includes: • Required tables • Many-to-many relationships • A switchboard • An About form • Relationships diagram report Exploring Office 2003 - Grauer and Barber

  5. Many-to-Many Relationships • A many-to-many relationship requires an additional table that has a one-to-many relationship to each of the related tables • The primary key of the additional table is the combination of the primary keys of the related tables Exploring Office 2003 - Grauer and Barber

  6. Many-to-many relationship is implemented by a pair of one-to-many relationships Implementing Many-To-Many Relationships Enforce Referential Integrity is selected Many-to-many relationship is implemented by a pair of one-to-many relationships Cascade Delete Related Records is selected Exploring Office 2003 - Grauer and Barber

  7. Referential Integrity • Ensures records in related tables are consistent with one another • Prevents adding a record to a related table with an invalid foreign key • Prevents deleting a record in the primary table when there are corresponding records in the related table Exploring Office 2003 - Grauer and Barber

  8. Cascaded Deletion • When a record in the primary table is deleted, Access simultaneously deletes the corresponding records in the related table Exploring Office 2003 - Grauer and Barber

  9. Cascaded Updating • When the primary key in the primary table is updated, Access automatically updates the value in the corresponding records in the related table Exploring Office 2003 - Grauer and Barber

  10. Hands-on Exercise 1 • Title of Exercise: Relationships and Referential Integrity • Objective: to create relationships between existing tables to demonstrate referential integrity and allow cascaded deletion of related records • Input file: Computer Store • Output file: Computer Store (modified) Exploring Office 2003 - Grauer and Barber

  11. Subforms, Queries, and AutoLookup • Main and subforms based on queries: • display information from multiple tables • display records other than by primary key • AutoLookup populates the corresponding fields once the primary key value is entered Exploring Office 2003 - Grauer and Barber

  12. Main form has fields from Orders and Customers tables Main Form and Subform Subform has fields from Order Details and Products tables Exploring Office 2003 - Grauer and Barber

  13. Main form detail Subform detail Designing a main and a subform Exploring Office 2003 - Grauer and Barber

  14. Hands-on Exercise 2 • Title of Exercise: Subforms and Multiple-Table Queries • Objective: to use multiple-table queries to create a main form and related subform; manually link the forms • Input file: Computer Store (from Exercise 2) • Output file: Computer Store (modified) Exploring Office 2003 - Grauer and Barber

  15. Main form detail Subform detail Designing a main and a subform Exploring Office 2003 - Grauer and Barber

  16. Parameter Queries • Prompts the user for criteria when executed • The prompt is enclosed in square brackets in the query design grid • A parameter query may prompt for any number of variables Exploring Office 2003 - Grauer and Barber

  17. Prompt is entered in square Brackets Parameter Query Prompt is displayed when query is run Exploring Office 2003 - Grauer and Barber

  18. Total Queries • Total Queries perform calculations on a group of records • Total row – Contains either Group by or aggregate entry • Group By – Records in the dynaset are to be grouped according to the like values • Sum Function – Specifies math to be performed on that field for each group of records Exploring Office 2003 - Grauer and Barber

  19. Total Queries Records are grouped by like values of OrderID Arithmetic operation to be performed on group Exploring Office 2003 - Grauer and Barber

  20. Hands-on Exercise 3 • Title of Exercise: Advanced Queries • Objective: to copy an existing query; create a parameter query; create a total query using Aggregate Sum function • Input file: Computer Store (after Exercise 2) • Output file: Computer Store (modified) Exploring Office 2003 - Grauer and Barber

  21. Hands-on Exercise 4 • Title of Exercise: Expanding the Database • Objective: to import a table from another database and modify the design • Input files: Sales Persons database Computer Store (after Exercise 3) • Output file: Computer Store (modified) Exploring Office 2003 - Grauer and Barber

  22. Summary • A many-to-many relationship requires an additional table • Many-to-many is implemented with a pair of one-to-many relationships • The Enforce referential integrity option prevents errors • Forms and subforms are based on queries Exploring Office 2003 - Grauer and Barber

  23. Summary (continued) • The Parameter query uses prompts • Aggregate functions perform calculations on groups of records • New tables may be added at any time without affecting data in the existing tables Exploring Office 2003 - Grauer and Barber

  24. End-of-chapter Exercises • Multiple Choice • Practice With Access • Exercise 1 – Understanding Database Design • Exercise 2 – Unmatched Query Wizard • Exercise 3 – An Improved Order Form • Exercise 4 – Computer Store Switchboard • Exercise 5 – Return to National Bank • Exercise 6 – Medical Research Database Design • Exercise 7 – Medical Research Switchboard Exploring Office 2003 - Grauer and Barber

  25. End-of-chapter Exercises (continued) • Practice With Access (continued) • Exercise 8 – National Conference Database Design • Exercise 9 – National Conference Switchboard • Additional Mini Cases • Health Clubs • The Morning Paper • The College Bookstore • Bob’s Burgers Exploring Office 2003 - Grauer and Barber

  26. Questions? Exploring Office 2003 - Grauer and Barber

More Related