SQL Server Mentalist

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.





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



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.



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 |