66 terms

# C175 Chapter 5

#### Terms in this set (...)

...
...
Normalization
is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies
Denormalization
produces a lower normal form
The three common normalization stages
1. first normal form (1NF)

2. second normal form (2NF)

3. third normal form (3NF)
Prime attribute
A key attribute; that is, an attribute that is part of a key or is the whole key
Key attribute
The attributes that form a primary key
Nonprime attribute (known also as a nonkey attribute)
An attribute that is not part of a key
First normal form (1NF)
Table format, no repeating groups, and PK identified
Second normal form (2NF)
1NF and no partial dependencies
Third normal form (3NF)
2NF and no transitive dependencies
Boyce-Codd normal form (BCNF)
Every determinant is a candidate key (special case of 3NF)
Fourth normal form (4NF)
3NF and no independent multivalued dependencies
Partial dependency
is a functional dependence in which the determinant is only part of the primary key
Transitive dependency
A condition in which an attribute is dependent on another attribute that is not part of the primary key
Repeating group
a characteristic describing a group of multiple entries of the same type for a single key attribute occurrence
Dependency diagram
a diagram depicts all dependencies found within a given table structure
Determinant
is any attribute whose value determines other values within a row
Atomic attribute
is one that cannot be further subdivided
Atomicity
Not being able to be divided into smaller units
Granularity
refers to the level of detail represented by the values stored in a table's row
A relational table must not contain a(n) _____
repeating group
1NF, 2NF, and 3NF are _____
normalization stages
Dependencies based on only a part of a composite primary key are known as _____ dependencies
partial
An attribute that cannot be further subdivided is said to display_____
atomicity
An atomic attribute _____
cannot be further subdivided
For most business transactional databases, we should normalize relations into _____
3NF
A dependency based on only a part of a composite primary key is called a_____
partial dependency
All relational tables satisfy the_____requirements
1NF (first normal form)
A(n) _____ join links tables by selecting only the rows with common values in their common attribute(s)
natural
In the_____, no row may contain two or more multivalued facts about an entity
4NF (fourth normal form)
A table that is in 1NF and includes no partial dependencies is said to be in _____
2NF
From a system functionality point of view, _____ attribute values can be calculated when they are needed to write reports or invoices
derived
Of the following normal forms, _____ is mostly of theoretical interest
DKNF
If you have three different transitive dependencies, _____ different determinant(s) exist
three
The_____ is central to a discussion of normalization
concept of keys
Most designers consider the BCNF as a special case of the _____
3NF
A _____ derives its name from the fact that a collection of multiple entries of the same type can exist for any single key attribute occurrence
repeating group
_____is a process to help reduce the likelihood of data anomalies
Normalization
Improving _____ leads to more flexible queries
atomicity
When a table contains only one candidate key, _____ are considered to be equivalent
the 3NF and the BCNF
A(n) _____ exists when there are functional dependencies such that XY is functionally dependent on WZ, X is functionally dependent on W, and XY is the primary key
partial dependency
A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key is said to be in _____
1NF
Before converting a table into 3NF, it is imperative that the table already be in _____
2NF
Normalization works through a series of stages called normal forms. For most purposes in business database design, _____ stages are as high as you need to go in the normalization process
three
Granularity refers to _____
the level of detail represented by the values in a table's row
From a structural point of view, 3NF is better than _____
2NF
Data redundancy produces _____
data integrity problems
Any attribute whose value determines other values within a row is known as a _____
determinant
The problem with transitive dependencies is that they still yield data
anomalies
A table that displays data redundancies yields _____
anomalies
Because a partial dependency can exist only when a table's primary key is composed of several attributes, a table whose_____key consists of only a single attribute is automatically in 2NF once it is in 1NF
primary
Attribute A _____ attribute B if all of the rows in the table that agree in value for attribute A also agree in value for attribute B
determines
A table that is in 2NF and contains no transitive dependencies is said to be in _____
3NF
If a table has multiple candidate keys and one of those candidate keys is a composite key, the table can have _____ based on this composite candidate key even when the primary key chosen is a single attribute
partial dependencies
An attribute that is part of a key is known as a(n) _____ attribute
prime
_____refers to the level of detail represented by the values stored in a table's row
Granularity
BCNF can be violated only if the table contains more than one _____ key
candidate
A(n) _____ exists when there are functional dependencies such that Y is functionally dependent on X, Z is functionally dependent on Y, and X is the primary key
transitive dependency
Dependencies can be identified with the help of a dependency_____
diagram
In a real-world environment, changing granularity requirements might dictate changes in primary key selection, and those changes might ultimately require the use of_____keys
surrogate
In a(n) _____ diagram, the arrows above the attributes indicate all desirable dependencies
dependency
Any attribute that is at least part of a key is known as a_____
prime attribute/key attribute
Some very specialized applications may require normalization beyond the _____
4NF
From a structural point of view, 2NF is better than _____
1NF
When designing a database, you should _____
make sure that entities are in normal form before table structures are created
When designing a new database structure based on the business requirements of the end users, the database designer will construct a data model using a technique such as_____
Crow's Foot notation ERDs
STUDY GUIDE