CS 430/630, Database
Systems
May, 2019
Practice Final Exam
E.
O'Neil
OPEN BOOK — OPEN NOTES -- CLOSED ELECTRONIC DEVICES
1. (30 points, 10 points per question.) Put the following queries in SQL form using the student-faculty-class database from our createdb.sql:
student(snum, sname,
major, standing, age)
faculty(fid, fname, deptid)
class(name, meets_at, room,
fid)
enrolled(snum, cname)
(a) Get names and room numbers for classes with more than 100 students.
(b) Get ids and majors of students who take no classes with teachers in department 22.
(c) Get names of classes enrolled in by all CS majors who are of junior ('JR') standing.
2. (20 points, 10 points each.) For the following SQL queries, state in words what will be retrieved (don't just repeat what the predicates mean, but INTERPRET what is being retrieved) and, using the values from the table contents displayed on the last page of the exam (different from midterm exam data) (you can tear it off), print the table you would expect SQL to print as an answer. On that last page there is also an example solution.
Table summary (see table data at end of exam):
emp(eid, ename, age, salary)
dept(did, dname, budget, managerid)
works(eid, did, pct_time)
(a) select d.dname,
count(*) from works w, dept d
where w.did
= d.did and d.budget
<= 1000000
and w.eid
in (select e.eid from emp
e where e.salary > 50000)
group by d.dname
(b) select d.dname
from emp e, dept
d
where e.eid
= d.managerid and e.salary
<=
(select
max(e1.salary) from emp e1, works w
where
w.did = d.did
and e1.eid <> e.eid)
3. (15 points) Entity Relationship
Diagrams. Consider these points about Airplane Seats on a certain flight
and Tickets for that flight at end of boarding time:
- Every seat has an id (unique, like 12C), and some are marked aisle
and others window seats.
a. Is this Seat-Ticket “reserves” relationship many-to-one, one-to-many, or many-to-many?
b. Draw an E-R diagram for it, showing attributes as well as the relationship and entities. Also show appropriate think lines and/or arrowheads.
4. (15 points) Foreign Keys. Suppose tables for airplanes and pilots have been defined as follows:
create table airplanes(
regno int primary key,
model varchar(30) not null,
capacity int
);
create table pilots(
ssn int primary key,
pname varchar(20) not null,
address varchar(40) not null,
phone varchar(20)
);
The relationship between them is indicated by the following E-R diagram:
Add a table for “is certified for”, the relationship shown in the E-R diagram, and be sure to include the expected primary key and foreign keys for it.
5. (20 points) JDBC Explain how you would change JdbcCheckup.java so that it no longer drops and creates a welcome table, but instead just attempts to read a previously-existing welcome table and see if it contains a single row with "Hello World!" in it, or not. If it does, output "OK", and if it doesn't, or if the table doesn't exist, output "missing". In particular, show the new code for method tryWelcomeExperiment.
6. (15) PL/SQL Consider the tables we used in homework 5. Write a PL/SQL function count2(neighborhood varchar) that counts the rows in apartments and rented_apartments in the argued neighborhood and returns the total rowcount. Let it throw any exception up to its caller.
7..(20) Normalization. Consider the following set F of FDs on the table T with attributes A B C D E.
F: (1) A -> B, (2) A C -> D, (3) C -> E
a. Name a Key K for T. Show it is a superkey by computing K+. Show K does not properly contain any smaller superkey.
b. Create a BCNF decomposition of the table T.
c. Are the tables of the decomposition in 3NF? Explain why or why not.
d. Does the BCNF decomposition preserve all the FDs? If not, which ones are not preserved?
8. (15) Database Security
a. Suppose Oracle users test1 and test2 have been created the same way the cs630 student users were created. Then user test1 loaded the textbook tables into the test1 schema as we did in early homework. What command can user test1 give to make the faculty table in schema test1 readable by user test2?
b. Give the command that user test2 would give to display all the rows of the faculty table in schema test1.
c. Suppose user test1 only wants user test2 to read ids and names, but not department ids, of faculty. Propose a way for user test1 to provide this data to user test2.
|
works |
emp |
|||||||
|
eid |
did |
pct_time |
eid |
ename |
age |
salary |
||
|
142519864 |
2 |
100 |
142519864 |
Susan
Martin |
39 |
56990 |
||
|
242518965 |
1 |
100 |
242518965 |
James
Smith |
68 |
27099 |
||
|
141582651 |
1 |
50 |
141582651 |
Mary
Johnson |
44 |
94011 |
||
|
141582651 |
5 |
50 |
11564812 |
John
Williams |
35 |
74098 |
||
|
489456522 |
1 |
25 |
254099823 |
Patricia
Jones |
28 |
101783 |
||
|
489456522 |
5 |
75 |
356187925 |
Robert
Brown |
28 |
35431 |
||
|
11564812 |
3 |
100 |
489456522 |
Linda
Davis |
26 |
25971 |
||
|
254099823 |
3 |
100 |
287321212 |
Michael
Miller |
62 |
131072 |
||
|
356187925 |
2 |
100 |
248965255 |
Barbara
Wilson |
48 |
95021 |
||
|
287321212 |
2 |
100 |
159542516 |
Matt
Nelson |
33 |
48990 |
||
|
248965255 |
3 |
100 |
90873519 |
Elizabeth
Taylor |
27 |
33055 |
||
|
159542516 |
4 |
100 |
dept |
|||||
|
90873519 |
2 |
100 |
did |
dname |
budget |
managerid |
||
|
|
|
|
1 |
Hardware |
1048572.12 |
141582651 |
||
|
|
|
|
2 |
Operations |
4099101 |
287321212 |
||
|
3 |
Legal |
222988.13 |
248965255 |
|||||
|
4 |
Marketing |
538099.54 |
287321212 |
|||||
|
5 |
Software |
400011.12 |
141582651 |
As an example, we would want the following answer for the query based on the table contents printed on page 134, shown on the practice midterm exam.
select distinct r.sid from reserves r where bid in (select r1.bid from reserves r1 where r1.sid = 31);
Example Answer: The select retrieves the sids of sailors that reserved boats reserved by sailor 31. (The Subquery will return the set of bids {102, 103, 104} The table printed out as the answer will be sids, after duplicates are removed:
|
r.sid |
|
22 |
|
31 |
|
64 |
|
74 |