Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to analyze the three paradigms of the database. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope you will have a certain understanding of relevant knowledge after reading this article.

I: Introduction

As a database learner, it is useful to understand the three paradigms of relational databases. However, the introduction of database paradigm in textbooks is academic definition, grammar shy, difficult to understand, so write down their understanding of database paradigm, to help beginners, but also for future reference. The following does not introduce the degree of normalization higher than 3NF, because it is basically not used in practical applications, the reason is also obvious (query cost becomes larger), so for many large and complex systems, the database design does not follow the so-called normal form, which is why there will be so-called inverse normalization, well, get to the point.

II: Introduction to Paradigm

a: zeroth normal form

The zeroth normal form refers to a design that does not use any normal form, and its behavior of adding data is very strange. Take a look at the following table:

Suppose a student takes three courses and gets a grade for each course. Then, a design using the zeroth normal form would be as follows

Table a_0

This makes it cumbersome to add data to the table, adding fields every time you add new data, and wasteful because other records in the table may not need so many fields

Lots of room. as shown in table a_1.

Table a_1

As you can see from this, it would be foolish not to apply any paradigms to the database, because not only would you create a large number of useless table fields, but you would also make the table structure very difficult to maintain. This leads to the introduction of the first normal form.

b: first normal form

The first normal form is an improvement on the basis of the zeroth normal form. Many people on the Internet think that the so-called first normal form means that all fields in the table are atomic and inseparable. I personally think this understanding is also correct. The reason is not explained. My understanding of the first normal form is that

Duplicate fields in the zeroth normal form are extracted as table data, thus forming a stable, redundant table structure with little data.

From this, we can conclude that the table structure conforming to the first normal form should be:

At this point, the structure of the table becomes stable, and the redundant information in the table is much less than the zeroth normal form. However, the first normal form is only the lowest satisfied normal form of relational database design, and there is still a lot of redundant information in the first normal form, so it is necessary to introduce the second normal form. formula.

c: Second normal form

The second normal form satisfies that attributes are completely functionally dependent on the primary key, so tables satisfying the second normal form also satisfy the first normal form, and the purpose of the second normal form is to eliminate partial dependencies in the table.

Here are a few concepts to explain,

1: Complete functional dependence

Let K and P be attribute sets. If all attributes in K together can deduce any attribute in P, and for any proper subset of K, no attribute in P can be deduced, then K is completely functionally dependent on P.

2: Partial functional dependence

It is similar to above except that there exists a proper subset in K such that it deduces any property in p.

Conceptual things are often difficult to understand. For example, it is convenient for everyone to understand:

Suppose there is a student grade table containing the following attributes (Student Id, Course Id, Course Score, Student Name), where the primary key is (Student id, Course id), and the data in the table is as follows:

Then, at this time, the design of this table does not satisfy the second normal form, because the primary key (student id, course id) can uniquely determine the name of the student, so it does not satisfy the attribute complete function dependence primary key, so it is not the second normal form.

From the table data above, it is clear that tables that do not satisfy the second normal form have at least the following disadvantages:

1: Data duplication, waste of space, because every record, to save the name of the student, so there is a lot of duplicate data.

2: Insert exception, if the student has no grades, then the student has no name.

3: Update exception, delete exception, etc.

Solution:

Putting the student_name field into the student table removes some of the dependencies from the table.

d: third normal form

The third normal form is to eliminate transitive dependencies in the table if the second normal form is satisfied.

By transitive dependence, we mean x-->y,y-->z, then we get y-->z.

Transitive dependencies often occur on primary keys, foreign keys, and attributes related to foreign keys. For example, suppose there is such a table.

Student table (student id, student name, department id, department name), where the primary key is (student id) and the foreign key is (department id)

Department table (department id, dean name), primary key is (department id)

Obviously, there is a transitive dependency here, since the student id uniquely identifies the faculty id, and the faculty id uniquely identifies the faculty name.

The data in the table are as follows

From the table data above, it is clear that tables that do not satisfy the third normal form have at least the following disadvantages:

1: Duplicate data, waste of space, because every record in the student table will record the name of the inpatient department, there is a lot of duplicate data.

2: Insert exception, if a new department is built, and there are no students in the department, the department has no name.

3: Update exception, delete exception, etc.

III: Experience in database design

1: The number of tables should not be too many, generally 20-30 is enough. If the number of tables is too large, consider assimilation, which puts roughly identical entities into one table.

2: When the information in the database is very large, do not use foreign keys (inverse normalization), because this may cause a very large performance loss.

3: Generally, storage space is consumed in exchange for efficiency.

About how to carry out the analysis of the three paradigms of the database to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.