Chapter 5 Database Design

CHAPTER 5 Database Design True-False Questions 1. The first step in the database design process is to create tables and columns from entities and attributes Answer: True Level: moderate Page: 151 [See Figure 5-1] 2. When creating a relational database design from E-R diagrams, first create a relation for each relationship. Answer: False Level: hard Page: 151 [See Figure 5-1] 3. Each entity in the extended E-R model is represented as a table in the relational database design. Answer: True Level: easy Page: 151 4. An entity needs to be examined according to normalization criteria before creating a table from it in the relational database design. Answer: False Level: moderate Page: 151-152 [See Figure 5-1] 5. When creating a table in the relational database design from an entity in the extended E-R model, the attributes of the entity become the rows of the table. Answer: False Level: easy Page: 151 6. One of the important properties of an attribute is whether or not it is required. Answer: True Level: moderate Page: 152 7. Whether or not an attribute is required is determined during the implementation phase. Answer: False Level: easy Page: 152 8. By default, the identifier of the entity becomes the foreign key of the corresponding table. Answer: False Level: easy Page: 151 9. The ideal primary key is short, numeric, and seldom changing. Answer: True Level: moderate Page: 153 5-1 Chapter 5 - Database Design 10. In IDEF1X terminology, candidate keys are called alternate keys. Answer: True Level: easy Page: 153 11. A surrogate key is appropriate when the primary key of a table contains a lengthy text field. Answer: True Level: easy Page: 154 12. Surrogate keys usually slow performance but are better representations of the users’ model. Answer: False Level: moderate Page: 154 13. Surrogate keys are normally not shown on forms or reports. Answer: True Level: easy Page: 154 14. In a relational database design, all relationships are expressed by creating a foreign key. Answer: True Level: easy Page: 156 15. Whenever we create a foreign key, we also create a referential integrity constraint. Answer: True Level: moderate Page: 156 16. Referential integrity constraints are not applied to insert actions. Answer: False Level: moderate Page: 156 [See Figure 5-6] and 129 17. By default, referential integrity constraints allows adding a new row to a child table even when the foreign key does not match a primary key values in the parent table. Answer: False Level: moderate Page: 156 [See Figure 5-6] and 129 18. When the key of one table is placed into a second table to represent a relationship, the key is called a “relational key” in the second table. Answer: False Level: moderate Page: 156 19 A foreign key is a key that does not belong in any table. Answer: False Level: moderate Page: 156 20. A referential integrity constraint policy that insures that foreign key values in a table are correctly maintained when there is a change to the primary key value in the parent table is called cascading updates. Answer: True Level: easy Page: 157 5-2 Chapter 5 - Database Design 21. A referential integrity constraint policy that insures that all rows containing a particular foreign key value in a table are eliminated from the table when the row containing the corresponding primary key value in a parent table is eliminated from the database is called incremental deletes. Answer: False Level: easy Page: 157 22. A referential integrity constraint policy that guarantees that a row in a parent table always has a required entry in a child table is enforced in most DBMS products. Answer: False Level: hard Page: 157 23. When transforming an extended E-R data model into a relational database design, the key of the parent entity should be placed as part of the primary key into the child entity when the child entity is non-ID-dependent. Answer: False Level: moderate Page: 158 24. When transforming an ID-dependent extended E-R data model relationship into a relational database design, the referential integrity constraints should allow parent updates to cascade. Answer: True Level: hard Page: 158 [See Figure 5-6] 25. When transforming an ID-dependent E-R data model relationship into a relational database design, and the parent entity has a surrogate primary key and the child entity has a data identifier, then the primary key of the child table should be the parent's surrogate key plus a surrogate key in the child. Answer: True Level: hard Page: 161 26. When transforming an ID-dependent E-R data model relationship into a relational database design, and the child entity is designed to use a surrogate key, then the relationship remains an ID-dependent relationship Answer: False Level: hard Page: 161 27. To represent a 1:1 binary relationship in a relational database design, the key of one table is placed into the second table. Answer: True Level: easy Page: 162 28. When placing a foreign key for a 1:1 relationship, the key of either table can be used as the foreign key in the other table. Answer: True Level: moderate Page: 162 5-3 Chapter 5 - Database Design 29. In a 1:N relationship, the term “parent” refers to the table on the “many” side of the relationship. Answer: False Level: easy Page: 164 30. In representing a 1:N relationship in a relational database design, the key of the table representing the parent entity is placed as a foreign key in the table representing the child entity. Answer: True Level: moderate Page: 165 31. In representing a 1:N relationship in a relational database design, the key of the table representing the entity on the “many” side is placed as a foreign key in the table representing the entity on the “one” side of the relationship. Answer: False Level: hard Page: 165 32. To represent a 1:N relationship in a relational database design, an intersection table is created. Answer: False Level: moderate Page: 166 33.. To represent a M:N relationship in a relational database design, a table is created to represent the relationship itself. Answer: True Level: moderate Page: 166 34. To represent a M:N relationship in a relational database design, an intersection table is created. Answer: True Level: easy Page: 166 35. The key of an intersection table is always the combination of the keys of both parents. Answer: True Level: moderate Page: 166 36. In many-to-many relationships in a relational database design the minimum cardinality from the intersection table to the parents is always 1. Answer: True Level: hard Page: 167 37. To represent an IS-A relationship in a relational database design, the IS-A relationship must be converted into a HAS-A relationship. Answer: False Level: easy Page: 169 5-4 Chapter 5 - Database Design 38. When transforming supertype / subtype entities into a relational database design, an entity is created for the supertype only. Answer: False Level: easy Page: 171-172 39. When transforming supertype / subtype entities into a relational database design, all of the attributes for the supertype table are placed into the subtype relations. Answer: False Level: hard Page: 171-172 40. When transforming supertype / subtype entities into a relational database design, the key of the supertype table is placed into the subtype table typically as the key. Answer: True Level: moderate Page: 171-172 41. A weak entity that is existence dependent but not ID-dependent can be represented in the relational database design just as a strong entity is, except that constraints noting the existence dependency need to be enforced. Answer: True Level: hard Page: 173 42. A weak entity that is existence dependent and ID-dependent can be represented in the relational database design just as is a strong entity. Answer: False Level: moderate Page: 174 43. For an ID-dependent weak entity, it is necessary to add the key of the parent entity to the weak entity’s table. Answer: True Level: easy Page: 174 44. When transforming an Extended E-R model into a relational database design, recursive relationships are treated fundamentally the same as other HAS-A relationships. Answer: True Level: hard Page: 177 45. Recursive M:N relationships are represented with an intersection table that shows pairs of related rows from a single table. Answer: True Level: hard Page: 178 46. A relationship between two instances of the same entity is called an inclusive relationship. Answer: False Level: easy Page: 177 47. Ternary relationships are handled just like binary relationships except that special considerations may have to be documented as business rules. Answer: True Level: hard Page: 179 5-5 Chapter 5 - Database Design 48. When transforming a ternary relationship into a relational database design, a MUST NOT constraint means the binary relationship indicates all combinations that must appear in the ternary relationship. Answer: False Level: moderate Page: 180 49. A null value is an attribute value that has been set to zero. Answer: False Level: moderate Page: 180 Multiple Choice Questions 50. Which of the following is not a step in the database design process? a.) create tables and columns from entities and attributes b.) select primary keys c.) represent relationships d.) create constraints and triggers e.) All of the above are steps in the database design process. Level: moderate Page: 152 [See Figure 5-1] 51. The first step in transforming an extended E-R model into a relational database design is to __________. a.) create a table for each relationship b.) evaluate the entities against the normalization criteria c.) create a table for each entity d.) remove any recursive relationships e.) document referential integrity constraints Level: easy Page: 151 52. One of the important properties of an attribute is whether or not it is a.) found in more than one entity b.) required c.) character or numeric d.) subject to normalization e.) subject to de-normalization Level: moderate Page: 152 5-6 . Chapter 5 - Database Design 53. Whether or not an attribute is required is determined a.) during the data modeling phase b.) during the database design process c.) during the implementation phase d.) during either a or b e.) during either a, b, or c . Level: moderate Page: 152 54. By default, the identifier of the entity becomes the _____________ of the corresponding table. a.) primary key b.) foreign key c.) supertype d.) subtype e.) either a or b Level: easy Page: 151 55. The ideal primary key is a.) short b.) numeric c.) seldom changing d.) a and b e.) a, b and c . Level: moderate Page: 153 56. In IDEF1X terminology, candidate keys are called __________________. a.) primary keys b.) foreign keys c.) alternate keys d.) unique keys e.) non-unique keys Level: easy Page: 153 57. Which of the following is not true about surrogate keys? a.) They are identifiers that are supplied by the system, not the users. b.) They have no meaning to the users. c.) They tend to slow database performance. d.) They are hidden on forms and reports. e.) The DBMS will not allow their values to be changed. Level: moderate Page: 154 5-7 Chapter 5 - Database Design 58. A surrogate key should be considered when a.) a relationship is M:N b.) a composite key is required c.) the key contains a lengthy text field d.) the key contains a number e.) an index needs to be created . Level: hard Page: 154 59. In a relational database design, all relationships are expressed by a.) creating a primary key b.) creating a foreign key c.) creating a supertype d.) creating a subtype e.) creating a line between entities Level: easy Page: 156 60. Whenever we create a foreign key, we also create a ______________________. a.) primary key constraint b.) foreign key constraint c.) supertype constraint d.) subtype constraint e.) referential integrity constraint Level: moderate Page: 156 61. To which of the following actions are referential integrity constraints not applied? a.) create b.) insert c.) update d.) delete e.) referential integrity constraints are applied to all of the listed actions Level: moderate Page: 157 [See Figure 5-6] 5-8 . Chapter 5 - Database Design 62. By default, referential integrity constraints ___________ adding a new row to a child table when the foreign key does not match a primary key values in the parent table. a.) allow b.) disallow c.) compel d.) is neutral about e.) must be implemented as triggers before Level: moderate Page: 157 [See Figure 5-6] 63. A referential integrity constraint policy that insures that foreign key values in a table are correctly maintained when there is a change to the primary key value in the parent table is called _________________________. a.) incremental updates b.) incremental deletes c.) controlled key adjustments d.) cascading updates e.) cascading deletes Level: easy Page: 157 64. A referential integrity constraint policy that insures that all rows containing a particular foreign key value in a table are eliminated from the table when the row containing the corresponding primary key value in a parent table is eliminated from the database is called _________________________. a.) incremental updates b.) incremental deletes c.) controlled key adjustments d.) cascading updates e.) cascading deletes Level: easy Page: 157 65. A referential integrity constraint policy that guarantees that a row in a parent table always has a required entry in a child table: a.) is known as enforcing minimum cardinality b.) needs to be documented by the database development team c.) is enforced in most DBMS products d.) a and b e.) a, b and c Level: moderate Page: 157 5-9 Chapter 5 - Database Design 66. When transforming an E-R data model into a relational database design, the key of the parent entity should be placed as part of the primary key into the child entity a.) when the child entity is ID-dependent b.) when the child entity is non-ID-dependent c.) when the child entity has a 1:1 relationship with the parent entity d.) when the child entity has a 1:N relationship with the parent entity e.) when the child entity has a recursive relationship with the parent entity . Level: moderate Page: 158 67. When transforming an ID-dependent E-R data model relationship into a relational database design, the referential integrity constraints should allow . a.) parent updates to cascade b.) child updates to cascade c.) child deletes to cascade d.) a and b e.) a, b and c Level: hard Page: 159 [See Figure 5-6] 68. When transforming an ID-dependent E-R data model relationship into a relational database design, and the parent entity has a surrogate primary key and the child entity has a data identifier, then the primary key of the child table should be . a.) the parent's surrogate key + the child's data key b.) the parent's surrogate key + a surrogate key in the child c.) either a or b will work, and neither way is preferable d.) either a or b will work, and method a is preferable e.) either a or b will work, and method b is preferable Level: hard Page: 161 69. When transforming an ID-dependent E-R data model relationship into a relational database design, and the child entity is designed to use a surrogate key, then . a.) the parent entity must also use a surrogate key b.) the relationship remains an ID-dependent relationship c.) the relationship changes to a non-ID-dependent relationship d.) a and b e.) a and c Level: moderate Page: 161 5 - 10 Chapter 5 - Database Design 70. The simplest form of a HAS-A relationship is: a.) ternary b.) 1:1 c.) 1:N d.) 0:1 e.) N:M Level: moderate Page: 162 71. When representing a 1:1 relationship in a relational database design a.) the key of each table must be placed as foreign keys into the other b.) the key of either table may be placed as a foreign key into the other c.) the key of both tables must be the same d.) the intersection table gets the key from both relations e.) both b and c . Level: easy Page: 162 72. To represent a one-to-many relationship in a relational database design . a.) the key of the child is placed as a foreign key into the parent b.) the key of the parent is placed as a foreign key into the child c.) an intersection table must be created d.) the key of the table on the “many” side is placed in the table on the “one” side e.) the keys of both tables are joined into a composite key Level: moderate Page: 165 73. When representing a one-to-many relationship in a relational database design a.) the parent is always on the one side of the "one-to-many" relationship b.) the child is always on the one side of the "one-to-many" relationship c.) either parent or child can be on the one side of the "one-to-Many" relationship, and the choice is arbitrary d.) either parent or child can be on the one side of the "one-to-Many" relationship, and special criteria indicate which table should be on the one side. e.) None of the above is correct. Level: moderate Page: 164 5 - 11 . Chapter 5 - Database Design 74. Many-to-many relationships are represented by . a.) two tables with an M:N relationship b.) two tables with a 1:N relationship c.) by an intersection table which has M:N relationships with the two tables d.) by an intersection table which has 1:N relationships with the two tables e.) by two intersection table which each have 1:N relationships with the two tables Level: moderate Page: 166 75. In many-to-many relationships in a relational database design a.) the key of the child is placed as a foreign key into the parent b.) the key of the parent is placed as a foreign key into the child c.) the keys of both tables are placed in a third table d.) the keys of both tables are joined into a composite key e.) c and d . Level: moderate Page: 166 76. In many-to-many relationships in a relational database design . a.) the intersection table is ID-dependent on one of the parents b.) the intersection table is ID-dependent on both of the parents c.) the minimum cardinality from the intersection table to the parents is always 1 d.) a and b e.) b and c Level: hard Page: 167 77. Which of the following is not true about representing subtypes in a relational database design? a.) One table is created for the supertype and one for each subtype. b.) All of the attributes of the supertype are added to the subtype relations. c.) The key of the supertype is typically made the key of the subtypes. d.) A 1:1 relationship is represented between the supertype and each subtype. e.) An instance of the supertype may be related to one instance each of several subtypes. Level: moderate Page: 170 5 - 12 Chapter 5 - Database Design 78. In a supertype-subtype structure, the relationship between an instance in the supertype and an instance in one of the subtypes is . a.) 1:1 b.) 1:N c.) N:1 d.) M:N e.) recursive Level: hard Page: 171 79. When transforming an Extended E-R model into a relational database design and creating a table for a weak entity that is not ID-dependent . a.) the dependency needs to be noted so that an application does not create a weak entity without its proper strong entity b.) the primary key of the strong entity must be placed as part of the primary key in the weak entity c.) deletions and updates must cascade from the parent to the child d.) a composite primary key must be created for the weak entity e.) both a and c Level: hard Page: 173 80. In representing a recursive relationship in a relational database design . a.) the parent rows and child rows are in different relations b.) the child rows and the parent rows are in the same table c.) the child rows are eliminated when they have the same key as the parent rows d.) the relationship must be reduced to a 1:1 relationship e.) the relationship must be converted into an IS-A relationship Level: moderate Page: 177 81. A relationship between two instances of the same entity is called a(n) _____________ relationship. a.) binary b.) reclusive c.) recursive d.) ternary e.) weak Level: easy Page: 177 5 - 13 Chapter 5 - Database Design 82. Which of the following is not true of recursive relationships? a.) When the recursive relationship is M:N, an intersection table is created. b.) The rows of a single table can play two different roles. c.) The techniques for representing the tables are the same as for non-recursive relationships except the rows are in the same table. d.) Recursive relationships can be 1:1, 1:N, or M:N relationships. e.) Even when the relationship is 1:N, a new table must be defined to represent the relationship. Level: moderate Page: 177-180 83. Which of the following is not true of ternary relationships? a.) can be treated as a collection of binary relationships b.) are represented in the same way as binary relationships c.) must involve at least one M:N relationship d.) involve three entities of different logical types e.) often require documentation of special considerations as business rules Level: hard Page: 179-180 84. The binary constraint MUST NOT indicates that . a.) a specific binary relationship must not be included in a ternary relationship b.) a table includes values that must not occur in a binary relationship c.) a table includes values that must not occur in a ternary relationship d.) a binary relationship includes value combinations that must not occur in a binary relationship e.) a binary relationship includes value combinations that must not occur in a ternary relationship Level: moderate Page: 180 85. The binary constraint MUST COVER indicates that . a.) a specific binary relationship must be included in a ternary relationship b.) a table includes values that must occur in a binary relationship c.) a table includes values that must occur in a ternary relationship d.) a binary relationship includes value combinations that must occur in a binary relationship e.) a binary relationship includes a set of value combinations that must all occur in a ternary relationship Level: moderate Page: 180 5 - 14 Chapter 5 - Database Design 86. A null value can mean . a.) the attribute is inappropriate for a given instance b.) the user has deleted an entity instance from the table c.) the value of the attribute is unknown d.) the attribute has a formula domain e.) both a and c Level: moderate Page: 180 Fill in the Blank Questions 87. When transforming an extended E-R model into a relational database design, first create a(n) table for each entity. Level: easy Page: 151 88. The final step in the database design process is to Level: hard Page: 152 [See Figure 5-1] re-examine normalization criteria . 89. One of the important properties of an attribute is whether or not it is Level: moderate Page: 152 required . 90. By default, the identifier of the entity becomes the __primary key_ of the corresponding table. Level: easy Page: 151 91. The ideal primary key is Level: moderate Page: 153 short , numeric and 92. In IDEF1X terminology, candidate keys are called Level: easy Page: 153 93. A(n) surrogate of a table. Level: easy Page: 154 94. seldom changing . alternate keys . key is a unique, system-supplied identifier used as the primary key The values of a(n) surrogate key on forms and reports. Level: moderate Page: 154 have no meaning to the users and are usually hidden 5 - 15 Chapter 5 - Database Design 95. A surrogate key should be considered when the key contains a(n) Level: hard Page: 154 lengthy text field . 96. In a relational database design, all relationships are expressed by creating a(n) foreign key Level: easy Page: 156 97. Whenever we create a foreign key, we also create a(n) Level: moderate Page: 156 referential integrity constraint . . 98. By default, referential integrity constraints _disallow_ adding a new row to a child table when the foreign key does not match a primary key values in the parent table. Level: moderate Page: 156 [See Figure 5-6] 99. A referential integrity constraint policy that insures that foreign key values in a table are correctly maintained when there is a change to the primary key value in the parent table is called cascading updates . Level: easy Page: 157 100. A referential integrity constraint policy that insures that all rows containing a particular foreign key value in a table are eliminated from the table when the row containing the corresponding primary key value in a parent table is eliminated from the database is called cascading deletes . Level: easy Page: 157 101. When creating a table for an ID-dependent entity, both the key of the the entity itself must appear in the table. Level: moderate Page: 158 parent and the key of 102. When transforming an ID-dependent E-R data model relationship into a relational database design, the referential integrity constraints should allow parent updates to cascade. Level: hard Page: 156 [See Figure 5-6] 103. When transforming an ID-dependent E-R data model relationship into a relational database design, and the child entity is designed to use a surrogate key, then the relationship changes to a non-ID-dependent relationship . Level: moderate Page: 161 104. The simplest form of a HAS-A relationship is a(n) Level: easy Page: 161 5 - 16 1:1 relationship. Chapter 5 - Database Design 105. When the key of one table is stored in another table, it is called a(n) Level: easy Page: 156 foreign key . 106. When representing a 1:1 relationship in a relational database design the key of may be placed as a foreign key into the other table. Level: moderate Page: 162 107. In 1:N relationships, the table on the “one” side is called the Level: easy Page: 164 parent . 108. In 1:N relationships, the table on the “many” side is called the Level: moderate Page: 164 109. In 1:N relationships, the foreign key is placed inside the Level: moderate Page: 165 either table child child . table. 110. To represent a one-to-many relationship in a relational database design the key of the parent table is placed as a foreign key into the other table. Level: moderate Page: 165 111. To represent a M:N relationship in a relational database design, create a table for the relationship itself. Level: moderate Page: 166 112. In representing a M:N relationship in a relational database design, a(n) created. Level: hard Page: 166 intersection table is 113. To represent a M:N relationship in a relational database design, in essence it is reduced to two 1:N relationships. Level: hard Page: 166 114. In many-to-many relationships in a relational database design the primary keys of both tables are joined into a(n) composite primary key in the intersection table. Level: moderate Page: 166 5 - 17 Chapter 5 - Database Design 115. In many-to-many relationships in a relational database design the minimum cardinality from the intersection table to the parents is always 1 . Level: hard Page: 167 116. When transforming an extended E-R model into a relational database design, relationship between the supertype and a given subtype is 1:1 . Level: moderate Page: 170 117. The key of subtypes is generally the key of the Level: easy Page: 171 supertype . 118. In a supertype-subtype structure, the relationship between an instance in the supertype and an instance in one of the subtypes is 1:1 . Level: hard Page: 171 119. In a 1:N recursive relationship, both the parent and child rows reside in the same Level: moderate Page: 177 table . 120. A relationship between two instances of the same entity is called a(n) __recursive___ relationship. Level: easy Page: 177 121. In general, a ternary relationship can be treated as a collection of Level: easy Page: 180 binary relationships. 122. In a(n) MUST NOT constraint, the binary relationship indicates combinations that are not allowed to occur in the ternary relationship. Level: hard Page: 180 123. In a(n) MUST COVER constraint, the binary relationship indicates all combinations that must appear in the ternary relationship. Level: hard Page: 180 124. A(n) Level: easy Page: 180 null value is an attribute value that has never been supplied. 5 - 18 Chapter 5 - Database Design Essay Questions 125. Briefly describe the process of converting an extended E-R model into a relational database design. To convert an extended E-R model into a relational database design, first tables and columns must be defined for each entity attribute. The attributes of the entity are represented as the columns of the table. Primary keys are then selected for each table, after which the relationships between the entities are represented through the creation of foreign keys. Next, constraints are specified. Finally the normalization criteria is re-examined, and, if appropriate, the relations are normalized to achieve a proper data design. 126. Explain the pragmatic reason for using surrogate keys. Primary keys are often duplicated as foreign keys to represent relationships. They are also commonly included in indexes, and are used to identify records to be retrieved by users. When the primary key contains a lengthy text field, this creates a large amount of duplicated data that must be frequently manipulated, thereby degrading database performance. For these reasons, it is often practical to use a surrogate key that is generated by the system and is relatively small and easy to manipulate. 127. Explain the representation of a many-to-many relationship in a relational database design. Many-to-many relationships cannot be directly represented in a relational database design. Therefore, many-to-many relationships are essentially broken into two one-to-many relationships by creating an intersection table that represents the relationship itself. The intersection table takes its key as a combination of the keys of the two original, or parent, entities. Each of the parent entities has a one-to-many relationship with the intersection table that is represented by placing the keys of the parents into the intersection table. 128. What are MUST NOT and MUST COVER constraints? Both MUST NOT and MUST COVER constraints occur when working with three tables in a ternary relationship. When a MUST NOT constraint occurs, it indicates that one binary relationship of two of the tables contains combinations of values that are not allowed to occur in the ternary relationship. When a MUST COVER constraint occurs, it indicates that one binary relationship of two of the tables contains combinations of values which must all appear in the ternary relationship. 5 - 19 Chapter 5 - Database Design 129. Explain the negative aspect of null values. A null value is an attribute value that has never been supplied. The disadvantage of allowing null values to exist in the database is that they create ambiguity. When an attribute has a null value, it is not possible to determine whether the attribute’s value is unknown, inappropriate for a given entity instance, or known to be blank. 5 - 20