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
|