Normalization
The transformation of complex user views and data stores to a set of smaller, stable, and easily maintainable data structures
Main objective of the normalization process
To simplify all the complex data items that are often found in user views
Normalization of a Relation is Accomplished in Three Major Steps
- Remove repeating groups
- Remove partial dependencies
- Remove transitive dependencies
First Normal Form (1NF)
- Remove repeating groups
- The primary key with repeating group attributes are
moved into a new table - When a relation contains no repeating groups, it is in first normal form
Second Normal Form (2NF)
- Remove any partially dependent attributes and place them in another relation
- A relation is created for the data that are only dependent on part of the key and another for data that are dependent on both parts
A partial dependency
when the data are dependent on a part of a primary key
Third Normal Form (3NF)
- Must be in 2NF
- Remove any transitive dependencies
A transitive dependency
When nonkey attributes are dependent not only on the primary key, but also on a nonkey attribute
Guidelines for Master File/Database Relation Design
- Each separate data entity should create a master database table
- A specific data field should exist on one master table
- Each master table or database relation should have
programs to create, read, update, and delete the records
Integrity Constraints
Entity integrity
Referential integrity
Domain integrity
Entity Integrity
- The primary key cannot have a null value
- If the primary key is a composite key, none of the fields in the key can contain a null value
Referential integrity
- Referential integrity governs the nature of records in a
one-to-many relationship - Referential integrity means that all foreign keys in the many table (the child table) must have a matching record in the parent table
Implications of referential integrity
- You cannot add a record in the child (many) table without a matching record in the parent table
- You cannot change a primary key that has matching child table records
- You cannot delete a record that has child records
Two ways referential integrity is implemented
- A restricted database updates or deletes a key only if
there are no matching child records - A cascaded database will delete or update all child
records when a parent record is deleted or changed
Domain integrity
Domain integrity rules are used to validate the data
Two forms of domain integrity
- Check constraints, which are defined at the table level
2. Rules, which are defined as separate objects and can be used within a number of fields
Anomalies
Data redundancy
Insert anomaly
Deletion anomaly
Update anomaly
Date redundancy
- When the same data is stored in more than one place in the database (Store data once!)
- Solved by creating tables that are in third normal form
Insert anomaly
- Occurs when the entire primary key is not known and the database cannot insert a new record, which would violate entity integrity
- Can be avoided by using a sequence number for the primary key
Deletion anomaly
Happens when a record is deleted that results in the loss of other related data
Update anomaly
When a change to one attribute value causes the database to either contain inconsistent data or causes multiple records to need changing
Retrieving and Presenting Database Data
- Choose a relation from the database
- Join two relations together
- Project columns from the relation
- Select rows from the relation
- Derive new attributes
- Index or sort rows
- Calculate totals and performance measures
- Present data
