cs630 practice midterm solution

CS 430/630, Database Systems                                                                            Fall ‘17

Practice Midterm Exam Solution

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!

NOTE: There are multiple ways to do queries. These solutions do not cover all the possible ways.

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

catalog(sid, pid, cost)
parts(pid, pname, color)

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

i.e., the parts that need division for-all checking are the ones that are red or green:

(πsid,pidCatalog)/(πpidσcolor = 'Red' ˅ color = 'Green' Parts)

Alternatively, you could find suppliers that supply all red parts, and separately, the set supplying all green parts, and then intersect those two sets.

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

All suppliers less those that supply parts named 'widget'

πsid Suppliers  -  πsid((σname='widget'Parts) ⨝ Catalog)

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.

select e.eid, e.ename, sum(d.budget) from emp e, works w, dept d
where e.eid = w.eid and w.did = d.did and e.age >= 50 and e.age <= 60
group by e.eid, e.ename

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

This is division. It's OK to use except (minus on Oracle):

SQL division:

select distinct e.ename from emp e where not exists
(
(select d.did from dept where dname like '%Sales')
except
(select w.did from works w where w.eid  = e.eid)
)

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

select distinct ename from emp e, works w
where e.eid = w.eid and w.did in (select w1.did from works w1 where w1.eid = 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;

The select returns ids and names of red boats reserved by sailor 22, and the number of times that sailor 22 reserved each such boat.

 bid bname count(*) 102 Interlake 1 104 Marine 1

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

The select returns boat names of boats that have no reservations in month 11 (November)

The subquery returns, for b.bid = 102  (one case is enough here). This boat is eliminated from the full results

 sid bid day 31 102 11/10/98

Only boat 101 has no such reservations. The SQL returns:

 b.bname Interlake

4. (15 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 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?  many-to-one: only one producer musician for each album

b. Draw an E-R diagram for it, showing attributes as well as the relationship.

Clearly the exact attribute names and relationship name are flexible.
Note: idnum should be underlined, since it is unique, so a key, and the only key, so the PK.
You could underline ssn, as "obviously" a key, but it's not stated as unique.

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

```create table emp(
eid int primary key,	did int references dept(did),  --red text added for c. (one way)        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 not null, -- blue text added as answer to a.
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.

Answer: You need to load emp first, then dept, so that the referenced manager rows are there in emp to satisfy the foreign key constraint in dept.

c. Each employee has a certain department. 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.

Answer: use a new column did with a foreign key constraint, either a column constraint as shown above in red, or a simpler column definition "did int," plus a table constraint

`foreign key(did) references emp(did)`

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.