assignment_of_module02

Assignment of Module02

Questions:

3.1 Discuss each of the following concepts in the context of the relational data model:

(a) relation

A relation is a uniquely named two-dimensional table with identically structured rows (technically tuples) and distinctly named columns (or attributes).

(b) domain

set of allowable values for one or more attributes.

(c) attribute

An attribute is a named column of a relation.

(d) tuple

a record of a relation or a row of a relation.

(e) degree and cardinality

The degree of a relation is the number of attributes

The cardinality of a relation is the number of tuples it contains

3.4 Discuss the properties of a relation

a.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)value

c.Each attribute has a distinct name

d.The value of an attribute are all from the same domain

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 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

Superkey (K) such that no proper subset is a superkey within the table. In each record, values of K uniquely identify that record (uniqueness). No proper subset of K has the uniqueness property (irreducibility).

(2)Primary Key

Candidate key selected to identify records uniquely within table.

(3) Foreign Keys

Column, or set of columns, within one table that matches primary key of some (possibly same) table.

Primary 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 table, 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 result 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 corresponding 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 are 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 key

Room (roomNo, hoteNo, type, price) roomNo is primary key; hoteNo is foreign key

Booking (hoteNo, guestNo, dateFrom, dataTo, roomNo) (guestNo, dateFrom) is composite key;

hoteNo is foreign key

Guest (guestNo, guestName, guestAddress)guestNo is the primary key

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.

3.8 Identify the foreign keys in this schema. Explain how the entity and referential 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 key’sattributes is NON-NULL.

Referential Integrity:In the relational data model,association between 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 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(δ price>50(Hotel))

the coding number of hotels whose room price is more than 50 pounds the coding number of hotels whose room price is more than 50 pounds

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

All the hotels' coding numbers,names and locations ,and their rooms'coding,type and price.

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

All the names of hotels whose room's price is above 50 pounds.

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

All the numbers,names and addresses of travellers,and the hotel codings,the time of checking in and out and the room number of the traveller whose reservation is after january 1th,2002.

e) Hotel⊿Hotel.hotelNo=Room.hotelNo(δ price>50(Room)) (‘⊿’means semi-join operation)

f) (∏guestNo,hotelNo (Booking∞（Guest)）)?∏hotelNo(δcity=’London’Hotel))

The coding numbers of hotels that have guest in London and the names of guest.

4.12

(a) List full details of all hotels.

Hotels

(b) List all single rooms with a price below $20 per night.

∧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.

(e) List the guests currently staying at the Grosvenor Hotel.

∏hotelName='Grosvernor'(Hotel∞Booking∞Guest)

(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

r'(Hotel∞Room

(g) List the guest details(guestNo, guestName, and guestAddress) of all guests staying at Grosvenor Hotel.

∏guestNo, guestName, guestAddress (δhotelName='Grosvernor'(Hotel∞Booking∞Guest))

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

make the graph more visiual, the information more comprehensive , reject the various commercial ads, and ensure the security