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

What is the definition of a paradigm? Normal Format: Normal Format is a knowledge in discrete mathematics, which is to solve the problem of data storage and optimization: after saving the storage of data, all the data that can be found through the relationship will not be stored repeatedly, and the ultimate goal is to reduce the redundancy of data.

Paradigm Background:

1. To solve data redundancy

2. It is for the optimization and storage of data

3. After saving the data, the data that can be obtained through the relationship will no longer be stored.

Paradigm concept:

1, is a hierarchical structure of the specification, divided into 6 layers

2. Each layer is stricter than the previous layer

3. To satisfy the next level of normal form, the premise is to satisfy the upper level.

Paradigms have guiding significance in the database, but they are not mandatory.

5, the database should not only consider the space problem, but also consider the efficiency problem, generally use the first third layer

6, six-layer paradigm, 1NF is the lowest level, the lowest requirements;6NF the highest level, the most stringent.

At present, there are six normal forms of relational database: the first normal form (1NF), the second normal form (2NF), the third normal form (3NF), the BCNF (BCNF), the fourth normal form (4NF) and the fifth normal form (5NF, also called perfect normal form). Generally speaking, the database only needs to satisfy the third normal form (3NF).

The first paradigm: ensuring that each column is indivisible

requirements

1. When designing a table to store data, if the data stored in the fields designed in the table still needs additional processing (splitting) before being taken out for use, then the table design does not satisfy the first normal form.

2. The first normal form requires fields to be atomic and indivisible.

For example:

Lecturer's substitute list

There is no problem in the design of the above table: but if the requirement is to check out the data, it is required to show when a teacher starts class and when the class ends:, then the substitute time needs to be split: does not meet the 1NF, the data does not have atomicity, and can be split again.

solutions

Solution: Split substitute time into two fields to solve the problem.

Second normal form: ensure that every column in the table is related to the primary key

requirements

1. In the data table design process, if there is a composite primary key (multi-field primary key), and there are fields in the table that are not determined by the entire primary key, but depend on a certain field in the primary key (part of the primary key).

2. There is a partial problem where the field depends on the primary key, which is called partial dependence.

3. The second paradigm is to solve the problem that table design does not allow partial dependencies

Lecturer with schedule

Composite PK: Lecturer + Class

In the above table: Substitute time, start and end fields are related to the current primary key (instructor and class), but gender does not depend on class, classroom does not depend on instructor, gender only depends on instructor, classroom only depends on class, gender and classroom dependence Part of the primary key: partial dependence. Does not conform to the second normal form.

solutions

Solution 1: Separate tables for gender and instructor, and separate tables for class and classroom.

Solution 2: Remove composite primary keys and use logical primary keys

Logical primary key: has no business meaning, such as self-increasing primary key

Business primary key: has business meaning, for example, ID number is used as primary key in person table

Third normal form: Ensure that each column is directly related to the primary key column, not indirectly

requirements

1. To satisfy the third normal form, the second normal form must be satisfied

2. Theoretically speaking, all fields in a table should be directly dependent on the primary key. If there is a field in the table design that does not directly depend on the primary key, but depends on a non-key field, and finally realizes the dependency on the primary key. This dependency relationship that does not directly depend on the primary key but depends on the non-primary key field is called transitive dependency.

3. The third paradigm is to solve the problem of transitive dependence

Lecturer with schedule

Primary key: ID

In the above design scheme: gender dependent lecturer exists, lecturer depends on primary key; classroom depends on class, class depends on primary key: gender and classroom both exist transitive dependence.

solutions

The fields with transfer dependencies and the dependent fields themselves are taken out separately to form a separate table, and then when corresponding information is needed, the corresponding entity table primary key is added.

Lecturer with schedule

Lecturer List

classroom table

inverse normal form

Sometimes, when designing tables, if there are several fields in a table that need to obtain information from other tables, theoretically speaking, you can indeed obtain the desired data, but the efficiency is a little lower. In some tables, you will deliberately not save the primary key of another table, but directly save the desired data information. In this way, when querying data, a table can directly provide data without multiple table queries ((low efficiency), but redundancy will increase.

After reading the above, do you have a general understanding of Mysql's paradigm? If you want to know more, welcome to pay attention to the industry information channel!

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.