数据库原理（双语）邓绪斌-关系操作作业.docVIP

《数据库原理》作业2 题目：关系操作 学生姓名 学 号 班 级 2015年10月 The following tables form part of a database held in a relational DBMS: Hotel(hotelNo, hotelName, city) Room(roomNo, hotelNo, type, price) Booking(hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest(guestNo, guestName, guestAddress) where Hotel contains hotel details and hotelNo is the primary key; Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key; Booking contains details of bookings and (hotelNo, guestNo, dateFrom) forms the primary key; Guest contains guest details and guestNo is the primary key. Questions: (1) Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to these relations. (2) Produce some sample tables for these relations that observe the relational integrity rules. Suggest some enterprise constraints that would be appropriate for this schema. In the Hotel schema defined in 1, generate the relational algebra expressions for the following queries: (a) List all hotels. (b) List all single rooms with a price below $20 per night. (c) List the names and cities of all guests. (d) List the price and type of all rooms at the Grosvenor Hotel. (e) List all guests currently staying at the Grosvenor Hotel. (f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied. (g) List the guest details (guestNo, guestName, and guestAddress) of all guests staying at the Grosvenor Hotel. In the Hotel schema defined in 1, and using relational algebra, create a view of all rooms in the Grosvenor Hotel, excluding price details. What are the advantages of this view? Suppose we are given relation r and s as below, in which we stipulate abc…z, please give the results of r∪s, r-s, r∩s, r×s, ?A,C(r), ?AB(r), and rr.As.B s, respectively. r s A B C A B C a b c d b a d f c d e g b d f a f h d g k Suppose we are given relation