6. DATABASE DESIGN
Response: Below are the database table needed for the PGP system. Student
responses will vary based upon assumptions made.
Sales Invoice Cash Receipt Customer Supplier
SI Line Item State
Quantity
Inventory Purchase Order Receiving Report
Supplier Invoice PO Line Item RR Line Item
Invoice Line Item Cash Disbursement
Explanation of tables and attributes;
1) The Payment Reference attribute in the Cash Receipts table contains
2) The Paid Reference attribute in the Supplier Invoice table contains the
3) The Customer Invoice, Supplier Invoice, Receiving Report, and Purchase
Order tables are associated with line item tables in a 1:M relation. The Line
Chapter 1 Page 2
7. DISTRIBUTED DATABASES
The best distributed approach is a replicated database.
Reasoning
8. DISTRIBUTED DATABASES
The best distributed approach is a partitioned database.
Reasoning
9. NORMALIZATION OF DATA
The first step is to remove repeating groups. The removal of repeating groups
results in the following two tables. Due Date has been removed because it is a
Student Table
Chapter 1 Page 3
Library Table
Number
The second step is to remove partial dependencies from Library table.
Book Title is partially dependent on the Book Call No. component of the
primary key. This assumes that the call number is unique to the book, and not
Transaction Table
Number
Book Table
Book Call No. is the primary key for the new table Book. Since no transitive
Chapter 1 Page 4
10. NORMALIZATION OF DATA
Table Textbook
Table Publisher
(PK)
Table Class
Number)
(FK)
Table Semester
#(PK)
Table Professor
# (PK)
Chapter 1 Page 5
11. NORMALIZATION OF DATA
Chapter 1 Page 6
Chapter 1 Page 7
PK Vendor Table
Chapter 1 Page 8
Chapter 1 Page 9
12. NORMALIZATION OF DATA
Chapter 1 Page 10
Chapter 1 Page 11
Note: This is an example of a many-to-many relation between the inventory and
vendor tables. The solution requires a link table, which also contains Unit Cost
13. NORMALIZATION OF DATA
See diagram below.
Customer Table
Invoice Table
Line Item Table
Inventory Table
* Could be a calculated field
14. NORMALIZATION OF DATA – PURCHASE ORDER
Table Employee
Chapter 1 Page 12
Table PO
(FK)
Table PO/Item
Table Item
Table Vendor
Chapter 1 Page 13
15. TABLE LINKING
Chapter 1 Page 14
16. ENTITY RELATIONSHIP DIAGRAM
Business Rules Represented by Problem 16 ERD.