ArticleDirectory
- I. Introduction to the relational database design paradigm
- Ii. Analysis of paradigm application examples
I. Introduction of the relational database design paradigm 1.1 first paradigm (1nf) Non-duplicate Columns
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.
1.2 The 2nf attribute of the second paradigm is completely dependent on the primary key [Removing the dependence of partial molecular functions]
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 as the primary keyword, 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 keyword. If so, this attribute and this part of the primary keyword 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.
1.3 The 3nf attribute does not depend on other non-primary attributes. [Removing transfer dependencies] The 3nf attribute must satisfy the 2nf attribute 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.
Ii. Analysis of paradigm application examples 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.
2.1 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) 2.1.1 Problem Analysis
Therefore, if the second paradigm is not met, the following problems may occur: data redundancy occurs when n students take the same course and "Credits" are repeated for n-1 times; the same student took M courses, and the name and age of the Students repeat the M course.
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.
2.1.2 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 ).
2.2 analyze the 3nf instance and check the student (student ID, name, age, gender, Department, Department address, Department phone number) in the student table above. The keyword is "student ID ", the following decision relationships exist:
(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. (For data updates, we will not analyze them in addition to the regular ones. We can perform analysis based on 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 ).
In summary, the above database tables comply with the I, ii, iii paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.
