CS 430/630, Database Systems                                                                 May, 2017
Practice Final Exam                                                                                  E. O'Neil

OPEN BOOK — OPEN NOTES -- CLOSED ELECTRONIC DEVICES
Note: the real exam will be shorter.

1.   Put the following queries in Relational Algebra form using the boats-sailors database from ourcreatedb.sql:
sailors(sid, sname, rating, age)
boats(bid, bname, color)
reserves(sid, bid, day)

 (a) Get names of boats reserved by any sailors of rating 10.

 

 

 

 

(b) Get ids of boats reserved by all sailors of rating 10.

 

 

 

2.  (30 points, 10 points per question.)  Put the following queries in SQL formusing 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 of students who take no classes outside of their department. Here the student's major places him/her in a certain department, and a class is assigned to a certain department by the department of its teacher.

 

 

 

 

 

 

(c) Get names of classes enrolled in by all CS majors who are of junior ('JR') standing.

 

 

 


 

3. (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)

 

 

 

 

 

 


 

4. (15 points)  Entity Relationship Diagrams. Consider these points about Pilots (for local flights) and Airplanes and the "is certified for" relationship at a certain airport:

 

a.       Is this Pilot-Airplane “is certified for” 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.

 

 

 

 

 

 

 

 

5.  (15 points)  Foreign Keys. Suppose tables for airplanes and pilots of the previous problem have been defined as follows. Add a table for “is certified for”, the relationship described in the last problem, and be sure to include the expected foreign keys for it.

 

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)
        );
 
 

 

6. 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".  

 

 

 

 

 

 

 

 

 

 

 

 

 

7. PL/SQL  Write a PL/SQL procedure reinit_flights(rowcount out integer) that counts the rows in assigned_flights and delayed_flights and returns the total rowcount, and then deletes all rows from the two tables.  Let it throw any exception up to its caller.

 

 

 

 

 

 

  8.Normalization. Consider the following set F of FDs on the table T with attributes A B C D E, and assume it is already a minimal cover.

 

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.       Is the decomposition 3NF? Explain why or why not.

 

 

 

 

d.       Does the BCNF decomposition preserve all the FDs?  If not, which ones are not preserved?

 

 

 


 

 9. 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.

 

 


 

 

 

 



 

 

Note: this data is slightly different from the midterm exam sheet.

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