The Relational Model and Normalization

Oct 23 • Notes • 7269 Views • 1 Comment on The Relational Model and Normalization

A Relational Database Management System (RDBMS) is a database management system that is based on the relational model as introduced by Dr. E.F. Codd. Every model should be according to the Codd rules or it satisfies Codd Rules. But in practice there is no DBMS that satisfies all these rules.Normalization is a step wise formal process that allows us to decompose database tables in such a way that both data redundancy and update anomalies are minimized. Relational model and normalization are two important aspects of database management system.

Relational table model

Difference between DBMS and RDBMS

In DBMS, relationship between two tables or files are maintained pro-grammatically whereas in RDBMS, relationship between two tables or files can be specified at the time of table creation. DBMS does not support client/server architecture whereas RDBMS supports client/server architecture. DBMS does not support distributed database whereas RDBMS supports distributed databases. DBMS is less of security whereas RDBMS is complete secure. Each table is given an extension in DBMS whereas many tables are grouped in one database in RDBMS.

Properties of Relational tables

Data values are automatic : This property implies that composite and multi-valued attributes are not allowed. There is no repeatation of groups or arrays of columns in a relational table.
Column values are same type : All values in a column come from the same domain.
Each of the row is unique: No two rows in a relational table are identical
Each of the column has a unique name : The ordering of the column in the relational table has no meaning. Columns can be retrieved in any order and in various sequences.
The sequence of rows and columns are insignificance : This property signifies that the column name of each column should be unique.

E. F. Codd’s 12 rules for Relational Databases

There are twelve rules formulated by E.F. Codd for RDBMS in 1970. If an RDBMS satisfies all these twelve rules, then full benefits of the relational database results can be obtained. The twelve rules are listed below.

Information Representation
Guaranteed Access
Systematic Treatment of null values
Database Description rule
Comprehensive data sub language
View Updating
High level update, insert, delete
Physical data independence
Logical data independence
The distribution rule
Non subversion
Integrity rule

EF Codd 12 Rules

Key : A key is that data item which exclusively identifies a record. Different types of keys are listed below:

Super key – A super key for an entity is a set of one or more attributes whose combined value uniquely identifies the entity in the entity set. For example, for an entity set Employees, the set of attributes can be considered to be a super key, if we assume that there are no two employees with the same attributes.
Primary key – The primary key uniquely identifies each record in a table and must never be same for two records.
Candidate key: A candidate key is an attribute or set of attributes that uniquely identifies a record. These attributes or combinations of attributes are called candidate keys.
Composite key: When a record cannot be uniquely identified y a single field , in such cases a composite key is used. A composite key is a group of fields that are combined together, to uniquely identify a record.
Secondary key: A secondary key is an attribute or combination of attributes that may not be a candidate key but classifies the entity set on a particular characteristics.
Foreign key: In a relation, the column whose data values correspond to values of a key column in another relation is called a Foreign key.

Integrity Constraints

An integrity constraints is the condition that can be applied on a database schema to restrict the data according to the need. If the condition is satisfied, then only it can be stored in the database. These integrity constraints can be applied on the database when the DBA of end users define the database of the schema. The DBMS checks these constraints when a database application is run. The purpose of these constraints is to ensure that there should not be any loss in data consistency due to changes made to the database by the authorized users.

Domain constraints

A domain is a set of atomic values. By atomic we mean that each value in the domain is invisible as far as the relational model is concerned. Domain constraints specify that the values of each attributes (A) must be an atomic value from the domain.dom(A). Domain constraints are the most elementary form of integrity constraint. They are tested easily by the system whenever a new data items is entered into the database.

Normalization

Normalization is a technique to organise the contents of the table for transactional databases and data warehouses(removing redundancy). This includes creating tables and establishing relationship between those tables according to rules designed both to protect data and make the database more flexible by eliminating two factors
(i) Redundancy
(ii) Inconsistent dependency

Goals of normalization

When we normalize the database, we have four goals:

Arrangement of data into logical groupings in such a way each group describes a small part of the whole.
Minimizing the amount of duplicate data, called redundancy , stored in a database.
Organizing the data such that, when you modify it, you make changes in one place.
Building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the data in storage.

Advantages of Normalization

It makes the database free from certain addition, updation and deletion anomalies.
It reduces the need to restructure the database as new kinds of data are introduced.
It makes the database more informative for users including different users and making different queries.
It avoids biasing the database design in favour of certain queries at the interference of other.
It permits the operations needed for data access to be simpler than they would otherwise have to be.

Types of Normal Form
Normally, there are five types of normal form. These are,

First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF) and Boyes Cord Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF) and Project Join Normal Form (PJNF)

About the Author: Niroj Pahi

Niroj Kumar Pahi B-Tech Computer Science Engineering From Oxford College of Engineering and Management, Bhubaneswar, Odisha. Currently Working in Ruva Customer Services pvt. Ltd as IT promotional Consultant