example of conceptual database design (based on chapter 2 in database management by mcfadden and...
TRANSCRIPT
Example of conceptual database design
(Based on Chapter 2 in Database Management
by McFadden and Hoffer, Ed. 3)
Events list for Pine Valley Furniture Sales
Customer data entry Customer order entry
Staff Product data entry
accounting Invoice entry
Sales manager Customer order
history query Discount rate query
Manager Discount rate query Backlog summary report Customer order history
query Customer order history
query Daily order log report
CSD manager Daily order log report
Products line manager Large customer query Backlog summary report
Product No: M128Description: Bookcase
Room: StudyFinish: Birch
Unit Price: $200.00Quantity on Hand: 10
Product display screen
E-R diagram for product display entry
PRODUCT
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Customer No: 1273Name: Contemporary Design
Address: 123 Oak St.City-State-Zip: Austin, TX 28384
Discount %: 5%
Customer data entry
E-R diagram for customer display screen
Customer_No
Name
Discount %
City-State-ZipAddress
CUSTOMER
Order entry screenOrder no: 61384 Customer No: 1273
Name: Contemporary DesignAddress: 123 Oak St.
City-State-Zip: Austin, TX 28384
Order date: 11/04/90 Promised date: 11/21/90
Product No. Description Quantity Ordered
Unit Price
M128 Bookcase 4 200.00B381 Cabinet 2 150.00R220 Table 1 500.00
E-R diagram for order entry
Placed-By
ORDER
PRODUCT
Customer_No
Order Date
Order_No
NameCity-State-Zip
Address
Product No
DescriptionUnit Price
Requested-On
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
Invoice screenInvoice No: 16389Invoice Date: 11/22/90
Order No: 61384Paid? (Y/N) Y
Customer No: 1273Name: Contemporary Design
Address: 123 Oak St.City-State-Zip: Austin, TX 28384
Product No.
Description
Qty.Ord.
Prev.Ship.
Qty.Ship.
Unitprice
TotalPrice
M128 Bookcase 4 0 2 200.00 400.00B381 Cabinet 2 0 1 150.00 150.00R220 Table 1 0 1 500.00 500.00
Total Amount 5 % Discount
Amount Due
1050.0052.50997.50
E-R diagram for invoice screen
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order_No
Name
Discount %
City-State-ZipAddress
Product No
DescriptionUnit Price
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Discount rate query
Name: Contemporary DesignDiscount %: 5%
Customer No: 1273
E-R diagram for discount rate inquiry
Customer_No
Name
Discount %CUSTOMER
Daily order log report
PAGE 1 DAILY ORDER LOG
11/04/90
Customer Name Order No. Promissed DateABC Office Supply
61397 11/20/90
Commonwealth Builder
61398 11/12/90
E-R diagram for daily order log
Placed-By
ORDEROrder Date
Order_No
Name
Promised Date
CUSTOMER
1
N
Customer order history
CUSTOMER ORDER HISTORY
Customer No: 1273Start Date: 11/01/90 End Date: 11/30/90
Order Date Order No. Product No..Quantity Ordered
11/04/90 61384 M128B381R210
421
11/06/90 61390 T100T160
21
E-R diagram for customer order history
Placed-By
ORDER
PRODUCT
Customer_No
Order Date
Order_No
Product No
Requested-OnQuantity Orderd
CUSTOMER
1
N
M
N
Large customer query
Large Customer QueryProduct No: M128
11/01/90 thru 12/31/90
Customer No: 1256Name: Commonwealth Builder
Volume: 30
E-R diagram for large customer query
Placed-By
ORDER
PRODUCT
Customer_No
Order Date
Name
Product No
Requested-On
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
Backlog summary reportPAGE 1
BACKLOG SUMMARY REPORT11/04/90
Product No. Backlog Quantity
B381B975B958E125
.M128
.
000
30
2
E-R diagram for backlog summary report
ORDER
PRODUCT
INVOICE
Product No
Requested-On Ship-On
Quantity ShippedQuantity Orderd
M
N N
M
Sales – local conceptual data model
Placed-By
ORDER
PRODUCT
Customer_No
Order Date
Order_No
NameCity-State-Zip
Address
Product No
DescriptionUnit Price
Requested-On
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
Discount %
Staff – local conceptual data model
PRODUCT
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Accounting – local conceptual data model
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order_No
Name
Discount %
City-State-ZipAddress
Product No
DescriptionUnit Price
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Manager – local conceptual data model
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
Product No
Requested-On Ship-On
Quantity Shipped
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N N
M
Sales manager – local conceptual data model
Placed-By
ORDER
PRODUCT
Customer_No
Order Date
Order_No
Product No
Requested-OnQuantity Orderd
CUSTOMER
1
N
M
N
Name
Discount %
CSF manager – local conceptual data model
Placed-By
ORDEROrder Date
Order_No
Name
Promised Date
CUSTOMER
1
N
Product line manager – local conceptual data
Placed-By
ORDER
PRODUCT
Customer_No
Order Date
Name
Product No
Requested-On
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
INVOICE
Ship-On
Quantity ShippedN
M
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Product No: M128Description: Bookcase
Room: StudyFinish: Birch
Unit Price: $200.00Quantity on Hand: 10
Product display screen
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Customer No: 1273Name: Contemporary Design
Address: 123 Oak St.City-State-Zip: Austin, TX 28384
Discount %: 5%
Customer data entry
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Order entry screenOrder no: 61384 Customer No: 1273
Name: Contemporary DesignAddress: 123 Oak St.
City-State-Zip: Austin, TX 28384
Order date: 11/04/90 Promised date: 11/21/90
Product No. Description Quantity Ordered
Unit Price
M128 Bookcase 4 200.00B381 Cabinet 2 150.00R220 Table 1 500.00
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Invoice screenInvoice No: 16389Invoice Date: 11/22/90
Order No: 61384Paid? (Y/N) Y
Customer No: 1273Name: Contemporary Design
Address: 123 Oak St.City-State-Zip: Austin, TX 28384
Product No.
Description
Qty.Ord.
Prev.Ship.
Qty.Ship.
Unitprice
TotalPrice
M128 Bookcase 4 0 2 200.00 400.00B381 Cabinet 2 0 1 150.00 150.00R220 Table 1 0 1 500.00 500.00
Total Amount 5 % Discount
Amount Due
1050.0052.50997.50
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Discount rate query
Name: Contemporary DesignDiscount %: 5%
Customer No: 1273
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Daily order log report
PAGE 1 DAILY ORDER LOG
11/04/90
Customer Name Order No. Promissed DateABC Office Supply
61397 11/20/90
Commonwealth Builder
61398 11/12/90
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Customer order history
CUSTOMER ORDER HISTORY
Customer No: 1273Start Date: 11/01/90 End Date: 11/30/90
Order Date Order No. Product No..Quantity Ordered
11/04/90 61384 M128B381R210
421
11/06/90 61390 T100T160
21
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Large customer query
Large Customer QueryProduct No: M128
11/01/90 thru 12/31/90
Customer No: 1256Name: Commonwealth Builder
Volume: 30
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Backlog summary reportPAGE 1
BACKLOG SUMMARY REPORT11/04/90
Product No. Backlog Quantity
B381B975B958E125
.M128
.
000
30
2
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Conceptual Data Model
Events list for Pine Valley Furniture
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Sales Customer data entry Customer order entry
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Staff Product data
entry
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
accounting Invoice
entry
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Sales manager Customer order
history query Discount rate query
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Manager Discount rate query Backlog summary report Customer order history
query Customer order history
query Daily order log report
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
CSD manager Daily order
log report
Placed-By
ORDER
PRODUCT
INVOICE
Customer_No
Order Date
Order_No
Name
Discount %
City-State-ZipAddress
Product No
Description
Room Finish
Unit Price
Quantity on Hand
Requested-On Ship-On
Shipped-For
Quantity Shipped
Paid?
Invoice DateInvoice No
Promised Date
Quantity Orderd
CUSTOMER
1
N
M
N
N
N
M
1
Products line manager Large customer query Backlog summary report