---
tags: 大三
---
# 資料庫管理系統導論 - 期末筆記 - 國立中興大學大資訊工程系 Winter 2020

## Database Final Exam Spring 2020

Provided by [tomy0000000](https://github.com/tomy0000000)

Stored at : <https://1drv.ms/b/s!AqQUMbNuI5zUidRtjOtMHsGFoNAKTQ>

1. [15 points] A hotel database needs to store the following information.The hotel offers three types of rooms, including single room, double room, and triple room. Every room is identified by its unique room number. Every employee at the hotel is any of a receptionist, a cleaning staff, or a kitchen staff. Each receptionist is identified by an employee number, and has her/his name and years of experience as attributes. The kitchen staff are characterized by their specific responsibilities, e.g., being a cook or a waiter. A single cleaning staff is assigned to clean each room every morning. For each cleaning assignment, the date and the status need to be provided. Receptionists allocate a unique room to each guest. The arrival and departure dates of a guest will in turn determine the occupation of a specific room. Guests are uniquely identified by their passport number, with name, phone numbers, arrival date, departure date, and credit card number as attributes. Children can accompany the guest. Each child is identified by his/her name. Draw an ER diagram that captures the above information. Also indicate the key attributes.

2. [10 points] According to your answers of question 1, reduce the E-R diagram into a set of relational schemas. Also specify the primary key of each of your schemas.Note that the number of your schemas should be kept as minimal as possible.

3. [10 points] Consider the reduction of the aggregation proj_guide (in slide 6.63) into relational schemas.
+ (a) Under what conditions the proj_guide is redundant and can be discarded?
+ (b) Under what conditions the proj_guide is redundant but cannot be discarded?

4. [20 points] Consider a relation schema R (W, X, Y, Z). For each of the following sets of functional dependencies on R, please identity the candidate key(s), identify the best normal form that R satisfies (INF, 2NF, 3NF, or BCNF), and point out the normal form violations.

5. 
6. 
7. 

<style>
#author{
border: 1px solid gray;
border-radius: 10px;
padding: 10px;
width: 80%;
margin: 10px auto;
}
</style>
<div id="author">
<p>111級中興資工</p>
<p>作者：<a href="https://github.com/liao2000">廖柏丞</a></p>

</div>