《assignment_of_module02》由会员分享，可在线阅读，更多相关《assignment_of_module02（3页珍藏版）》请在金锄头文库上搜索。
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 KeySuperkey (K) such that no proper subset is a superkey within the table. In each record, values of K uniquely identify that r
6、ecord (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 CNO001002
7、001003 002004 003005 004Primary 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
8、 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 dynami
9、c 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 corre
10、sponding 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
11、 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 keyRoom (roomNo, hoteNo, type, price) roomNo i
12、s primary 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 d
13、etails 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
14、 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 between
15、 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 for
16、eing 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( price50(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)Al
