数据库原理（双语）习题assignment - of - module02

Assignment of Module02

Questions:

2.1 Discuss each of the following concepts in the context of the relational data model: (a) relation (b) domain (c) attribute (d) tuple

(e) degree and cardinality

2.2 Discuss the properties of a relation

2.3 Discuss the difference between the candidate keys and the primary key of a relation. Example what is meant by a foreign key. How do foreign keys of relations relate to primary key? Give examples to illustrate your answer. (1)Candidate Key (2)Primary Key (3) Foreign Keys

2.4 Define the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules.

2.5 What is view? Discuss the difference between a view and a base relation.

Exercises :

The following tables form part of a database held in a relational DBMS: Hotel (hoteNo, hoteName, city) Room (roomNo, hoteNo, type, price)

Booking (hoteNo, guestNo, dateFrom, dataTo, roomNo) Guest (guestNo, guestName, guestAddress)

Where Hotel contains hotel details and hotelNo is the primary keys;

Room contains room details for each hotel and (roomNo, hoteNo) forms the primary key;

Booking contains details of bookings and (hoteNo, guestNo, dateFrom) forms the primary key; Guest contains guest details and guestNo is the primary key.

2.6 Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to these relations.

2.7 describe the relations that would be produced by the following relational algebra operations: a) ∏hotelNo(? price>50(Hotel))

b) ? Hotel.hotelNo=Room.hotelNo(Hotel × Room)

c) ∏hotelNo(Hotel∞Hotel.hotelNo=Room.hotelNo（? price>50(Room)）)

d) (? dataeTo≥’1-Jan-2002’(Booking))∝Guest

e) Hotel⊿Hotel.hotelNo=Room.hotelNo(? price>50(Room)) (‘⊿’means semi-join operation) f) (∏guestNo,hotelNo (Booking∞（Guest)）)÷∏hotelNo(? city=’London’Hotel))

2.8

(a) List full details of 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 the 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 Grosvenor Hotel.

2.9 Using relational algebra, create a view of all rooms in the Grosvenor Hotel, excluding price detail. What are the advantage of this view?

2.10 Represent, by means of one or more relations, the information contained in a timetable of departures from a railway station. Show the number, time, final destination, category and stops of every departing train.