Articles

SQL Server :- Can you explain First,Second and Third normal form in SQL server ?


In this video will talk about three important database techniques First normal form Second normal form and Third normal form We have created a simple database called as University Inside the University we have created a very simple table called as tblSchool In this table we have some 7 fields The first field says this student is belong to which Standard The second column says what is the roll number of student The third column says what is the first name, last name and the surname of the student what kind of syllabus the student has taken How much marks has acquired till now The total number of subjects which these marks acquired finally the average This table helps to store the name of the student, Total marks, Roll numbers etc To find the student name whose last name is Koirala where StudentName like %Koirala How about find people with Koirala also ensure that we don’t have any Harisingh The other two rows should be excluded We can write the SQL query. We can do search , do STR and lot of other functions like string function Then the SQL query will become complex it will become slower also The main root of the problem is the database design All the data is pushed into only one column The first name, the last name, the surname The select query finding its very difficult to segregate those columns break into pieces and do a search on that Break the student name into three more fields If we can segregate the student name into three more fields like First name, last name and surname then the search can be faster and simpler If we want to do a search it is easy now Fetch a surname where it is Koirala and the middle name is not Harisingh Our query has become simpler as compare to the first one where we were thinking about parsing something in If we divide the data into logical pieces the query can be simpler and database design will be much better, can have a better control over the data Dividing the data into logical pieces is the first rule of first normal form There are two rules to the first normal form The first rule of the first normal form is break the data into logical granular level for better data management and search query We broke the student name into first name, into middle name and into last name This breaking of the data should be done in an appropriate manner or it should be logical Fields like phone numbers Even though it has three logical pieces like Region code, Area code and Pone number The first rule of first normal form is Break the data into more logical granular level to handle the data in a better way There is the second rule also for the first normal form In the tblSchool table we have entered one more record here called as Mr. Shaam and entered the standard as Fifth Standard as compare to Shiv who is 5th Standard To fire a SQL query and get Students who are studying in 5th standard Go to the query analyzer We did a search using 5th Standard We are not able to find out Mr. Shaam and only able to find Mr. Siv The problem is with the data the way the data was entered It was not standardized The second problem is the values are also getting duplicated They are occurring again and again This complete data is getting duplicated This can be avoided by applying the second rule of the first normal form i.e. avoid repeating groups and repeating data If we are able to move this standards values to a different master table called as tblStandards To solve the duplicate data problem we have to apply The second rule of the fist normal form i.e avoid repeating data and repeating rows Create a separate table called as tblStandard We can move the complete data into tblStandard table rather than writing 5th standard again and again Because we are moving it to a centralized table We can also avoid the data entry problem One there is a duplicate data and the other one is a non-standardized data We can have only one kind of description This field will be the foreign key and the primary key will come from the master table which we have created Using the database diagram create the relationships We have added both the tables here Drag and drop standard id to create a relationships We have created a foreign key relationship with a tblStandard, tblStandard table is a primary key table with the Standard Id From there we are linking this key to the foreign key field Standard Id into tblSchool1 We will refer only the numeric values and not the actual string Both the problems are avoided now One is a duplicate data an the other one is any kind of non uniform data The relationships have been created and do some data entry One stands for 5th standard and 2 stand for 6th standard in the tblStandard table The duplicate data is removed as well as confusions are avoided Make a inner join with the tblStandard table to show the link which is looking like Written simple query which joins tblStandard Table with the tblSchool using the standard Id This is the second of the first normal form that avoid any kind of repeating data or repeating groups by moving it to master tables like standard tables Add one more record here We shouldn’t be allowed to add this data because in 1 standard we cannot have the same roll number Shiv has roll number 1 in 5th standard as well as khadak has roll number 1 in 5th standard i.e. which cannot be allowed to happen We have to avoid duplicate entries for students with the same standard id and with the same roll number We have to create a joint primary key on standard id and roll number to avoid such kind of data entry mistakes Go to the design of school1 table Create a a joint primary key on standard id and on the roll number fields Right click on these fields We have created a joint primary key If we try to add a person with the same roll nunber in the same standard an error occurred In order to understand the second problem lets do a simple select on the tblSchool table The syllabus field is more related to a standard IF 5th standard has a syllabus, 6th standard has syllabus But a student doesn’t have a specific syllabus Shiv will not have a specific syllabus or shaam as a student don’t have a specific syllabus Syllabus is more related to the standard on which the student is studying rather than directly to the student Currently our primary key is built of standard id and roll number The syllabus field is dependent more on standard and not on the roll number, roll number belongs to a student Syllabus is not connected with students directly There is a relation of the syllabus but that is not direct relationship This syllabus field is depend partially on the full primary key It is dependent on standard id but not dependent on roll number Tomorrow the 5th standard syllabus will have a extra subject like geography Update Shiv as well as Raju Add geography in their syllabus field Because this field is not related directly with the student This field is partially relying on the primary key and fully It is relying on standard id but not relying on the roll number This problem of a column depending partially on a primary key. its solved by using second normal form To implement the second normal form Get rid of the syllabus field from the main table To get rid of the syllabus field from the main table We have created a separate master table called a syllabus table We have a separate table called a syllabus table This syllabus table has two records currently 5th Standard syllabus and 6th standard syllabus Every syllabus has subjects for that create a subject table which has has three records currently physics, maths and history One syllabus will have multiple subjects For that create a intermediate table in table which connects the syllabus table with the subject table and creates one to many relationship One syllabus has multiple subject This syllabus is now linked with a standard master table We had created a a master table for standards This syllabus table is linked with the master table. We have not shown the foreign key over there the standard and the syllabus table is linked with the foreign key it is not shown in the image Actually creating these tables will link them by foreign keys The standard table entry is already there in the main table by the standard id In this way we have got rid of the syllabus field from the main table and we have ensured we have applied second normal form. Second normal form says any field in the table has to depend on the full primary key and not partially In this way we have applied second normal form and we have ensured the syllabus field is not part of the main table Now we have created the tbl_syllabus table Create the subject table Also create an intermediate table which will establish the one to many relationship between the syllabus and the subject table Now we have all the three tables Go to the database diagram and create one new database diagram and add all these new tables which we have created Now define primary keys in all these tables Without the primary keys we won’t be able to define the relationships In the tblStandard table add one more field which will link the tblStandard table with the tblSyllabus table we have deleted the syllabus from the tblSchool1 table i.e the main table There should be some linkage between the tblStandard table and the tblSyllabus table Insert one more field in the tbl_Standard table called as SyllabusId link that syllabus id with the tbl_ syllabus master table Go to the design of tbl_Standard table and add syllabus id with the data type int Go to the design again back make a relationships between the tblSyllabus and the tbl_Standard table The tbl_ syllabus table will be the primary key table and the tbl_standard table with the foreign key table This relationship is established here There are couple of other relationships also which we need to establish For that we have to firct connect the tbl_Syllabus table with the tbl_SyllabusSubject table and then in the tbl_Subject table with the tbl_Syllabus table, the tbl_SyllabusSubject table is the main connector between the tbl_Syllabus and the tbl_Subject table Connect the tbl_Syllabus Syllabusid with the SyllabusId of tbl_SyllabusSubject table We are almost done with the second normal form We have got rid of Syllabus field from the tblSchool1 table and it is only linked by the tbl_Standard table The tbl_Standard table knows which syllabus he has to contain and the tbl_Syllabus knows via the tbl_SyllabusSubject table which subjects he has In this way we implemented second normal form Now start filling some data into these tables and see how the structure is actually coming up Also make some entries into the tbl_Syllabus table Will create syllabus for two standards One is a 5th standard and the other one is a 6th standard We have entered both the syllabus as well as the subjects It is time we make entries into the tbl_SyllabusSubject table tbl_SyllabusSubject table connects both the tables and creates one to many relationship Take the 5th standard syllabus The 5th syllabus is represented in the syllabus table by the SyllabusId1 From the subject we can add the 5th standard for physics In the same way take the other one Add some entries for 6th standard syllabus We have made some entries into the tbl_SyllabusSubject table Make a inner join to see the data which is entered is proper or not The 5th standard syllabus is having physics as well as maths and the 6th standard syllabus is having Science as well as History The tblSchool table does not have any reference of the Syllabus field it is connected via the standard table This is how we have applied the second normal form and later the syllabus is further broken down by using the tblSubjects table to define subjects for each of each of these tables A field should depend completely on the primary key and not partially One of the fields is the average This average field is calculated by using total marks and total subject field Its a calculated field The third normal form says no need to have any kind of calculative fields or fields which depends on other fields To get average programmatically in program we can always divide the total marks by total subjects to get it The average field is more of a redundant field or a duplicate field To implement third normal form get rid of average field Go back to the table design To calculate the average of Student, just divide the total marks with the subjects We applied first normal form, second normal form and third normal form to make the table structure more much better First normal form says break the data in two pieces there one more role in the first normal form remove duplicate data The second normal form says any column in the table should depend on the full primary key and not partial And the third normal form says should not have any kind of calculative field No column should depend on the columns In this way by applying Normalization In this video we saw how we can make the table design much better and how to avoid redundant data In this video we saw how to implement first normal form, second normal form and third normal form in SQL Sever Thank you very much

Leave a Reply

Your email address will not be published. Required fields are marked *