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?