Database design Paradigm

Introduction of relational database design paradigm

1. The first normal form (1NF) has 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.

Then the characteristics of the first normal form are: 1 has the primary key 2, the primary key cannot be empty 3 The primary key cannot repeat 4) field can not be divided

2. The second paradigm (2NF) property is completely dependent on the primary key [eliminates partial function dependencies on the main code for non-primary properties]

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, you typically add a column to the table to store unique identities for each instance. 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 attribute 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, you typically add a column to the table to store unique identities for each instance. In short, the second paradigm is that attributes are completely dependent on primary keys.

3. The third paradigm (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.

4. Mode IV:

Main task: To satisfy the third normal form, eliminate the multivalued dependence

5. Mode V:

Definition: If each connection dependency in the relational schema r is contained by the candidate key of R, the R is the fifth normal form

When you see a definition, you know you want to eliminate connection dependencies, and you must ensure that the data is complete

(iv, the V paradigm is less applied, mainly to understand and apply the first three paradigms.) ）

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 the basic information

What did the students choose for the class?

What is the credit for each course?

Students belong to that department, what is the basic information of the Department.

1. Case study of the second paradigm (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)

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.

Solution

Change the selectcourse relationship table to the following three tables:

Student: Student (school number, name, age, sex, department, office address, Department of Telephone);

Course: Course (course name, credits);

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

2. Example analysis of the 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 update, delete exception is not analyzed here, you can refer to 2.1.1 for analysis)

According to the third normal form, the student relationship table can be 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).

Summarize:

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