Database design Guidelines (first, second, third paradigm description)------namely Database Design Classic three paradigms!

I. Introduction of relational database design paradigm 1.1 First normal form (1NF) no duplicate columns
The so-called first normal form (1NF) means that each column of a database table is an indivisible basic data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have more than one value or cannot have duplicate attributes. If duplicate attributes occur, you may need to define a new entity, which consists of duplicate attributes and a one-to-many relationship between the new entity and the original entity. In the first normal form (1NF), each row of the table contains information about only one instance. In short, the first paradigm is a column without duplicates.
Description: In any relational database, the first normal form (1NF) is the basic requirement of the relational pattern, and the database that does not satisfy the first normal form (1NF) is not a relational database.

1.2 Second normal form (2NF) attributes are completely dependent on the primary key [elimination of partial child function dependencies]
The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, satisfying the second normal form (2NF) must first satisfy the first normal form (1NF). The second normal form (2NF) requires that each instance or row in a database table must be divided into unique regions. For implementation differentiation it is often necessary to add a column to the table to store the unique identities of individual instances. For example, the Employee Information table adds an employee number (emp_id) column, because each employee's employee number is unique, so each employee can be uniquely differentiated. This unique property column is called the primary key or primary key, and the primary code.
The second normal form (2NF) requires that the attributes of an entity depend entirely on the primary keyword. Total dependency refers to an attribute that cannot exist only on a part of the primary key, and if so, this part of the attribute and the primary key should be separated to form a new entity, a one-to-many relationship between the new entity and the original entity. For implementation differentiation it is often necessary to add a column to the table to store the unique identities of individual instances. In short, the second paradigm is that attributes are completely dependent on primary keys.

1.3 Third normal form (3NF) property does not depend on other non-primary attributes [elimination of delivery dependencies]
Satisfying the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third normal form (3NF) requires that a database table not contain non-primary key information already contained in other tables. For example, there is a departmental information table in which each department has information such as department number (dept_id), department name, department profile, and so on. After the department number is listed in the Employee Information table, the department name, department profile, and other department-related information can no longer be added to the Employee Information table. If the departmental information table is not present, it should also be built according to the third normal form (3NF), otherwise there will be a large amount of data redundancy. In short, the third paradigm is that attributes are not dependent on other non-primary attributes.


II. Analysis of application examples of paradigm
The following is an example of a school's student system, which illustrates the application of these paradigms. First Paradigm (1NF): The fields in a database table are single attributes and cannot be divided. This single attribute is composed of basic types, including integer, real, character, logical, date, etc. In any current relational database management system (DBMS), it is impossible for a fool to make a database that does not conform to the first paradigm, because the DBMS does not allow you to divide one column of a database table into two or more columns. Therefore, it is impossible for you to design a database that does not conform to the first paradigm in the existing DBMS.
First of all, let's make sure that the content to be designed includes those. School number, student name, age, Sex, course, course credits, department, academic results, department address, Department of telephone and other information. For the sake of simplicity we only consider these field information for the time being. There are several aspects of our concern about this information.
Students have those basic information students choose those classes, what is the grade of each class is the number of students who belong to the Department of the Department, the basic information is what.
2.1 Example Analysis of second normal form (2NF)
First we consider putting all of this information into a table (student number, name, age, Sex, course, course credits, department, subject score, department address, telephone number), and the following dependencies exist.
(school number) → (name, age, sex, department, office address, Department telephone)
(course name) → (credits)
(Study number, course) → (subject result) 2.1.1 Problem Analysis
Therefore, the requirements of the second normal form are not met, and the following problems arise

Data redundancy: The same course is selected by n students, and the "credits" are repeated n-1 times; the same student took the M-class course, and the name and age were repeated m-1 times.

Update exception:
1 if the credit of a course is adjusted, the "credits" value of all the rows in the datasheet should be updated, otherwise the same course credit will be different.
2 If a new course is to be opened, no one has been selected for the time being. This way, because there is no "learn number" keyword, the course name and credits are not recorded in the database.

Delete exception: Assuming that a group of students have completed elective courses, these elective records should be removed from the database table. At the same time, however, the course name and credit information have been removed. Obviously, this can also cause an insert exception. 2.1.2 Solutions
The selectcourse to the following three tables: Students: Student (School number, name, age, sex, Department, department address, Department of the telephone); Course: Course (course name, credits); Elective relationship: Selectcourse (School number, course name, Grade )。 2.2 Example analysis of third paradigm (3NF)
Then look at the above student table student (school number, name, age, sex, Department, department address, telephone), the keyword is a single keyword "school number", because there are the following decision relationship:

(school number) → (name, age, sex, department, office address, Department telephone)
But there are also the following decision relationships
(school number) → (college) → (Institute location, college Telephone)
That is, there is a non-critical field, "College location", "College Phone" transfer function dependency on key field "learning number".
It also has data redundancy, update exceptions, insert exceptions, and delete exceptions. (Data updates, delete the exception is not analyzed in this, you can participate in 2.1.1 analysis)

According to the third paradigm, the student relationship table is divided into the following two tables to satisfy the third paradigm:

Student: (School number, name, age, sex, department);
Department: (Department, Office address, Department of Telephone).

Summary
The database table above is consistent with the I,II,III paradigm, eliminating data redundancy, updating exceptions, inserting exceptions, and deleting exceptions.