数据库代写 ABC Manufacturing

[FILE: case03-ABC-Mfg.doc]

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.

 

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

 

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

 

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

 

  1. As products are manufactured the product data is updated accordingly along with the part inventory data.

 

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

  1. S.D. KURTZ SHIPPING ADDRESS: 456 NO STREET

123 THAT STREET                                                                   HAMILTON, ONTARIO

TORONTO, ONTARIO                                                           L6K 5J4

A9B 8C7

 

PHONE: (416) 879-0045    (416) 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 OWING          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

  .

                                        .

                                       

Normalization Exercise 2

 

 

 

                     INVOICE

 

 

HILLTOP ANIMAL HOSPITAL                      DATE: JAN 13/2002

INVOICE # 987

 

  1. RICHARD COOK

123 THIS STREET

MY CITY, ONTARIO

Z5Z 6G6

 

PET              PROCEDURE                           AMOUNT

 

ROVER                 RABIES VACCINATION                     30.00

MORRIS                RABIES VACCINATION                     24.00

 

 

TOTAL                                        54.00

TAX (8%)                                    4.32

 

AMOUNT OWING                               58.32

 

 

 

UNF:

 

 

 

 

1NF:

 

 

 

 

2NF:

 

 

 

 

 

3NF:

 

     .