assignment_of_module02

1、Assignment of Module02Questions:3.1 Discuss each of the following concepts in the context of the relational data model:(a) relationA relation is a uniquely named two-dimensional table with identically structured rows (technically tuples) and distinctly named columns (or attributes).(b) domainset of

2、allowable values for one or more attributes.(c) attributeAn attribute is a named column of a relation.(d) tuplea record of a relation or a row of a relation.(e) degree and cardinalityThe degree of a relation is the number of attributesThe cardinality of a relation is the number of tuples it contains

3、3.4 Discuss the properties of a relationa.the relation has a name that is distinct from all other relation names in the relational schema;b.Each cell of the relation contains exactly one atomic (single)valuec.Each attribute has a distinct name d.The value of an attribute are all from the same domain

4、e.Each tuple is distinct there are no dulicate tuples;f.The order of attributes has no significance g.The order of tuples has no significance, theoretically.(however, in practice,the order may affect the effciency of accessing tuples.)3.5 Discuss the difference between the candidate keys and the pri

5、mary 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 Superkey (K) such that no proper subset is a superkey within the table. In each record, values of K uniquely identify that

6、record (uniqueness). No proper subset of K has the uniqueness property (irreducibility).(2)Primary KeyCandidate key selected to identify records uniquely within table.(3) Foreign KeysColumn, or set of columns, within one table that matches primary key of some (possibly same) table. course C CNO00100

7、2001003002004003005004Primary key：C Foreign key ：CNO constraint pk_course primary key(C), constraint Fk_course foreign key (CNO) references course(C)3.6 Define the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules.Entity Integrity：In a base ta

8、ble, no column of a primary key can be null.Referential Integrity：If FK exists in a table, either FK value must match a candidate key value of some record in its home table or FK value must be wholly null.3.7 What is view? Discuss the difference between a view and a base relation.View: The dynamic r

9、esult of one or more relational operations operating on the base relations to produce another relation .A view is a virtual relation that does not necessarily exist in the database but can be produced upon request by a particular user, at the time of request. Base relation: A named relation correspo

10、nding to an entity in the conceptual schema, whose tuples are physically stored in the database.A virtual relation that does not necessarily actually exist in the database but is produced upon request,at time of request.Contents of a view are defined as a query on one or more base relations.Views ar

11、e dynamic,meaning that changes made to base relations that affect view attributes are immediately reflected in the view.Exercises :The following tables form part of a database held in a relational DBMS:Hotel (hoteNo, hoteName, city) hoteNo is primary keyRoom (roomNo, hoteNo, type, price) roomNo is p

12、rimary key; hoteNo is foreign keyBooking (hoteNo, guestNo, dateFrom, dataTo, roomNo) (guestNo, dateFrom) is composite key; hoteNo is foreign keyGuest (guestNo, guestName, guestAddress) guestNo is the primary keyWhere Hotel contains hotel details and hotelNo is the primary keys; Room contains room de

13、tails 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.3.8 Identify the foreign keys in this schema. Explain how the entity and referentia

14、l integrity rules apply to these relations.Enitity Integrity : a rule is designed to assure that every relation has a primary key,and that the data values for that primary key are all valid.Every primary keysattributes is NON-NULL.Referential Integrity:In the relational data model,association betwee

15、n tables are defined through the use of foreifn keys.Association between HOTELS&ROOM table is defined by including Hotle_No attribute as a foreign key in ROOM. Referential Integrity constraint is a rule that maintains consistency among the rows of two relations. The rules state that if there is

16、a foreing key in one relation either each foreign key value must match a primary key value in another relation or theforeign key value must be NULL.4.8 describe the relations that would be produced by the following relational algebra operations:a) hotelNo(d price>50(Hotel)the coding number of hot

17、els whose room price is more than 50 pounds the coding number of hotels whose room price is more than 50 pounds b) d Hotel.hotelNo=Room.hotelNo(Hotel × Room)All the hotels' coding numbers,names and  locations ,and their rooms'coding,type 

18、;and price.c) hotelNo(HotelHotel.hotelNo=Room.hotelNo（d price>50(Room)）)All the names of hotels whose room's price is above 50 pounds.d) (d dataeTo1-Jan-2002(Booking)GuestAll the numbers,names and addresses of travellers,and the hotel codings,the time of checking in and out and the room

19、number of the traveller whose reservation is after january 1th,2002.e) HotelHotel.hotelNo=Room.hotelNo(d price>50(Room) (means semi-join operation)f) (guestNo,hotelNo (Booking（Guest)）)÷hotelNo(d city=LondonHotel)The coding numbers of hotels that have guest in London and the names of guest.4.

20、12 (a) List full details of all hotels.Hotels (b) List all single rooms with a price below $20 per night.roomNo,hotelNo(stype='single'price<50(Room) (c) List the names and cities of all guests.guestName,guestAddress(Guest) (d) List the price and type of all rooms at the Grosvenor Hotel.type,price(shotelName='Grosvernor'(HotelRoom) (e) List the guests currently staying at the Grosvenor Hotel.hotelName='Grosvernor'(HotelBookingGuest) (f) List