1. Answer questions regarding the following General Hardware Company's relational database.
SALESPERSON
Salesperson number
|
Salesperson name
|
Commission percentage
|
Year of hire
|
Office number
|
137
|
Baker
|
10
|
1995
|
1284
|
186
|
Adams
|
15
|
2001
|
1253
|
204
|
Dickens
|
10
|
1998
|
1209
|
361
|
Carlyle
|
20
|
2001
|
1227
|
CUSTOMER
Customer number
|
Customer name
|
Salesperson number
|
Headquarters location
|
0121
|
Main St. Hardware
|
137
|
New York
|
0839
|
Jane's Stores
|
186
|
Chicago
|
0933
|
ABC Home Stores
|
137
|
Los Angeles
|
1047
|
Acme Hardware Store
|
137
|
Los Angeles
|
1525
|
Fred's Tool Store
|
361
|
Atlanta
|
1700
|
XYZ Stores
|
361
|
Washington
|
1826
|
City Hardware
|
137
|
New York
|
2198
|
Western Hardware
|
204
|
New York
|
2267
|
Central Stores
|
186
|
New York
|
CUSTOMER EMPLOYEE
Customer number
|
Employee number
|
Employee name
|
Title
|
0121
|
27498
|
Smith
|
Co-Owner
|
0121
|
30441
|
Garcia
|
Co-Owner
|
0933
|
25270
|
Chen
|
VP Sales
|
0933
|
30441
|
Levy
|
Sales Manager
|
0933
|
48285
|
Morton
|
President
|
1525
|
33779
|
Baker
|
Sales Manager
|
2198
|
27470
|
Smith
|
President
|
2198
|
30441
|
Jones
|
VP Sales
|
2198
|
33779
|
Garcia
|
VP Personnel
|
2198
|
35268
|
Kaplan
|
Senior Accountant
|
PRODUCT
Product
|
Product
|
Unit
|
number
|
name
|
price
|
16386
|
Wrench
|
12.95
|
19440
|
I Iammer
|
17.50
|
21765
|
Drill
|
32.99
|
24013
|
Saw
|
26.25
|
26722
|
Pliers
|
11.50
|
SALES
Salesperson number
|
Customer number
|
Product number
|
Date
|
Quantity
|
137
|
0839
|
19440
|
2/21/2002
|
473
|
137
|
1700
|
24013
|
2/27/2002
|
170
|
137
|
2267
|
26722
|
3/1/2002
|
688
|
186
|
1047
|
16386
|
3/1/2002
|
1,745
|
186
|
0839
|
19440
|
3/12/2002
|
2,529
|
186
|
1700
|
21765
|
3/17/2002
|
1,962
|
361
|
0121
|
24013
|
3/21/2002
|
3,071
|
204
|
2267
|
21765
|
4/03/2002
|
809
|
204
|
0839
|
26722
|
4/17/2002
|
734
|
OFFICE
Office number
|
Telephone
|
Size (sq. ft.)
|
1253
|
901-555-4276
|
120
|
1227
|
901-555-0364
|
100
|
1284
|
901-555-7335
|
120
|
1209
|
901-555-3108
|
95
|
A. Draw the General Hardware Company Entity-Relational diagram.
B. Choose a primary key for each of the relations.
C. Choose foreign key(s) for each of the relations.
D. Determine the cardinality of the relationships between a. Salesperson and Customer b. Customer and Customer Employee c. Salesperson and Product d. Customer and Product e. Salesperson and Office E. Suppose that salesperson 361, Carlyle, left the company and his record was deleted from the SALESPERSON relation. Have it affected the database data integrity? Explain.
F. Suppose that after a number of transactions, the table CUSTOMER looks like following
CUSTOMER
Customer number
|
Customer name
|
Salesperson number
|
Headquarters location
|
0121
|
Main St. Hardware
|
337
|
New York
|
0839
|
Jane's Stores
|
186
|
Chicago
|
0933
|
ABC Home Stores
|
237
|
Los Angeles
|
1047
|
Acme Hardware Store
|
137
|
Los Angeles
|
1525
|
Fred's Tool Store
|
361
|
Atlanta
|
1700
|
XYZ Stores
|
161
|
Washington
|
What constraint, if any, has been violated?