Product Bill-of-Materials Report

Assignment: Create a ProductCode, Description, Price table based on given SQL code and sample reports/screens.

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:
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 Screen
1NF:
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 Report
1NF:
ProductExplosionReport( ProductCode, Description )
ProductPartsReqd( ProductCode, PartNum, PartDesc, QtyReqd )

2NF:
ProductExplosionReport( ProductCode, Description )
ProductPartsReqd( ProductCode, PartNum , QtyReqd)
Parts( PartNum, PartDesc )
3NF:
ProductExplosionReport( ProductCode, Description )
ProductPartsReqd( ProductCode, PartNum )
Parts( PartNum, PartDesc)
SQLCODE
create table Customers( CustId int primary key, CustName text, CustDiscount
decimal(4,2),
CreditLimit decimal(8,2) );
create table Orders( OrderId int primary key, OrderDate date, CustId int,
foreign key (custId) references customers);
create table OrderShippingAddress( OrderId int, ShippingAddress varchar(50),
foreign key (orderId) references Orders,
primary key(orderId, shippingAddress));
create table CustPhoneNum( CustId int, PhoneNo int,
foreign key (custId) references customers,
primary key(custId, PhoneNo) );
create table CustAddress( CustId int, Address varchar(100),
foreign key (custId) references customers,
primary key(custId, address) );
create table Products( ProductCode varchar(10) primary key, Description text,
Price decimal(6,2) );
create table OrderedProducts( OrderId int, ProductCode varchar(10) , Qty int,
BackOrdered int,
foreign key (orderId) references Orders,
foreign key (ProductCode) references Products,
primary key (OrderId, ProductCode) );
create table ProductsInventory( InventoryDate Date, ProductCode varchar(10),
QtyBackOrdered int, QtyOnHand int,
foreign key (ProductCode) references Products,
primary key(inventoryDate, productCode)
);
create table ProductExplosionReport( ProductCode varchar(10) primary key,
Description text,
foreign key (ProductCode) references Products,
);
create table Parts( PartNum int primary key, PartDesc text);
create table ProductPartsReqd( ProductCode varchar(10), PartNum int,

foreign key (ProductCode) references Products,
foreign key (Partnum) references Parts,
primary key(productCode, partnum)
);

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: [phone] [phone] 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
.
.