1 / 34

The Computer Store

The Computer Store. Many-to-Many Relationships. The Problem. Company needs a database for its Customers Name, address, phone, etc. Products Product description, quantity on hand, quantity on order and unit price Orders

cedric-adams
Download Presentation

The Computer Store

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. The Computer Store Many-to-Many Relationships

  2. The Problem • Company needs a database for its • Customers • Name, address, phone, etc. • Products • Product description, quantity on hand, quantity on order and unit price • Orders • Date order was received, customer, products ordered and quantity of each product

  3. Relationships • One-to-many relationship between customers and orders • One customer can place many orders, but a specific order is associated with only one customer • The CustomerID is the primary key in the Customers table • The CustomerID is the foreign key in the Order table

  4. Customers Table

  5. Orders Table (partial)

  6. One Customer can have many orders. Customer C0002 has order O007 and O008

  7. Additional Relationships • Many-to-many relationship between orders and products • One order can include many products • A specific product can appear in many orders

  8. Products Table (partial)

  9. 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 • Called a composite key, a combined key or a concatenated key

  10. Order Details Table (partial) • Many records with the same OrderID • Many records with the same ProductID • Combination of OrderID and ProductID is unique • Combined key is the primary key • O0001P0013

  11. Query: Which Order(s) include a Celeron 2.0Ghz Desktop System? • Search for the Celeron system in the Product table which gives the ProductID of P0001 • Search Order Details table for records containing ProductID of P0001 • This identifies order O0002

  12. Implementing Many-to-Many Relationships One-to-Many relationship between Customers and Products Many-to-Many relationship between Orders and Products is implemented by a pair of one-to-many relationships. Each record in the Products table can be associated with many records in the Order Detail table. Each record in the Orders table can be associated with many records in the Order Details table.

  13. What if you wanted to use a different set of criteria every time you ran the same query? A different customer name 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 If you misspell a field name, Access interprets this as a parameter query Parameter Queries

  14. Parameter Query [Enter Customer’s Last Name]

  15. Prompts for the Criterion Enter “Muddell”

  16. Dynaset for Condition “Muddell”

  17. ‘the key, the whole key, and nothing but the key, so help me Codd” • Edgar Codd (IBM) • A Relational Model of Data for Large Shared Databanks, Communicaitons of the ACM, June, 1970, pp. 377-387 • Good database design • The value of every field in a table is dependent on the primary key and on nothing but the primary key

  18. Normalization • Process of organizing a database • Removes all redundant data • Progress from one normal form to the next • Examines the dependencies (relationships) between the fields in a table • Eliminates partial and transitive dependencies

  19. Raw Data with No Normalization • Unstructured data organized in one table by OrderID • All potential anomalies • Each record is of variable length • Depending on number of products in a specific order • Eliminate repeating groups

  20. The First Normal Form: Order Details Table • One record for each product in each order • Every record is the same length • Primary key is a composite key of OrderID and ProductID • Product information depends on only part of the composite key (the ProductID) • Known as a partial dependency

  21. The Second Normal Form: Additional Tables Remove Partial Dependency • Create two additional tables • Products Table • Orders Table

  22. The Second Normal Form • All Product information depends on the ProductID • Orders table has transitive dependency • Date of order and CustomerID depend on OrderID • Customer info depends on CustomerID rather than OrderID

  23. The Third Normal Form: Additional Customer Table Removes Transitive Dependency • Four tables • Every field in every table is functionally dependent on the primary key of that table

  24. Third Normal Form Simplified • Identify the entities that exist in the system • Each requires it own table • Create the required tables and identify the primary key in each • Identify and implement the relationships • One-to-many relationship • Include the primary key of the “one” table as a foreign key in the “many” table • Many-to-many relationship • Requires an additional table, which contains the primary keys of the individual entities (composite key)

  25. 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

  26. Main form has fields from Orders and Customers tables Main Form and Subform Subform has fields from Order Details and Products tables

  27. Main form detail Subform detail Designing a main and a subform

  28. Main form detail Subform detail Designing a main and a subform

  29. 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

  30. Total Queries Records are grouped by like values of OrderID Arithmetic operation to be performed on group

  31. 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

  32. 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

More Related