ISE 224 Chapter 3 (Q61-100)
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
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
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
An attribute is also known as a ________. A) table B) relation C) row D) column
D
Given the functional dependency (A, B) → C, (A, B) is a ________. A) dependent variable B) determinant C) composite determinant D) Both B and C are correct
D
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
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
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
Which of the following is NOT true about the functional dependency A → (X, Y)? A) X is functionally dependent on A. B) A determines Y. C) X and Y together determine A. D) X and Y are functionally dependent on A.
C
Which of the following is true about the functional dependency (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.
C
A relation is also known as a(n) ________. A) table B) tuple C) relationship D) attribute
A
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
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
Given the functional dependency (A, B) → C, then ________. A) A and B together → C B) A → C C) B → A D) Both A and B are correct
A
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
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
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
The only reason(s) for having relations is to ________. A) store instances of functional dependencies B) store equation components C) store equation results D) Both B and C are correct
A
A determinant that determines all the other columns in a relation is a ________. A) record B) surrogate key C) foreign key D) candidate key
D
A combination of two or more columns used to identify rows in a relation is a ________. A) record B) composite key C) foreign key D) surrogate key
B
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
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
Saying that two entities are functionally dependent means that ________. A) the entities are always connected by a mathematical equation B) for one of the entities, if we are given the value of that entity, we can determine the value of one other entity C) for both entities, if we are given the value of one entity, we can determine the value of the other entity D) the functional dependency will have to be removed through normalization
B
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
A combination of one or more columns used to identify rows in a relation is a ________. A) record B) field C) key D) tuple
C
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
A tuple is also known as a ________. A) table B) relation C) row D) field
C
Given the functional dependency A → (B, C), A is a(n) ________. A) independent variable B) dependent variable C) determinant D) composite determinant
C
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
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
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.
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: Number Color 1 Red 2 Blue 3 White 4 Green 5 Green 6 Green 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.