Database of the three major paradigms

The database design paradigm is the specification that the database design needs to satisfy, the database that satisfies these specifications is concise, the structure is clear, at the same time, insert (insert), delete (delete) and update operation exception are not occurred. The reverse is messy, not only causing trouble to programmers in the database, but also being repulsive, potentially storing a lot of unwanted redundancy information.

Paradigm Description

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.

For example, the following database tables are consistent with the first paradigm:

Field 1

Field 2

Field 3

Field 4

and such a database table is inconsistent with the first paradigm:

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

The fields in the database table are single properties and cannot be divided. This single attribute is composed of basic types, including integer, real, character, logical, date, etc. It is clear that in any current relational database management system (DBMS), a fool is unlikely 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.

1.2 Second normal form (2NF) attributes are completely dependent on the primary key [elimination of partial child function dependencies]

If the relational schema R is the first normal form, and each of the non-primary attributes in R relies on a candidate key of R, it is called the second Normal mode pattern.

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. This unique property column is called the primary key or primary key, and the primary code.

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.

In short, the second paradigm (2NF) is completely dependent on the primary key for the non primary attribute.

The so-called complete dependency refers to the inability to exist only dependent on the main key part of the attribute (with function dependent w→a, if there is XW, there are x→a, then called W→a is local dependence, otherwise said W→a is a complete function dependency). If present, then 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.

Suppose the elective relationship table is Selectcourse (school number, name, age, course name, score, credits), the keyword is the combination of keywords (school number, course name), because there are the following decision relationships:

(School number, course name) → (name, age, grade, credits)

This database table does not satisfy the second normal form because the following decision relationships exist:

(course name) → (credits)

(school number) → (name, age)

That is, there are fields in the combination key that determine the non keyword.

Because it does not conform to 2NF, this elective relationship table has the following problems:

(1) 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.

(2) Update exception:

If you adjust the credits for a course, the "credits" values for all the rows in the datasheet are updated, otherwise the same course credits will be different.

(3) Insert exception:

Suppose that a new course is to be opened and 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.

(4) Delete exception:

If 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.

Change the selectcourse relationship table to the following three tables:

Student: Student (school number, name, age);

Course: Course (course name, credits);

Elective relationship: Selectcourse (School number, course name, grade).

Such database tables conform to the second normal form, eliminating data redundancy, updating exceptions, inserting exceptions, and deleting exceptions.

In addition, database tables for all single keys conform to the second normal form because there is no possible combination of keywords.

1.3 Third normal form (3NF) property does not depend on other non-primary attributes [elimination of delivery dependencies]

If the relational schema R is the second normal form and each of the non-primary attributes does not pass the candidate key that relies on R, then the R is called the third Normal mode.

Satisfying the third normal form (3NF) must first satisfy the second normal form (2NF). Third normal form (3NF) requires a database table that does 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.

Third normal form (3NF): On the basis of the second normal form, if no non-critical field exists in the data table, it conforms to the third normal form if the transfer function dependency of any candidate key field is not present. In short, the third paradigm is that attributes are not dependent on other non-primary attributes.

The so-called transfer function dependency means that if there is a "a→b→c" decision relationship, the C transfer function relies on A.

Therefore, a database table that satisfies the third normal form should not have the following dependencies:

key field → Non-critical field x→ non-critical field Y

Assuming the student Relationship table is student (school number, name, age, Institute, Institute location, college phone), the keyword is a single keyword "student number" because of the following decision relationships:

(school number) → (name, age, location of college, Institute, Telephone)

This database is 2NF compliant, but does not conform to 3NF because of 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 will also have data redundancy, update the exception, insert the exception and delete the exception, the reader can be informed by their own analysis.

The Student relations table is divided into the following two tables:

Student: (School number, name, age, college);

College: (College, location, telephone).

Such database tables conform to the third paradigm, eliminating data redundancy, updating exceptions, inserting exceptions, and deleting exceptions.