Skip to content
Advertisements

DBMS/U4 Topic 8 Join Dependencies and fifth Normal form

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

COMPANY PRODUCT
C1 pendrive
C1 mic
C2 speaker
C2 speaker

Company->->Product

Table – R2

AGENT COMPANY
Aman C1
Aman C2
Mohan C1

Agent->->Company

Table – R3

AGENT PRODUCT
Aman pendrive
Aman mic
Aman speaker
Mohan speaker

Agent->->Product

Table – R1⋈R2⋈R3

COMPANY PRODUCT AGENT
C1 pendrive Aman
C1 mic Aman
C2 speaker speaker
C1 speaker Aman

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

AGENT COMPANY PRODUCT
A1 PQR Nut
A1 PQR Bolt
A1 XYZ Nut
A1 XYZ Bolt
A2 PQR Nut

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

Table – R1

AGENT COMPANY
A1 PQR
A1 XYZ
A2 PQR

Table – R2

AGENT PRODUCT
A1 Nut
A1 Bolt
A2 Nut

Table – R3

COMPANY PRODUCT
PQR Nut
PQR Bolt
XYZ Nut
XYZ Bolt

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.

Advertisements

1 Comment »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: