Home

Normalization in RDBMS

Normalization in RDBMS

 

Normalization in RDBMS  is the process of decomposing a relation(table) based on functional dependency and primary key.

  • Un-Normalized Form
  • First Normal Form (1 NF)
  • Second Normal Form (2 NF)
  • Third Normal Form (3 NF)
  • Boyce – Codd Normal Form (BCNF)
  • Fourth Normal From (4 NF)
  • Fifth Normal Form (5 NF)

 

Un-Normalized Form
Un-Normalized relation contain non atomic values.Each row may contain multiple set of values for some of the columns.These multiple values in a single row are called non atomic value.
 

First Normal Form
A Relation is said to be in 1NF if the values in the domain of each attribute of relation are atomic.Each cell of the table must have single value.No two rows in a table may be identical.

Second Normal Form
A relation R is said to be in 2NF if it is in 1NF and there should not be any partial dependency. Here all the non key attributes are dependent on the key alone. No  attribute is depend upon a part of the key. Any relation having a key with single attribute is in 2NF.

Third Normal Form
A relation R is in 3NF if it is in 2NF and has no transitive dependency.Here all the non-key attributes are depend on the key alone.There should not be any dependency among the non-key attributes.

Boyce – Codd Normal Form BCNF
A relation R is in BCNF if every determinant is  a  candidate key.

Problem with BCNF: Given a relation R , Functional Dependency F, BCNF may or may not preserve all given functional dependencies.

Fourth Normal From
A Relation is in 4NF if it is in BCNF and has no multi valued dependency.

Fifth Normal Form
It deals with join dependency. A relation R is in 5NF if it has no join dependency.
Loss less Join Dependency  : When we join the decomposed relation then we must get the original relation without any loss.

Keys in RDBMS

KEYS in RDBMS: Primary Key, Candidate Key, Super Key

 

  • Super Keys
  • Candidate Key
  • Primary Key
Super Keys : Super key stands for superset of a key.
A Super Key is  a set of one or more attributes that are taken collectively and can identify all other attributes uniquely.
 

Candidate Keys
Candidate Keys are  super keys for which no proper subset is a super key. In other words candidate keys are minimal super keys.

Primary Key:
It is a candidate key that is chosen by the database designer to identify entities with in an entity set. Primary key is the minimal super keys. In the ER diagram primary key is represented by underlining the primary key attribute. Ideally a primary  key is composed of only a single attribute. But it is possible to have a  primary key composed of more than one attribute.
 
Composite Key
Composite  key consists of more than one attributes.

Example: Consider a Relation or Table R1. Let A,B,C,D,E are the attributes of this relation.
 
R(A,B,C,D,E)
A→BCDE   This means the attribute 'A' uniquely determines the other attributes B,C,D,E.
BC→ADE   This means the attributes 'BC' jointly determines all the other attributes A,D,E in the relation.

Primary Key  :A                         
Candidate Keys  :A, BC
Super Keys : A,BC,ABC,AD

ABC,AD are not Candidate Keys since both are not minimal super keys.

 

Functions of DBA

Functions of a Database Administrator

One of the main reasons for using DBMS is to have a central control of both data and the programs accessing those data. A person who has such control over the system is called a Database Administrator(DBA). The following are the functions of a Database administrator

  • Schema Definition
  • Storage structure and access method definition
  • Schema and physical organization modification.
  • Granting authorization for data access.
  • Routine Maintenance

Schema Definition
The Database Administrator creates the database schema by executing DDL statements.  Schema includes the logical structure of database table(Relation) like data types of attributes,length of attributes,integrity constraints etc.

Storage structure and access method definition
Database tables or indexes are stored in the following ways: Flat files,Heaps,B+ Tree etc..                                            

Schema and physical organization modification
The DBA carries out changes to the existing schema and physical organization.

Granting authorization for data access
The DBA provides different access rights to the users according to their level. Ordinary users might have highly restricted access to data, while you go up in the hierarchy to the administrator ,you will get more access rights.

Routine Maintenance
Some of the routine maintenance activities of a DBA is given below.

 Taking backup of database periodically
 Ensuring enough disk space is available all the time.
 Monitoring jobs running on the database.
 Ensure that performance is not degraded by some expensive task submitted by some users.
 Performance Tuning

 

DBMS Functions

Database Management System Functions

 

The Database Management System performs the following functions. 

  •  Data Dictionary Management
  •  Data Storage Management
  •  Data Transformation and Presentation
  •  Security Management
  •  Multi User Access Control
  •  Backup and Recovery Management
  •  Data Integrity Management
  •  Database Access Languages and Application Interface
  •  Database Communication Interface

Data Dictionary Management
The data dictionary stores the definitions of data elements and their relationships.This information is termed as metadata.The metadata includes definition of data, data types, relationship between data, integrity constraints etc. Any changes made in a database structure are automatically reflected in the data dictionary. In short the DBMS provides data abstraction and it removes structural and data dependency from the system.

Data Storage Management
The DBMS creates the complex structures required for data storage. The users are freed from defining,programming and implementing the complex physical data characteristics.

Data Transformation and Presentation
DBMS supports data independence.Hence  the DBMS translate logical request into commands that physically locate and retrieve the requested data. The DBMS formats the physically retrieved data according to the logical data format specifications.


Security Management
The DBMS creates a security system that enforces user security and data privacy within the database. Security rules determine the access rights of the users. Read/write  access is given to the user is specified using access rights.



Multiuser Access Control
The DBMS ensures that multiple users can access the database concurrently without compromising the integrity of the database. Hence the database ensures data integrity and data consistency.

Backup and Recovery Management
The DBMS provide backup and data recovery procedures to ensure data safety and integrity. DBMS system provide special utilities which allow the DBA to perform routine and special backup and restore procedures. Recovery Management deals with the recovery of the database after a failure.

Data Integrity Management
The DBMS promotes and enforce integrity rules to eliminate data integrity problems, thus minimizing the data redundancy and maximizing data consistency.

Database Access Languages and Application Interface
The DBMS provides data access via query language. A query language is a non-procedural  language, that is the user only need to specify what must be done without specifying how it is to be done. The DBMS's query language contains two components: a data definition language(DDL) and a data manipulation language(DML). The DBMS also provide data access to programmers via programming languages.

Database Communication Interfaces
Different users may access the database through a network environment.So the DBMS provide communication functions to access the database through computer network environment.