Complete an information and physical level design of a database for Holt Distributors. Holt Distributors starts on page 337 with the general description, and goes through page 343. Read the entire selection before doing any work. Summary of the design process you used, 1 full page minimum. You will be describing how you resolved the problem. Examples of some questions you may want to answer; o How did you start? o What were the most important facts given? o Did you follow the steps given in the book? And what were those steps? o What assumptions did you make? o What was the most important thing you learned? Summary of the database administration issues and how would you resolve those issues. 1 full page minimum Examples of some issues you may want to discuss; o Data integrity & concurrency o Access privileges o Security o Disaster planning o Achieving o DBMS Evaluation and Selection DBDL documentation of the entire database. E-R diagram of the entire database The following is required of the physical database; Tables set up with data, relationships established, appropriate indexes set up, appropriate data integrity rules in place Required reports and queries. Here are some changes to the textbook requirements that will simplify your work somewhat. The transaction requirements give you information on required tables. (page 339-340) Assume that ALL orders ship entirely, in other words there are no partial shipments. Either they ship the entire order or they wait until they have all the required units and then ship. Do not include the Customer PO information Freight cost is 10.265% of the total bill Set up the DB based on the current data. You will not need to create triggers/macros that cause data to be updated based on data entry/changes. (example - page 339-g-2 & 3) Fall 2017 Page 2 of 3 The report requirements start on page 340 and go to 342. Here are some changes to the textbook requirements. All reports should fit on 8.5 X 11 paper and can be several pages long. Create reports for the following; o a Territory List, here is an example of how you might set it up, this is only for territory 01 o b Customer Master List, here is an example of how you might set it up, this is just one customer o c - change to "Orders Not Shipped", include the customer number, name, order number, order date, part number, description, number ordered and price o d not required o e Daily Invoice Register, see Figure A-22, user should be able to pick a date for the report (so report is based on a parameter query) o f Monthly Invoice Register, same format as the daily, except this time for a whole month and user should be able to pick the month. o g Stock Status Report, here is an example of how to set this one up (not required-the asterisk part listed in the text) Need to Order = On Hand Reorder Point Fall 2017 Page 3 of 3 o h Reorder Point List, see example o i Vendor Report, here is an example of how you might set it up o j not required o k not required o l Customer Mailing Labels, see figure A-24 o m not required o n Monthly Sales Rep Commission Report o o not required Don't do the month-end-processing section that starts on page 342.

Holt Distributors buys products from its vendors and sells those products to its customers. The Holt Distributors operation is divided into territories. Each customer is represented by a single sales rep, who must be assigned to the territory in which the customer is located. Although each sales rep is assigned to a single territory, more than one sales rep can be assigned to the same territory.

When a customer places an order, the computer assigns the order the next available order number. The data entry clerk enters the customer number, the customer purchase order (PO) number, and the date. (Customers can place orders by submitting a PO, in which case, a PO number is recorded.) For each part that is ordered, the clerk enters the part number, quantity, and quoted price. (When it is time for the clerk to enter the quoted price, the computer displays the price from the master price list. If the quoted price is the same as the actual price, the clerk takes no special action. If not, the clerk enters the quoted price.)

When the clerk completes the order, the system prints the order acknowledgment/picking list form shown in Figure A-18 and sends it to the customer for confirmation and payment. When Holt Distributors is ready to ship the customers order, this same form is used to pick the merchandise in the warehouse and prepare it for delivery.

An order that has not been shipped (filled) is called an open order; an order that has been shipped is called a released order. When orders are released, the system prints an invoice, sends it to the customer, and then increases the customers balance by the invoice amount. Some orders are completely filled; others are only partially filled, meaning that only part of the customers order was shipped. In either case, when an entire order or a partial order has been shipped, the order is considered to have been filled and is no longer considered an open order. (Another possibility is to allow back orders when the order cannot be completely filled. In this case, the order remains open, but only for the back-ordered portion.) When the system generates an invoice, it removes the order from the open orders file. The system stores summary information about the invoice (number, date, customer, invoice total, and freight) until the end of the month. A sample invoice is shown in Figure A-19.

Most companies use one of two methods to accept payments from customers: open items and balance forward. In the open-item method, customers make payments on specific invoices. An invoice remains on file until the customer pays it in full. In the balance-forward method, customers have balances. When the system generates an invoice, the customers balance is increased by the amount of the invoice. When a customer makes a payment, the system decreases the customers balance by the payment amount. Holt Distributors uses the balance-forward method.

At the end of each month, the system updates and ages customers accounts. (You will learn about month-end processing requirements and the update and aging processes in the following sections.) The system prints customer statements, an aged trial balance (described in the report requirements section), a monthly cash receipts journal, a monthly invoice register, and a sales rep commission report. The system then removes cash receipts and invoice summary records from the database and sets month-to-date (MTD) fields to zero. When the system processes the monthly data for December, it also sets the year-to-date (YTD) fields to zero.

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.

d. Item Open Order Report: This report lists open orders organized by item and is shown in Figure A-21

e. Daily Invoice Register: For each invoice produced on a given day, list the invoice number, invoice date, customer number, customer name, sales amount, freight, and invoice total. A sample of this report is shown in Figure A-22.

f. Monthly Invoice Register: The monthly invoice register has the same format as the daily invoice register, but it includes data for all invoices that occurred during the selected month.

g. Stock Status Report: For each part, list the part number, description, price, MTD and YTD sales, units on hand, units allocated, and reorder point. For each part for which the number of units on hand is less than the reorder point, an asterisk should appear at the far right of the report.

h. Reorder Point List: This report has the same format as the stock status report. Other than the title, the only difference is that parts for which the number of units on hand is greater than or equal to the reorder point will not appear on this report.

i. Vendor Report: For each vendor, list the vendor number, name, address, city, state, and postal code. In addition, for each part supplied by the vendor, list the part number, the description, 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 the vendor.

j. Daily Cash Receipts Journal: For each payment received on a given day, list the number and name of the customer that made the payment and the payment amount. A sample report is shown in Figure A-23.

k. Monthly Cash Receipts Journal: The monthly cash receipts journal has the same format as the daily cash receipts journal, but it includes all cash receipts for the month.

l. Customer Mailing Labels: A sample of the three-across mailing labels printed by the system is shown in Figure A-24.

m. Statements: The system must produce a monthly statement for each active customer. A sample statement is shown in Figure A-25.

n. Monthly Sales Rep Commission Report: For each sales rep, list his or her number, name, address, MTD sales, YTD sales, MTD commission earned, YTD commission earned, and commission rate.

o. Aged Trial Balance: The aged trial balance report contains the same information that is printed on each customers statement.