[go: up one dir, main page]
Scribd
Upload
31 views2 pages

Lecture Notes For DBMS: Join Dependency and Fifth Normal Form (Project Join Normal Form)

The document discusses the fifth normal form (5NF) and join dependencies. The 5NF deals with decomposing relations that cannot be decomposed using other normal forms due to join dependencies between attributes. A relation is in 5NF if it cannot be decomposed further based on join dependencies between attribute sets. An example shows a relation representing departments, subjects, and students that satisfies 5NF by being decomposed based on a join dependency between the attributes.

Uploaded by

Vijay Vikram Singh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
31 views2 pages

Lecture Notes For DBMS: Join Dependency and Fifth Normal Form (Project Join Normal Form)

The document discusses the fifth normal form (5NF) and join dependencies. The 5NF deals with decomposing relations that cannot be decomposed using other normal forms due to join dependencies between attributes. A relation is in 5NF if it cannot be decomposed further based on join dependencies between attribute sets. An example shows a relation representing departments, subjects, and students that satisfies 5NF by being decomposed based on a join dependency between the attributes.

Uploaded by

Vijay Vikram Singh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Lecture Notes For DBMS

Join Dependency and Fifth Normal form(Project Join Normal Form):


 The normal forms discussed so far required that the given relation R if not in the given
normal form be decomposed in two relations to meet the requirements of the normal
form. In some rare cases, a relation can have problems like redundant information and
update anomalies because of it but cannot be decomposed in two relations to remove the
problems. In such cases it may be possible to decompose the relation in three or more
relations using the 5NF.

 The fifth normal form deals with join-dependencies which is a generalisation of the
MVD. The aim of fifth normal form is to have relations that cannot be decomposed
further. A relation in 5NF cannot be constructed from several smaller relations.

 A relation R satisfies join dependency *(R1, R2, ..., Rn) if and only if R is equal to the join
of R1, R2, ..., Rn where each Ri is a subset of the set of attributes of R

 A relation R is in 5NF (or project-join normal form, PJNF) if for all join dependencies of
the form *(R1, R2, ..., Rn), where each Ri is a subset of the set of attributes of R and
R = R1⋃ R2⋃...⋃Rn, at least one of the following holds.

o *(R1, R2, ..., Rn) is a trivial join-dependency (i.e., one of Ri is R)


o Every Ri is a super key for R.

 An example of 5NF can be provided by the example below that deals with departments,
subjects and students.

Department Subject Student


Comp. Sc. CP1000 John Smith
Mathematics MA1000 John Smith
Comp. Sc. CP2000 Arun Kumar
Comp. Sc. CP3000 Reena Rani
Physics PH1000 Raymond Chew
Chemistry CH2000 Albert Garcia

o The above relation says that Comp. Sc. offers subjects CP1000, CP2000 and
CP3000 which are taken by a variety of students. No student takes all the subjects
and no subject has all students enrolled in it and therefore all three fields are
needed to represent the information.
o The above relation does not show MVDs since the attributes subject and student
are not independent; they are related to each other and the pairings have
significant information in them. The relation can therefore not be decomposed in
two relations
 (dept, subject), and (dept, student)
without losing some important information.

Department of Computer Science By: Dipendra Air


Lecture Notes For DBMS

o The relation can however be decomposed in the following three relations


 (dept, subject), and
 (dept, student)
 (subject, student)
and now it can be shown that this decomposition is lossless
 Consider the Loan-Info-Schema discussed earlier. Suppose it is given that following join
dependency holds on the Loan-Info-Schema-

*((loan-number,branch-name), (loan-number, customer-name), (loan-number,amount))

Then it is not in 5th normal form as all of these relation schema doesn’t represent the
super keys so we should decompose it into three relations as given by the join
dependency i.e. we should have following three relation schemas in place of given Loan-
Info-Schema:
o (loan-number, branch-name),
o (loan-number, customer-name), and
o (loan-number, amount)

Department of Computer Science By: Dipendra Air

You might also like