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)