Download Chapter 04 Review Questions (Word)... Show Database Processing, Ninth Edition CHAPTER 4 The Relational Model and Normalization True-False Questions 1. A relation is a three-dimensional table. Answer: False Page: 120 Level: moderate 2. A characteristic of a relation is that the cells of the relation hold a single value. Answer: True Level: easy Page: 120 3. A characteristic of a relation is that the rows of a relation may hold identical values. Answer: False Level: moderate Page: 120 4. The columns of a relation are sometimes called “tuples.” Answer: False Level: easy Page: 122 5. Keys are always unique. Answer: False Page: 122 Level: easy 6. A tuple is a group of one or more columns that uniquely identifies a row. Answer: False Level: easy Page: 122 7. A row can be uniquely identified by a key. Answer: True Page: 122 Level: easy 8. A key can be composed of a group of attributes taken together. Answer: True Level: easy Page: 122 9. It is possible to have a relation that does not have a key. Answer: False Level: hard Page: 123 10. Attribute Y is functionally dependent on attribute X if the value of attribute X determines the value of Y. Answer: True Level: moderate Page: 123 4-1 Chapter 4 - The Relational Model and Normalization 11. The functional dependency noted as A B, means that the value of A can be determined from the value of B. Answer: False Level: easy Page: 123 12. In the functional dependency shown as A B, B is the determinant. Answer: False Level: moderate Page: 123 13. Functional dependencies can involve groups of attributes. Answer: True Level: easy Page: 124 14. A determinant of a functional dependency may or may not be unique in a relation. Answer: True Level: moderate Page: 125 15. A primary key functionally determines an entire row. Answer: True Page: 123 Level: hard 16. If a table meets the minimum definition of a relation, it has an effective or appropriate structure. Answer: False Level: moderate Page: 125 17. Undesirable consequences of changing the data in a relation are called “modification anomalies.” Answer: True Level: moderate Page: 125 18. A deletion anomaly exists when deleting data about one entity results in the loss of data about another entity. Answer: True Level: easy Page: 125 19. A constraint, based on a business rule, that requires an instance of an entity to exist in one relation before it can be referenced in another relation is called an insertion anomaly. Answer: False Level: hard Page: 126-127 20. A referential integrity constraint is based on a business rule. Answer: True Level: moderate Page: 127 21. The essence of normalization is taking a relation that has more than one theme and breaking it into multiple relations that each have only one theme. Answer: True Level: easy Page: 127 4-2 Chapter 4 - The Relational Model and Normalization 22. Breaking a relation into two or more relations may create the need for a referential integrity constraint to be defined. Answer: True Level: moderate Page: 127 23. Relations are classified into “normal forms” based on the types of modification anomalies that they are vulnerable to. Answer: True Level: hard Page: 127 24. The only normal form that guarantees that a relation is free from every possible type of anomaly is 5NF. Answer: False Level: hard Page: 127-128 25. Any table that meets the definition of a relation is in 2NF. Answer: False Level: easy Page: 128 26. A relation is in first normal form if all of its non-key attributes are dependent on part of the key. Answer: False Level: moderate Page: 128 27. To be in second normal form, a relation must contain no partial dependencies. Answer: True Level: moderate Page: 128 28. A relation is in second normal form if it is in first normal form and all of its non-key attributes are dependent on the entire key. Answer: True Level: moderate Page: 128 29. Relations that have a composite key and are in 1NF are automatically in 2NF. Answer: False Level: easy Page: 128 30. A transitive dependency exists when a non-key attribute is determined by only part of the key. Answer: False Level: moderate Page: 129 31. A relation is in 3NF if it is in 2NF and contains no transitive dependencies. Answer: True Level: easy Page: 129 32. A relation can have only one candidate key. Answer: False Page: 130 4-3 Level: moderate Chapter 4 - The Relational Model and Normalization 33. A relation is in Boyce-Codd Normal Form if it is in 3NF and every determinant is a candidate key. Answer: True Level: easy Page: 131 34. A multi-value dependency exists when a relation has at least three attributes, two of which are multi-value and their values depend only on the third attribute. Answer: True Level: hard Page: 132 35. A relation is in 4NF if it is in 3NF and contains no multi-value dependencies. Answer: True Level: moderate Page: 132 36. Fifth normal form deals with obscure problems with transitive dependencies. Answer: False Level: easy Page: 133 37. A relation is in DK/NF if every domain is the logical consequence of the definition of constraints and keys. Answer: False Level: hard Page: 133 38. In considering DK/NF, a constraint includes any rule governing static values in the relation except constraints that are time-based. Answer: True Level: moderate Page: 133 39. There is no known algorithm for converting a relation into DK/NF. Answer: True Level: moderate Page: 134 40. If A B and B A, then A and B have a many-to-many attribute relationship. Answer: False Level: moderate Page: 138 41. Attributes that have a one-to-one attribute relationship must occur together in at least one relation. Answer: True Level: moderate Page: 138 42. It is generally undesirable to have attributes that have a one-to-one attribute relationship appear in more than one relation together. Answer: True Level: hard Page: 139 43. In general, a row of a relation should have all of the data about one instance of the relation’s theme. Answer: True Level: moderate Page: 141 4-4 Chapter 4 - The Relational Model and Normalization 44. De-normalization is the process of constructing relations by synthesis of attribute relationships. Answer: False Level: moderate Page: 141 45. Relations may be unnormalized or de-normalized to improve database performance. Answer: True Level: easy Page: 143-144 Multiple Choice Questions 46. A relation is also known as a.) a table b.) a tuple c.) a relationship d.) an attribute e.) a field . Level: easy Page: 122 47. A tuple is also known as a a.) table b.) relation c.) row d.) field e.) file . Level: moderate Page: 122 48. An attribute is also known as a a.) table b.) relation c.) row d.) field e.) file . Level: moderate Page: 122 4-5 Chapter 4 - The Relational Model and Normalization 49. Which of the following, if any, is not true about keys? a.) can be a single attribute b.) can be a group of attributes taken together c.) can uniquely identify a row d.) must be functionally determined by another attribute e.) All of the above are true. Level: moderate Page: 123-125 50. A functional dependency in a relation a.) usually involves a formula b.) always involves a primary key c.) exists among tuples d.) must always be removed through normalization e.) none of the above . Level: moderate Page: 123 51. Which of the following is known to be true from the functional dependency shown as (A, B) (C, D)? a.) A is the determinant of C b.) A and B together are determined by C and D together c.) A and B together determine D d.) C and D together determine A e.) A determines B Level: moderate Page: 125 52. Which of the following is known to be true from the functional dependency shown as A (X, Y)? a.) X is functionally dependent on A b.) A determines Y c.) A is a determinant d.) X and Y are functionally dependent on A e.) All of the above. Level: moderate Page: 123-125 4-6 Chapter 4 - The Relational Model and Normalization 53. If the removal of facts about one entity results in the unintentional lose of data about another entity, this is referred to an a(n) ____________. a.) normalization anomaly b.) insertion anomaly c.) entity anomaly d.) deletion anomaly e.) removal anomaly Level: moderate Page: 125 54. The notation CUST-CAR[License] CAR-RENT[License] indicates . a.) License in CAR-RENT is a subset of License in CUST-CAR. b.) Before a value can be entered in the License field of the CAR-RENT relation, it must have a matching value in the License field of the CUST-CAR relation. c.) License in CUST-CAR is functionally dependent on License in CAR-RENT. d.) Before a value can be entered in the License field of the CUST-CAR relation, it must have a matching value in the License field of the CAR-RENT relation. e.) CUST-CAR and CAR-RENT are functionally dependent on the License field. Level: hard Page: 126 55. Which of the following is not true about normalization? a.) produces relations with a single theme b.) may create referential integrity constraints c.) reduces the number of functional dependencies in the schema d.) reduces anomalies e.) splits a poorly structured relation into two or more well-structured relations Level: moderate Page: 127 56. A table that meets the definition of a relation is in a.) First Normal Form b.) Second Normal Form c.) Third Normal Form d.) Boyce-Codd Normal Form e.) Fourth Normal Form Level: easy Page: 128 4-7 . Chapter 4 - The Relational Model and Normalization 57. Which of the following is not a requirement for 1NF? a.) cells must contain single values b.) all entries in a column must be of the same kind c.) no two rows may be identical d.) rows must be ordered by the value of the primary key e.) the order of the columns is insignificant Level: moderate Page: 120 58. A relation is in 2NF if it is in 1NF and all its non-key attributes are a.) dependent on part of the primary key b.) dependent on all of the primary key c.) independent of the primary key d.) independent of each other e.) independent of any other relation . Level: moderate Page: 128 59. Which of the following is a requirement of 2NF? a.) must contain a partial dependency b.) must contain a composite primary key c.) must contain no partial dependencies d.) must contain no transitive dependencies e.) must contain a multi-valued dependency Level: moderate Page: 128 60. A relation is in third normal form if it is in second normal form and a.) is dependent on part of the key b.) is dependent on all of the key c.) is independent of the key d.) has no transitive dependencies e.) is independent of any other relation Level: moderate Page: 129 61. Which of the following is a requirement of 3NF? a.) must contain a partial dependency b.) must contain a composite key c.) must contain no partial dependencies d.) must contain no transitive dependencies e.) must contain a multi-valued dependency Level: moderate Page: 129 4-8 . Chapter 4 - The Relational Model and Normalization EMPLOYEE (SSN, SupervisorID, Department) Key: SSN Functional Dependencies: SSN SupervisorID SupervisorID Department SSN 123-45-6789 234-56-7890 345-67-8901 456-78-9012 567-89-0123 678-90-1234 62. SupervisorID 100 200 100 300 100 200 Department Engineering Marketing Engineering Production Engineering Marketing Which type of dependency is present in the EMPLOYEE relation above? a.) partial dependency b.) transitive dependency c.) determinant dependency d.) multi-valued dependency e.) deletion dependency Level: hard Page: 129 63. A relation is in Boyce-Codd normal form if it is in 3NF and a.) every determinant is a candidate key b.) every determinant is a primary key c.) every attribute is a candidate key d.) there is more than one candidate key e.) there is more than one primary key Level: moderate Page: 131 64. A “candidate key” is . a.) a primary key. b.) any group of attributes that are a determinant c.) functionally dependent on the non-key attributes d.) an attribute or group of attributes that can be the primary key e.) the primary key selected to be the key of a relation Level: easy Page: 123 4-9 . Chapter 4 - The Relational Model and Normalization 65. A multi-value dependency exists when a relation has . a.) at least two attributes, both of them are multi-value, and their values depend on each other b.) at least two attributes, one of them is multi-value, and its value depends on the other c.) at least three attributes, two of them are multi-value, and their values depend on only the third attribute d.) at least three attributes, one of them is multi-value, and its value depends on the other two attributes e.) at least three attributes, all of them are multi-value, and their values depend on each other Level: hard Page: 132 66. A relation is in fourth normal form if it is in BCNF and it has no a.) transitive dependencies b.) multi-value dependencies c.) partial dependencies d.) deletion dependencies e.) referential integrity conflicts . Level: easy Page: 132 67. The anomalies addressed by moving from BCNF to 4NF generally deal with a.) excessive updates and redundancy of data for each entity b.) inability to uniquely identify an entity c.) inability to reconstruct relations once they have been decomposed d.) creation of identical rows in a relation e.) functional dependencies Level: hard Page: 132 68. Fifth normal form deals with . a.) excessive updates and redundancy of data for each entity b.) inability to uniquely identify an entity c.) inability to reconstruct relations once they have been decomposed d.) creation of identical rows in a relation e.) dependencies between referential integrity constraints Level: moderate Page: 133 4 - 10 . Chapter 4 - The Relational Model and Normalization 69. The advantage of having a relation in domain/key normal form is that a.) it takes less storage space than other normal forms b.) it is easily obtained from Boyce-Codd normal form c.) there is a relatively simple algorithm for obtaining DK/NF d.) it is obtained by enforcing referential integrity constraints e.) it is guaranteed to have no modification anomalies . Level: easy Page: 133 70. A relation is in domain/key normal form if . a.) every key of the relation is a logical consequence of the definition of constraints and determinants b.) every key of the relation is a logical consequence of the definition of constraints and domains c.) every constraint on the relation is a logical consequence of the definition of keys and determinants d.) every constraint on the relation is a logical consequence of the definition of keys and domains e.) every domain of the relation is a logical consequence of the definition of keys and constraints Level: moderate Page: 133 71. In the definition of DK/NF, which of the following is a type of constraint that is excluded from consideration? a.) edit rules b.) intra-relation constraints c.) functional dependencies d.) time-based constraints e.) inter-relation constraints Level: moderate Page: 133 72. Synthesizing relations means that the developers start with a a.) relation containing functional dependencies b.) relation without any functional dependencies c.) set of attributes with certain functional dependencies d.) set of domains and their constraints e.) set of anomalies based on dependencies Level: moderate Page: 138 4 - 11 . Chapter 4 - The Relational Model and Normalization 73. If two attributes A and B have a one-to-one attribute relationship, it is shown as a.) b.) c.) d.) e.) . A B and B A A B, but B not A B A, but A not B A not B and B not A AB Level: moderate Page: 138 74. If two attributes A and B have a many-to-one attribute relationship, it is shown as a.) A B and B A b.) A B, but B not A c.) B A, and A B d.) A not B and B not A e.) AB . Level: moderate Page: 138 75. If two attributes A and B have a many-to-many attribute relationship, it is shown as a.) A B and B A b.) A B, but B not A c.) B A, and A B d.) A not B and B not A e.) AB Level: moderate Page: 138 76. If two attributes A and B have a one-to-one attribute relationship in the relation R1(A,B,C), then the primary key is . a.) A b.) B c.) A or B d.) A and B e.) C Level: easy Page: 139 4 - 12 . Chapter 4 - The Relational Model and Normalization 77. If two attributes A and B have a many-to-one attribute relationship in the relation R2(A,B,C), then the primary key is . a.) A b.) B c.) A or B d.) A and B e.) C Level: easy Page: 139 78. When creating a database with attributes that have a one-to-one attribute relationship ________ . a.) the two attributes must appear in a relation together b.) the two attributes usually only appear in one relation together c.) other attributes that are functionally dependent on either of them may appear in a relation with them d.) either of the attributes can appear in relations without the other e.) all of the above Level: moderate Page: 139-140 79. If two attributes A and B have a many-to-many attribute relationship in the relation R3(A,B,C), then the primary key is . a.) A b.) B c.) A or B d.) A and B e.) C Level: easy Page: 139 80. If two attributes G and H have a many-to-one relationship exist in a relation R4(G, H, J) where J is a third attribute appearing in the relation, then . a.) G H and G J b.) G H, but H J c.) (G, H) J d.) J (G, H) e.) J G, and J H Level: moderate Page: 139 4 - 13 Chapter 4 - The Relational Model and Normalization 81. If two attributes A and B have a many-to-many attribute relationship, it is shown as a.) A B and B A b.) A B, but B not A c.) B A, and A B d.) A not B and B not A e.) AB Level: moderate Page: 139 82. When a relation has a key consisting of multiple attributes, you can add a new attribute to the relation ______________. a.) without any restriction b.) so long as it is functionally dependent on part of the key c.) so long as it is functionally dependent on all of the key d.) so long as it is functionally dependent on a non-key attribute e.) so long as it is a candidate key Level: moderate Page: 140 83. If two attributes A and B that have a many-to-many attribute relationship, and a third attribute C also appears in the relation expressing the many-to-many relationship, then ______________. a.) A B and B C b.) B A, but A C c.) (A, B) C d.) C (A, B) e.) C A, and C B Level: moderate Page: 139 84. In general, a row in a relation should have all of the data about ___________. a.) one instance of the relation's theme b.) each instance of the relation's theme c.) every instance of the relation's theme d.) no instance of the relation's theme e.) the relation's functional dependencies Level: easy Page: 141 4 - 14 . Chapter 4 - The Relational Model and Normalization 85. Database performance can be improved through appropriate use of ____________. a.) uncontrolled redundancy b.) de-normalization c.) data integrity violations d.) synchronous dependencies e.) both a and b Level: easy Page: 143-144 Fill in the Blank Questions 86. A(n) relation is a two-dimensional table. Level: easy Page: 120 87. In relational terms, a row is called a(n) Level: easy Page: 122 tuple . 88. In relational terms, a column is called a(n) Level: easy Page: 122 89. A(n) composite key Level: easy Page: 122 attribute . is a group of attributes that uniquely identifies a row. 90. A(n) candidate key is one of a group of keys that may serve as the primary key. Level: easy Page: 123 91. A(n) Level: easy Page: 123 primary key is a group of one or more attributes that uniquely identifies a row. 92. A key functionally determines the entire Level: easy Page: 123 row . 93. A(n) functional dependency is a relationship between attributes such that if we know the value of one attribute, we can determine the value of the other attribute. Level: easy Page: 123 4 - 15 Chapter 4 - The Relational Model and Normalization 94. If by knowing the value of A, we can find the value of B, then we would say that B is functionally dependent on A. Level: moderate Page: 123 95. In functional dependencies, the attribute whose value is known or given is referred to as the determinant . Level: moderate Page: 123 96. Unlike determinants, primary keys are always unique. Level: easy Page: 125 97. For some relations, changing the data can have undesirable consequences called modification anomalies . Level: moderate Page: 125 98. Every time we break up a relation during the normalization process, we may create referential integrity constraints. Level: moderate Page: 127 99. Normal forms are nested so that if a relation is in 2NF, it is also in 1NF. Level: easy Page: 127 100. A relation that is in domain / key normal form is assured to be free from all anomalies. Level: moderate Page: 128 101. Any table that meets the definition of a(n) Level: easy Page: 128 102. If a table is a relation then it is in Level: easy Page: 128 relation is said to be in first normal form. 1NF . 103. To be in 1NF, the cells of a table must contain a(n) Level: hard Page: 120 104. single value. A relation is in 2NF if it is in 1NF and all of its non-key attributes are dependent on all of the key . Level: moderate Page: 128 4 - 16 Chapter 4 - The Relational Model and Normalization 105. A relation is in second normal form if all its non-key attributes are dependent on the entire key. Level: easy Page: 128 106. A relation is in 2NF if it is in 1NF and has no partial dependencies . Level: easy Page: 128 107. A relation is in third normal form if it is in second normal form and has no transitive dependencies . Level: moderate Page: 129 108. A defining requirement for candidate key. Level: moderate Page: 131 Boyce-Codd normal form is that every determinant must be a 109. A relation is in BCNF if it is in 3NF and every Level: moderate Page: 131 determinant is a candidate key. 110. A relation is in BCNF if it is in 3NF and every determinant is a(n) Level: moderate Page: 131 candidate key . 111. Relations in BCNF have no anomalies in regard to functional dependencies . Level: hard Page: 131 112. A(n) multi-value dependency exists when a relation has at least three attributes, two of them are multi-value, and their values depend on only the third attribute. Level: easy Page: 132 113. A relation is in 4NF if it is in BCNF and it has no Level: moderate Page: 132 multi-value dependencies . 114. Fifth normal form deals with obscure dependencies that may not have any practical consequences. Level: moderate Page: 133 115. Domain / key normal form requires that every definition of domains and keys. Level: moderate Page: 133 4 - 17 constraint be a logical consequence of the Chapter 4 - The Relational Model and Normalization 116. In regard to DK/NF, a constraint is broadly defined to include all types of constraints except time-based constraints. Level: hard Page: 133 If the functional dependencies between two attributes is A B and B A, then they have a(n) one-to-one attribute relationship. Level: easy Page: 138 117. If the functional dependencies between two attributes is A B, but B not A, then they have a(n) many-to-one attribute relationship. Level: moderate Page: 138 118. If the functional dependencies between two attributes is A not B and B not A, then they have a(n) many-to-many attribute relationship. Level: moderate Page: 138 119. When synthesizing relations, if two attributes have the relationship A B and B A, then create a relation with A or B as the key. Level: easy Page: 139 120. When synthesizing relations, if two attributes have the relationship A B, but not B A, then create a relation with A as the key. Level: easy Page: 139 121. When synthesizing relations, if attributes have the dependencies (A, B) C, then create a relation with (A, B) as the key. Level: moderate Page: 139 122. 123. Relations are sometimes left unnormalized to improve Level: easy Page: 141 performance . Essay Questions 124. Describe the requirements that a table must meet to qualify as a relation. In order for a table to meet the requirements of a relation, it must satisfy certain conditions. First, the rows contain data about an entity or some portion of the entity. Second, the columns contain data that represent attributes of the entity – each column must contain the same kind of 4 - 18 Chapter 4 - The Relational Model and Normalization data for all rows – and each column must have a unique name. Third, the cells must contain single values – no group or array values are allowed. Finally, the rows must be unique. It is worth noting that the order of the columns and the rows is insignificant. 125. Explain the relationships among primary keys, functional dependencies, and uniqueness. All primary keys represent a functional dependency, since each primary key functionally determines the entire row. Since the primary key determines the entire row, if a primary key were duplicated in a relation, then the entire row would have to be duplicated. Since a relation can contain no duplicate rows, the primary key must be unique. However, all functional dependencies are not represented through primary keys. Therefore, the determinant of a functional dependency may or may not be unique. 126. Explain the essence of normalization that is implemented through the use of normal forms. The essence of normalization is to create relations that contain data on a single theme, and for each row in a relation to contain all the data about that theme for a given entity. This essence is implemented in the normal forms by taking a relation that contains data on more than one theme and splitting it into multiple relations that each contain data on a single theme. 127. Explain the concept of a transitive dependency and give an example not included in the book. A transitive dependency is evidenced through a functional dependency among non-key attributes. If the key of a relation determines the value of one attribute because it can determine the value of another attribute, then a transitive dependency exists. For example, if an employee is assigned a computer to work on and the computer has an assigned IP address for the corporate network, the relation EMPLOYEE (EmployeeID, ComputerID, IPaddress) with EmployeeID as the key, contains a transitive dependency. The IP address is not really determined by the EmployeeID, but rather by the ComputerID, such that EmployeeID determines IPaddress only because EmployeeID determines ComputerID which in turn determines IPaddress. The functional dependencies can be shown asEmployeeID ComputerID ComputerID IPaddress Therefore EmployeeID ComputerID Ipaddress 128. Explain the practical value that DK/NF has in the real world, and why that practical value is limited. Domain key normal form has practice value as a database design objective. Since a relation in domain key normal form is guaranteed to contain no anomalies, it is the preferred normalization goal. Many relations can achieve DK/NF while others cannot. The fact that many relations cannot be placed in DK/NF, and there is no method for determining which can and which cannot, limits the practical value of DK/NF as a design goal. Further, there is no algorithm for achieving DK/NF even for relations that can reach this ultimate level of normalization. 129. Contrast the analytic and synthetic perspectives of relational design. The analytic perspective of relational design starts from a relation and attempts to identify the anomalies to which it is vulnerable in order to determine which normal form it is in. The synthetic perspective starts with a collection of attributes and their functional dependencies. It 4 - 19 Chapter 4 - The Relational Model and Normalization then attempts to determine which relations should be formed from these attributes based on their attribute relationships. 130. Explain what it means to de-normalize, and why it may be appropriate. De-normalization is the process of taking a normalized set of relations and converting them into a smaller set of relations that is in a lower normal form and thus vulnerable to a greater number of anomalies. While the fully normalized set of relations is more desirable from a theoretical perspective, it does have some disadvantages. The greater the number of relations in the database, the more work that is required by the database whenever data must be retrieved from multiple relations to reconstruct the users’ view of the data. In some circumstances, the anomalies introduced by de-normalizing a data model may be considered an acceptable cost for the performance improvement from reducing the overhead work performed by the database. 4 - 20 What is the requirement for a table to be qualified as a relation?Requirements for a table to qualify as a relation: It must have a unique name. Every attribute value must be atomic (not multivalued, not composite). values for all their fields).
Which of the following is the correct technique for representing an M relationship using the relational model?Which of the following is the correct technique for representing a M:N relationship using the relational model? An intersection relation is created, and the keys of both parent entities are placed as a composite key in the intersection relation.
What would be introduced as criteria of a table being in first normal form?A table is in first normal form (1NF) if and only if all columns contain only atomic values—that is, each column can have only one value for each row in the table.
When an entity has a relationship to itself?It is possible for an entity to have a relationship with itself; for example, an entity Staff could have a relationship with itself, as one member of staff could supervise other staff. This is known as a recursive or involute relationship, and would be represented in an entity-relationship diagram as shown below.
|
Which normal form is defined as any table meeting the definition of a relation?
zusammenhängende Posts
Urheberrechte © © 2024 de.apacode Inc.
