Answer to Question #267669 in Databases | SQL | Oracle | MS Access for Jeevan

Question

#266615

Answer 1

Fourth normal form (4NF):

Fourth normal form is a level of database normalization where there are no non-trivial multivalued dependencies other than a candidate key. It builds on the first three normal forms (1NF, 2NF and 3NF) and the Boyce-Codd Normal Form (BCNF). It states that, in addition to a database meeting the requirements of BCNF, it must not contain more than one multivalued dependency.

properties – A relation R is in 4NF if and only if the following conditions are satisfied:

1. It should be in the Boyce-Codd Normal Form (BCNF).
2. the table should not have any Multi-valued Dependency.

A table with a multivalued dependency violates the normalization standard of Fourth Normal Form (4NK) because it creates unnecessary redundancies and can contribute to inconsistent data. To bring this up to 4NF, it is necessary to break this information into two tables.

Example – Consider the database table of a class which has two relations R1 contains Employee ID(EID) and Employee name (ENAME) and R2 contains Department id(DID) and Department name (DNAME).

Table – R1(EID, ENAME)

EIDENAMEE1AE2B

Table – R2(DID, DNAME)

DIDDNAMED1DD2D

When there cross product is done it resulted in multivalued dependencies:

Table – R1 X R2

EIDENAMEDIDDNAMEE1AD1DE1AD2DE2BD1DE2BD2D

Multivalued dependencies (MVD) are:

 EID->->DID; EID->->DNAME; ENAME->->DNAME

Joint dependency – Join decomposition is a further generalization of Multivalued dependencies. If the join of R1 and R2 over C is equal to relation R then we can say that a join

dependency (JD) exists, where R1 and R2 are the decomposition R1(A, B, C) and R2(C, D) of a given relations R (A, B, C, D). Alternatively, R1 and R2 are a lossless decomposition of R. A JD ⋈ {R1, R2, …, Rn} is said to hold over a relation R if R1, R2, ….., Rn is a lossless-join decomposition. The *(A, B, C, D), (C, D) will be a JD of R if the join of join’s attribute is equal to

the relation R. Here, *(R1, R2, R3) is used to indicate that relation R1, R2, R3 and so on are a JD of R.

Let R is a relation schema R1, R2, R3……..Rn be the decomposition of R. r( R ) is said to satisfy join dependency if and only if

Example –

Table – R1

CompanyProductC1pendriveC1micC2speakerC2speaker

Company->->Product 

Table – R2

AgentCompanyAmanC1AmanC2MohanC1

Agent->->Company 

Table – R3

AgentProductAmanpendriveAmanmicAmanspeakerMohanspeaker

Agent->->Product 

Table – R1⋈R2⋈R3

CompanyProductAgentC1pendriveAmanC1micAmanC2speakerspeakerC1speakerAman

Agent->->Product 

Fifth Normal Form / Projected Normal Form (5NF):

A relation R is in 5NF if and only if every join dependency in R is implied by the candidate keys of R. A relation decomposed into two relations must have loss-less join Property, which ensures that no spurious or extra tuples are generated, when relations are reunited through a natural join.

Properties – A relation R is in 5NF if and only if it satisfies following conditions:

1. R should be already in 4NF.
2. It cannot be further non loss decomposed (join dependency)

Example – Consider the above schema, with a case as “if a company makes a product and an agent is an agent for that company, then he always sells that product for the company”. Under these circumstances, the ACP table is shown as:

Table – ACP

AgentCompanyProductA1PQRNutA1PQRBoltA1XYZNutA1XYZBoltA2PQRNut

The relation ACP is again decompose into 3 relations. Now, the natural Join of all the three relations will be shown as:

Table – R1

AgentCompanyA1PQRA1XYZA2PQR

Table – R2

AgentProductA1NutA1BoltA2Nut

Table – R3

CompanyProductPQRNutPQRBoltXYZNutXYZBolt

Result of Natural Join of R1 and R3 over ‘Company’ and then Natural Join of R13 and R2 over ‘Agent’and ‘Product’ will be table ACP.

Hence, in this example, all the redundancies are eliminated, and the decomposition of ACP is a lossless join decomposition. Therefore, the relation is in 5NF as it does not violate the property of lossless join.