CS 430/630, Database Systems                                                                            Fall, 2017

Practice Midterm Exam                                   

OPEN PRINT BOOKS — OPEN NOTES, SOLUTIONS, ETC. --CLOSED ELECTRONIC DEVICES!

NAME _________________________________________________

Use the backs of pages for more room if you need to, but point out such uses on the main sheets!

I. (20 points, 10 points per question) Put the following queries in Relational Algebra form, using the parts-suppliers database from our createdb.sql:

suppliers(sid, sname, address)
catalog(sid, pid, cost)
parts(pid, pname, color)

 

(a) Find sids of suppliers that supply all red parts and all green parts.

 

 

 

 

 

(b) Find sids of suppliers that supply no parts named 'widget'.

 

 

 

 

2.  (30 points, 10 points per question.)  Put the following queries in SQL form.  Be careful to use the "distinct" keyword appropriately.  

Use the emp-dept database from our createdb.sql:

emp(eid, ename, age, salary)
works(eid, did, pct_time)
dept(did, dname, budget, managerid)

(a) Report employee eids and names and the total of all budgets of the departments that employee is working in, for employees of ages 50 to 60 inclusive.

(b) Report the names of employees who work in all departments with names ending with "Sales".

 

 

 (c) Find the names of employees who work in departments worked in by employee 10.

 

 

 

 

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 Figure 5.1, 5.2, and 5.3 reproduced below, print the table you would expect SQL to print as an answer.  (For partial credit, show the sets of elements returned by Subqueries and give reasoning for final answer. ) Assume type string for day here.

 I

 As an example, we would want the following answer for the query:

        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

 

(a)     select b.bid, b.bname, count(*) from reserves r, boats b

               where r.sid = 22 and b.color = 'red' and r.bid = b.bid

               group by b.bid, b.bname;

 

 

 

 

 

 

(b)     select distinct b.bname from boats b

            where not exists (select * from reserves r where r.day like '11%' and r.bid = b.bid)   -- Corrected 11/6 11am

 

 

 

 

 

 

4. (10 points)  Entity Relationship Diagrams

Consider these points about Albums and Musicians and the "produces" relationship at a certain recording studio:

• Each musician that records at Notown has an SSN (a unique id), a name, an address, and a phone number.
• Each album recorded on the Notown label has a unique identification number, a title, a copyright date, and a format (e.g., CD or whatever).
• Each album has exactly one musician who acts as its producer. A musician may produce several albums, of course. 

a. Is this Album-Musician produces 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.

 

 

 

 

5.  (10 points)  Foreign Keys. In createdb.sql, there is a nullable foreign key managerid in the dept table:

create table emp(
        eid int primary key,
        ename varchar(30) not null,
        age int,
        salary decimal(10,2)
        );
create table dept(
        did int primary key,
        dname varchar(20) not null unique,
        budget decimal(10,2),
        managerid int,
        foreign key(managerid) references emp(eid)
        );

a. Show the edit to the above create table commands to make the managerid into a not-null foreign key.

b. After the above commands (edited as specified in part a.) have been executed, resulting in two empty tables, which table should be loaded first, and why? Show an insert statement that would fail if the load were done in the wrong order.

c. Suppose each employee has a certain department (unlike the original setup in createdb.sql with the works table). Show how to add a column to emp and a foreign key constraint to ensure that each employee's department id has a good value.

Note that with foreign keys in both directions, there is no load sequence that will work. This can be resolved by adding the second foreign key after the load with just one foreign key, using alter table.

6. (10 points)  a. Create a view SupplierSummary that reports on each supplier in suppliers, with columns for supplier id (as sid), supplier name (as sname), and total number of parts each can supply (as totalparts).

b. Assuming the view in a. is now available, use it to write a query to find the suppliers with at least 5 parts that they supply. Report sid, sname, and number of parts.