Database assignment | Computer Science homework help

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. 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. Samples of the existing reports and screens are shown below. The pictures below are:

1.    Order screen

2.    Product-Inventory screen

3.    Product Bill-Of-Materials Report

 

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. Order 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. (e.g. a screw can be used in a chair, bar stool etc.).

 

Using all the provided information create relational notation for each of the 3 pictures below (e.g.

Order (order_id, xxx, xxx)) showing the 1st, 2nd, and 3rd normal forms of the data design.

 

1: Order Screen

 

1NF:

 

 

 

 

 

2NF:

 

 

 

 

 

3NF:

 

 

 

 

 

2: Product Inventory Screen

 

1NF:

 

 

 

 

 

2NF:

 

 

 

 

 

3NF:

 

 

 

 

 

3: Product Bill-Of-Materials Report

 

1NF:

 

 

 

 

2NF:

 

 

 

 

3NF:

SAMPLE REPORTS AND SCREENS

 

 

ABC MANUFACTURING ORDER FORM                                              DATE: AUG 30, 2002

 

ORDER # 9932

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

123 THAT STREET                                                                         ANYWHERE, CA 12345

ANYWHERE, CA 12345                                                                   

 

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

                                                  .

                                                           .

 

Using your completed part 1 document as input, create a new logical ERD using Visio 2013 (Crow’s Foot Database Notation template).

I want to see you map your 3NF relational notation into an ERD in Visio.

As stated in the overview document, decide which design (relational notation) you will be using (mine or yours).

Then create a new SQL Server database containing the needed tables and relationships.

Once you have implemented the database design upload the SQL Server database file (xxx.mdf) for grading.







Calculate Your Essay Price
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 10% with the coupon code: best10