🗊Презентация Steps in Normalization

Summary of Definitions of the Normal Forms Functional Dependency and Determinants The 1st Normal Form (1NF)   The 2nd Normal Form (2NF) Anomalies and Normalization Turning a Table with Anomalies into Single-Theme Tables The 3rd Normal Form (3NF) The Boyce-Codd Normal Form (BCNF) The 4th Normal Form (4NF) The 5th Normal Form (5NF) The Domain-Key Normal Form (DKNF)   

There are two approaches to the logical design of the database: -The top-down approach -Bottom-up approach There are two approaches to the logical design of the database: -The top-down approach -Bottom-up approach Method of E/R model is top-down approach. Method includes defining the entities, relationships and attributes tracing scheme E/R and mapping schema. Normalization is a bottom-up approach. This is a step in the decomposition of complex records are simple. Normalization reduces redundancy, using the principle of partition. Splitting is the conversion table in the smaller tables without losing information. Top-down approach is best suited to test the existing developments.

Through normalization we want to design for our relational database a set of files that Through normalization we want to design for our relational database a set of files that (1) contain all the data necessary for the purposes that the database is to serve, (2) have as little redundancy as possible, (3) accommodate multiple values for types of data that require them, (4) permit efficient updates of the data in the database, and (5) avoid the danger of losing data unknowingly.

Data redundancy means their repeatability. Redundancy increases the time it takes to update, add, and delete data. Data redundancy means their repeatability. Redundancy increases the time it takes to update, add, and delete data. Redundancy also increases the use of disk space, and, as a consequence, increases the number of disk accesses. Consequence of redundancy can be: - Update anomalies - insertion, updation and deletion of data can cause errors. - Inconsistency - the error rate increases with repeated recording of facts. - Undue consumption of disk space.

Normalization can be viewed as a series of steps (i.e., levels) designed, one after another, to deal with ways in which tables can be "too complicated for their own good". Normalization can be viewed as a series of steps (i.e., levels) designed, one after another, to deal with ways in which tables can be "too complicated for their own good". The purpose of normalization is to reduce the chances for anomalies to occur in a database. The definitions of the various levels of normalization illustrate complications to be eliminated in order to reduce the chances of anomalies. At all levels and in every case of a table with a complication, the resolution of the problem turns out to be the establishment of two or more simpler tables which, as a group, contain the same information as the original table but which, because of their simpler individual structures, lack the complication.

In general, a functional dependency is a relationship among attributes. In general, a functional dependency is a relationship among attributes. In relational databases, we can have a determinant that governs one other attribute or several other attributes. To go back to our mathematical examples for a moment, we could view the situation of functional dependency of several attributes on one determinant as being like having several linked functions that share an argument and can be displayed economically in just one table. For example, consider the following table that displays sample values of the algebraic functions y = x2, y = x3, and y = x4.

Definition: Definition: A table (relation) is in 1NF if 1. There are no duplicated rows in the table. 2. Each cell is single-valued (i.e., there are no repeating groups or arrays). 3. Entries in a column (attribute, field) are of the same kind. Note: The order of the rows is immaterial; the order of the columns is immaterial. Note: The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column--even, possibly, of all the columns).

A key attribute will, by the definition of key, uniquely determine the values of the other attributes in a table; i.e., all non-key attributes in a table will be functionally dependent on the key. A key attribute will, by the definition of key, uniquely determine the values of the other attributes in a table; i.e., all non-key attributes in a table will be functionally dependent on the key. But there may be non-key attributes in a table that determine other attributes in that table. Consider the following table2:

In Table2 the Level attribute can be said to be functionally dependent on the Major attribute. In Table2 the Level attribute can be said to be functionally dependent on the Major attribute. Thus we have an example of an attribute that is functionally dependent on a non-key attribute. This statement is true in the table per se, and that is all that the definition of functional dependence requires; but the statement also reflects the real-world fact that Library and Information Science is a major that is open only to graduate students and that Pre-Medicine and Pre-Law are majors that are open only to undergraduate students.

The table is in 2NF if it is in 1NF and every attribute in a row is functionally dependent upon the key to the whole, not only on his part. The table is in 2NF if it is in 1NF and every attribute in a row is functionally dependent upon the key to the whole, not only on his part. Instructions for converting tables in 2NF: Locate and delete the attributes that are functionally dependent only on the part of the key, not the key to the whole. Put this attributes in a separate table. Group the remaining attributes.

Table2 has another interesting aspect. Table2 has another interesting aspect. Its key is a composite key, consisting of the paired attributes, FirstName and LastName. The Level attribute is functionally dependent on this composite key, of course; but, in addition, Level can be seen to be dependent on only the attribute LastName. (This is true because each value of Level is paired with a distinct value of LastName. In contrast, there are two occurrences of the value Lynn for the attribute FirstName, and the two Lynns are paired with different values of Level, so Level is not functionally dependent on FirstName.)

Thus this table fails to qualify as a 2nd Normal Form table, since the definition of 2NF requires that all non-key attributes be dependent on all of the key. Thus this table fails to qualify as a 2nd Normal Form table, since the definition of 2NF requires that all non-key attributes be dependent on all of the key. (Admittedly, this example of a partial dependency is artificially contrived, but nevertheless it illustrates the problem of partial dependency.) We can turn Table 2 into a table in 2NF in an easy way, by adding a column for the Social Security Number, which will then be the natural thing to use as the key.

Anomalies and Normalization  Anomalies and Normalization  At this point it is appropriate to note that the main thrust behind the idea of normalizing databases is the avoidance of insertion and deletion anomalies in databases. How do anomalies relate to normalization? The simple answer is that by arranging that the tables in a database are sufficiently normalized (in practice, this typically means to at least the 4th level of normalization), we can ensure that anomalies will not arise in our database. Anomalies are difficult to avoid directly, because with databases of typical complexity (i.e., several tables) the database designer can easily overlook possible problems. Normalization offers a rigorous way of avoiding unrecognized anomalies.

Definition: Definition: A table is in 3NF if it is in 2NF and if it has no transitive dependencies. In order to discuss the 3rd Normal Form, we need to begin by discussing the idea of transitive dependencies. In mathematics and logic, a transitive relationship is a relationship of the following form: "If A implies B, and if also B implies C, then A implies C." An example is: "If John Doe is a human, and if every human is a primate, then John Doe must be a primate." Another way of putting it is this: "If A functionally governs B, and if B functionally governs C, then A functionally governs C." In the arrow notation, we have: [(A → B) and (B → C)] → (A → C)

By examining Table 4 we can infer By examining Table 4 we can infer that books dealing with history, cognitive psychology, and folksong are assigned to the PCL General Stacks collection; that books dealing with legal procedures are assigned to the Law Library; that books dealing with Greek literature are assigned to the Classics Library; that books dealing with library biography are assigned to the Library and Information Science Collection (LISC); and that books dealing with music literature are assigned to the Fine Arts Library. Further, we can infer that the PCL General Stacks collection and the LISC are both housed in the Perry-Castañeda Library (PCL) building; that the Classics Library is housed in Waggener Hall; and that the Law Library and Fine Arts Library are housed, respectively, in Townes Hall and the Fine Arts Building.

Thus we see that there is a transitive dependency in Table4: any book that deals with Thus we see that there is a transitive dependency in Table4: any book that deals with history, cognitive psychology, or library biography will be physically housed in the PCL building (unless it is temporarily checked out to a borrower); any book dealing with legal procedures will be housed in Townes Hall; and so on. In short, if we know what subject a book deals with, we also know not only what library or collection it will be assigned to but also what building it is physically housed in.

What is wrong with having a transitive dependency or dependencies in a table? What is wrong with having a transitive dependency or dependencies in a table? For one thing, there is duplicated information: from three different rows we can see that the PCL General Stacks are in the PCL building. For another thing, we have possible deletion anomalies: if the Yudof book were lost and its row removed from Table4, we would lose the information that books on legal procedures are assigned to the Law Library and also the information the Law Library is in Townes Hall. As a third problem, we have possible insertion anomalies: if we wanted to add a chemistry book to the table, we would find that Table4 nowhere contains the fact that the Chemistry Library is in Robert A.Welch Hall. As a fourth problem, we have the chance of making errors in updating: a careless data-entry clerk might add a book to the LISC but mistakenly enter Townes Hall in the building column. The solution to the problem is, once again, to place the information in Table4 into appropriate single-theme tables. Here is one such possible arrangement:

You can verify for yourself that none of these tables contains a transitive dependency; hence, all of them are in 3NF (and, in fact, in DKNF). You can verify for yourself that none of these tables contains a transitive dependency; hence, all of them are in 3NF (and, in fact, in DKNF). We can note in passing that the fact that Table5 contains the first and last names of Robert Graves in two different rows suggests that it might be worthwhile to replace it with two further tables, along the lines of:

Definition: A table is in BCNF if it is in 3NF and if every determinant is a candidate key. Definition: A table is in BCNF if it is in 3NF and if every determinant is a candidate key. The Boyce-Codd Normal Form (BCNF) deals with the anomalies that can occur when a table fails to have the property that every determinant is a candidate key. Here is an example, Table_6, that fails to have this property.  (In Table_6 the SSNs are to be interpreted as those of students with the stated majors and advisers.  Note that each of students 123-45-6789 and 987-65-4321 has two majors, with a different adviser for each major.)

Still another determinant is the attribute, Adviser, for each different value of Adviser determines a unique value of the attribute, Major. Still another determinant is the attribute, Adviser, for each different value of Adviser determines a unique value of the attribute, Major. (These observations about Table_6 correspond to the real-world facts that each student has a single adviser for each of his or her majors, and each adviser advises in just one major.) Now we need to examine these three determinants with respect to the question of whether they are candidate keys. The answer is that the pair, SSN and Major, is a candidate key, for each such pair uniquely identifies a row in Table6. In similar fashion, the pair, SSN and Adviser, is a candidate key. But the determinant, Adviser, is not a candidate key, because the value Dewey occurs in two rows of the Adviser column. So Table 6 fails to meet the condition that every determinant in it be a candidate key.

It is easy to check on the anomalies in Table6. It is easy to check on the anomalies in Table6. For example, if student 987-65-4321 were to leave Enormous State University, the table would lose the information that Semmelweis is an adviser for the Pre-Medicine major. As another example, Table 6 has no information about advisers for students majoring in history. As usual, the solution lies in constructing single-theme tables containing the information in Table 9.1. Here are two tables that will do the job.

The basic definition of NF 3 is inadequate and inappropriate for the tables: -Having multiple candidate keys. -Possible with composite keys. -Share overlapping candidate keys. The basic definition of NF 3 is inadequate and inappropriate for the tables: -Having multiple candidate keys. -Possible with composite keys. -Share overlapping candidate keys. To normalize the table under these conditions was proposed normal form Boyce-Codd (BCNF). Relation is in BCNF if it is in 3NF and every determinant is a candidate key. Instructions to convert a table in BCNF: - Locate and remove the overlapping candidate keys. - Place a part of the possible key and attribute from which it is functionally dependent in a separate table. - Group the remaining items in the table.

Input in the table intentional redundancy to improve query performance is called denormalization. Input in the table intentional redundancy to improve query performance is called denormalization. Denormalization is a decision to implement a compromise between performance and consistency of the data. Denormalization increases the usable space on the disk.

In this lesson, you learned that: There are two approaches to the logical design of the database: A "top down" Bottom up approach In this lesson, you learned that: There are two approaches to the logical design of the database: A "top down" Bottom up approach Methods of E/R model is a "top down" and normalization - a "bottom up". Normalization is used to simplify the table structure. Normalization is the design of the tables in accordance with the specified conditions in the form of certain normal forms. Table structure is always in a certain normal form.

The most important and commonly used normal forms are: -First Normal Form (1 NF) -Second Normal Form (2 NF) -Third Normal Form (3 NF) -Normal Form Boyce-Codd (BCNF) The most important and commonly used normal forms are: -First Normal Form (1 NF) -Second Normal Form (2 NF) -Third Normal Form (3 NF) -Normal Form Boyce-Codd (BCNF) Normalization theory is based on the fundamental concept of functional dependence. Functional relationships are due "many-to-many." A table is in 1NF, if each box contains a single value. A table is in 2NF, if it is in 1NF and every attribute in the line depends on the whole key, not a part of it. A table is in 3NF, if it is in 2NF and every non-key attribute is functionally dependent only on the primary key.

The basic definition of 3NF is inadequate and is not suitable for tables, at which: -There are multiple possible keys. -Candidate keys are composite. -Candidate keys overlap. The basic definition of 3NF is inadequate and is not suitable for tables, at which: -There are multiple possible keys. -Candidate keys are composite. -Candidate keys overlap. The relation is in normal form Boyce-Codd (BCNF) if and only if every determinant is a candidate key. Intentional redundancy in the input table to improve query performance is called denormalization. Denormalization is a compromise between performance and consistency of the data. Denormalization increases the usable space on the disk.