SOLVED CS443 Lab 1

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.