Database design Guidelines (first, second, third paradigm description) I, relational database design Paradigm Introduction 1.1 First normal form (1NF) No duplicate columns
The so-called First paradigm (1NF) refers to the fact 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 multiple values or cannot have duplicate properties. If duplicate attributes are present, you may need to define a new entity, which is composed 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 a table contains only one instance of information. In short, the first paradigm is a column with no duplicates.
Note: In any relational database, the first paradigm (1NF) is the basic requirement for relational schemas, and a database that does not meet the first normal form (1NF) is not a relational database.
1.2 Second normal form (2NF) property fully dependent on primary key [eliminate partial child function dependency]
The second paradigm (2NF) is established on the basis of the first paradigm (1NF), i.e. satisfying the second normal form (2NF) must first satisfy the first paradigm (1NF). The second normal form (2NF) requires that each instance or row in a database table must be divided by a unique region. It is often necessary to add a column to the table to store the unique identity of each instance. For example, the Employee Information table adds the 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 main code.
The second normal form (2NF) requires that the attributes of an entity depend entirely on the primary key. The so-called full dependency is the inability to have a property that depends only on the primary key, and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. It is often necessary to add a column to the table to store the unique identity of each instance. In short, the second paradigm is that properties depend entirely on the primary key.
1.3 Third normal form (3NF) property does not depend on other non-primary properties [eliminate transitive dependencies]
Satisfying the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (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, where each department has a department number (dept_id), a department name, a department profile, and so on. Then the department number is listed in the Employee Information table, the department name, department profile and other departments related information can no longer be added to the Employee Information table. If there is no departmental information table, it should be built according to the third paradigm (3NF), otherwise there will be a lot of data redundancy. In short, the third paradigm is that properties do not depend on other non-principal properties.
Ii. Analysis of Paradigm application examples
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 consists of a basic type, including Integer, real, character, logical, date, and so on. 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 these DBMS do not allow you to divide a 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 your existing DBMS.
First, let's make sure that the content to be designed includes those. School number, student name, age, gender, course, course credit, department, academic results, office 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 to this information that we say are concerned about.
- Students have the basic information
- What did the students do when they chose those classes?
- What are the credits for each lesson?
- Students belong to that department, the basic information of the Department is what.
2.1 Example analysis of the second normal form (2NF)
First of all, we consider that all of this information is placed in a table (student, name, age, gender, course, course credit, department, academic results, office address, Office Phone) The following dependencies exist.
(school number) → (name, age, gender, department, office address, Office phone)
(course name) → (credits)
(School number, course) → (academic score) 2.1.1 Problem Analysis
Therefore, the following problems will arise if the requirements of the second paradigm are not satisfied
Data redundancy: The same course is taught by n students, and "credits" are repeated n-1 times; the same student took the M course, and the name and age were repeated m-1 times.
Update exception:
1) If the credit of a course is adjusted, the "credit" value of all the lines in the data sheet should be updated, otherwise the same course credit will be different.
2) Suppose that a new course is to be opened and no one has yet been enrolled. Thus, the course name and credits cannot be recorded in the database because there is no "learning number" keyword.
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 were also removed. Obviously, this can also lead to an insertion exception. 2.1.2 Solutions
Change the course of the elective selectcourse to the following three tables:
- Student: Student (school number, name, age, gender, department, office address, Office phone);
- Course: Course (course name, credits);
- Elective relationship: Selectcourse (School number, course name, score).
2.2 Example analysis of the third paradigm (3NF)
Then look at the student table above student (school number, name, age, gender, department, office address, Office Phone), the keyword is a single keyword "study number", because the following decision relationship exists:
(school number) → (name, age, gender, department, office address, Office phone)
But there is a decision relationship
(school number) → (school) → (college location, college phone)
That is, there is a non-critical field "College location", "College phone" to the key field "study number" of the transfer function dependency.
It also has the condition of data redundancy, update exceptions, insert exceptions, and delete exceptions. (Updates to the update, delete the exception is not analyzed in this, you can participate in 2.1.1 analysis)
According to the third paradigm, the student relationship table is divided into the following two tables to satisfy the third paradigm:
Student: (School number, name, age, gender, department);
Department: (Department, Office address, Office phone).
Summarize
The database tables above are in compliance with the I,II,III paradigm, eliminating data redundancy, update exceptions, insert exceptions, and delete exceptions. Reprint Address: http://www.blogjava.net/xzclog/archive/2009/01/04/249711.html
Database design Guidelines (first, second, third paradigm description)
