Lec 9: Normalization in DBMS | Need of Normalization | DBMS Tutorials

so till now in the series of DBMS lectures we have already discussed what is functional dependency how to find out functional dependency from a given relation or table what are different types of keys and DBMS and how to find out all the candidate is present in a relation when a relation is given and functional dependencies are given on that relation right so that part is very important in normalization right so today I am going to start normalization we will discuss in this video we will discuss what is normalization what is need of normalization right and for which database normalization will be needed and for which database the normalization of the tables will be needed right and also we will discuss now what is the benefit or you can say what are the advantages of normalization what are the advantages of normalizing or table right so first of all I am NOT going to tell you the definition what ever written in the books or net the definition of normalization we will discuss some problems if we take some relational schema or some table or a relation then some problems in problems in that relation and how to resolve those problems then automatically you will get what is need of normalization and what is basically normalization right so let us take one example I'm taking a table or you can see a relation or you can say I'm taking a relational schema right suppose I am taking this relational schema you can say I'm taking this table order relation name student and this table I am having these attributes of a student I am going to store information of students right student ID name total credits of that student or you can say Marx and a department name building of that department in which building that department is and room number right all this information I am taking in one table now suppose I am inserting some data in the stable C student ieading is 1 and suppose name is Rahul right and total credit of the student L suppose fine out of pain I'm taking Department the name is CSC and building is suppose B 1 and room number is 1 0 1 right again I'm inserting a second student and the name is Jia credit 8 same department CSC building would be B 1 and room number would be 1 0 1 like this suppose I have inserted 6 a student information and I want to store another student information suppose to entitiy ID is 7 and name is 1 Shikha and suppose credits are 8 and Department is civil right so now you have to repeat the same information again that is the building and room number that is 1 1 0 fine like this if you will insert the student information then you have to repeat this information again suppose student is again I am inserting a student and Department of that student is CSE now again I have to repeat the building and the room number for this one so this is what redundancy of the information right see we are repeating this information again and again you can see for this also for this record also you are repeating this information here you can say if 2 students are in fashion designing Department then you have to repeat the building and the room number for both the tuples like this suppose I am taking a you know only 8 entries suppose millions or thousands of entries are there right and in thousands of entries may be suppose 100 or 200 students are from si si department so for each record for each row what obviously you need to specify that department that is ok but with the help with this this department name you need to specify building and room number in all the five hundred rows now what is the point of repeating this information again and again right so this is what redundancy why this problem occurs because I am taking a larger schema I am going to store more information in one table only yes I'm taking only one two three four five six six attributes suppose in this table also I am taking a chori name h OD room number and h OD you can say a phone number so for CSC one h OD would be there right and if students are hundred in CS a department so for those all hundred students you have to repeat their chori name or you can say there is OD information in all the hundred tuples now what is that that is redundancy so problem is what because of this larger schema now if we're divided if we decompose our table into smaller tables then maybe you can reduce the redundancy fine and see the tendency is the main or you can say redundancy is the root cause for all the problems fine inconsistency insertion problem deletion problem update problem all the further problems will be arised because there is redundancy in your table so if you reduce if you reduce or if you remove the redundancy see I am NOT saying that the normalization will remove the redundancy completely no but it will reduce the redundancy it will reduce the duplicate data right so if we are able to reduce the redundancy then obviously we are able to remove or we are able to reduce the problems that will occur because of redundant data right now what problems can occur if we have this data if we don't have smaller tables we have one larger schema where we want to store all the information in one table only right then what the problem can occur our first problem is what is the redundancy problem now because of this another problem can be there three main problems are there one insertion updation and deletion what we call is insertion anomaly deletion anomaly and updation anomaly anomaly is what basically anomaly is what when our data is having multiple copies right and at one place we update the data but we forget to update that same data in another place right so now we have what we can't say that which data is correct because at one place we have some other value at another place we have some other value for the same data right so that is what a data anomaly so now let us discuss the first enemy that is insertion anomaly what does that mean see if suppose I want to insert a department name or a department information in this table right suppose I want to insert I want to insert an an University we have one more department that is suppose mechanical right and room number of sorry the building is B 1 and room number is 120 right only I want to insert the department information but in this table you cannot insert this information why so because in this record in this tuple obviously you need to insert credits student name and student ID right if you insert here null null null but SIDS primary key that cannot be null right so that would be problematic situation we cannot insert information of a department until we have at least one student enrolled in that department but that is a problematic situation maybe students are not there students are still you know students yet not enrolled in that department but we have Department information but we are not if not able to insert that information in the stable why so because we are taking all the information in one table only fine that is insertion anomaly I hope you got now what insertion anomaly right we want to insert some information but you cannot insert that information because for that for inserting that information you need to insert some extra information right and if you don't have that extra information then you cannot insert this information in the stable right that is wrong right so that is an insertion anomaly now second anomaly is what updation anomaly now suppose I want to update the room number right but you can say that building of CSC Department right suppose I want to make this building CSC Department has been shifted from b1 2 c1 right maybe suppose in c1 room number is 1 0 1 that's fine or you suppose in a room number is also 3 0 1 now you have to update this information in all the tuples where the department is say State Department why so because while creating this table this relational this relational schema what we have to insert the information of this department right department name building and room number once for each student of that department and suppose in CSI department I have 150 students so now in 150 rows you have this information this redundancy right this complete information so now in 150 rows you how to update this information here also see 1 & 3 0 1 here also you can say in CSI I have c1 + 3 0 1 in this table I have only three tuples having serious information but maybe when you are actually storing the information the real world entities problem right Pena's actually are designing a database and maybe you can have millions of records in that case that would be difficult to update in each tuple and suppose by chance we have updated in two tuples and we forgot to update in this couple then that would be inconsistency here for CSC building his b1 and room number is 1 0 1 here for CSC building is c1 and 3 0 1 now which information is correct we can't say right so that is what that leads to inconsistency problems data is now an inconsistent state so this is not a good database design right so that is what update anomaly now third is what believed anomaly now what is this ability to normally see let us take an example I suppose I want to are delete this student information that is 6 student ideas the idea 6 name is a cash and credit 7 right now if you want to delete this information then obviously you have to believe this complete record complete couple from the stable and so when you are going to delete this information then automatically this department information will be deleted right and only one student is there in this department right but obviously when you are deleting a student maybe student has left the college it doesn't mean that we are going to delete the department from the college obviously that is not a case so but when you delete this one then we don't have any information of EC Department left now so that is what we delete and only that is what problematic situation right that should not be a case yes you can remove the student information but this department information still exists in the college or in the university fine so this is what would delete anomaly so now I hope you got all the three anomalies now how to remove how to what is the solution of this problem how can we solve these problems see suppose you decompose the stable into two tables like this now suppose I have decomposed or you can say have divided this table into two tables one is student one is department student table is having informations to end it ID name credit and department name and department table is having information of department that is a department name building and no number now you can see we have removed all the information of all the these problems why so because we have removed the redundancy see CSC the information for Cu State Department I'm storing only once building b1 and room number one zero one here we have to store for each student but here only once civil also ones fashion designing I am storing only once and EC also for once not for each student if in CSA Department we have hundred students then obviously we will store the information of hundred student in this table but this information building and room number no need to repeat this information for all the students just make a separate table and store that information only once fine see now here obviously these two tables will be related with each other because we are working with relational database management system there all the information is stored in the form of relation and each relation are related with each other having some relation with each other right how these tables are related with the help of foreign key I haven't a discussed foreign key I will make a proper video with help of an example I'll tell you what is foreign key but for now see just keep this thing in your mind that foreign key is used to establish the relation between tables right now see here here also I have Department name he rolls on your department name right so now if you are dividing this larger schema into smaller schema or you can say into two into smaller tables like this then obviously you have to write down some rules like if we are making a table Department name building and dome number then obviously you should write a rule that Department name will act as a primary key in this table right it's not like that department name we don't have any primary key in this relation fine now see Department name is primary key for this relation so now this department name would be foreign key right we are referring to this this department name will be referring to here to this primary key in this table right I'll discuss in detail what is foreign key right but here you can see directly or see that to establish the relation between this and this we are going to use department name so department name nickname is what foreign key now what about insertion anomaly now suppose I want to insert this information any Department information so no need to do anything directly you can insert this information mechanical b1 and 120 right obviously we can insert the information in this department table no need to insert any information regarding of this mechanical Department in this student table right but it's not like that we have divided in the stable so we can delete any record from the table we can insert any record from any in any table right here also we have some referential integrity constraints right that will be applied on foreign key right that constraints we will discussed later fine but here like this you can insert this fine but here we are not able to insert the data right now what about update anomaly see suppose I want to insert this si si Department building is a c1 same and this one is 3 0 1 so that's it you have to update only one couple not all the tuples like in this case so we have solved up the updation anomaly problem right now what about this delete anomaly problem same suppose I want to delete a record of the student right so simply you can delete this tuple it doesn't mean that we have deleted the information of the department we still have EC Department information of EC Department in this department table right we have only deleted this information from student table fine so we have removed all the three anomalies by be composing the stable into two tables right so this is what normalization so normalization how you can define normalization is a process of making the table free from insert update and delete anomalies and obviously save space by reducing the redundant data or duplicate data that is what normalization fine I hope now you got or you can easily write down the definition of normalization and as well as we have divided this into smaller tables then you can say that it will simplify the queries right you will have to write down simple queries fine now it's not like that we need normalization on the data every time sometimes it is better to use denormalized data fine when see I hope you have this you have heard about oil VPN o el ap if not then I will discuss later in a complete video it is what online transactional processing and analytical processing fine here in this case we need normalized data because this is what you can say transactional means day to day transaction insert update and modify or modification of or you can say deletion of the database some data from the database right these queries are very frequent so if these queries are very frequent so obviously we want that these anomalies would not occur so in OLTP system we need a normalized data mostly or you can say every time in ona P system or you can say data warehouse when we are going to store huge date or you can say data warehouse means where there we are storing use data or historical data fine analytical queries means here we perform complex queries in which we use to join multiple tables to get the result fine so rather than dividing the table because we have if smaller tables are there then obviously you have to join the tables so it's better that a data would be in one table only so no need of the joining of the tables fine so that is why in this case we need denormalized data basically data is stored in B normalized form to make the performance that right but here the data would be normalized form to get the performance better so now I hope you got what is need of normalization right or you can say what are benefits of normalization first point you can write here is what to reduce the redundancy from the table right if you reduce the redundancy then obviously what it will compact the data so obviously second point is to save the space benefits in benefits you can write benefits of normalization fine and to reduce or to remove these anomalies fine third or fourth point you can write as what many normalization minimize the null values fine obviously here if you will store some information and if you don't have another information then you will store there null in Arlen suppose I have student information but I don't have any Department information so here you need to store null null and null fine so it will minimize the null values another point you can write is what normalization will simplify the Curie's fine another point you can write down is what it is important for these type of system OLTP systems where insertion insert and delete and this update queries are you know exists very frequently right by the end users and another point you can write down is what it simplifies the database structure by looking at the structure orders table you can easily understand fine what this table is all about fine if you have suppose one large table having 15 to 20 a trade routes like Student Information Department a chori name faculty information then by looking at that table at that schema it would be difficult to understand but if we divide that into four tables like for student another table for Department separate table for faculty separate table for Authority separate table sinned by looking then by looking at those tables you can easily understand the schema fine and obviously that it will simplify the queries because if you write down a simple query on the stable then that would be difficult fine we will discuss it when we will write down the queries when we'll discuss that algebra fine so searching or you can say sorting and creating indexes will be faster after applying the normalization so these are all some important points about normalization another point is what see the composition of the stable is also not so easy to find the right decomposition is a very tough process fine here this is simple table by looking in this you can easily find out that we will separate the student information in one table we will separate this information Department information in another table that is very easy but sometimes it's not so easy to find out the right decomposition is also very tough fine because there could be some lossless decomposition and lossy decomposition and that lossy decomposition we don't want so what is that low setting of decomposition and loss let's take the composition that also we'll discuss later fine so that is also a very important point here how to decompose the table fine so that data will not get lost you will get a loss less decomposition now normalization is also having multiple levels means multiple normal forms for second third bcnf fourth fifth sixth normal form many normal forms are there fine so that also will discuss one by one in later videos so next video will discuss first normal form with the Rabanne example so now I'll see in the next video till then what I take

Channel: Jenny's Lectures CS IT

Views: 516,737

Keywords: insert update and delete anomaly in dbms, normalization, what is normalization in dbms, what is normalization in sql, what is normalization in database, normalization in sql, 1nf 2nf 3nf normalization in dbms, database normalization, normalization in dbms in hindi, what is normalization, what is normalisation in database, normalization concept in dbms, database normalization forms, normalization and its types in dbms in hindi, 1nf 2nf 3nf and bcnf in database normalization

Id: p-j9emhNVTg

Length: 23min 32sec (1412 seconds)

Published: Wed Dec 11 2019