Database three paradigm

Objectives of database three paradigms: 1. reduce data redundancy 2. eliminate exceptions (insert, delete, update) 3. to make data organizations more harmonious, the first paradigm (1NF): Non-duplicate columns the so-called first paradigm (1NF) means that each column of the database table is an inseparable basic data item. For example, an object cannot have multiple or duplicate attributes.

Objectives of database three paradigms: 1. reduce data redundancy 2. eliminate exceptions (insert, delete, update) 3. to make data organizations more harmonious, the first paradigm (1NF): Non-duplicate columns the so-called first paradigm (1NF) means that each column of the database table is an inseparable basic data item. For example, an object cannot have multiple or duplicate attributes.

Objectives of the database three paradigm:

1. reduce data redundancy

2. Eliminate exceptions (insert, delete, and update)

3. Make data organization more harmonious

1NF: No duplicate Column

The first paradigm (1NF) indicates that each column in the database table is an inseparable basic data item. For example, an object attribute cannot have multiple values or duplicate attributes.

Example:

Note: The attribute value in this table is "split. In the "phone" attribute, Xiaoming: the property value is divided into two.

Note: For Xiaoming in the "phone" attribute, the attribute value is divided into two.

Both of the above situations do not meet the first paradigm. The following table can be used to address the preceding situation:

Second Paradigm (2NF): Attributes fully depend on the primary key (removing the dependency of some molecular functions)

The second Paradigm (2NF) is established on the basis of the first paradigm (1NF), that is, the second paradigm must satisfy the first paradigm first. The second paradigm requires that each force or row in the database table be uniquely distinguished. To achieve differentiation, you usually need to add a column to the table to store the unique identifier of each instance.

The second paradigm requires that the attributes of an object fully depend on the primary keyword. Attributes that only depend on a part of the primary keyword cannot exist. If this attribute exists, it should be separated from the primary keyword to form a new entity.

Example:

A student must take a course in a specific classroom. So there are (students, courses)-> classrooms

A student must be taught by a specific teacher. So there are (students, courses)-> teachers

A student must take a certain course. So there are (students, courses)-> teaching materials

However, a course must specify a teaching material (student, course) as the primary key, but the course determines the teaching material. This is called incomplete dependency or partial dependency. In this case, the second paradigm is not satisfied! Because:

1. What should I do if the principal wants to add a new course called calculus and the teaching material is college mathematics? Students haven't selected courses yet, while students are the primary attributes. The primary attributes cannot be blank. How can we record the courses? Where can we record the teaching materials? ...... Depressed, right? (Insertion exception)

2. If no student learns the first-year Chinese Language (upper) or the first-year Chinese Language (lower) in the next semester, there will be no first-year Chinese (upper) in the table, and no "primary language 1" will be available. At this time, the principal asked: what teaching materials are used in the First-Year text (above ?...... Depressed, right? (Deletion exception)

3. The principal said: change the first-year Chinese (on) teaching materials to "University Chinese". 10000 of the students chose this course, which is a huge change! It's exhausting ...... Depressed, right? (Modification/update exception)

What should we do? Projection decomposition: divides a table into two or more tables:

3NF: attributes do not depend on other non-primary attributes (Transfer dependency is eliminated)

To satisfy the third paradigm, we must first satisfy the second paradigm. The third paradigm requires that a database and a database table do not contain the keyword information that has already been included 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. In short, the third paradigm is that attributes do not depend on other non-primary attributes.

Example:

A teacher must be able to determine a Teacher title. (Student, course)-> teacher-> title.

Problem:

1. The instructor has upgraded and changed to the Professor. To change the database, there are N entries in the table, and N changes have been made ...... (Modification exception)

2. I have no candidates for this teacher's class, and I have no record of the teacher's title ...... (Deletion exception)

3. A new teacher has not assigned any courses yet. Where can he remember his title ?...... (Insertion exception)

What should we do? Like above, projection decomposition:

Summary: there are not only three paradigms in the database, but also many paradigms. The higher the level of the paradigm, the more tables involved.

Problems caused by multiple tables:

1. Multiple tables need to be connected during query, increasing the query complexity.

2. Multiple tables need to be connected during query, reducing the performance of database query.

Therefore, the problems caused by data redundancy are not necessary to use the database paradigm.

However, the third paradigm greatly reduces data redundancy and causes insertion exceptions, update exceptions, and deletion exceptions. So it is enough to apply the third paradigm.