課程名稱︰資料庫管理
課程性質︰系必修
課程教師︰李瑞庭
開課學院：管理學院
開課系所︰資管系
考試日期（年月日）︰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.