The third paradigm of Oracle database design

First, the database design paradigm and its significance and shortcomings

The design paradigm of database is the specification that database design needs to meet, the normalization of database is the way to optimize the structure of table and how to organize the data into the table, so that the data is clearer and more concise. In practice, it is common to divide a database into two or more tables and define relationships between tables for data isolation, adding, deleting, and modifying a field only in one table, and then passing through the defined relationships to the remaining tables in the database (and the meaning of layered thinking is very much the same). This allows us to eliminate many of the opportunities for error or junk data and to reduce the amount of work necessary to update the information.

At present, there are six main paradigms: the first paradigm, the second paradigm, the third paradigm, the BC Paradigm, the fourth normal form and the fifth paradigm. Meet the minimum requirements called the first normal form, referred to as 1NF. On the basis of the first paradigm to further meet some requirements for the second paradigm, referred to as 2NF. The rest and so forth

Things tend to be multi-faceted, and the design paradigm also brings some trouble: it's difficult to operate because you need to contact multiple tables to get the data you need, and the higher the paradigm, the worse it will be. So the use of how high the paradigm needs to weigh the pros and cons, generally in the project, the use of the third paradigm is sufficient, good performance and easy to manage data.

Second, let us give an example of the three paradigms of database design

Description: Examples of "school computer room charge system" of the "Student Information table", "students up and down the record table" part of the field

1, the first paradigm 1NF

Definition: A field in a database table is a single attribute and cannot be divided.

Simply put, each attribute is an atomic term, indivisible.

1NF is the minimum condition that a relational pattern should have, and if the database design does not meet the first paradigm, it is not called a relational database. That is to say, as long as the relational database, it must satisfy the first normal form.

Let's start with a list of non-conforming 1NF 1-1

Cardno	Studentno	Studentname	Sex	Department	Cardcash	Userid	UserLevel	Time
001	021101	Xiao ming	Man	School of Education, Department of psychology, Class 1	100	Operator	Operator	2011/10/03,09:00

This table does not conform to 1NF because the department and Time fields can be re-divided, so you should change to table 1-2:

cardno		studentname	sex	academy	major	class	cardcash	userid	userlevel	date	time
001	021101	Xiao ming	Man	Faculty of Education	Department of Psychology	1	100	Operator	Operator	2011/10/03	09:00

2, the second paradigm 2NF

Definition: A non-critical field in a database table does not have a partial function dependency on any of the candidate key fields, which conforms to the second paradigm.

Note: What is function dependence, see Baidu Encyclopedia (http://baike.baidu.com/view/40008.htm).

If the value of a field a in a table is determined by the value of another field or a set of field B, it is called a function dependent on B. 》

2NF can reduce insert exceptions, delete exceptions, and modify exceptions.

Simply put, on the one hand, the second paradigm must satisfy the first paradigm, otherwise there is no need to talk about the second paradigm.

On the other hand, when the non-primary key information in a table is not determined by the entire primary key function, that is, when there is a part of the table that is not a primary key or that depends on the part of the primary key, the 2NF is usually violated.

Let's take a look at the above. Table 1-2 satisfying 1NF

cardno		studentname	sex	academy	major	class	cardcash	userid	userlevel	date	time
001	021101	Xiao ming	Man	Faculty of Education	Department of Psychology	1	100	Operator	Operator	2011/10/03	09:00

We see that in this table, Studentname,sex,academy,major,class,cardcash,userid,date,time can be determined by Cardno and Studentno. So you can use the combination of Cardno and Studentno as the primary key.

However, we find that Cardcash is not entirely dependent on Cardno and Studentno, only through the Cardno can be determined Cardcash, because a card, will certainly have the amount of the card. This creates a partial dependency. In this case, the second paradigm is not satisfied.

Modified to:

Let's look at another example, and the student-to-computer record table will be more obvious. Table 2-1

cardno

Studentno

studentname

sex

department

major

class

ondate

ontime

offdate

offtime

consumetime

consumemoney

001

0211

Xiao ming

Man

Faculty of Education

Department of Psychology

1

2011/10/14

09:00

2011/10/14

10:00

1

2

As we can see, in this table, Studentname,sex,department,major,class is directly dependent on studentno, and does not depend on other fields in the table, so that the design does not conform to the 2NF non-primary key information is not determined by the entire primary key function.

We can optimize the 1-2 and the 2-1 to:

3-1

Studentno	Cardno	Userid	UserLevel	Date	Time
021101	001	Operator	Operator	2011/10/03	09:00

3-2

Cardno	Cardcash
001	98

3-3

Cardno	Ondate	OnTime	Offdate	Offtime	Consumetime	Consumemoney
001	2011/10/14	09:00	2011/10/14	10:00	1	2

3-4

Studentno	Studentname	Sex	Academy	Major	Class
021101	Xiao ming	Man	Faculty of Education	Department of Psychology	1

3. The third Paradigm 3NF

Definition: On the basis of the second paradigm, if there is no non-critical field in the data table, the transfer function dependency on either of the candidate key fields is 3NF compliant.

Let's take a look at the optimized table in the example above 3-1

Studentno	Cardno	Userid	UserLevel	Date	Time
021101	001	Operator	Operator	2011/10/03	09:00

In the table, a userid can determine a userlevel. In this way, the UserID relies on Studentno and Cardno, and Userlevel relies on UserID, which leads to a transitive dependency, and 3NF is the elimination of that dependency.

We optimized the 3-1 to get:

4-1

Studentno	Cardno	Userid	Date	Time
021101	001	Operator	2011/10/03	09:00

4-2

Userid	UserLevel
Operator	Operator

We see that the third paradigm rule looks for properties that eliminate the primary key of a table that is not directly dependent on the first and second normal forms. We have created a new table for all the information that is not associated with the primary key of the table. Each new table holds information from the source table and the primary key that they depend on.

Iii. Summary

Database design normalization allows us to better adapt to change, enabling you to change business rules, requirements, and data without the need to reconstruct the entire system.

Oracle Database design Third paradigm