1 1000 201000 1 202000 1 101100 4 101200 2 50 00 100200 1 101100 250 00 101200 1
ID: 3751790 • Letter: 1
Question
1 1000 201000 1 202000 1 101100 4 101200 2 50 00 100200 1 101100 250 00 101200 1 300 00 30 00 50 00 3 2000 4 2000 5 3000 6 3000 7 3000 300 00 50 00 Where SKU is a "Stock have at least one SKU associated with it, and may have several. Use this table and the detailed discussion of normal forms on Keeping Unit number, which is similar to a part number. Here it indicates which product was sold on each line of the table Nate that one 99-100 to answer the fallowing questions A.Define INF. Is ORDER-ITEM in INFIf not, why not, and what would have to be done to put it into INR Make any chages necesary to put OOR myto tNFh requires you to create an additional table, make sure that the new table is also in 1NF B. Define ZNF. Now that ORDER-ITEM is in INF, ts it also in 2NF? If not, why not, and what would have to be done to put ito NR Make any changes necessary to put ORDER JTEM into 2NF. If this step requires you to create an additional table, make sure that the new table is also in 2NF C Define 3NF. Now that ORDERJTEM is in 2NF, is it also in 3NF? If not, why not, and what would have to be done to put it into 3NF? Make any changes necessary to put ORDER ITEM into 3NF. If this step requires you to create an additional table, make sure that the new table and any other tables created in previous steps are also in 3NF D. Define BCNF. Now that ORDER_ITEM is in 3NF, is it also in BCNF? If not, why not, and what would have to be done to put it into BCNF? Make any changes necessary to put ORDER JTEM into BCNF. If this step requires you to create an additional table, make sure that the new table and any other tables created in previous steps are also in BCNFExplanation / Answer
a) 1NF of first normal form is a property of a relation in relational database. In other word 1NF is any table which meets the definition of relation
Order_Item is:
ORDER_ITEM (OrderNumber, SKU, Quantity, Price)
hence ORDER_ITEM is in 1NF
b)
Second Normal Form (2NF) is a normal form used in normalization. A relation which is in 1NF, must meet additional criteria to qualify for 2NF. A relation is in 2NF if it is in 1NF and all nonkey attributes are determined by the entire primary key.
The primary key for this scenario is both OrderNumber and SKU.
But nonkey attribute 'Price' is determined only by SKU (and not by the entire primary key ie OrderNumber and SKU)
hence the ORDER_ITEM is not in 2NF
It can be in 2NF, if we create a new table ITEM where SKU is both the Primary Key and Foreign Key, and Price is moved out of ORDER_ITEM to the ITEM Table.
ORDER_ITEM (OrderNumber, SKU, Quantity)
ITEM(SKU, Price)
c)
Third Normal Form (3NF) is the normal form used to reduce the duplication of data and ensure referential integrity by ensuring that 1) the entity is in Second Normal Form, and 2) and no nonkey attributes are determined by any other nonkey attributes.
As the original question is not in 2NF, it is NOT in 3NF.
The fix for 2NF (ie creating new table) fixes this for 3NF as well. Then both ORDER_ITEM and ITEM both are in 3NF
d)
Boyce-Codd Normal Form (BCNF) is any table that 1) in Third Normal Form, and 2) and all determinates are candidate keys i.e. there are no non-trivial functional dependencies of attributes on anything other than a superset of a candidate key.
As the original question is not in 3NF, it is NOT in BCNF.
The fix for 2NF (ie creating new table) fixes this for 3NF and hence for BCNF as well. Then both ORDER_ITEM and ITEM both are in BCNF.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.