Project IS
Project Requirements Stage 2 15 Points
Step 1) (0 Points) Include your ERD with this submission. Stage 2 can’t be graded if your ERD is not included. Modify your ERD according to feedbacks given. If you have modified your ERD, submit the modified ERD.
Step 2) (15 Points) Transform the E-R model into a relational data model. Report the following 6 steps. Follow the example seen here.
a) Convert every entity into a table. Use underline(s) to identify the primary key of a table. See Example a) on the next page.
b) Convert all 1-n and 1-1 relationships. Clearly indicate attributes added as a result of converting a 1-1 or a 1-n relationships. Use italic fonts for this purpose. See Example b) on the next page.
c) Convert all m-n relationships. See Example b) on the next Page.
d) List the final conversion result. See Example c) on the next page. Use underlines to indicate primary keys. Identify foreign keys in each table.
e) After you have listed the tables based on the ERD, determine which normal form each table is in. If a table does not satisfy BCNF, either normalize it or clearly state the reasons why you choose to keep the table in a lower normal form. See Example d) on the next page.
f) List the final normalized tables with primary keys and foreign keys identified. See Example e) on the next page.
Example: Project ERD and the conversion process are shown on the next pages.
a): Convert all entities and get:
Vendor: (vendorId, vname, address, phone)
Order: (order#, Orderdate)
Product: (Stock#, price, Desc)
Payment: (PaymentNo, Pdate, Amount)
b): Convert all relationships
· Vendor-places-Order Relationship: This is a 1-n relationship with an attribute. Add the primary key of "1" entity (Vendor) to the many side (Order) as a foreign key. Relationship attribute is added too. Get:
Order: (order#, Orderdate, VendorId, OrderSource)
Foreign key: VendorId
· Order-Contains-Products Relationship: This is an m-n relationship with attributes, so make a new table, OrderDetails:
OrderDetails: (Order#, Stock#, Quantity, Price)
Foreign keys: Order#, Stock#
· Payment-pays for-Order: This is a 1-1 relationship. Since each payment must have a corresponding order but each order may not have a corresponding payment yet, to advoid null values, add order# (“1” side) to Payment (pretend “many” side). Get:
Payment: (PaymentNo, PDate, Amount, Order#)
Foreign key: Order#
c): Final Conversion Result:
Vendor: (vendorId, vname, address, phone)
Order: (order#, Orderdate, VendorId, OrderSource) Foreign key: VendorId
Product: (Stock#, price, Desc)
Payment: (PaymentNo, Pdate, Amount, Order#). Foreign key: Order#
OrderDetails: (Order#, Stock#, Quantity, Price). Foreign keys: Order#, Stock#
d): Normal Form Determinations:
All Tables are in BCNF.
e): Final Result after normalization
Vendor: (vendorId, vname, address, phone)
Order: (order#, Orderdate, VendorId, OrderSource) Foreign key: VendorId
Product: (Stock#, price, Desc)
Payment: (PaymentNo, PDate, Amount, Order#). Foreign key: Order#
OrderDetails: (Order#, Stock#, Quantity, Price). Foreign keys: Order#, Stock#
3 | Page
PaymentPaymentNoPDateOderOrder#OrdateVendorvnameaddressVendoridphoneProductStock#PricePaysForContainsPlaces1nm11nDescSourceQuantityPriceAmount
Payment PaymentNo PDate Oder Order# Ordate Vendor vname address Vendorid phone Product Stock# Price PaysFor M1 M2 M3 M4 M1 M2 M3 M4 Contains Places M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 1 n m 1 1 n Desc Source Quantity Price Amount