NORMALIZATION
Normalization is a systematic approach of decomposing tables to
eliminate data redundancy(repetition) and undesirable
characteristics like Insertion, Update and Deletion Anomalies.
1NF(first normal form):
In this normal form we tackle the problem of atomicity. Atomicity means a
single cell cannot contain multiple values. If a table contains composite or
multi valued attribute, it violates the normal form.
In the above table we can clearly see that the subject column has two
values. Thus it violated the 1NF. Now if we apply 1NF the above table
can be converted as below
By this we have achieved atomicity.
2NF(second normal form):
The first condition in 2NF is that the table should be in 1NF, the table
also should not contain partial dependency.
Subject_id Student_id Marks Subject Teacher
405 1203 50 Computer Niraj sir
405 1325 45 Computer Niraj sir
505 1325 55 English Suraj sir
The primary key for this table is a composition of two columns which is student_id &
subject_id but the teacher and subject only depends on subject_id. This is Partial
Dependency, where an attribute in a table depends on only a part of the primary
key and not on the whole key.
The above table can be converted to 2NF as follow:
Subject_id Student_id Marks
405 1203 50
405 1325 45
505 1325 55
Subject_id Subject Teacher
405 Computer Niraj sir
505 English Suraj sir
3NF(third normal form):
Third Normal Form is an upgrade to Second Normal Form. When a table is
in the Second Normal Form and has no transitive dependency, then it is in
the Third Normal Form.
Match no Match stadium population
101` Nepal vs china Dashrat stadium 4000
105 Nepal vs india Tu stadium 5000
108 Nepal vs oman Gautam buddha 20000
stadium
In the above table match no is the primary key. The match and
stadium column depends on match no but the population column
depends on the stadium.This is Transitive Dependency. When a non-
prime attribute depends on other non-prime attributes rather than
depending upon the prime attributes or primary key.
The above table can be converted to 3NF as follow:
Match no Match stadium
101` Nepal vs china Dashrat stadium
105 Nepal vs india Tu stadium
108 Nepal vs oman Gautam buddha
stadium
stadium population
Dashrat stadium 4000
Tu stadium 5000
Gautam buddha 20000
stadium
DDL statements: (Data Definition Language)
DDL allows you to create SQL statements to make operations with database data
structures (schemas, tables etc.).
Create
Drop
Alter
CREATE
CREATE statement is used to create a new database or table.
Syntax: Create table table_name(
Column1 datatype,
Column2 datatype,
………
);
Example: create table student (
Roll int,
Name varchar (40)
);
DROP
DROP statement allows you to remove database, table, index or stored procedure.
Syntax: drop table table_name;
Example: drop table student;
ALTER
ALTER is used to modify existing database data structures (database, table).
To remove column from a table.
Syntax: alter table table_name drop coumn_name;
Example: alter table student drop gpa;
DML statements (Data Manipulation Language):
DML is a Data Manipulation Language, it’s used to build SQL queries to manipulate (select,
insert, update, delete etc.) data in the database.
Select
Update
Insert
Delete
SELECT
SELECT query is used to retrieve a data from SQL tables.
Syntax: select column1,column2 from table_name;
Example: select * from student;
INSERT
INSERT command is used to add new rows into the database table.
Syntax: insert into table_name values ( value1, value2,….);
Example: insert into student values (1, ‘ram’);
UPDATE
UPDATE statement modifies records into the table.
Syntax: update table_name set column=value where condition;
Example: update student set gpa= 2.6 where roll=1;
DELETE
DELETE query removes entries from the table.
Syntax: delete from table_name where condition;
Example: delete from student where roll=1;
