solution of TEST01

TEST 01

1.For each of the following questions there are four choices marked A, B, C and D. Only one of them is correct. You should decide on the correct choice and write down its mark on the answer sheet.£¨Points: 24 £©

(1) It is C that ensures the data independence in database system.

A£®the schema and the external schema B£®the schema and the internal schema

C£®the two mappings between the three level of schemas D£®the three level architecture of schemas

(2) Which one of the following is not the property of relations? A

A£®Each attribute has a distinct data type. B£®Each attribute has a distinct name. C£®Order of rows has no significance. D£®Order of cols has no significance. (3) In relational model, entity integrity means that B . A£®there must not be empty rows in every relation B£®no attribute of a primary key can be null C£®attributes are allowed to be null D£®foreign keys are allowed to be null

£¨4£©A relation schema ___C__.

A£®can have exactly one candidate key B£®can have many primary keys C£®can have one or many candidate keys D£®can have one or many primary keys (5) Null represents a value for an attribute in a tuple that is D .

A£®zero B£®an empty set C£®a space D£®currently unknown or not applicable (6) For a relation, each candidate key C . A£®can only have at least two attributes B£®can only have at most one attributes C£®may have one or more attributes D£®must contain all the attributes of the relation £¨ALL-KEY£¬È«Â룩

(7) Suppose there are 10 tuples in each of relation R, S and W, then the number of tuples in R?S?W is C . A£®10 B£®30 C£®1000 D£®unknown (8) Which of the following operations is not supported by DDL ? C

A£®creating some views in a database B£®modifying table structures in a database C£® finding some rows from a table D£®creating some tables in a database

2. Given the following descriptions, create some appropriate relations for the management of the Library. (point:20)

In a library, a reader can borrow zero or more books; a book can be borrowed by zero or more readers; an author can write one or more books, but for each book, we only store its first author. Books should have attributes such as a unique book number, Title, publisher, publish time, price, ISBN, each book belongs to a certain category and there are a number of books for each category. Each category has a unique number and a description. Readers should have attributes such as a unique reader number, reader name, DOB(date of birth)gender(ÐÔ±ð), address; telephone number,

Email. Authors should have attributes such as A unique author number, author name, birth-year, gender, affiliation(ËùÔÚµ¥Î»), style. When a reader borrows a book, we must register the borrowing date, and the date due to return, when a reader returns a book, we must record the date of return..

There are several rooms in the library for list out all the books. In each room, there are a number of book shelves(Êé¼Ü). All the books are grouped by category, and then placed on certain book shelves. One book shelf lists the books that belong to a same category. That is to say different kinds of books were placed on different book shelves. Each room has a unique number, telephone, assistant and the number of book shelves, each book shelf has a unique code, length.

Reader£¨Rnumber, Rname, DOB£¬gender, address£¬tel£¬Email£©

BOOK£¨bookNO, Title, publisher, publish time, price, ISBN£¬Category, ShelfNO, AuthorNO£© AUTHOR£¨authorNO, author name, birth-year, gender, affiliation, style£© CATEGORY£¨CategoryNO,Descrioption£©

BOOKSHELF(ShelfNO,CategoryNO,Descripion,RoomNO) ROOM(RoomNO, telephone, assistant, shelf-number,) BORROW(Rnumber, bookNO, outdate, duedate, returndate)

3. Calculation of relational algebra (4 points each, total 20 points).

Suppose we are given relation R and S as below, please give results of the following relational algebra expressions.

1) ?C(T)¡É?C(V) 2) ?C(T)¡È?C(V)

3) ?C(T)-?C(V) 4) ?A,B,D( ?T.B=U.B (T¡ÁU)) 5) ?T.C=V.C=4 (T¡ÁV)

1) ?C(T)¡É?C(V)={ 3,4}

2) ?C(T)¡È?C(V)={3£¬4£¬5}

3) ?C(T)-?C(V) ={5}

4) ?A,B,D( ?T.B=U.B (T¡ÁU))={£¨1,2,4£©£¬£¨1,2,6£©, £¨1,2, 7£©,

(1,1,3), (1,1, 5), (1,1, 9), (2,1, 3), (2,1, 5), (2,1, 9),

£¨2,2,4£©£¬£¨2,2, 6£©,£¨2,2, 7£©, (3,1,3), (3,1, 5), (3,1, 9)}

5) ?T.C=V.C=4 (T¡ÁV)={(2,1,4,4,6), (2,1,4,4,7),

(2,2,4,4,6), (2,2,4,4,7),

(3,1,4,4,6), (3,1,4,4,7)}

4. The following tables form part of a database held in a relational DBMS. Driver (Dnumber, Dname, Dsex, Dage, Daddress,Spv_Dnum) Primary key: Dnumber, FK: Spv_Dnum references Driver(Dnumber) Bus (Bnumber, Btype, capacity) Primary key: Bnumber ,FK:

Schedule (RouteNo, Bnumber, Dnumber, date, d_time) Route£¨RouteNo£¬ departurestop,destination,stops£© Stops()

Primary key: (RouteNO,date,d_time) CK: (Dnumber,date) FK: Dnumber references Driver(Dnumber) Bnumber references Bus(Bnumber)

Each driver has a unique number represented by ¡®Dnumber¡¯. The name, sex, age, address of the driver represented by Dname, Dsex, Dage, Daddress respectively, and the Spv_Dnum is the number of the supervisor. Every bus has a unique number (Bnumber) and a type (Btype) and a capacity (=maximum number of passengers, ³µ×ÓµÄ×ùλÊý). Furthermore, we have a table that contains the scheduling of buses and drivers on existing routes£¨¹«½»Ïß·£©. All routes start at the central bus station. The attribute `time' indicates the departure time£¨·¢³µÊ±¼ä£©. One route has a number of buses and drivers, and for each day, a bus and a driver can only be scheduled on one route.

(1)

Identify the primary keys, candidate keys (if have) and foreign keys of the above relations.(point: 20)

(2) Formulate the following queries in RA and SQL respectively: (point:16)

Q1: List the numbers, types of the buses with a capacity of more than 60.

¡ÇBnumber,Btype(§¢capacity>60(Bus))

Q2: Give the numbers, names of the drivers who are older than 50.

¡ÇDnumber, Dname(§¢dage>50(Driver)

Q3: List the numbers of the buses who has never been scheduled. ¡ÇBnumber(Bus)- ¡ÇBnumber(Schedule) Q4: List the numbers ,names of the female drivers.

¡ÇDnumber, Dname(§¢Dsex =¡¯Female¡¯(Driver)