programming

INSY 3304 - Project 1

Order ID	Order Date	Cust ID	Cust Name	Cust Phone	Sales Rep ID	Sales Rep Name	Comm Class	Comm Rate	Dept ID	Dept Name	Prod ID	Prod Name	Prod Cat ID	Prod Cat Name	Prod Qty	Prod Price
100	1/24/2022	S100	John Smith	214-555-1212	10	Alice Jones	A	0.1	10	Store Sales	121	BD Hammer	1	Hand Tools	2	$8.00
											228	Makita Power Drill	2	Power Tools	1	$65.00
											480	1# BD Nails	4	Fasteners	2	$3.00
											407	1# BD Screws	4	Fasteners	1	$4.25
101	1/25/2022	A120	Renee Adams	817-555-3434	12	Greg Taylor	B	0.08	14	Corp Sales	610	3M Duct Tape	6	Misc	200	$1.75
											618	3M Masking Tape	6	Misc	100	$1.25
102	1/25/2022	J090	William Jones		14	Sara Day	Z	0	10	Store Sales	380	Acme Yard Stick	3	Measuring Tools	2	$1.25
											121	BD Hammer	1	Hand Tools	1	$7.00
											535	Schlage Door Knob	5	Hardware	4	$7.50
103	1/26/2022	B200	Ann Brown	972-555-7979	8	Kay Price	C	0.05	14	Corp Sales	121	BD Hammer	1	Hand Tools	50	$7.00
											123	Acme Pry Bar	1	Hand Tools	20	$6.25
104	1/26/2022	S100	John Smith	214-555-1212	10	Alice Jones	A	0.1	10	Store Sales	229	BD Power Drill	2	Power Tools	1	$50.00
											610	3M Duct Tape	6	Misc	200	$1.75
											380	Acme Yard Stick	3	Measuring Tools	2	$1.25
											535	Schlage Door Knob	5	Hardware	4	$7.50
105	1/26/2022	B200	Ann Brown	972-555-7979	8	Kay Price	C	0.05	14	Corp Sales	610	3M Duct Tape	6	Misc	200	$1.75
											123	Acme Pry Bar	1	Hand Tools	40	$5.00
106	1/27/2022	G070	Kate Green		20	Bob Jackson	B	0.08	10	Store Sales	124	Acme Hammer	1	Hand Tools	1	$6.50
107	1/27/2022	J090	William Jones		14	Sara Day	Z	0	10	Store Sales	229	BD Power Drill	2	Power Tools	1	$59.00
108	1/27/2022	S120	Wesley Sims	214-555-1234	22	Micah Moore	Z	0	16	Web Sales	235	Makita Power Drill	2	Power Tools	1	$65.00

Additional Information:

Home Improvement Warehouse is a store that sells home improvement products. All orders are picked up from the store. Each order must be sold to a specific customer. Each customer must have at least one order associated with him/her, and each customer is assigned to a specific sales rep. Each sales rep belongs to a specific department, and each department has at least one employee. Each sales rep is assigned to a commission class and earns the same commission rate for each of his/her sales, regardless of the sale amount or the department to which he/she belongs. It is possible to have a commission class to which no employees belong. Each order is for at least one product, but some products may not ever get ordered. Product prices may vary over time, and the price at the time of the sale must be captured for each transaction (assume that the prices shown in the table above are also the current prices). Each product belongs to a single category, but some categories may not have any products that belong to them.

Based on the information provided above, complete the following:

1. FIRST NORMAL FORM (1NF) – 20 pts:

a. Decompose the composite attributes into simple attributes.

b. Convert the table above to 1NF (eliminate repeating groups of data and select an appropriate PK).

c. Show the table structure format (table name with PK and all dependent attributes in parentheses).

d. Create a dependency diagram for the table above.

2. SECOND NORMAL FORM (2NF) – 20 pts:

a. Show the table structure format for each table in 2NF.

b. Create the dependency diagrams for the resulting tables.

3. THIRD NORMAL FORM (3NF) – 20 pts:

a. Convert to 3NF and show the table structure format for each table.

b. Create the dependency diagrams for the resulting tables.

4. ENTITY-RELATIONSHIP MODEL – 20 pts:

Using Chen notation, create an ERD showing all of the 3NF tables above. You must show the entities, relationships, connectivity, participation, and cardinality (it is not necessary to show the attributes on the ERD).

5. Submit Documents

Submit your documents from Steps 1 – 4, printed and bound (properly stapled or submitted in a way where the pages will not come apart or get out of order).