Today every data is stored in a database .so to make it consistent is important for any database because when data is not consistent it not provide appropriate result to the query .
So here we tell you how to make a table in a ITS NORMAL FORM this process called Database Normalization.
A Database management system is able to achieve its objectives only it has not this two problems:-->>
-->> repitation of data
-->> redudancy of data
Because of this two problems there are several problem arises for example-: inconcistency of data because when a data is repeated saveral places the problem of updating in database occurs because the same type data can not equaly updated at a time may be at one of the places the user did any maistake.
TO solve this problem the data stored in RDBMS in table in a normal form .To bring a table in its normal form called (Normalization ).In this process the complex table is divided into some parts and make a relationship among them.
The objective of Database to give the appropriate result to the user .
Here we are going to talk about database Normalization .
For making Database in its Normal form there is need for a language named SQL (strutured query language)here we also discuss about SQL.
OK come Let's see what is Normalization:-
THe main cause of problems arise in Database is the inconsistency of Database many other problem arise throw this problem.For example Entity (customer's address) stored in three tables ,let the address is changes in future so may be we change the address from two tables but one table may be miss to update in that case the security of database is not safe this type of data cause problem to update.
ID NAME ADDRESS ACC_TYPE ACC_NO.
1101 Ajay 29,K Street C 245960
1101 Ajay 29,K Street S 326221
1201 Anil A-44,RAj Nagar C 669245
1201 Anil A-44,RAj Nagar S 23781
1201 Anil A-44,RAj Nagar F 55421
IT is the result of a bad table .In this table repitation of data can be seen, we can break this table in two parts:-
ID NAME ADDRESS ACC_NO. ACC_TYPE ID
1101 Ajay 29,K street 245960 C 1101
1201 Anil A-44,Raj nagar 326221 S 1101
669245 C 1201
23781 S 1201
55421 F 1201
IT is the example of a simple table.Suppose are thousands of records so this repitation waste the storage space .
To get rid of this problem.Dr.EF Codd in 1970 give some laws whom we can follow to make a table in its normal form .
After Normalization some tables are developed which fulfill predefined conditions and rules.
The popular Normal model is :-
First Normal form
Second Normal form
Third Normal form
FUNCTIONAL DEPENDENCY:-
The thought of Normalization is based on the activities of the functional dependency .
suppose there is R (Relation) table its one attribute (coloum or field) A is dependend on second attribute B functionaly depended on when every value of A is depended only one value of B.
A B C
In the giaven relation B,functionaly depended on C and C is functionaly depended on B because the value of B is related with C's only one value.
a1
In this case if user wants to see the records related to a1 he has to face a condition of confusion that which record is right record a1 , b1 ,or a2,b2 If we see the dependency between B and A so we saw that A is depended on B because every value of B is related to A.
b1--------->>a1
b2-------->>a1
In this case if user wants to see the record of B he is not confused at all.
1NF FIRST NORMAL FORM:=>>
A Table is called in first normal form when it has only one value in a field .
ROLL_NO. NAME SUBJECT MARKS
1101 AMIT MATHS 90
PHYSICS 78
COMPUTER 84
1102 RITU MATHS 90
PHYSICS 61
COMPUTER 75
IS A ORDINARY TABLE
ROLL_NO. NAME SUBJECT MARKS
1101 AMIT MATHS 90
1101 AMIT PHYSICS 78
1101 AMIT COMPUTER 84
1102 RITU MATHS 90
1102 RITU PHYSICS 61
1102 RITU COMPUTER 75
THE above table is in first normal form
II ND (SECOND NORMAL FORM)
A Table called in second normal form when it is in first normal form and its every atrribute is fully depended on the table's primary key (pk) .suppose AB is the composite primary key if tables other attribute column C and D then C and D should be functionaly depended on AB. If C and D is functionaly depended on A and B so this table is not in second normal form.
STU_CODE STUDENTS CODE
PROJ_CODE PROJECT CODE
LANGUAGE WHICH LANGUAGE USED IN PROGRAMING
INSTRUCTER_ID FACULTY OF THE PROJECT
DURATION TIME FOR SUBMISSION OF PROJECT
THE GIVEN SCHEMA TABLE IS SOMETHING LIKE THIS:=
STU_COD PRO_COD LANGUAGE INSTRUCTER ID DURATION
101 P11 C++ I-1 7
102 P9 JAVA I-2 6
405 P11 RDBMS I-4 15
209 P9 VC++ I-3 10
209 P13 VC++ I-3 12
102 P13 JAVA I-2 15
This project's composit key (stu_code+pro_code) is its primary key during the activity of this database here are some of the problems face :=>>
To solve this tables errors :
Taken out the records which is depended only one value of table (stu_code or pro_code).
And let the table as it is after removing the records point out above so we can got our IInd normal table
STU_COD LANGUAGE INSTRUCTER ID
101 C++ I-1
102 JAVA I-2
405 RDBMS I-4
209 VC++ I-3
209 VC++ I-3
102 JAVA I-2
So here we tell you how to make a table in a ITS NORMAL FORM this process called Database Normalization.
INTRODUCTION
A Database management system is able to achieve its objectives only it has not this two problems:-->>
-->> repitation of data
-->> redudancy of data
Because of this two problems there are several problem arises for example-: inconcistency of data because when a data is repeated saveral places the problem of updating in database occurs because the same type data can not equaly updated at a time may be at one of the places the user did any maistake.
TO solve this problem the data stored in RDBMS in table in a normal form .To bring a table in its normal form called (Normalization ).In this process the complex table is divided into some parts and make a relationship among them.
The objective of Database to give the appropriate result to the user .
Here we are going to talk about database Normalization .
For making Database in its Normal form there is need for a language named SQL (strutured query language)here we also discuss about SQL.
OK come Let's see what is Normalization:-
THe main cause of problems arise in Database is the inconsistency of Database many other problem arise throw this problem.For example Entity (customer's address) stored in three tables ,let the address is changes in future so may be we change the address from two tables but one table may be miss to update in that case the security of database is not safe this type of data cause problem to update.
ID NAME ADDRESS ACC_TYPE ACC_NO.
1101 Ajay 29,K Street C 245960
1101 Ajay 29,K Street S 326221
1201 Anil A-44,RAj Nagar C 669245
1201 Anil A-44,RAj Nagar S 23781
1201 Anil A-44,RAj Nagar F 55421
IT is the result of a bad table .In this table repitation of data can be seen, we can break this table in two parts:-
ID NAME ADDRESS ACC_NO. ACC_TYPE ID
1101 Ajay 29,K street 245960 C 1101
1201 Anil A-44,Raj nagar 326221 S 1101
669245 C 1201
23781 S 1201
55421 F 1201
IT is the example of a simple table.Suppose are thousands of records so this repitation waste the storage space .
To get rid of this problem.Dr.EF Codd in 1970 give some laws whom we can follow to make a table in its normal form .
After Normalization some tables are developed which fulfill predefined conditions and rules.
The popular Normal model is :-
First Normal form
Second Normal form
Third Normal form
FUNCTIONAL DEPENDENCY:-
The thought of Normalization is based on the activities of the functional dependency .
suppose there is R (Relation) table its one attribute (coloum or field) A is dependend on second attribute B functionaly depended on when every value of A is depended only one value of B.
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
In the giaven relation B,functionaly depended on C and C is functionaly depended on B because the value of B is related with C's only one value.C F.D on B B F.D on C
Now we can see the attribute A and B the A is not functionaly depended on B because the one value of A related with two value of B b1 b2.
a1
a1 >>>>>>>>>>>>>|
b2
In this case if user wants to see the records related to a1 he has to face a condition of confusion that which record is right record a1 , b1 ,or a2,b2 If we see the dependency between B and A so we saw that A is depended on B because every value of B is related to A.b1--------->>a1
b2-------->>a1
In this case if user wants to see the record of B he is not confused at all.
1NF FIRST NORMAL FORM:=>>
A Table is called in first normal form when it has only one value in a field .
ROLL_NO. NAME SUBJECT MARKS
1101 AMIT MATHS 90
PHYSICS 78
COMPUTER 84
1102 RITU MATHS 90
PHYSICS 61
COMPUTER 75
IS A ORDINARY TABLE
ROLL_NO. NAME SUBJECT MARKS
1101 AMIT MATHS 90
1101 AMIT PHYSICS 78
1101 AMIT COMPUTER 84
1102 RITU MATHS 90
1102 RITU PHYSICS 61
1102 RITU COMPUTER 75
THE above table is in first normal form
II ND (SECOND NORMAL FORM)
A Table called in second normal form when it is in first normal form and its every atrribute is fully depended on the table's primary key (pk) .suppose AB is the composite primary key if tables other attribute column C and D then C and D should be functionaly depended on AB. If C and D is functionaly depended on A and B so this table is not in second normal form.
STU_CODE STUDENTS CODE
PROJ_CODE PROJECT CODE
LANGUAGE WHICH LANGUAGE USED IN PROGRAMING
INSTRUCTER_ID FACULTY OF THE PROJECT
DURATION TIME FOR SUBMISSION OF PROJECT
THE GIVEN SCHEMA TABLE IS SOMETHING LIKE THIS:=
PROJECT
STU_COD PRO_COD LANGUAGE INSTRUCTER ID DURATION
101 P11 C++ I-1 7
102 P9 JAVA I-2 6
405 P11 RDBMS I-4 15
209 P9 VC++ I-3 10
209 P13 VC++ I-3 12
102 P13 JAVA I-2 15
This project's composit key (stu_code+pro_code) is its primary key during the activity of this database here are some of the problems face :=>>
- The record of a student can't be stored whose instructer is not declared .
- One student data is repeated at many times.
- one pro_code and stu_code is repeated at many times.
- If one stu_code is changed it is difficult to update the table.
To solve this tables errors :
Taken out the records which is depended only one value of table (stu_code or pro_code).
And let the table as it is after removing the records point out above so we can got our IInd normal table
student table
STU_COD LANGUAGE INSTRUCTER ID
101 C++ I-1
102 JAVA I-2
405 RDBMS I-4
209 VC++ I-3
209 VC++ I-3
102 JAVA I-2
101 C++ I-1
102 JAVA I-2
405 RDBMS I-4
209 VC++ I-3
209 VC++ I-3
102 JAVA I-2
project Table
STU_COD PRO_COD DURATION
101 P11 7
102 P9 6
405 P11 15
209 P9 10
209 P13 12
102 P13 15
IIIrd Normal Form :-
In second normal form we see the dependency of the table's primary key to other attributes.In the IIIrd Normal Form we discuss further and we saw the other attributes dependency as well as pk.
A table is in its third normal form when its all non primary key attribute is functionaly depended on primary key attribute .
Every III Normal form table should be in IInd Normal Form and Ist Normal form :-
101 C++ I-1
102 JAVA I-2
405 RDBMS I-4
209 VC++ I-3
209 VC++ I-3
102 JAVA I-2
When we look at the above table we can see that attribute language and Instructer Id is depended on each other because only one value is related to anothers one value.
C++----->>I-1
java------>>I-2
VC++---->>I-3
RDBMS----->>I-4
SO IT IS NOT A IIIrd NORMAL FORM OF THE TABLE.
HERE WE GOING TO PRESENT THE IIIrd NORMAL FORM OF A TABLE:=>>
STU_COD LANGUAGE
101 C++
102 JAVA
405 RDBMS
209 VC++
209 VC++
102 JAVA
101 C++
102 JAVA
405 RDBMS
209 VC++
209 VC++
102 JAVA
LANGUAGE INSTRUCTER ID
C++ I-1
JAVA I-2
RDBMS I-4
VC++ I-3
VC++ I-3
JAVA I-2
THE ABOVE TABLE IS IN IIIrd NORMAL FORM
Here every value is functionaly depended on primary key.
.
No comments:
Post a Comment