Home > Link, MSBI, Optimization, Query, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, Vishal Pawar > BISQL – Laymen to SQL Developer # 31 – Functional Dependencies and Normalization For Relational Databases #2 – Levels of Relation Schema & Functional Dependency, Normal Forms Based on Primary Keys

BISQL – Laymen to SQL Developer # 31 – Functional Dependencies and Normalization For Relational Databases #2 – Levels of Relation Schema & Functional Dependency, Normal Forms Based on Primary Keys

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization

>>Chapter 4 : Index Structures of Files

>>Chapter 5 : Entity-Relationship Model

>>Chapter 6 : Relational Algebra

>>Chapter 7 : SQL

>>Chapter 8 : Functional Dependencies and  Normalization For Relational Databases<You are Here>

Continuing from my previous post on this series.

We are going to Cover the Following Points in this article

  • Levels of Relation Schema & Functional Dependency
  • Normal Forms Based on Primary Keys

    Levels of Relation Schema & Functional Dependency

    There are two levels of relation schema. They are:

    · Conceptual level schema: In this level schema it understands the database structures meaning, interrelationships and constraints. The basic components of the schema are the entity types, relationship types and attributes.

    · Physical level schema: in physical level schema it deals with specifying the internal storage, structures, indexes, access paths and file organizations for the database files. Along with this they design application programs which are implemented as transactions. This can be represented with the help of ER diagrams.

    Normal Forms Based on Primary Keys

    A relation schema R is in first normal form if every attribute of R takes only single atomic values. We can also define it as intersection of each row and column containing one and only one value. To transform the un-normalized table (a table that contains one or more repeating groups) to first normal form, we identify and remove the repeating groups within the table.

    E.g.

    Dept.

    D.Name

    D.No

    D. location

    R&D

    5

    [England, London, Delhi)

    HRD

    4

    Bangalore

    Figure A

    Consider the figure that each dept can have number of locations. This is not in first normal form because D.location is not an atomic attribute. The dormain of D location contains multivalues.

    There is a technique to achieve the first normal form. Remove the attribute D.location that violates the first normal form and place into separate relation Dept_location

    Functional dependency: The concept of functional dependency was introduced by Prof. Codd in 1970 during the emergence of definitions for the three normal forms. A functional dependency is the constraint between the two sets of attributes in a relation from a database.

    Given a relation R, a set of attributes X in R is said to functionally determine another attribute Y, in R, (X->Y) if and only if each value of X is associated with one value of Y. X is called the determinant set and Y is the dependant attribute.

    For eg.: Consider the example of STUDENT_COURSE database.

    STUDENT_COURSE

    Sid

    Sname

    Address

    Cid

    Course

    Maxmarks

    Marks Obtained (%)

    001

    Nupur

    Lucknow

    MB010

    Database concepts

    100

    83

    001

    Nupur

    Lucknow

    MB011

    C++

    100

    90

    002

    Priya

    Chennai

    MB010

    Database

    Concepts

    100

    85

    002

    Priya

    Chennai

    MB011

    C++

    100

    75

    002

    Priya

    Chennai

    MQ040

    Computer

    Networks

    75

    65

    003

    Pal

    Bengal

    MB009

    Unix

    100

    70

    004

    Prasad

    Bangalore

    MC011

    System Software

    100

    85

    In the STUDENT_COURSE database (Sid) student id does not uniquely identifies a tuple and therefore it cannot be a primary key. Similarly (Cid) course id cannot be primary key. But the combination of (Sid, Cid) uniquely identifies a row in STUDENT_COURSE. Therefore (Sid, Cid) is the primary key which uniquely retrieves Sname, address, course, marks, which are dependent on the primary key.

    image

    image

    Second Normal Form (2 NF)

    A second normal form is based on the concept of full functional dependency. A relation is in second normal form if every non-prime attribute A in R is fully functionally dependent on the Primary Key of R.

    Emp_Project:Emp_ProjectFigure 9.2: 2NF and 3 NF, (a) Normalizing

    EMP_PROJ into 2NF relations

    clip_image002

    Normalizing EMP_DEPT into 3NF relations

    A Partial functional dependency is a functional dependency in which one or more non-key attributes are functionally dependent on part of the primary key. It creates a redundancy in that relation, which results in anomalies when the table is updated.

    9.4.2 Third Normal Form (3NF)

    This is based on the concept of transitive dependency. We should design relational schema in such a way that there should not be any transitive dependencies, because they lead to update anomalies. A functional dependence [FD] x->y in a relation schema ‘R’ is a transitive dependency. If there is a set of attributes ‘Z’ Le x->, z->y is transitive. The dependency SSN->Dmgr is transitive through Dnum in Emp_dept relation because SSN-

    >Dnum and Dnum->Dmgr, Dnum is neither a key nor a subset [part] of the

    key.

    clip_image002[4]

    According to codd’s definition, a relational schema ‘R’ is in 3NF if it satisfies

    2NF and no no_prime attribute is transitively dependent on the primary key. Emp_dept relation is not in 3NF, we can normalize the above table by decomposing into E1 and E2.

    Note: Transitive is a mathematical relation that states that if a relation is true between the first value and the second value, and between the second value and the 3rd value, then it is true between the 1st and the 3rd value.

    Example 2:

    Consider a relation schema ‘Lots’ which describes the parts of land for sale in various countries of a state. Suppose there are two candidate keys: property_ID and {Country_name.lot#}; that is, lot numbers are unique only within each country, but property_ID numbers are unique across countries for entire state.

    Based on the two candidate keys property_ID and {country name,Lot} we know that functional dependencies FD1 and FD2 hold. Suppose the following two additional functional dependencies hold in LOTS.

    FD3: Country_name -> tax_rate

    FD4: Area -> price

    Here, FD3 says that the tax rate is fixed for a given country coutryname taxrate, FD4 says that price of a Lot is determined by its area, area

    price. The Lots relation schema violates 2NF, because tax_rate is partially

    dependent upon candidate key { Country_namelot#} Due to this, it decomposes lots relation into two relations – lots1 and lots 2.

    Lots1 violates 3NF, because price is transitively dependent on candidate key of Lots1 via attribute area. Hence we could decompose LOTS1 into LOTS1A and LOTS1B.

    A relation schema R is in 3NF when it satisfies the conditions below.

    1. It is fully functionally dependent on every key of ‘R’

    2. It is non_transitively dependent on every key of ‘R’

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: