3902 Some Sample Questions
1. The University would like to report on credit hours utilized in departments by students according to various ranges. They would like to see a count of the number of students for each range. Currently the ranges they use are given the following table:
Banding
BandSK / BandDesc / SortSequence / LowerValue / UpperValue3 / low / 1 / 0 / 20
2 / average / 2 / 20 / 40
1 / high / 3 / 40 / 90
4 / extreme / 4 / 90 / 1000000
The schema below summarizes credit hours taken by students in each department. Give the SQL statement to list, for the Computing department, the number of students in each of the above ranges; two columns appear in the result: BandDesc and NumberOfStudents. The result should be ordered according to the SortSequence attribute.
The student table will have two attributes: sNo, sName
The department table will have two attributes: dNo, dName
The enrollAggr table has three attributes: sNo, dNo, creditHours
2. Consider the following database for keeping track of account transactions and the resulting balance. In the table below we happen to see the transactions in order by accountId and date. As you can see, transactions do not occur every day. For a given date, there may be transactions for an account, but usually there are none. The date/time attribute for transaction is a timestamp attribute that records the date and time of the transaction. For convenience below, we only show the date. If you were interested in knowing the balance in an account, say account 1, on April 1 you would look for the last transaction on or before April 1 for account 1. In the case below we see the balance in account 1 on April 1 is $135.00.
accountId / Fname / Lname / City1 / April / Lee / Winnipeg
2 / Peter / Smith / Brandon
3 / Steven / Stevens / Winnipeg
4 / Lou / Adams / Selkirk
trxId / accountId / Date/time / Amount / Type / Balance
2 / 1 / March 22, 2005 / 25.00 / Deposit / 125.00
4 / 1 / March 28, 2005 / 30.00 / Deposit / 155.00
7 / 1 / March 31, 2005 / 20.00 / Withdrawal / 135.00
10 / 1 / April 4, 2005 / 100.00 / Deposit / 235.00
3 / 2 / March 22, 2005 / 25.00 / Deposit / 125.00
5 / 2 / March 28, 2005 / 30.00 / Deposit / 155.00
6 / 3 / March 28, 2005 / 30.00 / Deposit / 155.00
8 / 3 / March 31, 2005 / 20.00 / Withdrawal / 135.00
11 / 3 / April 4, 2005 / 100.00 / Deposit / 235.00
1 / 4 / March 2, 2005 / 125.00 / Deposit / 222.00
9 / 4 / April 1, 2005 / 100.00 / Deposit / 322.00
Using SQL determine answers for the following. Note that the data above is sample data; your SQL needs to be general in the sense that it would work for any set of data.
· Which is the first transaction?
· Which is the last transaction?
· What is the balance in account 1 on April 1?
· What is the balance in each account as of April 1?
· How many transactions are there for account 2 in April?
· Which accounts did not have any transactions in March?
· Which accounts had more than 10 transactions in March?
· You are to insert a deposit transaction where you know the accountId, the date/time, and the deposit amount. Do the insert (in one statement, or several if you prefer).
3. What do the following acronyms stand for:
OLTP, SQL, EERD, DML, DDL
4. What are the notable contributions of P. Chen? E. Codd? R. Bayer?
5. Express the following SQL Statement in Relational Algebra:
Select lastname, firstname, orderid
From Employees INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE orders.orderdate > Jan 1, 2000
6. Give an example of a hash function that could be used to map UofW student numbers (i.e. student records) to a file of 500 primary buckets. (It does not have to be a good hash function)
7. If we increase the order of a b+=tree, what happens to the tree?
8. Consider the following diagram.
a) Map the design to a relational database using one relation for each entity type / class
b) Create sample data where you have at least two rows in each table
9. Consider a b+-tree of order p=3, where an internal node will have a minimum of 2 pointers and a maximum 3 pointers, and where a leaf can have at least 2 key/pointer pairs and a maximum of 3 key/pointer pairs.
Illustrate the b+-tree after records with student numbers given below are inserted in the order given. It is recommended that you show the b+-tree after each split.
Student Number1234
9999
2222
3434
7777
5678
5555
6655
7755
10. Consider a Linear Hash file consisting of 5 buckets. This file has eight of the following nine records already inserted.
Student Number / HashKey / HashKey / HashKey / HashKey / HashKey / HashKeyDecimal / Binary / Mod 2 / Mod 4 / Mod 8 / Mod 16
1234 / 42 / 101010 / 0 / 2 / 2 / 10
9999 / 60 / 111100 / 0 / 0 / 4 / 12
2222 / 56 / 111000 / 0 / 0 / 0 / 8
3434 / 56 / 111000 / 0 / 0 / 0 / 8
7777 / 42 / 101010 / 0 / 2 / 2 / 10
5678 / 21 / 010101 / 1 / 1 / 5 / 5
5555 / 7 / 000111 / 1 / 3 / 7 / 7
6655 / 7 / 000111 / 1 / 3 / 7 / 7
7755 / 15 / 001111 / 1 / 3 / 7 / 15
The file with the first eight records:
22223434 / 5678 / 1234
7777 / 5555
6655 / 9999
010 110 210 310 410
0002 012 102 112 1002
Split pointer = 1
Assume
v Splitting Policy: split whenever the load factor exceeds 0.85. Note that a ninth insertion causes the load factor to exceed 0.85
v Primary area block capacity is 2
v Overflow is handled by chaining records in a separate overflow area; overflow records are unblocked (i.e. block capacity in overflow is 1). Presently no records are in an overflow bucket.
Insert the ninth record (the one with student number 7755), and
a) Illustrate the file after the split that must occur.
b) What is the value of the split pointer after the 9th insertion?
c) Calculate the average successful search length (assuming each record is equally likely of being chosen). Show your calculation.
11. The Northwind database involves Customers, Employees, Orders, Products, Suppliers, Shippers, etc.
a) These four questions require relational algebra.
i. Give a relational algebra statement to obtain the customer id and company name for each customer.
ii. Give a relational algebra statement to obtain product information where the product has a unit price that is more than $100.
iii. Give relational algebra statements to obtain order information for the customer with company name “ABC Printing”. Include the contact name, order id, and order date in the information you obtain.
iv. Give relational algebra statements to obtain the company name of customers who have ordered every part that Northwind carries.
b) These four questions require SQL.
i. The reports to attribute in Employees specifies the employee who is the supervisor. For each supervisor, list the supervisor’s last name, and list the last name, first name, and title of the persons they supervise.
ii. List the names of categories that have 10 or more products.
iii. The unit price appears in the order detail table and in the product table. List the name of a product if the unit price recorded in an order detail row differs from the unit price in the product table.
iv. List the last name of the employee who has the largest employee number.
1