The computer store
Sponsored Links
This presentation is the property of its rightful owner.
1 / 34

The Computer Store PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

The computer store

The Computer Store

Many-to-Many Relationships

The problem

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



  • 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

Customers table

Customers Table

Orders table partial

Orders Table (partial)

The computer store

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

Additional relationships

Additional Relationships

  • Many-to-many relationship between orders and products

    • One order can include many products

    • A specific product can appear in many orders

Products table partial

Products Table (partial)

Many to many relationships

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

Order details table partial

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

Query which order s include a celeron 2 0ghz desktop system

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

Implementing many to many relationships

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.

Parameter queries

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

Parameter query

Parameter Query

[Enter Customer’s Last Name]

Prompts for the criterion

Prompts for the Criterion

Enter “Muddell”

Dynaset for condition muddell

Dynaset for Condition “Muddell”

The key the whole key and nothing but the key so help me codd

‘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



  • 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

Raw data with no normalization

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

The first normal form order details table

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

The second normal form additional tables remove partial dependency

The Second Normal Form: Additional Tables Remove Partial Dependency

  • Create two additional tables

    • Products Table

    • Orders Table

The second normal form

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

The third normal form additional customer table removes transitive dependency

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

Third normal form simplified

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)

Subforms queries and autolookup

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

Main form and subform

Main form has fields from

Orders and Customers


Main Form and Subform

Subform has fields from Order Details and

Products tables

Designing a main and a subform

Main form detail

Subform detail

Designing a main and a subform

Designing a main and a subform1

Main form detail

Subform detail

Designing a main and a subform

Total queries

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

Total queries1

Total Queries

Records are grouped by

like values of OrderID

Arithmetic operation to be

performed on group



  • 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

Summary continued

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

  • Login