265 - Chapter 1 Homework

265 - Chapter 1 Homework - Chapter 1 Homework 1. Vendors...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Chapter 1 Homework 1. Vendors (Team 1) – Master Table VendorNo Name Address Phone Balance 1234 XYZ Company 2222 Poyntz 532-6834 400.00 1235 ABC Company A234 Anderson 532-8405 0.00 1236 Farce Company 5432 Sunset 532-8432 1,000.00 Inventory Items Master Table (According to the company's purchasing guidelines, each inventory item can be purchased from only one vendor) ItemNo VendorNo Description QtyOnHand 888 1234 Widgit 45 889 1234 Thingamajig 100 890 1235 Watsit 1,000 Price List – Reference Table (ItemNo, MinQty, MaxQty, Price) (The company receives a different price for items ordered, depending on the quantity ordered. This files collects and stores information from vendors about these prices for different ranges of quantities, as defined by the MinQty and MaxQty values). Item No (Primary and Foreign Key) MinQty MaxQty Price 888 0 25 45.00 888 26 1,000 43.00 888 1,000 9,999,999,999 40.00 889 0 500 10.00 889 500 9,999,999,999 9.50 890 0 9,999,999,999 5.00 Purchase Orders – Event Table PONo VendorNo Date 222 1235 8/25/03 223 1234 8/26/03 224 1234 8/26/03 PO Items Ordered – Event Detail Table PONo(Primary and Foreign Key) ItemNo(Primary and Foreign Key) Qty OrderPrice 222 890 40 5.00 223 888 100 43.00 223 889 400 9.50 224 888 500 42.00 For each table above: a. Identify the primary key in the table – Primary Keys Are Yellow b. Identify foreign keys, if any – Foreign Keys Are Red c. Identify whether the table is a master table, reference table, event table, or event detail table – This is in Green
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
I STARTED HIGHLIGHTING AND COLOR CODING MY ANSWERS, BUT I ALSO PUT THEM INTO THE TABLE BELOW TO MAKE IT EASIER TO FOLLOW. Primary Keys Foreign Keys Type of Table Vendors Vendor No ---NONE--- Master Inventory Items Item No Vendor No Master Price List Item No, MinQty, Max Qty (composite key) Item No(Primary and Foreign key) Reference Table Purchase Orders PO No Vendor No Event Table PO Items Ordered PO No, Item No (composite key) PO No, Item No (Both are Primary and Foreign Keys) Event Detail 2. Do Problem 5. (Team 2) Violations of entity integrity: - In the Invoices Table, the third line down. Entity integrity requires that the primary key cannot be blank. It must have a unique value. - Also in the Invoice-Items Table, lines four and five. Entity integrity requires that each primary key (or in this case the composite key) be unique. This has identical numbers in the invoice # and Item # columns which make up the composite key. Violations of referential integrity:
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 6

265 - Chapter 1 Homework - Chapter 1 Homework 1. Vendors...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online