1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Normalization in detail

Functional Dependency
---------------------
    An attribute A of a relation R is said to be functionally dependent
    on another attribute B (of same relation),
    if and only if, with each value of B in R,
    precisely one value of A is associated.

    For one value of A in R, there may be several values of B in R associated.

e.g.,    empno        name        deptno        doj

    Here name,deptno and doj are functionally dependent on empno.
    For one department_id there may be multiple values in empno.


It is represented as,        empno    --------------->    name
                    --------------->    deptno
                    --------------->    doj





Full Functional Dependency
---------------------------

    The attribute A is fully functionally dependent upon B
    if it is functionally dependent upon B
    and not on any subset of B.
    This applies in cases where B is a composite attribute.

e.g.,        student_roll
                --------------->term
                --------------->grade
        course_no


    Both term and grade are fully functionally dependent on student_roll and course_no.

    If we assume that a course is offered in only one term,
    then the attribute term becomes functionally dependent on course_no only
    and thus is not fully functionally dependent on the composite attribute.




Properties of Functional Dependencies
-------------------------------------

    1. Transitivity
    ----------------

if x -----> y and y -----> z then x ----->z

    2. Additivity
    -------------

if x -----> y and x -----> z then x -----> yz

    3. Distributivity
    ------------------

if x -----> yz then x -----> y and x ----->z

    4. Pseudo transitivity
    ----------------------

if x -----> yand yw -----> z then xw -----> z

    5. Augmentation
    ----------------

if x -----> y and w -----> z then xw -----> yz




Multivalued Dependency
-----------------------

    In a relation, if an attribute has a well defined,
    corresponding set of values in a second attribute,
    then second attribute is said to have a multivalued dependence on first attributel.

            attribute1 ------>-------> attribute2

e.g.,            course ------>-------> faculty







Unnormalized table
-------------------

Table Name: Faculty

Faculty    Module    Track    Area    Off_loc
=========================================
F1    SQL    Dev    MUM    MH
    PLS    Dev    MUM    MH
    JDBC    Jav    MUM    MH
-----------------------------------------
F2    SQL    Dev    HYD    AP
    JDBC    Jav    HYD    AP
-----------------------------------------
F3    PLS    Dev    KOL    WB
    JDBC    Jav    KOL    WB
    RMI    Jav    KOL    WB
-----------------------------------------
F4    PT    Dba    BBS    OR
-----------------------------------------

Disadvantage: such a table will make relational operations on tables rather complicated.



1NF
-------

Table Name: Faculty

Faculty    Module    Track    Area    Off_loc
=========================================
F1    SQL    Dev    MUM    MH
F1    PLS    Dev    MUM    MH
F1    JDBC    Jav    MUM    MH
-----------------------------------------
F2    SQL    Dev    HYD    AP
F2    JDBC    Jav    HYD    AP
-----------------------------------------
F3    PLS    Dev    KOL    WB
F3    JDBC    Jav    KOL    WB
F3    RMI    Jav    KOL    WB
-----------------------------------------
F4    PT    Dba    BBS    OR
-----------------------------------------


Defination:
For a relation in its first normal form, every value in it is atomic or nondecomposable.


    Faculty
        ----------> Area (on faculty)
        ----------> Off_loc (on faculty)
        ----------> Track (on both)
    Module


Disadvantages:
---------------
    1. One can not insert a fact that a faculty member specialises
    in a particular area, unless he also teaches a course.
    This happens because the primary key for the relation is
    a combination of faculty and module and we can not have a null value for module.

    2. In the event of a faculty member discontinuing a particular module,
    the corresponding tuple has to be deleted.
    However, if this tuple is the only tuple for the faculty member
    then the deletion results in our losing the information that
    the particular faculty member specialises in a specific area.

    3. The Off_loc or area attribute values appear for a faculty member
    as many times as the number of tuples for the particular faculty member.
    This causes data redundancy.
    If a faculty member changes his area of specialisation,
    then all the tuples for the faculty members have to be searched
    and changed accordingly.
    This may create also data inconsistancy.

    This problems occured as area and off_loc are not fully functionally
    dependent on the composite primary key.



2 NF
-------

Table Name: Offerings

Faculty    Module    Track    
========================
F1    SQL    Dev    
F1    PLS    Dev    
F1    JDBC    Jav    
-------------------------
F2    SQL    Dev    
F2    JDBC    Jav    
-------------------------
F3    PLS    Dev    
F3    JDBC    Jav    
F3    RMI    Jav    
-------------------------
F4    PT    Dba    
-------------------------

Table Name: Faculty

Faculty    Area    Off_loc
=======================
F1    MUM    MH

-----------------------
F2    HYD    AP

-----------------------
F3    KOL    WB

-----------------------
F4    BBS    OR
-----------------------

Defination:
    A relation is said to be in the second normal form
    if it is in the first normal form and if each of its nonkey attributes
    is fully functionally dependent on its primary key.

-- lossless join
-- the candidate keys are fully functionally dependent on their respective primary keys.


    faculty                
        --------> track        faculty ---------> area
    module                 ---------> off_loc

    
disadvanges
-----------
    1. If an off_loc has been allotted to a particular area without a faculty,
    this info can not be inserted.

    2. From Faculty table, if we delete any faculty, for whom only one tuple exists,
    the area and off_loc information will also be deleted.

    3. Updation in this table may create data redundancy and data inconsistency problem.

    This problem occured because the attribute off_loc is
    actually functionally dependent upon area and
    as a consequence of this is dependent upon faculty.
    Therefore off_loc is transitively dependent on faculty.
    To remove the transitivity, we further split faculty table into two relations:
    Fac_Area and Area-Loc.


3 NF
----


Table Name: Offerings

Faculty    Module    Track    
========================
F1    SQL    Dev    
F1    PLS    Dev    
F1    JDBC    Jav    
------------------------
F2    SQL    Dev    
F2    JDBC    Jav    
------------------------
F3    PLS    Dev    
F3    JDBC    Jav    
F3    RMI    Jav    
------------------------
F4    PT    Dba    
------------------------


Table Name: Fac_area

Fac_name        Area
========================
F1        MUM
F2        HYD
F3        KOL
F4        BBS


Table Name: Area_Loc

Area    Off_Loc
================
MUM    MH
HYD    AP
KOL    WB
BBS    OR

Defination:
A relation is in the third normal form if it is in the second normal form
and its each nonkey attribute is nontransitively dependent on the primary key.

However, the relation obviously contains data redundancy.

1. If a faculty member withdraws from SQL, all the relevant tuples will have to be searched for and deleted.

2. If the module SQL is also offered in DBA track, as many tuples as there are faculty members teaching SQL will have to be created to show that SQL is offered in DBA.

These problems arise because of the multivalued dependencies involved in the relation Offerings. To overcome this problem, the relation Offerings can be split into two relations Course_Fac and Course_Track.


4 NF
----

Table Name: Course_Fac

FAC_NAME    module
================
F1    SQL    
F1    PLS    
F1    JDBC    
----------------
F2    SQL    
F2    JDBC    
----------------
F3    PLS    
F3    JDBC    
F3    RMI    
----------------
F4    PT    

Table Name: Course_Track

COURSE    TRACK
================
SQL    DEV
PL/SQL    DEV
FORMS    DEV
FUN1    DBA
FUN2    DBA
PT    DBA
JDBC    JAV
RMI    JAV

Table Name: Fac_area

Fac_name    Area
========================
F1        MUM
F2        HYD
F3        KOL
F4        BBS

Table Name: Area_Loc

Area    Off_Loc
================
MUM    MH
HYD    AP
KOL    WB
BBS    OR

A normal form is in the fourth normal form if and only if, it is in the first normal form and whenever a multivalued dependency x ----->----> y, then all the attributes of the relation are also functionally dependent on x.


BCNF
----

A normalized relation is in Boyce_Codd Normal Form if every determinant in it is a candidate key.


A determinant is an attribute of a relation on which some other atribute of the relation is
fully functionally dependent. A determinant can be a composite attribute.

3rd NF forbids transitive dependencies of the non-key attributes on the primary key of the
relation.

However, it is not satisfactory for relations involving two overlapping candidate keys.
For overlapping, the two candidates have to be composite keys.

This happens because the defination permits a non-functional dependence of an attribute on
the primary key, if the attribute itself is a part of some other key in the relation and
thus is not a nonkey attribute.


Table Name: Student
--------------------

ROLL    NAME        COURSE GRADE
===================================
10012    Praveen        SQL    A
10012    Praveen        PLSQL    B
10012    Praveen        JDBC    B
10013    Deepak        SQL    A
10013    Deepak        Fun1    A
10014    Nishith        Fun1    B
10014    Nishith        Fun2    A
10014    Nishith        PT    B
10015    Ashok        RMI    C

Let us assume that in the above relation, both roll and name are unique.
Therefore, there are two candidate keys which are each composite and are overlapping.
They are:
1. roll and course
2. name and course

This relation is in 3 NF according to the defination of 3 NF. The only nonkey attribute is
grade which is non_transitively dependent upon the primary key which can be taken to be, say,
the composite key comprising roll and course.

However, the relation poses problems with the insertion, deletion and modification operations,
if GRADE is not fully functionally dependent on the primary key.

Problems
--------
1. Suppose one wants to insert the information that a student Reema has a roll number 10015.
It can't be done unless she has undertaken a course.

2. If a student has taken only one course, there will be only one tuple correpsonding
to the student in the relation. If the student drops out of the course,
the tuple has to be deleted. But the deletion will cause the loss of information that
the student has a particular roll.

3. In the relation, it is found that the name of roll 10014 is Nishitha instead of Nishith.
Then all the three tuples of 10014 have to be searched for and the name changed.
Otherwise there will be a data inconsitency amongst the tuples corresponding to 10014.

These problems can be avoided if the relation is split into two relations as followes:

Table Name: Student

ROLL    NAME    
================
10012    Praveen        
10013    Deepak        
10014    Nishith        


Table Name: Grade

ROLL    COURSE    GRADE
======================
10012    SQL    A
10012    PLSQL    B
10012    JDBC    B
10013    SQL    A
10013    Fun1    A
10014    Fun1    B
10014    Fun2    A
10014    PT    B




                    ***********

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy