chapter 3 (73-100)

74) When designing a database, one of the candidate keys in a relation is selected as the ________. A) composite key B) primary key C) foreign key D) surrogate key

B

92) Describe the requirements that a table must meet to qualify as a relation.

: 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. Third, each column must contain the same kind of data for all rows. Fifth, each column must have a unique name. Sixth, the cells must contain single values—no group or array values are allowed. Seventh, the order of the columns and the rows is insignificant. Finally, the rows must be unique.

95) Briefly describe the various tasks of the primary key.

: The primary key is used for four primary tasks. First, it is used to uniquely identify the rows in a table. Second, it is used to represent rows in relationships. Third, most DBMS products use the values of the primary key to organize the storage of the relation. Finally, primary keys are used in indexes and other structures to improve performance for search operations

79) Having to enter facts about two entities when we want to enter facts about only one is an example of ________. A) insertion anomaly B) update anomaly C) deletion anomaly D) normalization anomaly

A

82) 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

A

83) A relation is in second normal form (2NF) if and only if it is in 1NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every candidate key is a determinant

A

90) In general, each relation should have ________. A) one and only one theme B) one or more themes C) exactly two themes D) one or two themes

A

98) Explain the concept of a surrogate key.

A surrogate key is an artificial key that is created to act as the primary key for a relation. The surrogate key is a unique, numeric value that is appended to the relation. Surrogate keys are used in situations when no suitable primary key exists within the user data, or when all available primary keys within the data are too cumbersome for an efficient design. Surrogate key values have no meaning to the users and are normally hidden on all forms, reports, and displays. Most DBMS products can automatically generate values for surrogate keys as needed.

84) A relation is in third normal form (3NF) if and only if it is in 2NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every candidate key is a determinant

B

88) A relation is in fourth normal form if it is in BCNF and it has no ________. A) transitive dependencies B) multivalued dependencies C) partial dependencies D) deletion dependencies

B

94) Distinguish between the primary key and a candidate key.

Both the primary key and a candidate key can uniquely identify the rows in a table. The primary key is the candidate key that is chosen by the database designer, working with the users, to uniquely identify rows and to represent relationships. Although any candidate key could, by definition, be selected to act as the primary key, the choice of primary key is often based on design decisions such as the amount of foreign key data that would be generated

76) A key consisting of one or more columns that is a primary key in another relation is a ________. A) composite key B) candidate key C) foreign key D) surrogate key

C

77) Referential integrity constraints are used to limit the possible values of a ________. A) composite key B) candidate key C) foreign key D) surrogate key

C

81) Suppose that you need to update one value of the column SalesCost in a relation. The way the relation is constructed, this value needs to be changed in three different rows. However, you only change the value in two of the rows. You have just created a(n) ________. A) normalization anomaly B) insertion anomaly C) update anomaly D) deletion anomaly

C

87) If a relation is in BCNF, and each multivalued dependency has been moved to a relation of its own, then the first relation is in ________. A) Third Normal Form B) Boyce-Codd Normal Form C) Fourth Normal Form D) Domain/key normal form

C

75) An artificial column added to a relation to serve as the primary key is a ________. A) composite key B) candidate key C) foreign key D) surrogate key

D

78) A ________ is used to limit the possible values of a foreign key. A) composite key B) surrogate key C) functional dependency D) referential integrity constraint

D

80) If the removal of facts about one entity results in the unintentional loss of data about another entity, this is referred to as a(n) ________. A) normalization anomaly B) insertion anomaly C) update anomaly D) deletion anomaly

D

85) A relation is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every determinant is a candidate key

D

86) If a table is designed so that every determinant is a candidate key, then that relation is in ________. A) First Normal Form B) Second Normal Form C) Third Normal Form D) Boyce-Codd Normal Form

D

89) 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

D

96) Explain the relationships among primary keys and functional dependencies.

Relations store instances of functional dependencies in their rows. 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.

99) Explain the essence of normalization that is implemented using 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 contains data on a single theme. The different normal forms protect against the occurrence of modification anomalies. In practice, making sure all relations are in Boyce-Codd Normal Form (BCNF), or in Fourth Normal Form (4NF) if multivalued dependencies are found, insures high reliability of the database.

100) Explain the steps in the process of putting a relation straight into Boyce-Codd Normal Form (BCNF).

The steps are: (1) Identify all functional dependencies in the relation. (2) Identify every candidate key in the relation. (3) Check to see if all determinants are candidate keys. a. If so, the relation is in BCNF-STOP. b. If not, then: i. Move the columns of the functional dependency of the determinant that is not a candidate key to a new relation. ii. Make the determinant of that functional dependency the primary key of the new relation. iii. Leave a copy of the determinant in the original relation as a foreign key. iv. Create a referential integrity constraint between the relations. NOTE: If there is more than one functional dependency that needs to be moved, move the functional dependency with the most columns first. (4) Repeat step (3) until you reach 3a. - STOP.

93) Explain the terms relation, tuple, and attribute.

The terms relation, tuple, and attribute are used primarily by database theoreticians. These terms are synonymous with the terms table, row, and column, respectively, in regards to a relational database. They are also equivalent to the terms file, record, and field, which tend to be used by many traditional data processing professionals.

97) Explain the concept of a foreign key

To implement a relationship within a relational database, the primary key of one relation is placed as an attribute in another relation. This attribute is called a foreign key in the second relation because it is the primary key of a relation that is foreign to the table in which the field resides.

91) What is a functional dependency? Give an example not used in the text

We say that a functional dependency exists when the value of one or more attributes determines the value of another attribute. For example, suppose we have a six-sided die numbered and colored as follows: ***see chart ** In this case, Number functionally determines Color, written as Number → Color. Then, if we know the Number is 3, we know the Color is White. Note that the reverse is not true—Color does not determine Number since if we are given the Color Green the number could be 4, 5, or 6. The attribute on the left-hand side of the dependency is called the "determinant." Thus, Number is a determinant. Although equations can be expressed as functional dependencies [as when the equation TotalCost = NumberSold * ItemCost is written as the functional dependency (NumberSold, ItemCost) → TotalCost] functional dependencies are not equations. Also note that a determinant can have more than one attribute [as does (NumberSold, ItemCost) in the last example] and when it does it is called a composite determinant.