HELP! Please check if my answers are correct. For instance, am I mising keys?

Concepts of Database Management (8th Edition) - Chapter A, Problem 2E

Complete an information-level design for Holt Distributors.

Submit your completed design in DBDL (database design language) format showing all keys (primary, foreign, alternate, and secondary). It is not necessary to show all the normalization steps for each of the user views, although you may find this to be a useful tool to ensure that your tables are in 3NF (third normal form).

(Hint: There are a total of 11 tables in the Final Design)

QUESTIONS:

Transaction requirements. The following transaction requirements are required by Holt Distributors:

a. Enter and edit territories (territory number and name).

Enter and edit sales reps (sales rep number, name, address, city, state, postal code, MTD sales, YTD sales, MTD commission, YTD commission, and commission rate). Each sales rep represents a single territory.

c. Enter and edit customers (customer number, name, first line of address, second line of address, city, state, postal code, MTD sales, YTD sales, current balance, and credit limit). A customer can have a different name and address to which goods are shipped (called the ship-to address). Each customer has a single sales rep who is located in a single territory. The sales rep must represent the territory in which the customer is located.

d. Enter and edit parts (part number, description, price, MTD and YTD sales, units on hand, units allocated, and reorder point). Units allocated is the number of units that are currently present on some open orders. The reorder point is the lowest value acceptable for units on hand without the product being reordered. On the stock status report, which will be described later, an asterisk indicates any part for which the number of units on hand is less than the reorder point.

e. Enter and edit vendors (vendor number, name, address, city, state, and postal code). In addition, for each part supplied by the vendor, enter and edit the part number, the price the vendor charges for the part, the minimum order quantity that the vendor will accept for this part, and the expected lead time for delivery of this part from this vendor.

Order entry (order number, date, customer, customer PO number, and order detail lines). An order detail line consists of a part number, a description, the number ordered, and the quoted price. Each order detail line includes a sequence number that is entered by the user. Detail lines on an order must print in the order of this sequence number. The system should calculate and display the order total. After all orders for the day have been entered, the system prints order acknowledgment/picking list reports (see Figure A-18). In addition, for each part ordered, the system must increase the units allocated for the part by the number of units that the customer ordered.

The invoicing system has the following requirements: 1. Enter the numbers of the orders to be released. For each order, enter the ship date for invoicing and the freight amount. Indicate whether the order is to be shipped in full or in part. If an order is to be partially shipped, enter the number shipped for each order detail line. The system will generate a unique invoice number for this invoice. 2. Print invoices for each of the released orders. (A sample invoice is shown in Figure A-19.) 3. Update files with information from the printed invoices. For each invoice, the system adds the invoice total to the current invoice total. It also adds the current balance and the MTD and YTD sales for the customer that placed the order. The system also adds the total to the MTD and YTD sales for the sales rep who represents the customer. In addition, the system multiplies the total by the sales reps commission rate and adds this amount to the MTD commission earned and the YTD commission earned. For each part shipped, the system decreases units on hand and units allocated by the number of units of the part or parts that were shipped. The system also increases the MTD and YTD sales of the part by the amount of the number of units shipped multiplied by the quoted price.

4. Create an invoice summary record for each invoice printed. These records contain the invoice number, date, customer, sales rep, invoice total, and freight. 5. Delete the released orders.

Receive payments on account (customer number, date, and amount). The system assigns each payment a number, adds the payment amount to the total of current payments for the customer, and subtracts the payment amount from the current balance of the customer.

Report requirements. The following is a list of the reports required by Holt Distributors: a. Territory List: For each territory, list the number and name of the territory; the number, name, and address of each sales rep in the territory; and the number, name, and address of each customer represented by these sales reps.

b. Customer Master List: For each customer, list the customer number, the bill-to address, and the ship-to address. Also list the number, name, address, city, state, and postal code of the sales rep who represents the customer and the number and name of the territory in which the customer is located.

c. Customer Open Order Report: This report lists open orders organized by customer. It is shown in Figure A-20.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MY ANSWER:

Database Design Language:

Territory (Territory Number, Territory Name, Region, Quoted Price)

Sales Rep (EmployeeID, MTD Sales, YTD Sales, MTD Commission, YTD Commission, Commission Rate, Territory Number)

FK Territory Number => Territory

FK EmployeeID => Employee

Employee (EmployeeID, Employee Name, Employee Address1, Employee Address2, Employee City, Employee State, Employee Zip, Employee Phone)

Customer (Customer Number, First Name, Last Name, Address1, Address2, City, State, Zip Code, Phone Number, MTD, Sales, YTD Sales, Current Balance, Credit Limit, Ship Name, Ship Address1, Ship Address2, Ship City, Ship State, Ship Zip Code, Current Invoice Total, Current Payment Total, Current Amount, Over 30, Over 60, Over 90, Previous Balance, EmployeeID)

FK EmployeeID => Sales Rep

Part (Part Number, Vendor Number, Part Description, Unit Price, MTD Sales, YTD Sales, YTD Sales, Units On Hand, Units Allocated, Reorder Point, Vendor Price, Minimum Order Quantity, Expected Lead Time)

FK Vendor Number => Vendor

Vendor (Vendor Number, Name, Address, City, State, Zip Code, Phone Number, Credit_Limit)

Order Detail (Order Number, SEQ Number, Part Number Vendor Number, Number Ordered, Quoted Price, Line Total Comments)

FK Order Number => Order

FK Part Number, Vendor Number => Part

Invoice (Invoice Number, Ship Date, Freight, Ship Status)

Invoice Detail (Invoice Number, Order Number, SEQ Number, Quantity Shipped)

FK Order Number, SEQ Number => Order Detail

FK Invoice Number => Invoice

Payment (Payment Number, Customer Number, Payment Date, Amount)

FK Customer Number => Customer

Obtaining Information from Existing Documents (continued) 10/15/2013 Invoice 11025 HOLT DISTRIBUTORS 146 NELSON PLACE BRONSTON, MI 49802 SOLD SHIP TO: TO: Smith Rentals 153 Main St Suite 102 Grandville, MI 49494 A & B Supplies 2180 Halton P Arendville, MI 49232 Custome P.O. No Our Order No. 12424 Order Date 10/02/2013 1015/2013 10-Brown, Sam Ship Date Sales R 1354 antit Order | Ship | BIO | Item Number | Description | Price AT414 0 BT222 Lounge Chair $42.00 Arm Chair Amount $210.00 $204.00 4 4 $51.00 Freight $42.50 Pay This Amount $456.50 FIGURE 6-14: Invoice for Holt Distributors 31 Obtaining Information from Existing Documents (continued) 10/15/2013 Invoice 11025 HOLT DISTRIBUTORS 146 NELSON PLACE BRONSTON, MI 49802 SOLD SHIP TO: TO: Smith Rentals 153 Main St Suite 102 Grandville, MI 49494 A & B Supplies 2180 Halton P Arendville, MI 49232 Custome P.O. No Our Order No. 12424 Order Date 10/02/2013 1015/2013 10-Brown, Sam Ship Date Sales R 1354 antit Order | Ship | BIO | Item Number | Description | Price AT414 0 BT222 Lounge Chair $42.00 Arm Chair Amount $210.00 $204.00 4 4 $51.00 Freight $42.50 Pay This Amount $456.50 FIGURE 6-14: Invoice for Holt Distributors 31