Assignment on ABC Manufacturing
5 Pages1225 Words1505 Views
Added on 2019-09-22
Assignment on ABC Manufacturing
Added on 2019-09-22
ShareRelated Documents
ABC MANUFACTURINGCUSTOMER ORDER AND PRODUCT APPLICATIONThe 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 screen2.Product-Inventory screen3.Product Bill-Of-Materials ReportCustomer Order and Product Application Considerations:1.Each customer must be on file before an order can be placed. The name, address(s), phonenumber(s), and credit limit must be recorded. All other data items are optional. If there is noshipping 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 willbe 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 Screen1NF:Order_Screen( OrderId, Date, CustId, CustName, CreditLimit, ShippingAddress, MailingAddress1,MailingAddress2, PhoneNum1, PhoneNum2, CustDiscount, ProductCode1, Desc1, Qty1, BackOrdered1, Price1, Desc2, Qty2, BackOrdered2, Price2, ......, Desc10, Qty10, BackOrdered10, Price10 )2NF:Order_Screen( OrderId, Date, CustId, CustName, CreditLimit, ShippingAddress, MailingAddress1,MailingAddress2, PhoneNum1, PhoneNum2, CustDiscount, ProductCode1, Desc1, Qty1, BackOrdered1, Price1, Desc2, Qty2, BackOrdered2, Price2, ......, Desc10, Qty10, BackOrdered10, Price10 )3NF:Orders( OrderId, Date, CustId )OrderShippingAddress( OrderId, ShippingAdddress )Customers(CustId, CustName, CustDiscount, CreditLimit )CustPhoneNum( CustId, PhoneNo )CustAddress( CustId, Address )Products( ProductCode, Desc, Price )OrderedProducts( OrderId, ProductCode, Qty, BackOrdered ) 2: Product Inventory Screen1NF:ProductsInventory( Date, ProductCode, Desc, QtyOnHand, QtyBackOrdered, Price ) 2NF:ProductsInventory( Date, ProductCode, QtyBackOrdered, QtyOnHand ) Products( ProductCode, Desc, Price ) 3NF:ProductsInventory( Date, ProductCode, QtyBackOrdered, QtyOnHand ) Products( ProductCode, Desc, Price ) 3: Product Bill-Of-Materials Report1NF:ProductExplosionReport( ProductCode, Description ) ProductPartsReqd( ProductCode, PartNum, PartDesc, QtyReqd )
End of preview
Want to access all the pages? Upload your documents or become a member.