上海财经大学数据库系统课程考试卷

上海财经大学数据库系统课程考试卷（A）

2005——2006学年第二学期

姓名学号班级得分

1.True or False. (20 points)

1).External/Conceptual Mapping is the key to physical data in

dependence.

2).DA is the person who provides the necessary technical sup

port for implementing those decisions.

3).It is the job of the DML processor to choose an efficient

way to implement user’s request.

4).Relational data model represents the data in a database a

s a collection of tables.

5).Indexes allow the database management system to directly

access the rows of the table on which the index has been define d.

6).Since the logical data structures are intended for the appli

cation programmers, the database designer is not involved in their development.

7).Views are only descriptions of data. They are not real tabl

es.

8).Denormalization causes the redundant storage of informatio

n.

9).Horizontal splitting of a relational variable always creates r elational variables for different primary key ranges of the original r elational variable.

10).For an unordered retrieval request, different executions of the request may return the retrieved rows in a different sequence.

11).A relationship type can again be the source or target of a relationship type.

12).A business relationship type can always be translated into

a relationship type of the entity-relationship model for the applicati on domain.

13).The updating of redundant information for violations of th

e Third Normal Form cannot be controlled by triggers.

14).SQL does not permit users to define their own types, colu mns must be defined in terms of built-in types only.

15).Once a transaction commits, its updates may not survive i n the database when there is a subsequent system crash.

16).Any transaction which was in progress at the time of the system failure must be redo at restart time.

The checkpoint record gives a list of all transactions that were in progress at the time the checkpoint was taken.

If transaction A holds an S lock on tuple t, then a request fro m some distinct transaction B for a X lock on t will be denied.

Breaking the deadlock involves choosing one of the deadlocked transactions and commit it.

20).If all transactions obey the "two-phase locking protocol," al

l possible interleaved schedules are serializable.

2.Choose the right answer of the following exercises.

(30 points)

1).Given the tables:

Customer Account

The statement:

SELECT * FROM Customer, Account

will return how many rows?

A. 2

B. 3

C. 5

D. 6

2).Given the tables:

EMP INCOME

IN COME was defin ed as follo

ws:

CR EATE TABL

E INCOME

(Empid CHAR(3), MonthNo CHAR(3), Income DECIMAL(6,2),

CONSTRAINT const1 FOREIGN KEY (Empid) REFERENCES EMP(Empid) ON DELETE CASCADE);

How many rows would be deleted from INCOME if the following command is issued ?

DELETE FROM EMP WHERE Empid = '2';

A. 0

B. 1

C. 2

D. 3

3). Given the following tables:

Stud

SC

The following results are desired:

Which of the following joins will yield the desired resul ts?

A. SELECT * FROM Stud, SC WHERE Stud.Sno=SC.S#

B. SELECT * FROM Stud, SC WHERE Stud.Sno=S

C.S#(+)

C. SELECT * FROM Stud , SC WHERE Stud.Sno(+)=SC.S#

D. SELECT * FROM Stud , SC WHERE Stud.Sno(+)=SC.S# (+)

4).Given the table T1, created by:

CREATE TABLE EMP

(ID CHAR(3) PRIMARY KEY,

Name CHAR(8),

Age Integer,

CONSTRAINT const1 CHECK (Age>18 and Age<60));

The following SQL statements are issued:

INSERT INTO EMP(ID, Age) VALUES ('001',25);

INSERT INTO EMP(ID, Age) VALUES (‘002’,35);

INSERT INTO EMP(ID, Age) VALUES (‘003’,15); COMMIT;

How many rows are inserted into Table EMP?

A. 0

B. 1

C. 2

D. 3

5).Given the following tables:

Stud SC

Which of the following statements removes the rows fr om the Stud table that have courses in the SC table?

A. DELETE FROM Stud WHERE Sno IN (SELECT Sno FROM SC);

B. DELETE FROM Stud WHERE S# IN (SELECT S# FROM SC);

C. DELETE FROM Stud WHERE Sno IN (SELECT S# FROM SC);

D. DELETE FROM Stud WHERE S# IN (SELECT Sno FROM SC);

6).Relvar R(A,B,C,D,E,F)satisfies the following FDs:

A a BC

B a EF

Which of the following is the candidate key ?

A. null set

B. AC

C. AD

D. ABD

7).Relvar ACCOUNT(Cno, Cname, AccountNo, Balance)satisfies the f ollowing FDs:

Cno a Cname

AccountNo a {Cno, Balance}

Which of the following is the best answer ?

A. R∈1NF

B. R∈2NF

C. R∈3NF

D. R∈BCNF

8).Relvar R(A,B,C,D,E)satisfies the following FDs:

A a BCDE

Which of the following is the best answer ?

A. R∈1NF

B. R∈2NF

C. R∈3NF

D. R∈BCNF

9).Relvar R (S#, SNAME, P#, QTY) satisfies the following FDs:

Which of the following is the best answer ?

A. R∈1NF

B. R∈2NF

C. R∈3NF

D. R∈BCNF

10).Relvar R(S#,STATUS,CITY) satisfies the following FDs: S# a CITY

CITY a STATUS

Replace R by the two projections R1 and R2 as follows: R1(S#, CITY) with Primary Key S#

R2(S#, STATUS) with Primary Key S#

Which of the following is the right answer ?

A. Projections R1 and R2 of relvar R are independent.

B. The decomposition is dependency preservation.

C. The decomposition is a nonloss decomposition.

D. The decomposition is dependency preservation and nonloss decomposition

11).Which two of the following choices are objectives the normaliz ation of relation variable wants to achieve?

A. Remove redundancies within relation variables.

B. Improve the performance of the database being designed.

C. Reduce the size of the relation variables.

D. Remove redundancies across relation variables.

E. Avoid data inconsistencies and other problems resulting from ins ert, update, or delete operations.

12).Let transactions T1,T2 be defined to perform the following ope rations:

T1: Add four to B

T2: Treble B

Suppose transactions T1 and T2 are allowed to execute concurrently. If B has initial value three. How many possible correct results are ther e?

A. 1

B. 2

C. 3

D. 4

13).When can tuple types be merged?

A. Tuple types with always corresponding foreign key values can be merged.

B. Tuple types with always corresponding primary key values can b e merged.

C. Tuple types with different foreign key values can be merged.

D. Tuple types with different primary key values can be merged.

14).Assume that the delete rule for a referential constraint is CAS CADE. Choose a case for which the deletion of a parent row would sti ll fail.

A. The parent table has another foreign key.

B. Another referential constraint with delete rule RESTRICT prevents the deletion of the parent row.

C. Its referential table has more than one candidate keys.

D. The parent table has more than one candidate keys.

15).Which two of the following methods can ensure the correctnes s of derivable data ?

A. Not storing them and deriving them each time they are needed.

B. Revaluating and storing the data which affect the derivable data.

C. Triggers reevaluating and storing the data which affect the deriv able data.

D. Triggers reevaluating and storing the derivable data each time d ata affecting the derivable data are inserted, updated, or deleted.

3.Assume that you have the following entity-relationship model:

Furthermore, assume that the entity types and relationship types have the following instances:

Which instances will be deleted after entity instance C2 of entity type C has been deleted? (8 points)

4.Consider the following four relations for an Order database:

CREATE TABLE Product

(Pno CHAR(4) PRIMARY KEY, Pname VARCHAR2(40), Price NUMBER(7,2), Inventory int);

CREATE TABLE Customer (Cno CHAR(5) PRIMARY KEY, Cname CHAR(20), Company CHAR(30),

City CHAR(20),

Tel CHAR(15));

CREATE TABLE Orders

(Ono CHAR(5),

Order_date DATE,

Cno CHAR(5),

Freight INT,

Shipment_date DATE, PRIMARY KEY(Ono),

FOREIGN KEY(Cno) REFERENCES Customer(Cno));

CREATE TABLE Order_items

(Ono CHAR(5),

Pno CHAR(4),

Qty int,

Discount NUMBER(4,2),

PRIMARY KEY(Ono,Pno));

Specify the following queries in relational algebra:

(10 points)

1).Get the name and telephone number for all customers wh

o is located in Shanghai.

2).Get the Order number, product name and quantity for all

orders.

3).Get the Order Number for whose orders which ordered all

the products.

4).Get the product number which is ordered by customer ‘C0

01’.

5. Based on the above database, specify the following operate in SQL.

(18 points)

1).Get order number, order date and freight of all

orders.

2).For all orders, get order number, product name, quantity a

nd price. Order the result by order number and product name.

3).Find the order number of all orders which didn’t order ‘P0

01’ product.

4).Find all the customer names and the total number of orde

rs they ordered.

5).Add a foreign key reference on the order_items table whic

h ensure that the order will not order an nonexistent product.

6).Delete all order items of order ‘O001’.

6. Assume that R and R1through R n are relational variables satisfying the following conditions:

They all have the same primary key.

At all times, each primary key value of R1through R n occ urs in R.

Which further condition must be satisfied for R1through R n being a p erfect decomposition of R?

(3 Points)

7. Relvar R{A,B,C,D,E,F,G,H,I} satisfies the following FDs:

A →E

AB →C

EG →H

Compute the closure {ABG}+under this set of FDs.

(4 Points)

8. A relvar TIMETABLE is defined with the following attributes:

D: Day of the week (1 to 5)

P: Period within day (1 to 8)

C: Classroom number

T: Teacher name

L: Lesson name

The tuple {D:d,P:p,C:c,T:t,L:l} appears in this relvar if and only if at ti me {D:d,P:p} lesson l is taught by teacher t in classroom c. You can assu me that lessons are one period in duration and that every lesson has a n ame that is unique with respect to all lessons taught in the week.

1).What are the candidate keys of relvar TIMETABLE?

2).Is relvar TIMETABLE in 3NF?

(7 points)

上海财经大学《数据库系统》课程考试卷（A）标准答案

2005——2006学年第二学期

1、是非题答案填入下面的表格中。(共20分)

（“T”表示相应题目的描述是正确，“F”表示相应题目的描述是错误的）

2、选择题的答案请填入下面的表格中。(共30分)

3、把要删除的实例（instances）在下面的表格中圈出来。(共8分)

4、关系代数: (共10分)

1)(Customer Where City=”上海”) {Cname, Tel}

2)(Order_items Join Product) {Ono, Pname, Qty} 或

((Order_items Times Product )

Where Order_items.Pno= Product.pno) {Ono, Pname, Qty}

3)orders{Ono} DIVIDEBY Product{Pno} PER Order_items{Ono,Pn

o}

4)((Orders Where Cno=’C001’ ) Join Order_items) {Pno}

5、SQL: (共18分)

1)Select Ono, Odate, Freight

From Order;

2)Select order number, product name,

quantity, price*(1-discount)

From Order_itmes, Product

Where Order_itmes.Pno=Product.Pno

Order By Ono, Pno;

3)SELECT DISTINCT Ono

FROM Order

WHERE NOT EXISTS

(SELECT *

FROM Order_item

WHERE Ono=Order.Ono AND

Pno="P001")；

4) Select Cname,Count(Ono);

From Customer, Orders

Where https://www.360docs.net/doc/ad14612749.html,o=https://www.360docs.net/doc/ad14612749.html,o

Group By Cname;

5) Alter Table Order_items

ADD Constraint FK1 Foreign Key Pno

REFERENCE Product ON DELETE CASCADE;

6) Delect

From Order_items

Where Ono=’O001’;

6. At all times, each primary key value of R occurs in R1 … Rn. (共3分)

7. {ABG}+={ABCEGH} (共4分)

8.

1)candidate keys:{L}, {D,P,T}, {C,D,P}

2) ∈3NF