Original link: https://chegva.com/5384.html
First Normal Form: Fields are not subdividable
The first normal form is that attributes are indivisible, each field should be indivisible, and a field only stores one piece of information. (atomicity)
-
mistake
| connect |
|---|
| Lao Wang, 1381234**, Beijing |
-
correct
| name | Telephone | Place |
|---|---|---|
| Lao Wang | 1381234** | Beijing |
Second Normal Form: Primary Key Constraint
The second normal form requires a primary key in the table, other fields in the table depend on the primary key, and a table only represents one thing , so the second normal form only needs to remember the primary key constraint. For example, if a table is a student table, and the student table has a field student ID with a unique value, then all other fields in the student table can be obtained according to this student ID field. The meaning of relying on the primary key is also related, because The value of the student number is unique, so there will be no problem that the stored information does not match, that is, the name of student 001 will not be stored in student 002. (uniqueness)
Table: student number, course number, name, credits;
This table clearly shows two transactions: student information, course information; since the non-primary key fields must depend on the primary key, the credit here depends on the course number , and the name depends on the student number , so it does not conform to the second paradigm.
There may be problems:
-
数据冗余:, each record contains the same information; -
删除异常：delete all student grades, and delete all course information; -
插入异常：The student has not selected a course and cannot be recorded in the database; -
更新异常：Adjusting course credits, all rows adjusted.
Correct way:
Student: Student (student number, name);
Course: Course (course number, credits);
Course selection relationship: StudentCourse (student number, course number, grade).
Third Normal Form: Foreign Key Constraints
The third normal form requires that there should be no fields in other tables that store the same information, each column is directly related to the primary key, non-primary key fields cannot depend on each other, and each column is guaranteed to be directly related to the primary key . Usually the implementation is to establish associations through foreign keys, so the third normal form only needs to remember the foreign key constraints. (redundancy)
-
mistake
| order number | order date | user ID | username | User age | User gender |
|---|---|---|---|---|---|
| 1001 | 2020-03-01 12:39:54 | 2001 | Lao Wang | 38 | male |
-
correct
order form
| order number | order date | user ID |
|---|---|---|
| 1001 | 2020-03-01 12:39:54 | 2001 |
user table
| user ID | username | User age | User gender |
|---|---|---|---|
| 2001 | Lao Wang | 38 | male |
Generally speaking, the database only needs to satisfy the third normal form ( 3NF ).
No redundant database design can do it. However, a database without redundancy is not necessarily the best database. Sometimes, in order to improve operational efficiency, it is necessary to lower the paradigm standard and properly retain redundant data. The specific approach is: follow the third paradigm in conceptual data model design, and consider the work of lowering paradigm standards in physical data model design. To reduce the paradigm is to increase the field, allow redundancy, and达到以空间换时间的目的.
[Example]: For example, in the order table, the existence of the field “Amount” indicates that the design of the table does not satisfy the third normal form, because “Amount” can be obtained by multiplying “Unit Price” by “Quantity”, indicating that “Amount” is redundant field. However, adding the redundant field “amount” can improve the speed of query statistics, which is the practice of exchanging space for time.
In Rose 2002 , there are two types of specified columns: data columns and computed columns . Columns like “Amount” are called “Calculated Columns”, while columns like “Unit Price” and “Quantity” are called “Data Columns”.
refer to:
This article is reprinted from: https://chegva.com/5384.html
This site is for inclusion only, and the copyright belongs to the original author.