Normalization Flashcards
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
Projection
Series of rules
Normal form
any multivalued attribute have been removed
First Normal Form
Main Goals of Normalization
- Minimize data redundancy, thereby avoiding anomalies and conserving 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
Any multivalued Attribute have been removed, so there is a single value (possibly null) at the intersection of each row and column of the table
First Normal Form (1NF)
Any functional dependencies have been removed
Second Normal Form (2NF)
Any transitive dependencies have been remove
Third Normal Form (3NF)
Constraints between two attributes or two sets of attributes
Functional Dependencies
For any relation of R, attribute B is functionally dependent on attribute A if, for every valid instance A, that the value of A uniquely determines the value of B
Functional Dependencies
Attribute on the left-hand side of the arrow in a functional dependency
Determinants
SSN, VIN, ISBN are
Determinants
Attribute, or combination of attributes, that uniquely identifies a row in a relation
Candidate Keys
This property implies that each nonkey attribute is functionally dependent on that key
Unique identification
No attribute in the key can be deleted without destroying the property of unique identification
Non-redundancy
A relation that has a primary key and in which there are no repeating groups
First Normal Form
if new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication
Anomalies
Insertion
if we delete the Dining Table from Order 1006, we lose information concerning this item’s finish and price
Anomalies
Deletion
changing the price of product ID 4 requires update in several records
Anomalies
Update
The primary key consist of only one attribute (such as attribute Product_ID in the PRODUCT relation). By definition, there cannot be a partial dependency in such a relation
Second Normal Form
No nonkey attributes exist in the relation (thus all of the attributes in the relation are components of the primary key). There are no functional dependencies in such a relation
Second Normal Form
Evey nonkey attribute is functionally dependent on the full set of primary key attributes (such as the attribute Order_Quantity in the ORDER_LINE relation)
Second Normal Form
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
a situation in which a non-key attribute of a table depends on only a part of the primary key.
Partial Dependency
consists of one or more columns whose data contained within are used to uniquely identify each row in the table.
primary key
is a set of one or more columns in a table that refers to the primary key in another table.
foreign key