[attachment=9463]
Relational Database Design
Purpose of Normalization
Anomalies
Functional Dependencies
Normalization
Keys
SuperKey
a set of attributes whose values together uniquely identify a tuple in a relation
Candidate Key
a superkey for which no proper subset is a superkey…a key that is minimal .
Can be more than one for a relation
Primary Key
a candidate key chosen to be the main key for the relation.
One for each relation
Keys can be composite
Normalization
Normalization is a formalized procedure to eliminating redundancy from data by the progressive use of ‘non-lose decomposition’, which involves splitting records without losing information.
In reducing, the data model to the state where each bit of information is only held in one place, the update process is much simpler, more efficient and inconsistencies in the database are impossible.
Normalization is a technique for producing a set of relations with desirable properties, given the data requirements of an enterprise.
There are 2 different kinds of dependencies involved up to 5 NF
Functional dependency
Multivalued dependence
Lossless-join and Dependency Preservation Properties
When creating ‘smaller relations’ from original relations through decomposition with normalization, we need to recognize two important properties of decomposition:
- Lossless-join property enables us to find any instance of original relation from corresponding instances in the smaller relations. (“Re-build”)
- Dependency preservation property enables us to enforce a constraint on original relation by enforcing some constraint on each of the smaller relations. (“Preserve constraints”)
First Normal Form
A relation schema R is in 1NF if the domains of all attributes are atomic.
A relation in which the intersection of each row and column contains one and only one value.
Solution
Remove the repeating group
In case of multi-valued
Create new relation
Columns = Key + multi-valued
Take its determinant with it
Second Normal Form(2NF)
A relation is in second normal form if and only if
It is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key.