MySQL Prog CSIS1550 Chapter 2 Premiere Products Database Design Homework Using t

Question

MySQL Prog

CSIS1550 Chapter 2 Premiere Products Database Design Homework

Using the understanding of database design concepts and tools in Chapter 2 along with the data represented in the Premiere Products database, answer the following question.

Problem Statement:

The PART table in the Premiere Product database introduced in Chapter 1 is described in DBDL as follows.

PART (PartNum, Description, OnHand, Class, Warehouse, Price)

The table is normalized to 3NF (Third Normal Form) and ready to be used in the database system, because there is:

NO repeating groups, so it passes the 1NF test

NO partial dependency, so it passes the 2NF test

NO transitive (or hidden) dependency, so it passes the 3NF test

Now take a look at this PART table:

PART (PartNum, Description, OnHand, Class, Warehouse, Price, (OrderNum, OrderDate, CustomerNum, CustomerName, NumOrdered, QuotedPrice))

Question 1:

Determine the normalization of this PART table. You need to explain it using the proper database normalization terms.

Question 2:

After determining the normalization level, convert it to the Third Normal Form (3NF) using the correct set of normalization tools discussed in Chapter 2 and the class discussion INCLUDING FUNCTIONAL DEPENDENCY and DEFINITION OF DIFFERENT NORMALIZATION LEVELS.

You MUST show the DBDL of ALL the tables at the end of your answer when they reach the Third Normal Form (3N.F) including the Primary Key.

Explanation / Answer

======================================================================
PART (PartNum, Description, OnHand, Class, Warehouse, Price, (OrderNum, OrderDate, CustomerNum, CustomerName, NumOrdered, QuotedPrice))
Question 1:
Determine the normalization of this PART table. You need to explain it using the proper database normalization terms.

---------
Answer:
---------
   -------------------------------------------------------------
   Dependency relation for the given schema and are in 3NF
   -------------------------------------------------------------
   PartNum -> Description, OnHand, Class, Warehouse, Price
   (Descriotion,OnHand, Class, Warehouse, Price are obtained by PartNum)
      
   OrderNum -> OrderDate, CustomerNum, CustomerName
   (OrderDate, CustomerNum, CustomerName are obatained by OrderNum)

   CustomerNum -> CustomerName
   (CustomerName is obtained by CustomerNum)

   PartNum, OrderNum > NumOrdered, QuotedPrice
   (NumOrdered, QuotedPrice are obtained by combination of PartNum, OrderNum)

======================================================================
Question 2:
After determining the normalization level, convert it to the Third Normal Form (3NF) using the correct set of normalization tools discussed in Chapter 2 and the class discussion INCLUDING FUNCTIONAL DEPENDENCY and DEFINITION OF DIFFERENT NORMALIZATION LEVELS.
You MUST show the DBDL of ALL the tables at the end of your answer when they reach the Third Normal Form (3N.F) including the Primary Key.

---------
Answer:
---------
   ------------------------------------------------------------
   The database tables are from 3NF relations are as follows.
   ------------------------------------------------------------
   PART (PartNum, Description, OnHand, Class, Warehouse, Price)
   Orders (OrderNum, OrderDate, CustomerNum)
   Customer (CustomerNum, CustomerName)
   PartOrders (PartNum, OrderNum, NumOrdered, QuotedPrice)
  
======================================================================

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.