Description
Do the assignment in the PDF below
Unformatted Attachment Preview
CS443- Assignment 1
Questions:
1) Just by observing the tables below, what would you choose the primary key of each table? Based
on the explanation of the attributes 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.
2) Without looking at the product table, can the “Price” column in the products table be used as the
primary key if the store only carries 5 items? Why? Why not? What assumption do you need to
make for the price to be the primary key? Is your assumption realistic?
3) Without looking at the Office table, can you make “City” to be the Primary key? Why or Why
not? What if you are obligated to use City as the primary key or part of the primary key. What
would you change (add /delete/ modify) to make “City” as the primary key or part of the
composite primary key? What would you make as your assumption? Is your assumption realistic?
Hint: Some cities like “San Marcos” may exist in more than one state.
Salesreps Table:
Offices Table:
Customers Table:
CUST_NUM
———2111
2102
2103
2123
2107
2115
2101
2112
2121
2114
2124
2108
2117
2122
2120
2106
2119
2118
2113
2109
2105
COMPANY
——————-JCP Inc.
First Corp.
Acme Mfg.
Carter and Sons
Ace International
Smithson Corp.
Jones Mfg.
Zetacorp
QMA Assoc.
Orion Corp.
Peter Brothers
Holm and Landis
J.P. Sinclair
Three-Way Lines
Rico Enterprises
Fred Lewis Corp.
Solomon Inc.
Midwest Systems
Ian and Schmidt
Chen Associates
AAA Investments
CUST_REP
————–103
101
105
102
110
101
106
108
103
102
107
109
106
105
102
102
109
108
104
103
101
CREDIT_LIMIT
——————-50000
65000
108000
40000
35000
20000
65000
108000
45000
43200
40000
55000
35000
30000
50000
65000
25000
60000
20000
25000
45000
Orders Table:
ORDER_NUM
———112961
113012
112989
113051
112968
113036
113045
112963
113013
113058
112997
112983
113024
113062
112979
113027
113007
113069
113034
112992
112975
113055
113048
112993
113065
113003
113049
112987
113057
113042
ORDER_DAT
——— ——-17-DEC-89
11-JAN-90
03-JAN-90
10-FEB-90
12-OCT-89
30-JAN-90
02-FEB-90
17-DEC-89
14-JAN-90
23-FEB-90
08-JAN-90
27-DEC-89
20-JAN-90
24-FEB-90
12-OCT-89
22-JAN-90
08-JAN-90
02-MAR-90
29-JAN-90
04-NOV-89
12-OCT-89
15-FEB-90
10-FEB-90
04-JAN-89
27-FEB-90
25-JAN-90
10-FEB-90
31-DEC-89
18-FEB-90
02-FEB-90
CUST
——-2117
2111
2101
2118
2102
2107
2112
2103
2118
2108
2124
2103
2114
2124
2114
2103
2112
2109
2107
2118
2111
2108
2120
2106
2106
2108
2118
2103
2111
2113
REP
—-106
105
106
108
101
110
108
105
108
109
107
105
108
107
102
105
108
107
110
108
103
101
102
102
102
109
108
105
103
101
MFR
——REI
ACI
FEA
QSA
ACI
ACI
REI
ACI
BIC
FEA
BIC
ACI
QSA
FEA
ACI
ACI
IMM
IMM
REI
ACI
REI
ACI
IMM
REI
QSA
IMM
QSA
ACI
ACI
REI
PRODU
———2A44L
41003
114
K47
41004
4100Z
2A44R
41004
41003
112
41003
41004
XK47
114
4100Z
41002
773C
775C
2A45C
41002
2A44G
4100X
779C
2A45C
XK47
779C
XK47
4100Y
4100X
2A44R
QTY
—–7
35
6
4
34
9
10
28
1
10
1
6
20
10
6
54
3
22
8
10
6
6
2
24
6
3
2
11
24
5
AMOUNT
———–31500
3745
1458
1420
3978
22500
45000
3276
652
1480
652
702
7100
2430
15000
4104
2925
31350
632
760
2100
150
3750
1896
2130
5625
776
27500
600
22500
PRICE
———79
2750
355
180
1875
107
117
652
250
134
4500
148
54
225
55
1425
2500
177
76
4500
975
25
243
475
350
QTY_ON_HAND
———————210
25
38
0
9
207
139
3
24
203
12
115
223
78
277
5
28
37
167
12
28
37
15
32
14
Products Table:
MFR
—–REI
ACI
QSA
BIC
IMM
ACI
ACI
BIC
IMM
QSA
REI
FEA
IMM
BIC
ACI
IMM
ACI
QSA
ACI
REI
IMM
ACI
FEA
IMM
REI
PRODU
———2A45C
4100Y
XK47
41672
779C
41003
41004
41003
887P
XK48
2A44L
112
887F
41089
41001
775C
4100Z
XK48A
41002
2A44R
773C
4100X
114
887X
2A44G
DESCRIPTION
——————-RATCHET LINK
WIDGET REMOVER
REDUCER
PLATE
900-LB BRACE
SIZE 3 WIDGET
SIZE 4 WIDGET
HANDLE
BRACE PIN
REDUCER
LEFT HINGE
HOUSING
BRACE HOLDER
RETAINER
SIZE 1 WIDGET
500-LB BRACE
WIDGET INSTALLER
REDUCER
SIZE 2 WIDGET
RIGHT HINGE
300-LB BRACE
WIDGET ADJUSTER
MOTOR MOUNT
BRACE RETAINER
HINGE PIN
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
105
Name
Bill Adams
Age
37
Rep_Office
13
Title
Hire_Date
Sales Rep 12-FEB-88
Manager Quota
104
350000
Sales
367911
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
REI
Product_Id
2A45C
Description
RATCHET LINK
Price
79
Qty_On_Hand
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
112961
Order_Date
17-DEC-89
Cust
2117
Rep
106
MFR
REI
Product
2A45C
QTY
7
Amount
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
2111
JCP Inc 103
Cust_Rep Credit_Limit
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:
City
Region:
Mgr:
Target:
Sales:
id of each office. Each office has a different office id
It is the city where the office is located
It is the region (western or eastern) where the office is located
It is the id of the sales person who is the manager of that office
It is the target sale of that office
It is the total sales made in that office up to now
Example:
Office
22
City
Denver
Region
Western
Mgr
108
Target
300000
Sales
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.
Purchase answer to see full
attachment
