Question

(Data base Management) Our company allows each sales representative to place sales orders (SOs) for customers, for items that they may need. The company refers to “items” and “products” as the same thing, as we are a service provider and do not produce any physical “product” that we sell, outright. Consider the following SO (Sales Order) table:

Attribute	Sample	Sample	Sample	Sample	Sample	Sample
SALES_ORDER_NUM	211345	211345	211345	211346	211347	211348
SALES_ORDER_LINE_NUM	1	2	3	1	1	1
SALE_DATE	15-Oct-15	15-Oct-15	15-Oct-15	15-Oct-15	16-Oct-15	17-Oct-15
PROD_NUM	CG-3422	TK-4567	CF-6789	CG-3422	EM-8765	CG-3422
PROD_NAME	Coffee Grinder	Tea Kettle	Coffee Filter	Coffee Grinder	Espresso Machine	Coffee Grinder
CUST_CODE	211	211	211	456	157	876
CUST_NAME	Buzz Light Year	Buzz Light Year	Buzz Light Year	Homer Simpson	Chewbacca	Java et. al
QUANT_SOLD	1	1	4	1	1	5
PROD_UNIT_PRICE	$34.50	$57.55	$14.50	$25.99	$500.27	$34.50
SALES_REP_ID	43	43	43	16	15	23
SALES_REP_NAME	Hans Solo	Hans Solo	Hans Solo	R2-D2	Leia Fischer	Darth Jones
DEPT_NAME	NorthEast	NorthEast	NorthEast	Southern	Mid West	West Coast
DEPT_CODE	200	200	200	100	300	500

• The table does not contain repeating groups
• Any SO must have at least one item/product being purchased
• An SO can reference many items/products
• Each SO is placed by only one customer , on a given date
• Each customer has placed at least one SO at some point in time; many customers have placed with more than one SOs
• The company uses a list of sales representative that receive the SOs.
• Each of the sales representative has received SOs in the past.
• Each of the sales representative can receive several SOs.
• Each of the products has been part of several SOs.
• Each sales representative works for one department. Each department can employ multiple sales representative.
• Each department will have at least one sales representative to server customers in that region.

2a: Draw the Dependency Diagram for the Sales Order System shown above – AS IT IS (1NF).

   Show all partial and transitive dependencies and mark them as such.

2b. Draw the dependency diagram for the Sales Order system in 2NF. Name your Entities and

mark any transitive dependencies with an arrow.

Answer 1

1. SALES_ORDER_NUM ------------> CUST_CODE
2. CUST_CODE ------------------> CUST_NAME
3. SALES_REP_ID-------------> SALES_REP_NAME
4. DEPT_NAME-------->DEPT_CODE
5. PROD_NUM-------->PROD_NAME
6. SALES_REP_ID-------->DEPT_CODE, DEPT_NAME

The primary keys of SO table is (SALES_ORDER_NUM, CUST_CODE, SALES_REP_ID)

Partial Dependencies are as follows

CUST_CODE------->CUST_NAME

SALES_REP_ID------>SALES_REP_NAME

SALES_REP_ID--------> DEPT_NAME, DEPT_CODE

Transitive Dependencies are as follows

PROD_NUM------>PROD_NAME

DEPT_NAME----->DEPT_CODE

--------------------------------------------------------------------------------------------------------------------------------------------------

2B. Now the table should be in 2NF form, so we need to perform the following decomposition

SO=( SALES_ORDER_NUM, SALES_ORDER_LINE_NUM, SALES_DATE, PROD_NUM, PROD_NAME, CUST_CODE, QUANT_SOLD, PROD_UNIT_PRICE, SALES_REP_ID)

Primary key is bold and underlined

CD(CustomerDetails) =(CUST_CODE, CUST_NAME)

SR(Sales Representative)=(SALES_REP_ID, SALES_REP_NAME,DEPT_NAME)

Department=(DEPT_NAME, DEPT_CODE)