清华大学数据库原理课件期末试题A答案

Final-Term Exam Answers

Version A

1. Answer:

1)

Reduce data redundancy and inconsistency 2)

Convenience to access data 3)

Avoid data isolation 4)

Ensure the certain type of consistency constraints 5)

Ensure atomicity 6)

Guard against concurrent-access anomalies 7)

Provide data security 8) Storage Access optimization

2. Answer:

2.1 Cardinality constraint of E-R

Cardinality constraints are specified in UML in the same way as in E-R

diagrams, in the form l..h , where l denotes the minimum and h the maximum number of relationships an entity can participate in.

E.g.,

2.2 Schema of a relation

Relation schema is the logical design of a relation in database,

corresponding to the programming-language notion of type definition.

E.g., Account-schema=(account-number, branch-name, balance)

2.3 Domain Domain is the set of permitted values of an attribute.

E.g., the attribute of name is string domain (or superset of char)

3. Answer:

()()()A1, R.A2, A3R.A2S.A2r1R S ==πσ?

4. Answer:

4.1

Select ssn from Books, Authors

where Books.isbn=Authors.isbn and Books.type<>’fiction ’;

Select ssn from Books, Authors

Where Books.isbn = Authors.isbn and Books.type in (‘technical’, ‘self-help’)

Select ssn from Books natural inner join Authors

Where (Books.type = ‘technical’) or (Books.type = ‘self-help’)

4.2

Select ssn from Authors

where not exist ( select * from Books where Authors.isbn=Books.isbn and

Books.type=’fiction’)

Select ssn from Authors

except all

select ssn from Authors, Books

where Authors.isbn=Books.isbn and Books.type=’fiction’

Select ssn from Authors

Where ssn not in (select ssn from Authors natural inner join Books

Where Books.type = ‘fiction’)

4.3

Select distinct A.isbn from Authors as A, Authors as B

where A.ssn<>B.ssn and A.isbn=B.isbn

Select distinct isbn from Books

where not unique (select ssn from Authors where books.isbn = Authors.isbn)

4.4

Select ssn, COUNT(*) from Authors group by ssn;

4.5

Create table Authors (

ssn CHAR(9),

isbn CHAR(20),

PRIMARY KEY (ssn, isbn),

FOREIGN KEY (isbn) REFERENCES Books

)

5. Answer:

5.1

(B,D); (A,E); (C,B); (A,C)

5.2

(B,D); (A,E); (A,B,C) Candidate Keys: A,B or A,C

5.3

BCNF don’t preserve the dependency of A,B C

6. Answer

6.1 D

6.2 B

6.3 B

6.4 A

6.5 B or D(We consider the assignment when Object ID is null)

7. Answer:

7.10, Unlimited

7.20 or 1

7.3

Person (pName)

Sport (sName)

Athlete (pName)

Coach Of (pName, sName)

Plays (pName, sName, Position)

8. Answer:

The method I can preserve the original order of data, if data has been listed by order. In addition, it can make use of space the most efficiently.

The method II can also make use of space efficiently and is much faster than the method I.

But it just works with fixed-length records.

The method III is a fast way to do deletion with space waste. It does work with both fixed-length and variable-length records.