the computer store n.
Skip this Video
Download Presentation
The Computer Store

Loading in 2 Seconds...

play fullscreen
1 / 34

The Computer Store - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'The Computer Store' - cedric-adams

Download Now 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
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
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”

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

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