Relational Database design paradigm

Relational Database design paradigm

Introduction

The relationships in relational databases must meet certain requirements, that is, they must meet different paradigms.

Currently, relational databases have six paradigms: 1nf, 2nf, 3nf, 4nf, and 5nf) and the sixth paradigm (6nf ). The first paradigm (1nf) meets the minimum requirements ). The second Paradigm (2nf) that meets more requirements on the basis of the first paradigm, and the other paradigms are similar. Generally, databases only need to satisfy the 3nf.

No repeated column in the first paradigm (1nf)

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.

2nf attribute

Completely dependent on the primary key [removing some function dependencies of non-primary attributes on the primary code]

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

The second Paradigm (2nf) requires that the attributes of an object fully depend on the primary keyword. The so-called full dependency refers to the fact that there cannot be an attribute that only depends on a part of the primary key word. If so, this attribute and this part of the primary key word should be separated to form a new entity, the relationship between the new object and the original object is one-to-multiple. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. In short, the second paradigm is that attributes fully depend on the primary key.

3nf attributes

Independent from other non-primary attributes [Removing transfer dependencies]

The third paradigm (3nf) must satisfy the second Paradigm (2nf) first ). In short, 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. In short, the third paradigm is that attributes do not depend on other non-primary attributes.

Analysis of paradigm Application Instances

The following uses a school's student system as an example to illustrate the application of these paradigms. First, the first paradigm (1nf): the 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. 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.

First, let's determine the content to be designed including those. Student ID, Student name, age, gender, course, course credits, department, subject score, subject address, telephone number, and other information. For simplicity, we only consider the field information. We are concerned with the following information.

What basic information does students have?

What are the scores of the students who have chosen the courses?

What are the credits of each course?

The student belongs to that department. What is the basic information of the department.

2nf instance analysis

First, we should consider placing all this information in a table (student ID, Student name, age, gender, course, course credits, department, subject score, subject address, and phone number) the following dependencies exist.

(Student ID) → (name, age, gender, Department, Department address, Department phone number)

(Course name) → (credits)

(Student ID, course) → (subject score)

　　Problem Analysis

Therefore, the following problems may occur if the second paradigm is not met:

Data redundancy: N students take the same course, and n-1 times for "Credits"; m courses for the same students, and 1-1 times for names and ages.

Update exception:

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

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

Deletion exception: if a group of students have completed the electives of the course, 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.

　　Solution

Change the selectcourse table to the following three tables:

Student: Student (student ID, name, age, gender, Department, Department address, Department phone number );

Course: Course (Course name, credits );

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

3nf instance analysis

Next, let's look at the student table student (student ID, name, age, gender, Department, Department address, Department phone number). The keyword is a single keyword "student ID", because the following decision-making relationship exists:

(Student ID) → (name, age, gender, Department, Department address, Department phone number)

However, the following decision relationships still exist:

(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 also has data redundancy, update exceptions, insertion exceptions, and deletion exceptions. (Data update. if an exception is deleted, this will not be analyzed. For details, refer to 2.1.1)

Based on the third paradigm, the Student Relationship table can be divided into the following two tables to meet the third paradigm:

Student (student ID, name, age, gender, Department );

Department: (Department, Department address, Department phone number ).

Summary

The above database tables comply with the I, ii, iii paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.