CS 4400 SAMPLE QUIZ 4 4th ed. Chapters 10, 11 (11.1 and 11.2), 13 (13.1-13.8.2) and 14 (14.1-14.4.1) 1. Desirable properties of relational normalization include (a) minimizing insertion/deletion anomalies (b) minimizing redundancy (c) minimizing update anomalies (d) all of the above 2. A relation schema R is in 3rd normal form if (a) each nonprime attribute in R is fully dependent on every key (b) all attributes in R have atomic domains (c) R satisfies 2nd normal form and no nonprime attribute of R is transitively dependent on the primary key (d) R contains only 3 keys 3. If a relation R is decomposed into {R1, R2, ..., Rn} and the decomposition is lossless then (a) the natural join of R1, R2, ..., Rn will have the same number of tuples as the original relation for R (b) the natural join of R1, R2, ..., Rn can have more tuples than the original relation for R (c) the relations R1, R2, ...,Rn are each in 3rd normal form (d) none of the above 4. If the following functional dependencies, {A -> B, B -> C} hold for database schema R(A,B) and S(B,C), then the join of R and S will be (a) lossy (b) lossless (c) non lossless (d) none of the above 5. If we use the algorithm for producing a Dependency Preserving and Lossless Join Decomposition into 3rd normal form with the relation schema of R(A,B,C) and the set of Fds as {AB -> C, B -> A}, then the the algorithm would produce as output (a) a relation schema with (B,C) and a relation schema with (A,B) (b) a relation schema with (A,B,C) (c) a relation schema with (A,B,C) and a relation schema with (A,B) (d) a relation schema with (A), a relation schema with (B) and a relation schema with (C) 6. If we use the algorithm for producing a Lossless Join Decomposition into Boyce-Codd normal form with the relation schema of R(A,B,C) and the set of Fds as {AB -> C, C -> A}, then the the algorithm would produce as output (a) a relation schema with (C,A) and a relation schema with (C,B) (b) a relation schema with (A,B,C) (c) a relation schema with (C,A) and a relation schema with (A,B) (d) a relation schema with (A), a relation schema with (B) and a relation schema with (C) 7. We have the set of Fds, {B -> C, C -> A, B -> D}, for the relation schema R(A,B,C,D). Which of the following decompositions has the dependency preserving property? (a) a decomposition with relation schemas (C,A) and (C,B,D) (b) a decomposition with relation schemas (A,C,D) and (B,D) (c) a decomposition with relation schemas (C,A) and (A,B,D) (d) all of the above 8. Given the following schema R(Emp#,Dept#,City) with functional dependencies, F = {Emp# -> City, Emp# -> Dept#, Dept# -> City, Emp#,Dept# -> City}, which set of functions dependencies is a minimal cover for F? (a) {Emp#Dept# -> City, Emp# -> Dept#} (b) {Emp# -> City, Emp# -> Dept#} (c) {Emp# -> City, Dept# -> City} (d) {Emp# -> Dept#, Dept# -> City} 9. Which of the following functional dependencies are redundant: {A -> B; AD -> C; DB -> E; B -> C} for the relation R(A,B,C,D,E). (a) B -> C (b) A -> B (c) AD -> C (d) DB -> E 10. Which of the attributes are extraneous in the functional dependency, ABC -> D, considering the relational schema R(A,B,C,D,E,G) and FDs {B -> E; C -> G; EG -> D }. (a) A (b) B (c) C (d) none of the above 11. Which nonkey attribute(s) cause a violation of 2NF, based on the primary key definition where the primary key is SID,CID for the relation R(SID,CID,CNAME,SNAME,GRADE) with FDs: {SID -> SNAME; CID -> CNAME; SID,CID -> GRADE}. (a) SNAME (b) CNAME (c) GRADE (d) both (a) and (b) 12. We have the set of Fds, {Snumb,Pnumb -> Qty; Snumb -> City; City -> Status}, for the relation schema SupPart(Snumb,Pnumb,City,Status,Qty). Which of the following is the closure with respect to Snumb? (a) {Snumb} (b) {Snumb,City} (c) {Snumb,City,Status} (d) {Snumb,Pnumb,Qty,City,Status} 13. We have the set of Fds, F = {Dnumber -> Dname; Dnumber -> MgrSSN} for the relational schema Department(Dnumber,Dname,MgrSSN,Dlocation). Which of the following is a key for Department? (a) Dnumber (b) Dnumber,Dlocation (c) Dlocation (d) none of the above 14. We have the set of Fds, F = {Dnumber -> Dname; Dnumber -> MgrSSN} for the relational schema Department(Dnumber,Dname,MgrSSN,Dlocation). Which of the following is a superkey for Department? (a) Dnumber,Dlocation (b) Dnumber,Dlocation,Dname (c) Dnumber,Dlocation,MgrSSN (d) all of the above 15. Given the relation schema Bank(BankID,AccountNumb,Balance,Customer) with FDs: {BankID,AccountNumb -> Balance; BankID,AccountNumb -> Customer; Customer -> BankID}. What is the highest normal form for the relation schema Bank? (a) first (b) second (c) third (d) Boyce Codd 16. We have the set of Fds, F = {A,B -> C; C -> D; A,B -> D, B-> A} for the relation schema Sample(A,B,C,D). Which of the following set of Fds is equivalent to F? (a) {A,B -> C; A,B -> D} (b) {C -> D; B -> A} (c) {B -> C; C -> D; B -> A} (d) {A,B -> C; C -> D; A,B -> D} 17. An index is used in relational database systems (a) to improve the efficiency of normalizing relations (b) to improve the efficiency of retrieving tuples from a relation (c) to improve the efficiency of the Create Table statement (d) none of the above 18. Tracks on a disk are (a) the smallest unit of access for reading/writing (b) concentric circles on a platter, which are comprised of sectors (c) composed of a number of cylinders (d) non magnetized areas on a disk's surface 19. Consider a disk with the following characteristics: 8,192 cylinders, a block size of 4096 bytes, an average rotational latency of 5ms, an average seek time of 7ms, a block transfer time of 0.5ms. How much time would it take to bring in 100 blocks that are randomly stored on disk into the main memory buffer area? (a) 1250ms (b) 62ms (c) 57ms (d) 12.5ms 20. The difference between a dense index and a sparse index is that (a) a dense index contains keys and pointers for a subset of the records whereas a sparse index contains keys and pointers for every record (b) a dense index can only be a primary index whereas a sparse index can only be a secondary index (c) a dense index contains keys and pointers for each record whereas a sparse index contains keys and pointers for a subset of the records (d) no difference 21. The difference between files storing spanned versus unspanned records is that (a) a file with spanned records will use less disk space for storing records than with unspanned records, if an integral number of records do not fit in a block (b) a file with spanned records can have records that are stored on more than one disk block (c) a file with spanned records must be used when the size of a record is larger than the block size (d) all of the above 22. Which of the following represent possible index combinations for a file? (a) one primary index and one or more secondary indexes (b) one primary index and one clustered index (c) one primary index, one clustered index and one secondary index (d) all of the above 23. Which of the following represents a correct B tree organization of order 5? (a) ------------------------ || 9 || 17 || || || / ------\----------------- / \ / \ / \ ------------------------ ------------------------ || 2 || 5 || 7 || || || 12 || 15 || 16 || || ------------------------ ------------------------ (b) ------------ || 15 || 22 || / ------|----- \ / | \ / | \ ------------ ------------ ------------ || 9 || 12 || || 17 || || || 51 || || ------------ ------------ ------------ (c) ------------ || 12 || 17 || / ------|----- \ / | \ / | \ ------------ ------------ ------------ || 9 || || || 15 || || || 22 || 51 || ------------ ------------ ------------ (d) None of the above 24. For a B+ tree of order 10, consisting of 3 levels, the maximum number of leaf nodes would be (a) 121 (b) 100 (c) 1000 (d) 36 25. The insertion of a record in a B+ tree will always cause the height of the tree to increase by one when (a) the tree consists of only a root node (b) the record is to be inserted into a full leaf node (c) all the nodes in the path from the root to the desired leaf node are full before insertion (d) all the nodes in the B+ tree are half full ANSWERS ************************************************************ 1. d 2. c 3. a 4. b 5. b 6. a 7. a 8. d 9. c 10. a 11. d 12. c 13. b 14. d 15. c 16. c 17. b 18. b 19. a 20. c 21. d 22. a 23. d 24. b 25. c ********************************************************************