﻿ fff

CS 430/630, DatabaseSystems                                                                  May 2017

Practice Final Exam Solutions                                                                    E. O'Neil

OPEN BOOK — OPEN NOTES-- CLOSED ELECTRONIC DEVICES

NAME _________________________________________________

1.   Put the following queries in Relational Algebra form using the boats-sailors database from our createdb.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.

This is division:   Pi(sid, bid)R/(Sigma(rating=10)B)

2.  (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.
select c.name, c.room from class c
where 100 < (select count(*) from class c1, enrolled e, student s
where c1.name = e.cname and e.snum = s.snum and c1.name = c.name)

Note: we don't need the student table in the join if there are FK constraints on the columns in enrolled, as there should be.
Note: For portability, the (select ...) is on the right-hand side of the inequality, to satisfy Entry SQL-92, but current databases should be able to handle it on either side.

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

Here we can find students who take some class outside their department, and eliminate them from the set of all students:

select s.snum from student s
except
select s.snum from student s, enrolled e, class c, faculty f
where s.snum = e.snum and e.cname = c.name and c.fid = f.fid and f.dept <> s.major

or, more portably:

select s.snum from student s
where s.snum not in
(select s1.snum from student s1, enrolled e, class c, faculty f
where s1.snum = e.snum and e.cname = c.name and c.fid = f.fid and f.dept <> s.major)

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

This is division. One way:

select c.name from class c where not exists
(select s.snum from students s where major='CS' and standing='JR'
except
select e.snum from enrolled e where e.cname = c.name
}

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;

For each department with budget no more than 1000000, find the number of their employees earning over 50000.

Dname            count(*)
-------------------

Legal        2
Software     1

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

List department names for departments where some non-manager employee of the department makes at least as much as the manager of the department.

dname
--------------------------------
Legal

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:

• Every airplane has a registration number (unique), and each airplane has a model (e.g., Piper Cub) and has a capacity (max number of passengers).
• A number of pilots for local flights work at the airport. For each, you need to store name, SSN (unique), address, and phone number.
• Each pilot is certified for one or more airplanes.
• Each airplane has at least one certified pilot.
• Further, the certification of a certain pilot for a certain airplane occurred on a certain date, to be recorded as a “since” date of the certification.

a.       Is this Pilot-Airplane “is certified for” relationship many-to-one, one-to-many, or many-to-many?

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

create table is_certified_for (

regno int,

ssn int,

since date,

primary key(regno, ssn),

foreign key (regno) references airplanes(regno),

foreign key (ssn) references pilots(ssn))

6. JDBC Explain how you would change JdbcCheckup.java so that it no longer drops andcreates 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".

static void tryWelcomeExperiment(Connection conn) throws SQLException {
// Create a statement
Statement stmt = conn.createStatement();
ResultSet rset = null;

try {
rset = stmt.executeQuery("select * from welcome");
if (rset.next()) { // look at first row back
if (rset.getString(“msg”).equals(“Hello World!”)
&& !rset.next())  // no next row
System.out.println(“OK”);
elseSystem.out.println(“missing”);

} else System.out.println(“missing”); // case of empty table

} catch (SQLException e) {   // case of no table
System.out.println(“missing”);
throw e;
} finally {
stmt.close(); // clean up statement resources, incl. rset
}

}

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.

create or replace procedure reinit_flights (rowcount out integer) as
count1 int;
count2 int;
begin

select count(*) into count1 from assigned_flights;
select count(*) into count2 from delayed_flights;

rowcount := count1 + count2;
delete from assigned_flights;
delete from delayed_flights;

end;

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 anysmaller superkey.

A must be in the key since it doesn’t appear on the RHS of any FD

A+ = A, = AB by (1)  not key by itself

AC+ = AC, =ACB by (1), = ACBD by (2), = ABCDE by (3), so superkey
C+ = C, = CE by (3), so AC is a key

b.      Create a BCNF decomposition of the table T.
Since AC is a key, FD (2) is OK for BCNF, while (1) or (3) have non-key LHS, soviolate BCNF
Use (1) to decomp into AB and ACDE, then see (3) is violating BCNF for ACDE, so decomp again, into CE and ACD
Full decomp: AB, CE, ACD

c.       Is the decomposition 3NF? Explain why or why not.

Yes, each has only a key-> non-key FD, so qualifies as BCNF and 3NF.

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

All are preserved.

9. Database Security

a.       Suppose Oracle users test1 and test2 have been created like the cs630 student users were created,and 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 inschema test1 readable by user test2?

Grant select on faculty to test2;

b.      Give the command that user test2 would give to display all the rows of the faculty table in schema test1.

Select * from test1.faculty;

c.       Suppose user test1 only wants user test2 to read ids and names, but not department ids, offaculty. Propose a way for user test1 to provide this data to user test2.

User test1 does:  create view faculty_names as select fid, fname from faculty;

grant select on faculty_names to test2;  -- (and not granting select on faculty itself)

For problem 3: tear this off and use it, and don’t turn it in.

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