Database Design Project and need coding to execute the Program

1

CSCI-760 Database Systems

Database Design Project

Holt Distributors has determined that a database should be designed to handle the new

requirements. A database is needed that will satisfy the following requirements.

General Description.

Holt Distributors is a distributor. It buys products from its vendors and sells these 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

resides. Although each sales rep is assigned to a single territory, more than one may be

assigned to the same territory.

When a customer places an order, the order is assigned a number. The customer number, the

order number, the customer purchase order (PO) number, and date are entered. (Customers

can place orders by sending in a purchase order. For orders that are placed in this fashion, the

PO number is recorded.) For each part that is ordered, the part number, quantity, and quoted

price are entered. (When it is time for the user to enter the quoted price, the price from the

master price list for parts is displayed on the screen. If the quoted price is the same as the

actual price, no special action is required. If not, the user enters the quoted price.) The order

may also contain special charges, for which a description of the charge and the amount of the

charge is entered. Finally, an order may include comments, in which case the comment is

entered. Following this, a form is printed that is a combination order acknowledgment/picking

list. This form, which is shown in Figure 1, is sent to the customer as a record of the order he or

she has placed. A copy of the form is also used when the time comes to “pick” the merchandise

that was ordered in the warehouse.

Until the order is filled, it is considered to be an open order. When the order is filled (which

may be some time later), it is said to be released. At this point, an invoice (bill) is printed and

sent to the customer, and the customer’s balance is increased by the amount of the invoice.

The order may have been filled completely or it may have been partially filled (for less than the

full amount originally requested). In either case, since the goods have 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 would

remain open but only for the back –ordered portion. Holt Distributors does not allow back

orders, however.) When an invoice (see Figure 2) is generated, the order is removed from the

file of open orders. Summary information is stored concerning the invoice (number, date,

customer, invoice total, shipping and tax) until the end of the month. Many companies employ

2

basically two methods for accepting payments from customers: open items and balance

forward. In the open-item approach, customers make payments on specific invoices. An invoice

remains on file until it is completely paid. In the balance forward approach, customers simply

have balances. When an invoice is generated, the customer’s balance is increased by the

amount of the invoice. When a payment is made, the customer’s balance is decreased by the

amount of the invoice. Holt Distributors uses the balance-forward approach.

Figure 1

At the end of each month, customers’ accounts are updated and aged. (The description of

month-end processing in the requirements that follow contains details of the update and aging

process.) Statements, an aged trial balance (defined under report requirements), a monthly

cash receipts journal, a monthly invoice register, and a sales rep commission report are printed.

Cash receipts and invoice summary records are then removed from the database. Month-to-

date fields are set to zero. If it is also the end of the year, year-to-date fields are set to zero.

3

Figure 2

Transaction Requirements. The following are the transaction requirements:

1. Enter/edit territories (territory number and name).

2. Enter/edit sales reps (sales rep number, name, address, city, state, ZIP, MTD sales, YTD

sales, MTD commission, YTD commission, and commission rate). Each sales rep

represents a single territory. (MTD stands for month-to-date and YTD stands for year-to-

date.) .

3. Enter/edit customers (customer number, name, first line of address, second line of

address, city, state, ZIP, MTD sales, YTD sales, current balance, and credit limit). A

customer may have a different name and address to which goods will be shipped, called

the “ship-to” address. Each customer has a single sales rep and resides in a single

territory. The sales rep must-represent the territory in which the customer resides.

4. Enter/edit parts (part number, description, price, MTD and YTD sales, units on hand,

units allocated, and reorder point). Units allocated are the number of units that are

currently “spoken for”; that is, the number of units of this part that are currently

4

present on some open orders. The reorder point is the lowest value acceptable for units

on hand without reordering the product.

5. Enter/edit vendors (vendor number, name, address, city, state, ZIP). In addition, for

each part supplied by the vendor, enter/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.

6. Order entry (order number, date, customer, customer PO number, and the order detail

lines). An order detail line consists of a part number, description, number ordered, and

quoted price. The system should calculate and display the order total. After all orders

for the day have been entered, customer order report (see Figure 1) is printed. In

addition, for each part ordered, the units allocated for the part must be increased by the

number of units that were ordered.

7. invoicing cycle:

a. Enter the numbers of the orders to be released. For each order, enter the ship date

for invoicing, the shipping charge and tax. Indicate whether the order is to be

shipped in full or partially shipped. lf it 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.

b. Print invoices for each of the released orders. A sample invoice is shown in Figure 2.

c. Update files with information from the invoices just printed. For each invoice, the

invoice total is added to the current invoice total, the current balance, and MTD and

YTD sales for the customer who placed the order. The total is also added to MTD and

YTD sales for the sales rep who represents the customer, and the total multiplied by

the sales rep’s commission rate, is added to MTD commission earned and YTD

commission earned. For each part shipped, units on hand and units allocated are

decremented by the number of units of the part that were shipped. MTD and YTD

sales of the part are increased by the product of the number of units shipped and

the quoted price.

d. Create invoice summary record for each invoice printed. These records contain the

invoice number, date, customer number, sales rep, and invoice total.

e. Delete all the released orders.

8. Receive payments on account (customer number, date, amount). Each payment is

assigned a number. The amount of the payment is added to the total of current

payments for the customer and is subtracted from the current balance of the customer.

Report Requirement. The following are the report requirements:

Commented [N1]: Customer info. Maybe customer number

Commented [N2]: Rep info. Sales_rep number

5

1. Territory list. For each territory, list the number and name of the territory, the number,

name, and address of each of the sales reps in the territory, and the number, name, and

address of each of the customers represented by these sales reps.

2. Customer master list. For each customer, list the number and both the address and the

ship-to address. Also list the number, name, address, city, state, and ZIP of the sales rep

who represents the customer as well as the number and name of the territory in which

the customer resides.

3. Open orders by customer. This report lists open orders organized by customer and is

shown in Figure 3.

Figure 3

4. Open orders by item. This report lists open orders organized by item.

5. Daily invoice register. For each invoice produced on a given day, list the invoice number,

the invoice date, the customer number, the customer name, the sales amount, and the

invoice total. A sample of this report is shown in Figure 4.

6

6. Monthly invoice register. The monthly invoice register has the same format as the daily

invoice register but includes all invoices for the month.

Figure 4

7. Stock status report. For each part, list the part number, description, price, TD 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.

8. 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.

9. Vendor report. For each vendor, list the vendor number, name, address, city, state, and

ZIP. In addition, for each part supplied by the vendor, list the part number, 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 this

vendor.

10. Daily cash receipts journal. For each payment received on a given day, list the number

and name of the customer who made the payment, together with the amount of the

7

payment. A sample of the report is shown in Figure 5.

Figure 5

11. Monthly cash receipts journal. The monthly cash receipts journal has the same format

as the daily cash receipts journal but includes all cash receipts for the month.

12. Customer mailing labels.

13. Statements. Monthly statements are to be produced; a sample is shown in Figure 6.

14. 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

the commission rate.

15. Aged trial balance. The aged trial balance is a report containing the same information

that is printed on the statements.

Month-End Processing. Month-end processing consists of taking the following actions at the

end of each month:

1. Update customer account information. In addition to the customer's credit limit, the

system must maintain the actual balance, current invoice total, and current payment

total whenever an invoice is produced or a payment is received. Monthly statements

8

are printed.

Figure 6

2. The current invoice total is set to zero, the current payment total is set to zero, and the

previous balance is set to the current balance in preparation for the coming month.

3. Print the monthly invoice register and the monthly cash receipts journal.

4. Print a monthly sales rep commission report.

5. Zero out all MTD fields. If it also happens to be year end, zero out all YTD fields.

6. Remove all cash receipts and invoice summary records. (In practice, such records would

be moved to historical type of database in order to allow for the possibility of future

reference. For the purpose of this illustration, we have disregarded this fact.)