Normalization Homework 2

CASE 3

ABC MANUFACTURING CUSTOMER - ORDER AND PRODUCT APPLICATION

The ABC Manufacturing company has a completely automated application system. The system, however, resides on index files and does not allow for decision support at all. In order to move to ad hoc queries, and "what if" queries, the company has decided to convert the existing system to a database.

Initially, the only criterion for the application is to replace the existing system with a database system. No ad hoc screen or reports have been anticipated. You will see the reports and screens that exist currently.

Use this case to perform the task that the module has asked of you.

Customer Order and Product Application Considerations

  1. Each customer must be on file before an order can be placed. The name, address(s), phone number(s), and credit limit must be recorded. All other data items are optional. If there is no shipping address, then the mailing address is used instead. Since customers can have identical names, a customer id has been assigned to each customer.
  2. Each order will have a computer generated id number. The order can have up to 10 line items. Discounts can be given to preferred customers and this discount amount will be recorded on the customer's record. Customers without a discount amount will not be given a discount.
  3. Each product listed on the order will show the standard price for that product. Discounts will be shown at the bottom of the order form.
  4. Orders that can be filled or partially filled are shipped immediately, and the product data is updated accordingly. Orders, or partial orders that cannot be filled will be backordered.
  5. As products are manufactured the product data is updated accordingly along with the part inventory data.
  6. A customer can place numerous orders. Products can be ordered by many different customers. The same part can be used in numerous products. (eg. a screw can be used in a chair, bar stool etc.)

SAMPLE REPORTS AND SCREENS

ABC MANUFACTURING ORDER FORM DATE: AUG 30, 2002
ORDER # 9932
MR. S. D. KURTZ

MAILING ADDRESS SHIPPING ADDRESS
456 NORTH HIGH STREET
FREDRICKTOWN, OHIO 43022
(740) 879-0045
456 EAST VINE STREET
MOUNT VERNON, OHIO 43050
(740) 786-3241

CUSTOMER DISCOUNT: 3%

ITEM # PRODUCT CODE DESCRIPTION QTY BACKORDERED FILLED PRICE/UNIT AMOUNT
1 FR223 HALF SIZE REFRIGERATOR 2 0 2 750.99 1501.98
2 TB101 PATIO TABLE 5 2 3 150.00 450.00
3 CH089 PATIO CHAIRS 20 0 20 35.00 700.00

TOTAL 2651.98
DISCOUNT AMT 79.56
AMOUNT OWED 2572.42

PRODUCTS INVENTORY AS AT: AUG 30, 2002

PRODUCT CODE DESCRIPTION QTY ON HAND QTY BACKORDERED PRICE
CH089 PATIO CHAIRS 140 0 35.00
FR223 HALF SIZE REFRIGERATOR 10 0 750.99
. . .        
TB101 PATIO TABLE 0 2 35.00
. . .        

ABC MANUFACTURING PRODUCT EXPLOSION REPORT

PRODUCT CODE DESCRIPTION PART NUMB. PART DESCRIPTION QTY REQUIRED
CH089 PATIO CHAIRS WOOD223 1 X 2 - 30" WOOD 8
    SCRW110 1.25" SCREWS 26
...        
TB101 PATIO TABLE WOOD995 2 X 4 - 48" WOOD 12
    SCRW110 1.25" SCREWS 34
...        

Steps to perform

  1. Determine a set of entities for the needed database that correctly model this information.
  2. Determine the attributes for each entity
  3. Find the functional dependancies for each table and it's attributes
  4. Normalize into 3NF
  5. Show the final design as a list of well defined tables, with Primary keys and Foreign keys clearly specified

Solution

Topic revision: r2 - 2013-11-11 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback