1NF 2NF 3NF DBMS

Whenever you design a database, the most important thing to do is to take care of the normalization rules, by definition normalization says to reduce data redundancy and create more tables than before. The database can handle more tables instead of tables that are not normalized. forms, so creating more tables is much more suitable for the database than for the wrong tables. The advantages that normalization provides are flexibility, designs, flexibility, security of the database, the database is much safer with normalization because more tables are formed and there are cases in which it is not necessary. some information must be hidden from some users, so it counts in data security, reduces data redundancy in a much more organized way, these are the types of normalization we will talk about, by the way, when considering forms standardization, you can't skip the above form, which means you have to do it first. go through the first normal form, then the second normal form and finally the third normal form, you can't skip the first two and go directly to the third, first normal form for a table to be in a first normal form, two criteria are needed, two things that should not happen in this table first there are no repeated values ​​in a group and there are no repeated groups in the table, what does that mean?

We will see in the example. In this example the employee table is in perfect shape. The employee id is the primary key and has values ​​of name, address and phone number so far so good. What if there is a possibility that employees have more than one phone number? You can write them like this by separating them with columns that repeat values ​​in one column and violate first normal form in the other. possibility of writing it write them in different columns and that is repeating columns repeating columns again violates the first normal form remember what we gathered from the definition of the first normal form the table cannot be in a first normal form if it contains repeated values ​​or repeated columns columns Repeated are those that contain almost the same type of information but in different columns.

The solution to the problem is that we will remove the part that is creating the problem in this table, which is the phone numbers, and we will create a separate table for the phone numbers. and connect them with the relevant value which is the name in this case and then join both tables now you are in the first normal form, although the tables have increased from 1 to 2, but there are no repeating values ​​or repeating columns, the database can handle more easily. more than one table but it doesn't handle repeated values ​​or repeated columns so we will have to deal with that and now this table is in the first condition of perfect normal form the table does not contain repeated values ​​or repeated columns for a table to be in a second normal form, it should have three things, it should be in the first normal form, there is no partial dependency.

Partial dependency can only occur when there is a composite key. What is the composite key? Composite key is when there is more than one primary key. The key column means that we simply cannot identify the result of a particular column in this example. If we just create a student ID primary key, we can't conveniently take a record of any student because what if we say give me the result of two zero one now two? zero one we can't be sure if we are asking for one two zero ones for the software architecture output or the quality assurance output.

Similarly, what would happen if someone asked us to give a result of 205? 205 is also enrolled in quality software history and architecture so we make another column as primary key now we can easily judge a record now the question will be to give us a result of student id 201 in course quality control or give us a result of student id 205 in history course now the student id was already a primary key also we have created the course as primary key and when there are more primary key columns they are called combined composite key now more about the partial dependency student id and course are key columns, namemarks and teacher are non-key columns Here in this table now what is partial dependency?

Partial dependency is when any of the non-key columns do not depend on all the primary key columns, like here a teacher only depends on the course, which course a teacher teaches, it only depends on the code. It has nothing to do with the student ID, both are key columns. Here is a classic situation where the non-key column is only partially dependent on the key columns, so this is called partial dependency and then when there is partial dependency. we need to split that column into another table which only partially depends on flags and name both totally depend on both keys so there is no need to show the column and remove it from main table and it is combined with course column two tables and both tables do not have any partial dependency so now it is considered in second normal form for a table to be in a third normal form it must be in a first normal form and a second normal form and the third normal form should not have a transitive dependency what is transitive dependency?

We will see in the example now. One more thing to note is that if a keyless column can be guessed from another keyless column, that means it is violating the third rule of normal forms and If we need to split the table, we can review the examples to make it more understandable in this table. The student id is the primary key column and the exam type name and maximum marks are non-key columns of the table, we can guess that if a student opts for viva it means that the maximum marks will be 20, the student opts for theory, it will be one hundred points and practical will be 50 points, which means that we can guess the maximum marks column from the exam type column and it also means that the maximum marks column and transitively depends on column of exam type, so if a student from id 209 comes and is opting for a practical exam, we will already know that the maximum marks for the practices would be 50.

This is the classic case of violating the third normal form, we will break the column of maximum ratings and we will put it is on the other table now it is not in third normal form there is no transitive dependency between the tables the transitive dependency is a little complicated so we will see another example of that now the mesh match number is the terrain and the capacity of the main key teams, these are the ones that don't -The main keys in this table are absolutely clear: if the match is played in mcg, the mcg capacity is 80,000.

If it is played in Hobart, it is 30,000. Likewise, in the other field we already know the capacity of the field, so if it matches. The number 9 is played in mcg and will automatically put 80,000 in the capacity column, so again the capacity table transitively depends on the floor table, again violating third normal form, we will break the table by removing the transitive part of the table which is the placement capacity. in another table, last example for third normal form in this table, if someone's designation is manager, the salary he will receive will be 80,000 in the database table, if there is any transitive dependency between the columns that are not are key, we will only break the part that transitively depends on another part.

Transitive dependency also occurs when you perform some type of calculation on tables like in this table, the amount of profit depends on the total ticket sales multiplied by the ticket price. a transitive dependency if thousand tickets are sold and each ticket is 200 then there is a profit of 200,000 so if some calculation can be done on the table it is also considered in third normal form