Description
Questions:
1) 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.
2) 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?
3) 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 350000 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 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 number of a particular order. Each order is given a different order number
Order_Date: It is the date that order was made
Cust: It is the customer id of the customer who makes the order
Rep: It is the id of the sales rep who takes care of the order
Mfr: It is the manufacturer code associated with the product that the customer orders
Product: It is the product id of the product the customer orders
QTY: It is the quantity of the product the customer orders
Amount: 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 Inc103 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.