ÔN TẬP MIDTERM

PRINCIPLES OF DATABASE MANAGEMENT

● Hình thức: Tự Luận.
● Note: Viết tay, không giới hạn.
● Nội dung: RA, Tree, SQL Server, ERD

I. RA
A. First Schema
Suppliers(sID, sName, address)
Parts(pID, pName, colour)
Catalog(sID, pID, price)
Catalog[sID] ⊆ Suppliers[sID]
Catalog[pID] ⊆ Parts[pID]
Note:
● In this schema, everywhere we want values to match across
relations, the attributes have matching names. And everywhere the
attributes have matching names, we want values to match across
relations.
● This means that natural join will do exactly what we want in all
cases.
1. If sID is a key for the Suppliers relation, could it be a
key for the Catalog relation?
Just because it is a key in one relation doesn’t mean it is in another; being
a key is relative to the relation. But is it a key for Catalog? No. We almost
surely want to be able to list multiple parts by one supplier in our catalog.
2. Find the names of all red parts.

3. Find all prices for parts that are red or green. (A part
may have different prices from different manufacturers.)
 4. Find the sIDs of all suppliers who supply a part that
is red or green.

5. Find the sIDs of all suppliers who supply a part that

is red and green.
Trick question. Each tuple has only one colour, and each part has only one
tuple (since pID is a key), so no part can be recorded as both red and
green.
6. Find the names of all suppliers who supply a part that
is red or green.

B. Second Schema
Employees(number, name, age, salary)
Supervises(boss, employee)
Supervises[boss] ⊆ Employees[number]
Supervises[employee] ⊆ Employees[number]
Note:
● In this schema, wherever we want values to match across relations,
the attributes do not have matching names. This means that natural
join will not force things to match up as we’d like.
● In fact, since there are no attribute names in common across the two
relations, natural join is no different from Cartesian product.
● We are forced to use selection to enforce the necessary matching.
1. What does it say about our domain that employee is a
key for Supervises?
Every employee has one boss.
2. Does the schema allow for an employee with no boss?
Yes.
 3. How would the world have to be different if boss were a
key for Supervises?
It would mean that every boss could have at most one employee. Not very
sensible!
4. How would the world have to be different if both boss
and employee together were a key for Supervises?
This would imply that neither alone is a key, since keys are minimal. Thus,
bosses could have multliple employees (sensible) and employees could
have multiple bosses (possibly sensible).
5. Find the names and salaries of all bosses who have an
employee earning more than 100.

C. Third Schema
This schema is for a salon. Services could be things like “haircut” or
“manicure”.
Clients(CID, name, phone)
Staff(SID, name)
Appointments(CID, date, time, service, SID)
Appointments[CID] ⊆ Clients[CID]
Appointments[SID] ⊆ Staff[SID]
Note:
● In this schema, everywhere we want values to match across
relations, the attributes have matching names. But there are also
attributes with matching names whose values we do not want to
match across relations.
● In those cases, that natural join will get rid of many tuples that we
need, so we must use Cartesian product and make any necessary
matching happen using select. (Unless we can remove the problem
attributes first.).
 1. Find the appointment time and client name of all
appointments for staff member Giuliano on Feb14. (Assume
that you can compare a date value to “Feb 14” using “=”). At
each step, use projection to pare down to only the attributes
you need.

2. Now solve the same problem but begin by putting all

three relations together in full — with all of their attributes.
This time, we mustn’t use natural join or we’ll force the client name and
staff names to match, which would be very inappropriate! So we use
Cartesian product and are stuck enforcing all the things that do need to
match, like SID when we combine Staff and Appointments. I’m not going
to write out this version of the query.
3. Which answer is better?
The first is more “efficient” because it produces smaller intermediate
relations. But since this is all just math, it doesn’t matter! (And in a
DMBS, where queries are actually executed and can therefore be more or
less efficient, the DBMS optimizes our queries.)

II. Tree
III. SQL Server
1. Consider the following relations:

The meaning of these relations is straightforward; for example, Enrolled

has one record per student-class pair such that the student is enrolled in the
class.
Write the following queries in SQL. No duplicates should be printed in
any of the answers.
a) Find the names of all Juniors (level = JR) who are
enrolled in a class taught by Ivana Teach.

b) Find the age of the oldest student who is either a History

major or enrolled in a course taught by I. Teach.

c) Find the names of all classes that either meet in room

R128 or have five or more students enrolled.

d) Find the names of all students who are enrolled in two

classes that meet at the same time.

e) Find the names of faculty members who teach in every

room in which some class is taught.
f) Find the names of faculty members for whom the
combined enrollment of the courses that they teach is less than
five.

g) For each level, print the level and the average age of
students for that level.

h) For all levels except JR, print the level and the average
age of students for that level.

i) For each faculty member that has taught classes only in

room R128, print the faculty member’s name and the total
number of classes she or he has taught.

j) Find the names of students enrolled in the maximum

number of classes.
 k) Find the names of students not enrolled in any class.

l) For each age value that appears in Students, find the level
value that appears most often. For example, if there are more FR
level students aged 18 than SR, JR, or SO students aged 18, you
should print the pair (18, FR).

2. The following relations keep track of airline flight

information:

Note that the Employees relation describes pilots and other kinds of
employees as well; every pilot is certified for some aircraft, and only pilots
are certified to fly. Write each of the following queries in SQL. (Additional
queries using the same schema are listed in the exercises for Chapter 4.)
a) Find the names of aircraft such that all pilots certified to
operate them have salaries more than $80,000.
 b) For each pilot who is certified for more than three aircraft,
find the eid and the maximum cruisingrange of the aircraft for
which she or he is certified.

c) Find the names of pilots whose salary is less than the price
of the cheapest route from Los Angeles to Honolulu.

d) For all aircraft with cruisingrange over 1000 miles, find

the name of the aircraft and the average salary of all pilots
certified for this aircraft.
Observe that aid is the key for Aircraft, but the question asks for aircraft names; we
deal with this complication by using an intermediate relation Temp:

e) Find the names of pilots certified for some Boeing aircraft.

f) Find the aids of all aircraft that can be used on routes

from Los Angeles to Chicago.
g) Identify the routes that can be piloted by every pilot who
makes more than $100,000.

h) Print the enames of pilots who can operate planes with

cruisingrange greater than 3000 miles but are not certified on
any Boeing aircraft.
i) A customer wants to travel from Madison to New York
with no more than two changes of flight. List the choice of
departure times from Madison if the customer wants to arrive in
New York by 6 p.m.

j) Compute the difference between the average salary of a

pilot and the average salary of all employees (including pilots).

k) Print the name and salary of every nonpilot whose salary

is more than the average salary for pilots.
 l) Print the names of employees who are certified only on
aircrafts with cruising range longer than 1000 miles.

m) Print the names of employees who are certified only on

aircrafts with cruising range longer than 1000 miles, but on at
least two such aircrafts.

n) Print the names of employees who are certified only on

aircrafts with cruising range longer than 1000 miles and who are
certified on some Boeing aircraft.

3. Consider the following relational schema and briefly

answer the questions that follow
a) Define a table constraint on Emp that will ensure that
every employee makes at least $10,000.

b) Define a table constraint on Dept that will ensure that all

managers have age > 30.

c) Define an assertion on Dept that will ensure that all

managers have age > 30. Compare this assertion with the
equivalent table constraint. Explain which is better.
 d) Write SQL statements to delete all information about
employees whose salaries exceed that of the manager of one or
more departments that they work in. Be sure to ensure that all the
relevant integrity constraints are satisfied after your updates.

IV. ERD
V. Reference
1. https://www.eecs.yorku.ca/~papaggel/courses/eecs3421/
docs/tutorials/tut1-ra.pdf
2. https://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdit
ion/qbe.pdf
3. https://www.eecs.yorku.ca/~papaggel/courses/eecs3421/
docs/tutorials/tut2-ra.pdf
4. https://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdit
ion/supporting_material.htm#Students%20and%20Instructors