rwDISS750finalexamWord文档下载推荐.docx

1、a. Cartesian product of r and s, r s. SQL run 1a.sql 1 select * 2* from r, s A B C C D E - - - - - - a2 b2 c2 c1 d1 e1 a3 b3 c3 c1 d1 e1 a2 b2 c2 c2 d2 e2 a3 b3 c3 c2 d2 e2b. r join s, r |X| r.C= s.C s. run 1b.sql 2 from r, s 3* where r.C = s.Cc. r natural join s, r * s. run 1c.sql 1 select r.A, r.B

2、, r.C, s.D, s.E A B C D E - - - - - a2 b2 c2 d2 e2d. r semi-join s, r |X s. run 1d.sql 1 select * 3 where exists 4 （select * 5 from s 6* where r.C = s.C） A B C - - - a2 b2 c2e. s semi-join r, s |X r. run 1e.sql 3 where exists 5 from r 6* where s.C = r.C） C D E c2 d2 e2f. r left outer join s. run 1f.

3、sql 3* where r.C = s.C（+） a3 b3 c3g. r right outer join s. run 1g.sql 2 from r, s 3* where r.C（+） = s.C c1 d1 e1h. s left outer join r. run 1h.sql 3* where s.C = r.C（+）i. s right outer join r. run 1i.sql 3* where s.C（+） = r.Cj. r full outer join s. run 1j.sql 3 where r.C = s.C（+） 4 union 5 select *

4、6 from r, s 7* where r.C（+） = s.CB. Given the following table T and the set of functional dependencies F:TABCDa1b1c1d1c2d2a2d3c3d4F = A B, C B, D ABC, AC Da. What is the key for table T? （5 points） D & AC Test A, C, D, & AC to be keys: A+ = A = AB A B Therefore A is not a key C+ = C = CB C B Therefo

5、re C is not a key D+ = D = DABC D ABC Therefore D is a key AC+ = AC = ACB A B = ACBD AC D Therefore AC is a keyb. What is the highest normal form for table T? Please explain. （10 points） Table T is of 1st normal form for the following reasons: 1. It is of 1st normal form because it has no nonatomic

6、attributes or nested relations. 2. It is NOT of 2nd normal form because every nonprime attribute （B in this case） is not fully dependent on the key （AC）.c. Given the following records or rows: （20 points） （a5, b6, c7, d8） （a2, b2, c1, d8） （a3, b1, c4, d3） （a1, b1, c2, d5） Please indicate which recor

7、d can be added into table T without violating any of functional dependencies in F. If the record or row can not be legally added, please indicate which functional dependency is violated. （a2, b2, c1, d8） violates F = A B, C B, AC D （a3, b1, c4, d3） violates F = D ABC （a1, b1, c2, d5） violates F = AC

8、 DC. Answer the following multiple choice questions: （60 points） （please select the most appropriate one）1. _a_ is an index specified on the ordering key field of an ordered file of records. a.） Primary index b.） Clustering index c.） Secondary index d.） None of the above2. _C_ is an index specified

9、on the nonkey ordering field of an ordered file of records. a.） Primary index b.） Secondary c.） Clustering index d.） None of the above3. _b_ is an index specified on the key or nonkey nonordering field of a file of c.） Clustering index d.） None of the above4. In _a_, the number of （first-level） inde

10、x entries is equal to the number of data blocks in data file. c.） Clustering index d.） None of the above5. In _c_, the number of （first-level） index entries is equal to the number of distinct index values in data file. a.） Primary index b.） Secondary6. In _b_, the number of （first-level） index entri

11、es is equal to the number of records in data file.7. _b_ is/are the dense index（es）. c.） Clustering index d.） Both b.） and c.）8. _d_ is/are the nondense index（es）. c.） Clustering index d.） Both a.） and c.）9. _a_（see note）_ use（s） block anchoring on the data file. a.） Primary index b.） SecondaryNOTE:

12、 Clustering index can also use block anchoring if every distinct value of the ordering field starts a new block; otherwise it does not. The answer given above assumes “always use（s）”. If this is not assumed, then the answer is d.） Both a & c10. _a_ uses the least number of blocks to hold necessary i

13、ndex entries （Assume the number of records is very big）. a.） Primary index b.） Secondary index c.） Clustering index d.） Multilevel index11. _d_ has the least number of block accesses to search and retrieve a record （Assume the number of records is very big）. a.） Primary index b.） Secondary index12.

14、_d_ guarantee（s） the access structure is always balanced when alot of updates such as insertions and deletions of file records occur . a.） M-Way Search tree b.） Dynamic hashing c.） Multilevel index d.） B-tree and B+-tree13. _c_ guarantee（s） that the number of index block accesses is equal to the lev

15、el of the index structures. c.） Multilevel index d.） B-tree and B+-tree14. _d_ operation（s） on relation tables is/are materialization or implementation of the relationships of conceptual schema database in entity-relationship diagram or model. a.） Select operation b.） Cartesian product c.） Projectio

16、n d.） Natural join15. A key in a database table is a a. ） minimal superkey b.） a proper superset of a candidate key. c.） proper subset of a candidate key d.） neither a.） nor b.）16. A foreign key can make reference to c a.） an attribute that is a primary key of another table. b.） an attribute that is

17、 a primary key of the same table. c.） both a.） and b.） d.） neither a.） nor b.）.17. A referential integrity constraint states c a.） a foreign key attribute value must refer to an existing primary key value. b.） a foreign key attribute value can be NULL. c.） both a.） and b.）.18. The higher normalized

18、database schema implies d a.） more decomposed database tables. b.） more join operations when data from these decomposed tables are combined. c.） slower processing for queries involved the joining these decomposed tables. d.） all of a.）, b.）, and c.）.19. The lossy-join means d a.） loss of correct tup

19、les during the join operation. b.） loss of spurious tuples during the join operation. c.） loss of join path. d.） loss of correct information.20. If a database table is in the first normal form, it is also in a a.） the non-first normal form. b.） the second normal form. c.） the third normal form. d.）

20、the Boyce-Codd normal form.21. If a database table is in Boyce-Codd normal form, it is also in d a.） the first normal form. b.） the second normal form. c.） the third normal form. d.） all of a.）, b.）, and c.）.22. If a database table is in the third normal form, it is also in d c.） the Boyce-Codd norm

21、al form. d.） both a.） and b.）23. If a database table is in the second normal form, it is also in a a.） the first normal form. b.） the third normal form. c.） the Boyce-Codd normal form. d.） all of a.）, b.）, and c.）.24. The lossless join means c a.） no correct tuple lost. b.） no spurious tuple lost. c

22、.） no correct information lost. d.） no join path lost.25. The higher normalized database schema implies d a.） less insertion anomalies. b.） less deletion anomalies. c.） less update anomalies. d.） all of a.）, b.）, and c.）.26. If there is a one-to-one relationship between entity A and B, then c a.） th

23、ere exists a functional dependency from the primary key in B to the primary key in A, i.e., PK（B）PK（A）. b.） there exists a functional dependency from the primary key in A to the primary key in B, i.e., PK（A）PK（B）.27. If there is a one-to-many relationship from entity A to B （A is on one-side, and B is on many-side）, then a28. If there is a many-to-many relationship between entity A and B, then d a.） there exists a functional dependency from the primary key in B to the prima