Shulou(Shulou.com)06/01 Report--

This article is to share with you about the necessity of database standardization. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The main purpose of the standardization process is to overcome the defects of insertion exception, deletion exception and large redundancy in the logical structure of the database. Database normalization enables database designers to better understand the current data structure within the organization and eventually get a series of data entities. Database standardization can effectively reduce the degree of database redundancy through the design of database tables.

Database normalization process

The standardization of relational database is more popular than the standardization of tables.

The necessity of standardization:

According to the requirements of the project, we will create the corresponding database tables to complete the storage of the data in the project. This has become a fixed process for doing a project, but when you really start to deal with business requirements, you will realize that the setting of your table is unreasonable, which leads to repeated storage of data, insertion exception, deletion exception, update exception and so on. At this time, it is necessary to re-plan the table, which is not only a waste of time, but also consumes human and financial resources, and is very uneconomical, so standardization is very necessary. So I'm going to teach you the method of standardizing tables here today.

Before teaching the standardized database method, let me introduce you to the following knowledge:

Functional dependence of key knowledge points

The definition may be a little difficult to understand, let me explain briefly here: a functional dependency describes a mapping relationship between two sets, which is the same as a function, for example, y = x ^ 2, for x, one x corresponds to a y value, but there is no case that an x corresponds to multiple y values, so it can be said that the y function depends on x, but for y There is a situation where one y value corresponds to multiple x values, so x is not functionally dependent on y. This is functional dependency.

Next, let's introduce several special functional dependencies:

Complete functional dependency

Definition:

If X-> Y does not exist for any proper subset of X, X'- > Y, then we say that the functional dependency of X-> Y is a complete functional dependency.

A simple explanation: the function z = x + y, for z: the z function depends on x and y, but z does not depend on x or y alone, which means that the dependence of z function on x and y is a complete functional dependency.

Some functional dependencies:

Definition:

If X-> Y, but Y is not completely dependent on X, this dependency is called partial complete dependency. That is to say: the function z = x + 0y can be seen, that is to say, the z function depends on x and y, but z depends on x alone, so this is part of the functional dependency.

Transfer function dependencies:

Definition:

If X-> Y, Y-> Z, and does not hold, Y-> X does not hold. Then it is said that the Z transfer function depends on X.

This is relatively simple, the function group z = x ^ 2, x = 2y can be simplified to z = 4y ^ 2, it is easy to see that z is a function dependent on x, x depends on y, and z-> x is not true, this is a transitive functional dependency.

The second key knowledge point-key

Candidate key: a property (field) or a property group (multiple fields) can be completely determined by other properties (fields) in the relational schema (table). That is, other properties (fields) are completely dependent on that property (field) or property group (multiple fields).

Primary key: if there is more than one candidate key, select one of them as the primary key. The value of the attribute or attribute group selected as the primary key in each meta-ancestor (row) in the relational schema (table) is not allowed to repeat and take the value as null.

Primary attribute: the attribute in any candidate key is called the primary attribute. If the candidate key is made up of multiple attributes, then each of these attribute groups is the primary attribute.

Non-primary attribute: an attribute that is not included in any key is called a non-primary attribute.

Foreign key: an attribute or attribute group is not a primary key in the current relational schema (table), but an identity that acts as a primary key in another relational schema (table) is called a foreign key.

After introducing the above basic knowledge points, let's start to learn the specification process of database tables:

If you want to standardize the table, you first need a standard to weigh whether the scale has been standardized. This standard is the paradigm.

There are six kinds of paradigms: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the BC paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF).

In the above six paradigms, in general, it is perfect that we need to standardize the table to BCNF, but in a real project, we only need to achieve 3NF.

Next, we focus on the first four paradigms:

The first paradigm: all attributes in relational schema R are inseparable data items.

To put it simply, as long as you can build the table, the table has satisfied the first normal form. For example, the student table (student_id, course_id, student_name, age, sex, grade, sdept, sdept_director), it is obvious in this table that the grade term is determined by student_id and course_id, so the combination of these two items should be used as the primary key.

The second normal form: on the basis of satisfying the first normal form, satisfying the non-primary attributes is completely dependent on the primary key of R.

This requires the use of what was mentioned earlier, to determine whether the non-primary attribute is completely dependent on the primary key. Change the structure of the table if it is not satisfied. For example, student tables (student_id, course_id, student_name, age, sex, grade, sdept_id, sdept_director) because student_id and course_id are combined as primary keys, but for other fields such as name, age and sex, they are completely dependent on the attribute student_id, so they are partially dependent on student_id and course_id as primary keys. This does not satisfy the definition of the second normal form, so we should take out the grade and split this large table into two small tables: student (student_id, name, age, sex, sdept_id, sdept_director) and student_score (student_id, course_id, grade).

The third paradigm: remove the transitive dependency while satisfying the second paradigm.

For example: student table (student_id, student_name, age, sex, sdept, sdept_director), it is obvious that each major determines a professional director, so sdept_director delivery depends on student_id, so another table student (student_id, student_name, age, sex) and sdept (sdept_id, sdept_name, sdept_director) should be split to satisfy the third paradigm.

BC Paradigm: while satisfying the third normal form, meet the following three points:

1. All primary attributes are completely dependent on other candidate keys that do not contain their own.

2. All non-primary attributes are completely dependent on each candidate key.

3. No attribute completely depends on any set of non-primary attributes.

The previous three paradigms are all kinds of constraints on the non-principal attributes, and the BC paradigm is based on them, and then constrains the principal attributes, which solves the problem of partial dependence between the principal attributes, and there is no problem that the primary attributes are completely dependent on the non-principal attributes. Our student table student (student_id, student_name, age, sex), the primary key is student_id, so the primary attribute is student_id. Obviously, the first two items have been satisfied, because the student's name may be duplicated, so there is no functional dependency between student_id and student_name, so the student table satisfies the BC paradigm.

Thank you for reading! About the necessity of database standardization is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people 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.