Database Normalization Flashcards
Enumeration:
Things to avoid when designing a Database
Repetition of Information
Inability to Represent Certain Information
Loss of Information
Process of applying a series of techniques and rules to a database in order to reduce data redundancy
Normalization
Process of decomposing relations with anomalies to produce smaller, well-structured relations
Normalization
Normalization techniques which involves
splitting a relation into two or more relations
Projection
Series of rules
Normal Form
Enumeration:
Main Goals of Normalization
- Minimize data redundancy, avoid anomalies, conserve storage space
- Simplify the enforcement of referential integrity constraints
- Make it easier to maintain data (insert, update, delete)
- Provide a better design that is an improved
representation of the real world and a stronger basis for future growth
Identify the Normal Form:
Any multivalued Attribute have been removed
First Normal Form (1NF)
Identify the Normal Form:
Any functional dependencies have been removed
Second Normal Form (2NF)
Identify the Normal Form:
Any transitive dependencies have been removed
Third Normal Form (3NF)
Identify the Normal Form:
Any remaining anomalies that result from functional dependencies have been removed
Boyce/Codd Normal Form
Identify the Normal Form:
Any multivalued dependencies have been removed
Fourth Normal Form (4NF)
Identify the Normal Form:
Any remaining anomalies have been removed
Fifth Normal Form (5NF)
Constraints between two attributes or two sets of attributes
Functional Dependencies
The attribute or set of attributes that uniquely determines the value of another attribute
Determinant
The attribute whose value is determined by the another attribute
Dependent
Attribute on the left-hand side of the arrow in a functional dependency
Determinant
Enumeration:
A candidate key must satisfy the following properties
Unique identification
Non-redundancy
Candidate-key properties:
This property implies that each nonkey attribute is functionally dependent on that key
Unique identification
Candidate-key properties:
No attribute in the key can be deleted without destroying the property of unique identification
Non-redundancy
Determinants vs. Candidate Key
All candidate keys are determinants but not all determinants are candidate keys.
Identify the Normal Form
A relation that has a primary key and in which there are no repeating groups
First Normal Form
Enumeration:
Anomalies in 1NF
Insertion
Deletion
Update
Identify the Anomaly:
This anomaly occurs when inserting a new record into a database requires the duplication of existing data
Ex. if new product is ordered for order 1007 of existing customer, customer data must be re -entered, causing duplication
Insertion
Identify the Anomaly:
This anomaly occurs when deleting a record from a database causes the loss of related data
Ex. if we delete the Dining Table from Order 1006, we lose information concerning this item’s finish and price
Deletion
Identify the Anomaly:
This anomaly occurs when modifying data in one part of a database requires multiple changes in other parts of the database
Ex. changing the price of product ID 4 requires update in several records
Update
A relation that is in second normal form and has no transitive dependencies present
Third Normal Form
A functional dependency between two (or more) nonkey attributes
Transitive Dependency
Attribute, or combination of attributes, that can uniquely identify a row in a relation
Candidate Keys
Occurs when an attribute (or a set of attributes) within a relation depends on only a part of the composite primary key rather than the entire primary key
Partial Dependency
occurs when an attribute (or a set of attributes) within a relation depends on the entire primary key
Full Dependency
