数据库原理三、四章作业

1.Please answer the questions of chapter3

Chapter 3 3.1

(a)relation

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

(b)attribute

an attribute is a named column of a relation.

(c)domain

set of allowable values for one or more attributes.

(d)tuple

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

(e)intension and extension

the database schema is called the intension of the database.

Extension: the database instance is called the extension

(f)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.3,

Discuss the differences between a relation and a relation schema. What is relational database schema?

(1)A relation is a table with columns and rows ;A collection of normalized relations with distinct relation names.

(2)A set of relation schemas , each with a distinct name .

3.4,d iscuss the properties of a relation

A relation has the following properties:

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 differences between the candidate keys and the primary key of a relation . Explain what is meant by a foreign keys of relations relate to candidate keys ? Given examples to illustrate your answer.

(1)candidate keys：

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

Primary Key:

Candidate key selected to identify records uniquely within table.

A primary key may consist of only a single attribute or may be concatenated

No attribute in a primary key can ever be null - referred to as the entity integrity constraint

Every tuple must have, by definition, a unique value for its primary key

A attribute, or a minimal group of attributes, that uniquely identifies a tuple(record) within a relation(table).

（2）course

C(课程号) CNO（先修课程号）

001 ————

002 001

003 002

004 003

005 004

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.

(2)entity integrity and referntial integrity

(3)

3.7

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.

2. Please do the exercises of chapter3&chapter4,and submit the solution.

Chapter 33.8

(1)Room(roomNo, hotelNo,type,price) foreign key :hotelNo

Constraint fk_Room_Hotel foreign key(hotelNo) references Hotel(hotelNo)

(2)Booking(hotelNo,guestNo,dataFrom,dataTo,roomNo)

foreign key :hotelNo guestNo roomNo

Constraint fk_Booking_Room foreign key (hotelNo) references Room(hotelNo)

Constraint fk_Booking_Guest foreign key (guestNo) references Room(guestNo)

Constraint fk_Booking_Room foreign key (roomNo) references Room(roomNo)

Chapter 4 4.8,

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

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

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

(4)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.

(5)

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

4.12 (only use Relational Algebra),

(a)∏hotelName(Hotel)

(b)∏roomNo,hotelNo(σtype='single'∧price<50(Room))

(c)∏guestName,guestAddress(Guest)

(d)∏type,price(σhotelName='Grosvernor'(Hotel∞Room))

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

(f)(∏σhotelName='Grosvernor'(Hotel∞Room))∞(∏

guestName(∏σhotelName='Grosvernor'(Hotel∞Booking∞Guest)) (g)

4.13∏city,roomNo,type(σhotelName='Grosvernor'(Hotel∞Room))

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