Description
do the SQL homework 4-10 in the pdfs below
Unformatted Attachment Preview
CS443 – Assignment 8
Question 1:
Use extended hashing technique to insert the employees in which their corresponding binary Empl_IDs are
shown below. Assume you can have two employees per block. Show the depth of both global and local
directories. The bits should be considered from left to right as shown in the lecture..
00001, 10000, 10100, 00111, 11011, 11101, 10010, 01111, 10101
Question 2:
Use extended hashing technique to insert the employees in which their corresponding binary Empl_IDs are
shown below. Assume you can have two employees per block. Show the depth of both global and local
directories. The bits should be considered from left to right as shown in the lecture.
01011, 01010, 11110, 10000, 10010, 11111
Question 3:
Consider the following records of question 1: (Again Digits should be considered from left to right)
00001, 10000, 10100, 00111, 11011, 11101, 10010, 01111, 10101
Load the records into files using Dynamic Hashing Scheme. You can put two records per block. Show the
directory at each step, and the global and local depths.
Question 4:
Consider the following records of question 1: (Again Digits should be considered from left to right)
01011, 01010, 11110, 10000, 10010, 11111
Load the records into files using Dynamic Hashing Scheme. You can put two records per block. Show the
directory at each step, and the global and local depths.
Page 1 of 1
CS 443 – Assignment 9
Question 1:
Insert the following into B+ tree of order 3. Show your work step by step with proper illustration of
pointers as shown in pages 50-62 in multi-way trees lecture
90, 22, 27, 24, 28, 20, 51, 63, 8, 80, 15, 71, 35, 55
Question 2:
Insert the following into B_ tree of order 3. Show your work step by step with proper illustration of
pointers as shown in pages 20-42 in multi-way trees lecture
2, 99, 9, 71, 16, 11, 15, 13, 91, 92, 94
Question 3:
Consider the following B-tree.
a) Redraw the tree after deleting 14.
b) Again, redraw the tree after deleting 15.
c) Again, redraw the tree after deleting 25.
Question 4:
Consider the following B+ tree:
a)
b)
c)
d)
e)
Delete 29 and redraw the tree after that
Next delete 10 and redraw the tree after that
Next delete 26 and redraw the tree after that
Next delete 24 and redraw the tree after that
Next delete 9 and redraw the tree after that
CS443- Assignment 4
Questions:
1) Consider the following normalized tables (The tables are the same as the one in A1)
– Create ERD for these tables
– Write the Create table statements with proper constraints for these tables
– Make sure to run the create table code to ensure that there is no error.
Constraints:
– Include all primary key constraints
– Include all foreign keys constraints
– Check constraints of greater than or equal to zero should be on:
o Age, Quota, sales for salesreps, Qty, Amount, price, Qty_On_Hand, Credit_Limit,
Target, offices.sales
– Not Null constraints should be on:
o Name, Hire_Date, Company, region, and city
Salesreps
Empl_Num Name
105
Bill Adams
109
Mary Jones
102
Sue Smith
106
Sam Clark
104
Bob Smith
101
Dan Roberts
110
Tom Synder
108
Larry Fitch
103
Paul Cruz
Age
37
31
48
52
33
45
41
62
29
Rep_Office Title Hire_Date
13
Sales Rep 12-FEB-88
11
Sales Rep 12-OCT-89
21
Sales Rep 10-DEC-86
11
VP Sales 14-JUN-88
12
Sales Mgr 19-MAY-87
12
Sales Rep 20-OCT-86
Sales Rep 13-JAN-90
21
Sales Mgr 12-OCT-89
12
Sales Rep 01-MAR-87
Manager Quota Sales
104
350000 367911
106
300000 392725
108
350000 474050
275000 299912
106
200000 142594
104
300000 305673
101
75985
106
350000 361865
104
275000 286775
…..
Orders
Order_Num
112961
113012
112989
113051
112968
113036
113045
………
Order_Date
17-DEC-89
11-JAN-90
03-JAN-90
10-FEB-90
12-OCT-89
30-JAN-90
02-FEB-90
Cust
2117
2111
2101
2118
2102
2107
2112
Rep
106
105
106
108
101
110
108
Mfr
REI
ACI
FEA
QSA
ACI
ACI
REI
Product
2A44L
41003
114
K47
41004
4100Z
2A44R
QTY
7
35
6
4
34
9
10
Products
Mfr_ID
REI
ACI
QSA
BIC
IMM
ACI
……..
Product_ID
2A45C
4100Y
XK47
41672
779C
41003
Description
RATCHET LINK
WIDGET REMOVER
REDUCER
PLATE
900-LB BRACE
SIZE 3 WIDGET
Price
79
2750
355
180
1875
107
Qty_On_Hand
210
25
38
0
9
207
Customers
Cust_Num
2111
2102
2103
2123
2107
2115
2117
Company
JCP Inc
First Corp.
Acme Mfg.
Carter and Sons
Ace International
Smithson Corp.
J.P. Sinclair
Cust_Rep
103
101
105
102
110
101
106
Credit_Limit
50000
65000
50000
40000
35000
20000
35000
Offices
Office City
22
Denver
11
New York
12
Chicago
13
Atlanta
Region
Western
Eastern
Eastern
Eastern
Mgr
108
106
104
105
Target Sales
300000 186042
575000 692637
800000 735042
350000 367911
21
Western
108
725000
Los Angeles
835915
Amount
31500
3745
1458
1420
3978
22500
45000
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
105
Bill Adams
37
Age
Rep_Office Title Hire_Date
Manager Quota Sales
13
Sales Rep 12-FEB-88
350000
104
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.
Assignment 2
No Duplicate Rows are allowed in the following questions
1) Write a select statement to list all the columns from the Offices table. ‘Select *’ is not allowed. Return the list
in DESC Office order.
2) List the product ID, mfr ID, Name (its description), and dollar value of quantity on hand (price * quantity) of
the products whose dollar value is greater than 20000. Return the list in descending order by their dollar
value.
3) List the Order Number, Order Date, Customer Number, and Sales Rep Number for orders for part ‘779C’ or
‘773C’. (Use a compound search condition – OR.)
4) List the Name and Age of all Sales Reps in the following offices: 11; 13. (Use the set membership test – IN.)
5) List the names of all Sales Reps who have the letter ‘a’ (this is lower case a) as the second character of their
name.
6) Return the Sales Rep ID and Name of any Sales Rep not assigned to an office yet.
7) Show all the sales rep names with last name “Smith” or “Jones”.
8) List different titles in the sales reps table. Only list each title once and unknown titles should be ignored.
Duplicate rows are not allowed.
9) List the description of the products which are more than 6 character and less than 10 character long. No
duplicate row is allowed. You can use the build in function length() to do this. For example, length(name)
return the number of characters for attribute called “name”.
10) For all order Amount less than $700, list the order num with the name of the rep who placed the order and
the name of the customer who made the order and the name of the rep for that customer
11) List the Mfr Id, the Product Id, Company and PRICE of all products bought by customers where customer
number is one of (2111,2112,2105,2119) and where the amount of the order < $10,000. Order the results by
ascending Company. Duplicate row are not allowed.
12) List all orders number, the name of the rep who took the order , the city where the rep has his/her office. Print
the employee whose office is in either Chicago or Denver;
13) List all customers (Customer number, Company, and Credit Limit) and orders (Order Number,
Amount) where the Amount is within $10,000 of the Credit Limit. Therefore:
- Credit_limit should be greater than zero and
- Amount should be greater than zero and
- Amount should be less than credit_limit and
- Credit_limit – Amount
Purchase answer to see full
attachment
