The computer store
Download
1 / 34

The Computer Store - PowerPoint PPT Presentation


  • 88 Views
  • 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

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

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


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


Relationships
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





Additional relationships
Additional Relationships O007 and O008

  • 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 O007 and O008

  • 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) O007 and O008

  • 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 System?

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 time you ran the same query?

[Enter Customer’s Last Name]


Prompts for the criterion
Prompts for the Criterion time you ran the same query?

Enter “Muddell”


Dynaset for condition muddell
Dynaset for Condition “Muddell” time you ran the same query?


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


Normalization
Normalization me Codd”

  • 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 me Codd”

  • 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 me Codd”

  • 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 Dependency

  • 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 Transitive Dependency

  • 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 Transitive Dependency

  • 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 Transitive Dependency

Orders and Customers

tables

Main Form and Subform

Subform has fields from Order Details and

Products tables


Designing a main and a subform

Main form detail Transitive Dependency

Subform detail

Designing a main and a subform


Designing a main and a subform1

Main form detail Transitive Dependency

Subform detail

Designing a main and a subform


Total queries
Total Queries Transitive Dependency

  • 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 Transitive Dependency

Records are grouped by

like values of OrderID

Arithmetic operation to be

performed on group


Summary
Summary Transitive Dependency

  • 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) Transitive Dependency

  • 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


ad