Lab #1 Solution

Questions:




Just by observing the tables below, what would you choose the primary key of each table? Based on base the explanation of the attributes of the given below, write down all the foreign keys. For example, if you think that Rep_Office is a foreign key in the Salesreps table, you should write:



-Rep_Office is a foreign key in the Salesreps table because it refers to the primary key

(office attribute) of the offices table.

Do the same for every foreign key that you find in the entire database.




Without looking at the product table, can the “Price” column in the products table be used as the primary key? Why? Why not? What assumption do you need to make for the price to be the primary key? Is your assumption realistic?



Without looking at the Office table, if you were obligated to use “City” as the primary key of the offices table, what restrictions would you have to put in this column to make it work?



Salesreps

Empl_Num Name
Age
Rep_Office Title
Hire_Date
Manager
Quota
Sales
105
Bill Adams
37
13
Sales Rep
12-FEB-88
104
350000
367911
109
Mary Jones
31
11
Sales Rep
12-OCT-89
106
300000
392725
102
Sue Smith
48
21
Sales Rep
10-DEC-86
108
350000
474050
106
Sam Clark
52
11
VP Sales
14-JUN-88


275000
299912
104
Bob Smith
33
12
Sales Mgr
19-MAY-87
106
200000
142594
101
Dan Roberts
45
12
Sales Rep
20-OCT-86
104
300000
305673
110
Tom Synder
41


Sales Rep
13-JAN-90
101


75985
108
Larry Fitch
62
21
Sales Mgr 12-OCT-89
106
350000
361865
103
Paul Cruz
29
12
Sales Rep
01-MAR-87
104
275000
286775
….



















Orders

Order_Num
Order_Date
Cust
Rep
Mfr
Product
QTY
Amount
112961
17-DEC-89
2117
106
REI
2A44L
7
31500
113012
11-JAN-90
2111
105
ACI
41003
35
3745
112989
03-JAN-90
2101
106
FEA
114
6
1458
113051
10-FEB-90
2118
108
QSA
K47
4
1420
112968
12-OCT-89
2102
101
ACI
41004
34
3978
113036
30-JAN-90
2107
110
ACI
4100Z
9
22500
113045
02-FEB-90
2112
108
REI
2A44R
10
45000
.........




Products

Mfr_ID
Product_ID
Description
Price
Qty _On_Hand
REI
2A45C
RATCHET LINK
79
210
ACI
4100Y
WIDGET REMOVER
2750
25
QSA
XK47
REDUCER
355
38
BIC
41672
PLATE
180
0
IMM
779C
900-LB BRACE
1875
9
ACI
41003
SIZE 3 WIDGET
107
207
........











Customers

Cust_Num
Company
Cust_Rep
Credit_Limit
2111
JCP Inc
103
50000
2102
First Corp.
101
65000
2103
Acme Mfg.
105
50000
2123
Carter and Sons
102
40000
2107
Ace International
110
35000
2115
Smithson Corp.
101
20000
2117
J.P. Sinclair
106
35000



Offices

Office
City
Region
Mgr
Target
Sales


22
Denver
Western
108
300000
186042


11
New York
Eastern
106
575000
692637


12
Chicago


Eastern
104
800000
735042
13
Atlanta
Eastern
105
350000
367911





21 Los Angeles Western 108 725000 835915






Description of the database:




Salesreps table:










Empl_Num:
Employee Id of the sales person. Each sales rep (employee) is given a different


employee id










Name:
Name of the sales person






Age:
Age of the of the sales person






Rep_Office:
It is the id of the office where sales person is working




Title:
Title of the sales person








Hire_Date:
The date when the salesperson was hired




Manager:
The employee id of the his/her boss




Sales:
Total sales made by the sales person since he/she has been hired


Example:












Empl_Num Name


Age
Rep_Office Title Hire_Date
Manager Quota Sales
105
Bill Adams
37
13
Sales Rep 12-FEB-88
104
350000367911



This indicates that Bill Adams is a 37 years sales Rep, with the employee id 105. He was hired on Feb 12, 1988 and work in office 13 ( office 13 is in Atlanta – see offices table). The employee id of his boss is 104 (employee id 104 is Bob Smith – see Salesreps table). Bill Adams’s sales Quota is $350000 and his total sales is $367911.







Products table:








Mfr_Id:
It is the manufacturer id of the product




Product_Id:
It is the Product id of the product




Description:
It is the description of this product




Price:
Price per unit






Qty_On_Hand: number of this product available in stock


Example:








Mfr_Id
Product_Id
Description
Price
Qty _On_Hand
REI
2A45C
RATCHET LINK
79
210



This indicates that “RATCHET LINK” is a product with product Id 2A45C made by manufacturer REI (where REI is the three letters code for the manufacturer). The price of “RATCHET LINK” is $79.00 per unit. There are 210 pieces are currently available. It is important to note that manufacturer may make the same product. Clearly, a manufacturer may make more than one product.






Orders table:

Order_Num:




Order_Date:

Cust:




Rep:

Mfr:

Product:




QTY:

Amount:










Order number of a particular order. Each order is given a different order number




It is the date that order was made

It is the customer id of the customer who makes the order




It is the id of the sales rep who takes care of the order

It is the manufacturer code associated with the product that the customer orders

It is the product id of the product the customer orders




It is the quantity of the product the customer orders




It is the total amount of money (Quantity ordered * price per unit) the customer pays for




the product



Example:



Order_Num




Order_Date




Cust




Rep




MFR




Product




QTY




Amount
112961




17-DEC-89

2117

106

REI




2A45C

7

31500



This indicates that sales rep 106 (who is Sam Clark – see salesreps table) took order 112961 for customer

2117 (who is “J.P. Sinclair” – see customer table) on Dec 17, 1989. Customer 2117 ordered 7 piece of the

product REI 2A45C (which is “RATCHET LINK” – see products table). Customer 2117 paid total of




$31500. This amount also refers to one of the sales (not all the sales) made by sales rep 106 (who is “Sam

Clark”)







Customer table:

Cust_Num: It is the id of the customer. Each customer has a different id

Company: It is the name of the company (the name of the customer)

Cust_Rep: It is the sales person who represents this customer

Credit_Limit: It is the credit limit of the customer (company) associated with each order the customer




requests (not with all the orders the customer has requested). For example, if the Credit_Limit of a customer is $50,000. Based on this Credit Limit, the customer makes an order. Then this credit Limit is reset back to $50, 000 for the next order the customer makes.




Example:



Cust_Num Company




Cust_Rep Credit_Limit



2111




JCP Inc 103




50000



This indicates customer id 2111, known as “JCP Inc” is represented by the sales person 103 (who is “Paul Cruz” – see salesreps table). “JCP Inc’s” credit limit for every specific order is $50,000.







Offices table:










Office:
id of each office. Each office has a different office id
City
It is the city where the office is located


Region:
It is the region (western or eastern) where the office is located
Mgr:
It is the id of the sales person who is the manager of that office
Target:
It is the target sale of that office




Sales:
It is the total sales made in that office up to now
Example:










Office
City
Region
Mgr
Target
Sales
22
Denver
Western
108
300000
186042



This indicates office 22 is in “Denver”. Denver is in Western region of North America. The target sale of this office is $300,000. The total sale made in this office is $186, 042 up to now.