fifth normalization form

Posted on 2005-05-04
Last Modified: 2008-02-01
I like to  know what would cause someone to use the fifth normalization? I can't really understand why someone would break a table down like that just to put it back together with a "join". I can see how it is done, but what logic would someone have... what reason is there?
Question by:mhho
    LVL 8

    Expert Comment

    Normalisation is a subject open to interpretation. It is generally accepted that 3nf is usually the most efficient level of normalisation, however this in not always the case and is always open to developer opinion. Someone taking a design to 5nf would generally be regarded as a pedantic w**ker, doing it for kicks not for any practical purpose.

    LVL 14

    Accepted Solution

    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 Ri are subsets of the set of attributes of R.

    A relation R is in 5NF (or project-join normal form, PJNF) if for all join dependencies at least one of the following holds.

    (a) (R1, R2, ..., Rn) is a trivial join-dependency (that is, one of Ri is R)
    (b) Every Ri is a candidate key for R.

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

    dept 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

    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.

    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 loosing some important information. 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.

    LVL 8

    Expert Comment

    Someone who thinks the higher the number, the better it must be! A VP recently asked what it would take to get one of our databases to fifth normal form.... I'll let you imagine the reaction of the DBA.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    Read about achieving the basic levels of HRIS security in the workplace.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now