Relation in 1NF have following drawbacks:
(1) Data redundancy. (2) Extra storage space. (3) Extra data entry cost. (4) Database inconsistencies.
As a result of redundancy the relations in 1NF have problems driving modifications. These are called modification anomalies. Relations in 1NF have updating, insertion and deletion anomalies. Because of these drawbacks and anomalies, tables in 1NF are difficult to manipulate (use). To remove these drawbacks and anomalies, tables in 1NF are further normalized into tables in 2NF and 3NF.
Functional Dependence: Let R be relation: A and B is said to be functionally dependent on A if each value of A is associated with one and only one value of B. [Note: B is functional dependence and A is the same as A identifies B and we write A→B] For example: consider a relation Employee Salary is functional dependence on emp # (i.e. Emp # → Salary) Emp # is not functional dependence on Salary (i.e. Salary → Emp #) emp_Name is functional dependence on Emp # etc.
Full functional dependence: Let R be a relation. An attribute or a collection of attribute B of R is said to be fully functionally dependent on another collection of attributes A is B is functionally dependent on the whole of A but not on any subset of A.
Example: consider the relation
PROGRAMMER_ACTIITY [P-A1]
There are four candidates’ keys.
Programmer # + Package #,
Programmer – Name + Package – Name,
Programmer # + Package Name and
Programmer Name + Package #.
We chose Programmer # + Package # as primary key.
Prog #, Pack #, Prog_Name, Pack_Name are the prime attributes (attributes which are mumber of atleast one candidate key). Total-hours-work (THW) is the only non-prime attribute.
THW is functional dependence on (Prog + Pack #) or on any of the candidate key.
THW is not functional dependence on Prog #.
THW is not functional dependence Prog_Name and Prog_Name is functional dependence on Prog #.
Prog # → Prog_Name
Peackage # ↔ Pack_Name etc
(1) THW is fully functionally dependent on
Prog # + Pack #
Similary, Prog_Name is full functional dependence on Prog #
Pack # is full functional dependence on Package_Name and vice-vera.
Second normal form (2NF):
A relation R in 2NF if, it is in 1NF and Every non-prime attribute of R is full Functional dependence on each candidate key of R.
Example (1): The relational programmer-activity is in 2NF, because its only non prime attribute THW is full functional dependence on each candidate keys.
Example (2): The relation supply-source is not in 2NF.
Part # + Supl # is the candidate key. Supl_Name, Supl_Details, Price are non-prime attributes. Price is functional dependence on (Part # + Supl #). But Supl_details is functional dependence on Supl # only, not on the whole candidate key (Part # + Supl #), hence the relation Supply_source is not in 2NF. Relations not in 2NF have some drawbacks (data redundancy, extra storage space, and inconsistence). It also has some modifications anomalies s.a. insertion, deletion and updating anormalizes.
For example: (1) We cannot enter details about a supplier unitl the suppliers a part. If the supplier does not supply a part, there is no key. (2) If supplier (say unique) temporarily ceases to supply a part (say Part # = 3) then the deletion of that record (5th record) will also delete the details of the supplier. It would normally be describable that Supl-details be persevered etc. these drawbacks and anormalizes (irregularities) can be removed by slitting the relation into two relations in 2Nf as shown in figure. Only price is full functional dependence on the candidate-key (Part # + Supl #). Hence these 3 attribute are kept in one relation (say Part). All other attributes are removed to the separate relation (say Supplier) which has Supl # as its key.
In general every attribute in a relation should be dependent on the entire key otherwise it should be removed to a separate relation.
Third normal form (3NF) Or (Transitive dependence (Td)):
Let A, B, C be three as attributes or distinct collection of attributes of a relation R. If C is functional dependence on B and B is functional dependence on A then C is functional dependence on A. if the inverse mapping is non simple (i.e. if A is not functional dependence on B or B is not functional dependence on C), then is said to be transitively dependent on A.
Example: consider the relation Emp_porject.
Completion-Date is functional dependence on project #. Project # is functional dependence on Emp #. Also is not functional dependence on project #. Hence Completion date is transitive dependence on Emp 3. As the relation Emp_project has Transitive dependence it is not in 3NF.
Definition: A relation R is in 3NF if, it is in 2NF amd every non-prime attribute of R is non transitively dependent on each candidate key of R.
Third normal form: A relation 2NF can have insertion, deletion and updating anomalies. To remove then the last normalization step is used, which converts 2NF to 3NF. This step removes the transitive dependence in the tables which are in 2NF.
Transitive dependence (non-full dependence): Suppose that A, B and C are three attribute or distinct collections of attributes on the relation R. if C is functional dependence on B and B is functional dependence on A then C is functional dependence on A. if the inverse mapping in non simple (i.e. A is not functional dependence on B or B is not functional dependence on C) then C is said to be transitively dependant on A. following diagram shows that C is transitive dependence on A.
If A → B → C and if B =!→ A, or C =! → B.
Then C is transitive dependence on A.
The T dependence can be removed by splitting the relation into two relations in third normal form such that:
A → B B → C
Example: Consider following relation.
A = Emp #, B = Proj #, C = CMP_Date
Then B → A i.e. A → B i.e. A → C
But B =!→ C.
C D is transitive dependence.
Here, Project # → compilation date (i.e. Completion adte is functional dependence on Proj #)
And Emp # → Project #
Project # is functional dependence on Emp #
However, Projecct # =!→ Emp #.
Completion date is transitively dependent on Emp #.
Hence this relation Employee is not in 3NF. It can be converted to 3NF buy the Transitive dependence that splitting it into two relations as follows:
Definition: A relation R is in #NF if, it is in 2NF and it has no transitive dependence
Advantages: (1) Relations in 3NF are clear, simple and easy to use. (2) Need less storage space. (3) No insertion, deletion and updating anomalies.
(Some software renders of non-relational systems recommend that the date should be in 3NF)
Non relation date Tree / Plax
↓ (first step of N)
Relation 1NF
↓ (it may not full functional dependence)
↓
Relation 2NF
↓
↓ (it may)
Relation in 3NF
These record types are connected by a binary relation.
Primary key: A key which uniquely identifies the record.
Secondary key: A key identifies group of record.
Concatenate key: A key which could represent as primary key.
* Multi valued Dependency: It’s required that tules of a certain form be present in the relation. Hence multi valued dependencies are classified under tuple generating type of dependencies. Intuitively, a MVD (abbreviation of multi valued dependency) X →→Y read as “there is a MVD of Y on X” or “X multi determines Y”, if for a given value XЄ DOM (X), a set of values, Y Є DOM (Y) can be associated and this association does not depend in any way on the values of the attribute in R-XY.
* Join Dependency: The types of constraints called joined dependencies that generalized MVD, and lead to another normal from called project join normal from (PJNF). The loss less join condition is one of the most important criteria for a good database design. A loss less join binary decomposition is equivalent to an MVD. A Relation can have a loss less join decomposition on to three or more schemes, although no such decomposition onto any pair of schemes exits.
End OF UNIT II
<<<