课程名称︰数据库管理
课程性质︰系必修
课程教师︰李瑞庭
开课学院：管理学院
开课系所︰资管系
考试日期（年月日）︰105/11/09
考试时限（分钟）：130
试题 :
Database Management Systems
- Midterm, Nov. 9, 2016 -
1. (10%) How is a one-to-one relationship recorded in a relational schema? Why?
2. (10%) What constraints can a delete operation violate? Why?
3. (10%) What is a complete set of relational algebra operations? Why?
4. (10%) What is a full functional dependency? What is it used for?
5. (10%) Given a relation S(R,E,L,A,T,I,O,N) with the following dependencies,
R -> ELATION, AT -> RELION, A -> IO, N -> A and E -> L, normalize S into 3NF
and BCNF.
6. (25%) Consider the following database schema, where the primary attributes
are underlined.
Sailor(_SID_, SName, Rating, City, Age)
Boat(_BID_, BName, Color, City)
Reserve(_SID_, _BID_, _Date_, Price)
Write relational algebra expressions for the following queries.
(a) For each city, find the sailor with highest rating.
(b) Find the sailors always reserving boats in the same city.
(c) Find the sailors who reserve every boat reserved by Luffy.
(d) Find the sailors always reserving red boats.
(e) Find the number of sailors living in the cities without boat rental.
7. (25%) Write SQL statements for the following queries with respect to the
database schema shown in question 6.
(a) Find the number of boats in each city.
(b) For each city, find the youngest sailor with rating greater than 8.
(c) Find the sailors making more than 50 reservations in their inhabiting
cities.
(d) Find the sailors who did not make any reservations this year.
(e) Find the sailors having reserved boats in every city.