[Reprint] explanation of the three major paradigms of the database, and explanation of the three major paradigms

[Reprint] explanation of the three major paradigms of the database, and explanation of the three major paradigms

The design paradigm of databases is the specifications that need to be met by database design. databases that meet these specifications are concise and have clear structures. At the same time, there will be no insert or delete operations) and update operations are abnormal. On the contrary, it is a mess, which not only creates troubles for database programmers, but also features an ugly face. It may store a large amount of unnecessary redundant information.

 

Paradigm description

 

1.1 columns in the first paradigm (1NF) with no duplicates

 

The first paradigm (1NF) means that each column in the database table is an inseparable basic data item. The same Column cannot contain multiple values, that is, an attribute in an object cannot have multiple values or duplicate attributes. If duplicate attributes exist, you may need to define a new object. A new object consists of duplicate attributes. The new object has one-to-multiple relationships with the original object. In the first paradigm (1NF), each row of the table contains only information of one instance. In short, the first paradigm is a non-repeated column.

 

Note: In any relational database, the first paradigm (1NF) is the basic requirement for the relational model. databases that do not meet the first paradigm (1NF) are not relational databases.

 

For example, the following database tables conform to the first paradigm:

 

 

Field 1

Field 2

Field 3

Field 4

 

Such database tables do not conform to the first paradigm:

 

 

Field 1	Field 2	Field 3	Field 4
		Field 3.1	Field 3.2

 

Fields in the database table are single attributes and cannot be divided. This single attribute is composed of basic types, including integer, real number, complex type, logical type, and date type. Obviously, in any current Relational Database Management System (DBMS), dummies cannot make databases that do not conform to the first paradigm, because these DBMS do not allow you to divide one or more columns 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.

 

1.2 The 2NF attribute of the second paradigm is completely dependent on the primary key [Removing the dependence of partial molecular functions]

 

If the relational mode R is the first paradigm, and every non-primary attribute function in R depends on a candidate key of R, it is called the second paradigm.

The second Paradigm (2NF) is established on the basis of the first paradigm (1NF), that is, to satisfy the second Paradigm (2NF) must satisfy the first paradigm (1NF) first ). The second Paradigm (2NF) requires that each instance or row in the database table be able to be distinguished by a unique region. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. This unique attribute column is called as the primary keyword, primary key, and primary code.

 

For example, the employee ID (emp_id) column is added to the employee information table. Because each employee's employee ID is unique, each employee can be uniquely distinguished.

In short, the second Paradigm (2NF) is that non-primary attributes fully depend on primary keywords.

 

Full dependency is an attribute that cannot depend only on A part of the primary keyword (with function dependency W → A, if XW exists, X → A is true, so w→a is A local dependency; otherwise, w→a is A full function dependency ). If this attribute exists, this part of the attribute and the primary keyword should be separated to form a new object. The relationship between the new object and the original object is one-to-multiple.

 

Assume that the course selection relation table is SelectCourse (student ID, name, age, course name, score, and credits), and the keywords are combined keywords (student ID, course name), because the following deciding relation exists:

 

(Student ID, course name) → (name, age, score, credits)

This database table does not meet the second paradigm because of the following decision relationships:

 

(Course name) → (credits)

 

(Student ID) → (name, age)

 

That is, fields in the combined keywords determine non-keywords.

 

Because 2NF is not met, this course selection relation table has the following problems:

 

(1) data redundancy:

 

The same course is selected by n students, and "Credits" are repeated for n-1 times. The same student takes m courses, and the name and age are repeated for m-1 times.

 

(2) Update exception:

 

If the credits of a course are adjusted, the "Credits" value of all rows in the data table must be updated. Otherwise, different credits may occur for the same course.

 

(3) insertion exception:

 

For example, if you want to open a new course, no one will take the course. In this way, the course name and credits cannot be recorded in the database because the "student ID" keyword is not yet available.

 

(4) Deletion exception:

 

Assuming that a group of students have completed their electives, These electives should be deleted from the database table. However, the course name and credit information are also deleted. Obviously, this will also cause insertion exceptions.

 

Change the SelectCourse table to the following three tables:

 

Student: Student (Student ID, name, age );

 

Course: Course (Course name, credits );

 

Course Selection relationship: SelectCourse (student ID, course name, score ).

 

Such database tables conform to the second paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.

 

In addition, all database tables with single keywords comply with the second paradigm, because it is impossible to have a combination of keywords.

 

1.3 The 3NF attribute does not depend on other non-primary attributes [eliminate transmission dependencies]

 

If the relational mode R is the second paradigm, and each non-primary attribute does not pass a candidate key dependent on R, R is called the third paradigm.

The third paradigm (3NF) must satisfy the second Paradigm (2NF) first ). The third paradigm (3NF) requires that a database table do not contain information about non-primary keywords already contained in other tables.

 

For example, there is a department information table, where each department has a department ID (dept_id), department name, Department profile, and other information. After the Department numbers are listed in the employee information table, you cannot add the Department name, Department profile, and other information related to the department to the employee information table. If the department information table does not exist, it should also be constructed based on the third paradigm (3NF), otherwise there will be a large amount of data redundancy.

 

Third Paradigm (3NF): Based on the second paradigm, if there is no transfer function dependency for any candidate keyword segment in the data table, it complies with the third paradigm. In short, the third paradigm is that attributes do not depend on other non-primary attributes.

 

The so-called pass function dependency refers to the existence of "A → B → C" decision relationship, then the C transfer function depends on.

 

Therefore, database tables that meet the third paradigm should not have the following dependency:

 

Keyword field → non-Keyword field x → non-Keyword field y

 

Assume that the Student relationship table is Student (Student ID, name, age, school, school location, school phone number), and the keyword is single keyword "Student ID", because the following decision relationship exists:

 

(Student ID) → (name, age, school, school location, school phone number)

 

This database complies with 2NF but does not comply with 3NF because of the following decision relationships:

 

(Student ID) → (school location, school phone number)

 

That is, the transfer function dependency of the non-Keyword section "school location" and "college phone" on the keyword section "student ID" exists.

 

It can also cause data redundancy, update exceptions, insertion exceptions, and deletion exceptions. You can analyze and learn this information on your own.

 

The student relationship table is divided into the following two tables:

 

Student (student ID, name, age, school );

 

School: (school, location, phone number ).

 

Such database tables conform to the third paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.