ÔN TẬP MIDTERM PRINCIPLES OF DATABASE MANAGEMENT
● Hình thức: Tự Luậ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? 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. 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? 2.
Does the schema allow for an employee with no boss? 3.
How would the world have to be different if boss were a key for Supervises? 4.
How would the world have to be different if both boss
and employee together were a key for Supervises? 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. 3. Which answer is better? II. Tree
Các bạn tự xem file Tree rồi sau ó luyện tập ổi tất cả các RELATIONAL
ALGEBRA các bạn ã làm bên trên thành dạng Tree nhé. 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 I. 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. 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 Tham khảo:
https://www.guru99.com/er-diagram-tutorial-dbms.html
https://course.ccs.neu.edu/cs3200sp18s3/ssl/lectures/lecture_07_erd.pdf
A. Design an ERD for the description below.
B. Design an ERD for the description below. C. YRB.com 1.
How does one determine the price a given customer paid
for a given book? Should an attribute price be added to buys? 2.
Is it possible for two customers to buy the same book but
for different prices? If so, how is this possible? If not,
how does the logic of the E-R diagram prohibit this? 3.
Does the customer always pay the lowest price for which
he or she is eligible (eligible)? If not, is there an easy
way to modify the E-R diagram in order to assure this? 4.
Does the E-R diagram ensure that the Offer under which
a customer buys a book is, in fact, legitimate? That is, an
offer is for a particular club’s members. Are we
guaranteed that the customer belongs to the
corresponding club? Why or why not? 5.
There is a serious flaw (at least one) in the design in
Figure 1, at least in as far as any bookseller would be concerned. What is the flaw? 6.
Redesign the E-R from Figure 1 to fix this.
V. Reverse from Schema to ERD VI. 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