Database Concepts 6th Edition By Kroenke - Test Bank

Database Concepts 6th Edition By Kroenke – Test Bank

 

Instant Download – Complete Test Bank With Answers

 

 

Sample Questions Are Posted Below

 

Database Concepts, 6e (Kroenke/Auer)

Chapter 5  Database Design

 

1) The first step in representing entities using the relational model is to determine which identifier will be used as the key.

Answer:  FALSE

Diff: 2      Page Ref: 260

 

2) Relations should always be normalized to the highest degree possible.

Answer:  FALSE

Diff: 1      Page Ref: 264

 

3) If a weak entity is ID-dependent but not existence-dependent, it can be represented using the same techniques as a strong entity.

Answer:  FALSE

Diff: 3      Page Ref: 266

 

4) The key of the parent entity becomes part of the key of an ID-dependent entity.

Answer:  TRUE

Diff: 1      Page Ref: 266-267

 

5) From a pragmatic standpoint, the only important rule of normalization is that the determinant of every functional dependency must be a candidate key.

Answer:  TRUE

Diff: 2      Page Ref: 267-269

 

6) A table that meets the definition of a relation is in 1NF.

Answer:  TRUE

Diff: 1      Page Ref: 267

 

7) Boyce-Codd Normal Form was created to address the problem of multivalued dependencies.

Answer:  FALSE

Diff: 3      Page Ref: 268

 

8) DK/NF requires that all constraints on data values be logical implications of the definitions of domains and keys.

Answer:  TRUE

Diff: 2      Page Ref: 268

 

9) The technique for representing E-R relationships in the relational model is dependent on the minimum cardinality.

Answer:  FALSE

Diff: 1      Page Ref: 270

 

 

10) For a 1:1 relationship, the key of each table should be placed in the other table as the foreign key.

Answer:  FALSE

Diff: 2      Page Ref: 269-271

11) Relationships that are 1:1 do not require referential integrity constraints.

Answer:  FALSE

Diff: 2      Page Ref: 261-271

 

12) In certain circumstances, there may be a preference as to which table in a 1:1 relationship contains the foreign key.

Answer:  TRUE

Diff: 1      Page Ref: 261-271

 

13) When applied to 1:N relationships, the term “parent” refers to the many side of the relationship since a child may have many parents.

Answer:  FALSE

Diff: 1      Page Ref: 272-273

 

14) To represent a 1:N relationship in the relational model, the key of the entity on the one side of the relationship is placed as a foreign key in the entity on the many side of the relationship.

Answer:  TRUE

Diff: 2      Page Ref: 272-273

 

15) To represent a 1:N relationship in the relational model, the key of either entity may be placed as a foreign key in the other entity.

Answer:  FALSE

Diff: 2      Page Ref: 272-273

 

16) In the relational model, many-to-many relationships cannot be directly represented by relations the way 1:1 and 1:N relationships can.

Answer:  TRUE

Diff: 3      Page Ref: 273-274

 

17) To represent a M:N relationship in the relational model, an intersection relation is created to represent the relationship itself.

Answer:  TRUE

Diff: 1      Page Ref: 273-274

 

18) The key for an intersection relation is always the combination of the keys of the parent entities.

Answer:  TRUE

Diff: 2      Page Ref: 274

 

19) All recursive relationships are 1:1.

Answer:  FALSE

Diff: 2      Page Ref: 279

 

20) Recursive relationships can be represented in the relational model using the same techniques that are used for binary relationships.

Answer:  TRUE

Diff: 2      Page Ref: 279-282

 

21) Microsoft Access uses the same pure N:M relationships that occur in data modeling.

Answer:  FALSE

Diff: 2      Page Ref: 288

22) As far as Microsoft Access is concerned, there are no N:M relationships.

Answer:  TRUE

Diff: 1      Page Ref: 288

 

23) As far as Microsoft Access is concerned, there are no 1:N relationships.

Answer:  FALSE

Diff: 1      Page Ref: 288

 

24) By default, Microsoft Access creates 1:1 relationships between tables.

Answer:  FALSE

Diff: 2      Page Ref: 291-292

 

25) To create a 1:1 relationship in Microsoft Access, the Indexed property of the foreign key column must be set to Yes (No Duplicates).

Answer:  TRUE

Diff: 3      Page Ref: 291-292

 

26) Which of the following is the first step in representing entities using the relational model?

1. A) Define a table for each entity.
2. B) Determine identifiers.
3. C) Determine foreign keys.
4. D) Examine the entity against normalization criteria.
5. E) Define a primary key.

Answer:  A

Diff: 2      Page Ref: 260

 

27) Which of the following would be a reason to denormalize a relation?

1. A) Relax security
2. B) Lack of design time
3. C) End user preference
4. D) Improve performance
5. E) None of the above

Answer:  D

Diff: 2      Page Ref: 264

 

 

28) Which of the following is true about representing a weak entity with the relational model?

1. A) If the weak entity is existence-dependent, the key of the parent must be part of the key of the weak entity.
2. B) If the strong entity has a minimum cardinality of 1, the key of the weak entity must be part of the strong entity.
3. C) If the weak entity is ID-dependent, the key of the weak entity must be part of the key of the parent entity.
4. D) If the weak entity is ID-dependent, the key of the parent entity must be part of the key of the weak entity.
5. E) If the parent entity is existence-dependent, then the minimum cardinality of the weak entity is zero.

Answer:  D

Diff: 2      Page Ref: 266-267

29) Which normal form is defined as any table meeting the definition of a relation?

1. A) 1NF
2. B) 2NF
3. C) BCNF
4. D) 4NF
5. E) DK/NF

Answer:  A

Diff: 1      Page Ref: 267

 

30) E. F. Codd and R. Boyce developed Boyce-Codd Normal Form (BCNF) to resolve anomalies in the:

1. A) 1NF.
2. B) 2NF.
3. C) 3NF.
4. D) 4NF.
5. E) DK/NF.

Answer:  C

Diff: 2      Page Ref: 267-269

 

31) Which normal form was developed in order to eliminate multivalued dependencies?

1. A) 3NF
2. B) BCNF
3. C) 4NF
4. D) 5NF
5. E) DK/NF

Answer:  C

Diff: 2      Page Ref: 268

 

 

32) Which normal form deals with the problem of tables that can be split apart but not correctly joined back together?

1. A) 3NF
2. B) BCNF
3. C) 4NF
4. D) 5NF
5. E) DK/NF

Answer:  D

Diff: 3      Page Ref: 268

 

33) The fundamental rule of normalization can be stated as:

1. A) every table must meet the definition of a relation.
2. B) every determinant must be a candidate key.
3. C) every domain must be a logical consequence of the constraints.
4. D) every key must be a candidate key.
5. E) every constraint must be a determinant.

Answer:  B

Diff: 3      Page Ref: 267-269

34) Given the generic relation:

GENERIC (PKey1, PKey2, Attribute1, Attribute2, Attribute3),

and the functional dependencies:

(PKey1, PKey2) → Attribute1 and PKey2 → (Attribute2, Attribute3),

which of the following is true?

1. A) GENERIC is not fully normalized.
2. B) PKey1 is a determinant.
3. C) PKey2 is a candidate key.
4. D) GENERIC is in DK/NF.
5. E) All of the above

Answer:  A

Diff: 2      Page Ref: 267-269

 

35) Which of the following is not necessarily true of a relation that is in 4NF?

1. A) It is in 2NF.
2. B) It is in DK/NF.
3. C) It has no multivalued dependencies.
4. D) It is in Boyce-Codd Normal Form.
5. E) All of the above are

Answer:  B

Diff: 3      Page Ref: 267-269

 

 

36) DK/NF requires:

1. A) every determinant must be a logical consequence of the definition of domains.
2. B) every domain of data values must be a logical consequence of the definition of keys.
3. C) every constraint on data values must be a logical consequence of the definition of domains and keys.
4. D) every key must be a logical consequence of the definition of constraints and determinants.
5. E) every consequence of data values must originate in the definition of determinants and keys.

Answer:  C

Diff: 3      Page Ref: 267-269

 

37) Which of the following is true when representing a 1:1 binary relationship using the relational model?

1. A) The key of the entity with the highest minimum cardinality must be placed in the other entity as a foreign key.
2. B) The key of each entity must be placed in the other as a foreign key.
3. C) The key of either entity is placed in the other as a foreign key.
4. D) The key of the entity with the most attributes must be placed in the other entity as a foreign key.
5. E) Both entities must have the same primary key.

Answer:  C

Diff: 1      Page Ref: 269-271

38) Given TABLE_A (Attribute1, Attribute2, Attribute3) and TABLE_B (Attribute4, Attribute5, Attribute6) shown in the figure below, which of the following would display the correct placement of foreign keys in the relational model?

 

1. A) TABLE_A (Attribute1, Attribute2, Attribute3)

TABLE _B (Attribute4, Attribute5, Attribute6, Attribute1)

1. B) TABLE _A (Attribute1, Attribute2, Attribute3, Attribute4, Attribute5)

TABLE _B (Attribute4, Attribute5, Attribute6)

1. C) TABLE _A (Attribute1, Attribute2, Attribute3, Attribute4)

TABLE _B (Attribute4, Attribute5, Attribute6, Attribute1)

1. D) TABLE _A (Attribute1, Attribute2, Attribute3)

TABLE _B (Attribute4, Attribute5, Attribute6)

1. E) TABLE _A (Attribute1, Attribute2, Attribute3, Attribute6)

TABLE _B (Attribute4, Attribute5, Attribute6)

Answer:  A

Diff: 2      Page Ref: 269-271

 

 

39) Which of the following is the correct technique for representing a 1:N relationship in the relational model?

1. A) The key of the entity on the one side is placed into the relation for the entity on the many
2. B) The key of the child is placed into the relation of the parent.
3. C) The key of either relation can be placed into the other relation.
4. D) The key of the entity on the many side is placed into the relation for the entity on the one
5. E) An intersection relation is created, and the keys from both parent entities are placed as keys in the intersection relation.

Answer:  A

Diff: 2      Page Ref: 272-273

 

40) Given the entities PRODUCT and SUPPLIER shown in the figure below, which of the following would represent the correct placement of foreign keys?

 

1. A) PRODUCT (ProductID, Description, Cost)

SUPPLIER (SupplierID, ContactName, PhoneNumber)

1. B) PRODUCT (ProductID, Description, Cost)

SUPPLIER (SupplierID, ContactName, PhoneNumber, ProductID)

1. C) PRODUCT (ProductID, Description, Cost, SupplierID)

SUPPLIER (SupplierID, ContactName, PhoneNumber, ProductID)

1. D) PRODUCT (ProductID, Description, Cost, ContactName)

SUPPLIER (SupplierID, ContactName, PhoneNumber)

1. E) PRODUCT (ProductID, Description, Cost, SupplierID)

SUPPLIER (SupplierID, ContactName, PhoneNumber)

Answer:  E

Diff: 2      Page Ref: 272-273

41) Which of the following is the correct technique for representing a M:N relationship using the relational model?

1. A) An intersection relation is created, and the key of either entity is placed as a key in both the intersection relation and in the other relation.
2. B) An intersection relation is created with a surrogate key, which is placed in each of the parent entities.
3. C) An intersection relation is created, and the keys of both parent entities are placed as a composite key in the intersection relation.
4. D) The key from either relation is placed as a foreign key in the other relation.
5. E) None of the above

Answer:  C

Diff: 2      Page Ref: 273-275

 

 

42) Given the PRODUCT and SUPPLIER entities in the figure below, which of the following would represent the correct placement of foreign keys?

 

1. A) PRODUCT (ProductID, Description, Cost)

SUPPLIER (SupplierID, ContactName, PhoneNumber)

1. B) PRODUCT (ProductID, Description, Cost, SupplierID)

SUPPLIER (SupplierID, ContactName, PhoneNumber, ProductID)

1. C) PRODUCT (ProductID, Description, Cost)

SUPPLIER (SupplierID, ContactName, PhoneNumber)

PRODUCT_SUPPLIER (ProductID, SupplierID)

1. D) PRODUCT (ProductID, Description, Cost, SupplierID)

SUPPLIER (SupplierID, ContactName, PhoneNumber)

1. E) PRODUCT (ProductID, Description, Cost)

SUPPLIER (SupplierID, ContactName, PhoneNumber)

PRODUCT_SUPPLIER (ProductID, SupplierID)

Answer:  E

Diff: 3      Page Ref: 273-275

 

43) What relationship pattern is illustrated in the following schema?

 

PRODUCT (ProductID, Description)

SUPPLIER (SupplierID, ContactName, PhoneNumber)

PRODUCT_SUPPLIER (ProductID, SupplierID, Cost)

 

ProductID in PRODUCT_SUPPLIER must exist in ProductID in PRODUCT

SupplierID in PRODUCT_SUPPLIER must exist in SupplierID in PRODUCT

1. A) Association relationship
2. B) Intersection relationship
3. C) Recursive relationship
4. D) Strong entity relationship
5. E) Supertype/subtype relationship

Answer:  A

Diff: 2      Page Ref: 275-278

 

44) What relationship pattern is illustrated in the following schema?

 

VEHICLE (VehicleID, Cost)

CAR (VehicleID, NumberOfSeats)

TRUCK (VehicleID, CargoCapacity)

 

VehicleID in CAR must exist in VehicleID in VEHICLE

VehicleID in TRUCK must exist in VehicleID in VEHICLE

1. A) Association relationship
2. B) Intersection relationship
3. C) Recursive relationship
4. D) Strong entity relationship
5. E) Supertype/subtype relationship

Answer:  E

Diff: 2      Page Ref: 278

 

45) What relationship pattern is illustrated in the following schema?

 

EMPLOYEE (EmployeeID, OfficePhone, Manager)

 

Manager in EMPLOYEE must exist in EmployeeID in EMPLOYEE

1. A) Association relationship
2. B) Intersection relationship
3. C) Recursive relationship
4. D) Strong entity relationship
5. E) Supertype/subtype relationship

Answer:  C

Diff: 2      Page Ref: 279-282

 

46) Microsoft Access does not create N:M relationships because:

1. A) Microsoft Access creates databases based on database designs instead of data models.
2. B) Microsoft Access creates databases based on data models instead of database designs.
3. C) Microsoft Access cannot implement association relationships.
4. D) Microsoft Access cannot implement supertype/subtype relationships.
5. E) Microsoft Access cannot implement recursive relationships.

Answer:  A

Diff: 3      Page Ref: 288

 

47) As far as Microsoft Access is concerned, there are no:

1. A) 1:1 relationships.
2. B) 1:N relationships.
3. C) N:1 relationships.
4. D) N:M relationships.
5. E) recursive relationships.

Answer:  D

Diff: 2      Page Ref: 288

48) By default, when Microsoft Access creates a relationship between two tables, it creates a(n):

1. A) 1:1 relationship.
2. B) 1:N relationship.
3. C) N:M relationship.
4. D) association relationship.
5. E) recursive relationship.

Answer:  B

Diff: 2      Page Ref: 291-292

 

49) To create a 1:1 relationship between two tables in Microsoft Access:

1. A) the Indexed property of the foreign key column must be set to
2. B) the Indexed property of the foreign key column must be set to Yes (Duplicates OK).
3. C) the Indexed property of the foreign key column must be set to Yes (No Duplicates).
4. D) the Data Type of the foreign key column must be set to
5. E) the Smart Tag property of the foreign key column must be set to Foreign Key.

Answer:  C

Diff: 2      Page Ref: 291-292

 

50) After a 1:1 relationship has been created between two tables in Microsoft Access, the Relationship Type of One-To-One appears:

1. A) in the Relationship Type property of the primary key column in table Design View.
2. B) in the Relationship Type property of the foreign key column in table Design View.
3. C) in the table object in the Relationships window.
4. D) in the Show Table dialog box.
5. E) in the Edit Relationships dialog box.

Answer:  E

Diff: 2      Page Ref: 291-292

 

51) The first step of database design is to define a table for each ________.

Answer:  entity

Diff: 1      Page Ref: 260

 

52) Once a table has been defined, it should be examined according to ________ criteria.

Answer:  normalization

Diff: 2      Page Ref: 260

 

53) There are cases where it is possible to normalize a table too far, in which case there may be a need for ________.

Answer:  denormalization

Diff: 2      Page Ref: 264

 

54) If a table meets the definition of a relation, it is said to be in ________.

Answer:  first normal form (1NF)

Diff: 1      Page Ref: 267

 

 

55) ________ eliminated some anomalies found in the third normal form, and was later followed by a fourth and fifth normal form.

Answer:  Boyce-Codd Normal Form (BCNF)

Diff: 2      Page Ref: 267-269

56) R. Fagin defined the ________, and a relation in this form has no anomalies.

Answer:  domain key / normal form (DK/NF)

Diff: 1      Page Ref: 267-269

 

57) To normalize a relation, the determinant of every functional dependency should be a(n) ________.

Answer:  candidate key

Diff: 2      Page Ref: 267-269

 

58) To represent a many-to-many relationship in the relational model, a(n) ________ table is used.

Answer:  intersection

Diff: 1      Page Ref: 273-275

 

59) For a(n) ________ weak entity, it is necessary to add the key of the parent entity to the weak entity’s relation so that this added attribute becomes part of the weak entity’s key.

Answer:  ID-dependent

Diff: 2      Page Ref: 275-278

 

60) A(n) ________ is a relationship among entities of the same class.

Answer:  recursive relationship

Diff: 2      Page Ref: 279

 

61) Microsoft Access does not create N:M relationships because Microsoft Access creates databases based on ________.

Answer:  database designs

Diff: 2      Page Ref: 279

 

62) As far as Microsoft Access is concerned, there are no ________.

Answer:  N:M relationships

Diff: 2      Page Ref: 288

 

63) By default, when Microsoft Access creates a relationship between two tables it creates a(n) ________ relationship.

Answer:  1:N

Diff: 2      Page Ref: 288

 

64) To create a 1:1 relationship between two tables in Microsoft Access, the Indexed property of the foreign key column must be set to ________.

Answer:  Yes (No Duplicates)

Diff: 3      Page Ref: 291-292

 

65) After a 1:1 relationship has been created between two tables in Microsoft Access, the Relationship Type of One-To-One appears in the ________.

Answer:  Edit Relationships dialog box

Diff: 3      Page Ref: 291-292

66) Explain the process of representing an entity using the relational model.

Answer:  To represent an entity using the relational model, first define a relation for the entity. The name of the entity is the name of the relation. Each attribute in the entity becomes a column in the relation. If the entity had a unique identifier that would make an appropriate primary key, then that attribute is made the key. If there is no such attribute, then the development team discusses identifiers with the users to determine if an acceptable key attribute exists. If not, then a surrogate key may be used. Finally, the relation is evaluated against the normalization criteria. Changes to the design may be necessary to satisfy the normalization requirements.

Diff: 2      Page Ref: 260-264

 

67) What is denormalization and why can it be desirable?

Answer:  Denormalization is the process of consolidating relations that are in a higher normal form into a single relation that is in a lower normal form, thereby making it susceptible to more anomalies. Denormalization can be desirable for two reasons. First, sometimes the act of normalizing a table can cause it to become too cumbersome or contrived, which makes it difficult to work with. Therefore denormalization may be preferable when the dangers of the additional anomalies are considered to be of less importance than the ease of working with the relations. Second, processing multiple tables requires more processing overhead than processing a single table. Therefore, performance can often be improved by using a single, denormalized table than multiple, normalized tables. When the performance gain outweighs the dangers of the additional anomalies, the denormalized table may be preferred.

Diff: 2      Page Ref: 264

 

68) What are “normal forms”?

Answer:  Normal forms are a way of classifying tables based on the types of anomalies to which they are vulnerable. Each normal form in the group — 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF — addresses anomalies that were identified in the preceding normal form. Domain Key normal form is another type of normal form, but it does not build off of the other normal forms. Any table in DK/NF is known to have no anomalies.

Diff: 2      Page Ref: 267-269

 

69) What is Boyce-Codd Normal Form, and why was it developed?

Answer:  The Boyce-Codd Normal Form (BCNF) is a normal form that falls between 3NF and 4NF. E. F. Codd developed the first three normal forms (1NF, 2NF, and 3NF) to deal with various table anomalies. After it was discovered that some anomalies could exist in tables in 3NF, Codd and R. Boyce developed BCNF to deal with the 3NF anomalies.

Diff: 3      Page Ref: 267-269

 

 

70) What makes DK/NF different from all of the other normal forms?

Answer:  Domain Key Normal Form (DK/NF) is different from 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF in terms of its evolution and the scope of anomalies addressed. All normal forms previous to DK/NF were developed by an evolutionary process in which each normal form represented a solution to a given anomaly that was found to exist in the preceding normal form. Therefore, even with 5NF the possibility exists that more anomalies are present but haven’t yet been found. DK/NF developed outside this evolutionary process as the result of an investigation to find a set of conditions that would prevent any anomalies from existing. Therefore, a table in DK/NF cannot have any anomalies, known or unknown.

Diff: 3      Page Ref: 267-269

71) Explain the representation of ID-dependent weak entities using the relational model.

Answer:  A weak entity is represented in the relational model similarly to the representation of a strong entity. First, a relation is defined for the entity. Each attribute in the weak entity becomes a column in the relation. The primary key of the strong entity on which the weak entity is ID-dependent is added to the relation and is made a part of the primary key of the weak entity’s relation along with the weak entity’s identifier. Finally, the relation is evaluated according to the normalization criteria, and any necessary design changes are made to normalize the relation.

Diff: 2      Page Ref: 275-278

 

72) What is an association relationship, and how does it differ from an N:M relationship?

Answer:  An association relationship is very similar to an N:M relationship except that the intersection table has attributes of its own. This means that in addition to the foreign key fields linking to the two strong entities, there is at least one additional field in what would otherwise be called the intersection table but is now an association table. For example, the intersection table ENROLLMENT for STUDENT and CLASS showing student enrollment in each class would normally have two columns: StudentID and ClassID. However, we can turn this intersection table into an association table by adding the column Grade, which records each student’s grade in each class.

Diff: 2      Page Ref: 275-278

 

73) Write the schema to represent the entities below, including tables, the proper placement of the foreign key, and referential integrity constraint.

 

Answer:  STUDENT (StudentID, StuName, StuMajor, StuPhone, AdvisorID)

ADVISOR (AdvisorID, AdvName, AdvOffice, AdvPhone)

 

AdvisorID in STUDENT must exist in AdvisorID in ADVISOR.

Diff: 2      Page Ref: 272-273

 

 

74) How are many-to-many relationships addressed in the relational model?

Answer:  Many-to-many relationships cannot be directly addressed in the relational model; therefore, they must be reduced to one-to-many relationships. This is done by creating an intersection relation that represents the complex many-to-many relationship. This intersection relation is given the keys of the two parent relations and both of these are used together to make the primary key of the intersection relation. The parent entities will have one-to-many relationships with the intersection relation, which can be represented normally within the relational model.

Diff: 2      Page Ref: 273-275

75) How are one-to-one recursive relationships addressed using the relational model?

Answer:  One-to-one recursive relationships are addressed just the same as one-to-one nonrecursive relationships. The only difference is that both of the related entity instances are in the same entity class. The key of either instance is placed in the other instance as a foreign key. In the case of a recursive relationship, this means that a new attribute is added to the entity class with the recursive relationship. For each instance, this new attribute will contain the value of the key attribute of the instance that is related.

Diff: 2      Page Ref: 279-282

 

76) How are 1:1, 1:N and N:M relationships handled in Microsoft Access?

Answer:  By default, when Microsoft Access creates a relationship between two tables, it creates a 1:N relationship. N:M relationships are created in Microsoft Access, as in all other DBMS products, as two 1:N relationships linking the two tables (based on the original two entities) through an intersection table. As far as Microsoft Access is concerned, there are no N:M relationships! To create a 1:1 relationship between two tables in Microsoft Access, the Indexed property of the foreign key column in the table containing the foreign key must be set to Yes (No Duplicates) before the relationship is created. With the property set, the relation is automatically created as a 1:1 relationship.

Diff: 2      Page Ref: 288-294