Reference no: EM13702599

Question 1: Create an ERD based on the Crow's Foot model, using the following requirements:
• An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative.
• The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices.
• An INVOICE can include many detail lines (LINE), each of which describes one product bought by the customer.
• The product information is stored in a PRODUCT entity.
• The product's vendor information is found in a VENDOR entity.

Question 2: Given the following business scenario, create a Crow's Foot ERD using a specialization hierarchy if appropriate.

Granite Sales Company keeps information on employees and the departments that they work in. For each department, the department name, internal mail box number, and office phone extension are kept. A department can have many assigned employees, and each employee is assigned to only one department. Employees can be salaried employees, hourly employees, or contract employees. All employees are assigned an employee number. This is kept along with the employee's name and address. For hourly employees, hourly wage and target weekly work hours are stored (e.g. the company may target 40 hours/week for some, 32 hours/week for others, and 20 hours/week for others). Some salaried employees are salespeople that can earn a commission in addition to their base salary. For all salaried employees, the yearly salary amount is recorded in the system. For salespeople, their commission percentage on sales and commission percentage on profit are stored in the system. For example, John is a salesperson with a base salary of $50,000 per year plus 2-percent commission on the sales price for all sales he makes plus another 5 percent of the profit on each of those sales. For contract employees, the beginning date and end dates of their contract are stored along with the billing rate for their hours.

Question 3: Normalize the following table structure. Show the dependency diagram for each of the normal forms. Do not go beyond the 3rd normal form.

PATIENT

Patient_ID Insurance Co ID Doctor_ID Fname P_Lname P_street P_City P_Zip P_Phone Ins_First_contact Ins_Second_contact Ins_Phone Doc_Fname Doc_Lname Doc_Beeper

Question 4: Normalize the following table structure. Show the dependency diagram for each of the normal forms. Do not go beyond the 3rd normal form.

Attribute Name	Sample Value	Sample Value	Sample Value	Sample Value	Sample Value
INV_NUM	211347	211347	211347	211348	211349
PROD_NUM	AA-E3422QW	QD-300932X	RU-995748G	AA-E3422QW	GH-778345P
SALE_DATE	15-Jan-2010	15-Jan-2010	15-Jan-2010	15-Jan-2010	16-Jan-2010
PROD_LABEL	Rotary sander	0.25-in. drill bit	Band saw	Rotary sander	Power drill
VEND_CODE	211	211	309	211	157
VEND_NAME	NeverFail, Inc.	NeverFail, Inc.	BeGood, Inc.	NeverFail, Inc.	ToughGo, Inc.
QUANT_SOLD	1	8	1	2	1
PROD_PRICE	$49.95	$3.45	$39.99	$49.95	$87.75