Oracle paradigm reference

Standardization refers to the process of removing redundancy from your data storage. If the database design is fully standardized, when all the tables are connected by keywords, no data repetition will appear ). The advantage of standardization is obvious. It avoids data redundancy and naturally saves space. It also provides a fundamental guarantee for data consistency, this eliminates data inconsistency and increases efficiency.

1nf;
First Normal
Form)

The first paradigm is the lowest requirement for Standardization. The first paradigm requires that the data table cannot have duplicate records, that is, there is a keyword. The second requirement of 1nf is that each field cannot be further divided, that is, it has been assigned to the minimum value. The definition of relational database determines that the database meets this condition. The primary keyword meets the following conditions:
1.
The primary key field is unique in the table.
2. No duplicate in the primary key field
3. The primary key field cannot have a null value.
4. Each record must have a primary keyword.
5.
The primary keyword is the minimum subset of the keywords.

The relationship pattern that satisfies 1nf has many unnecessary duplicate values and increases the possibility of omission during data modification. To avoid such data redundancy and data update omissions, the second Paradigm (2nf) is introduced ).

The second Paradigm (
Second normal
Form)

Definition: If a link belongs to 1nf and all the non-primary key fields fully depend on the primary key word, it is called the second paradigm, which is abbreviated as 2nf.
Here is an example to illustrate the problem: a warehouse store has four fields (part number, Warehouse number, part quantity, and warehouse address ),
This library conforms to 1nf, where "part number" and "Warehouse Number" constitute the primary keyword.
However, because the "repository address" only depends on the "repository number", that is, it only depends on a part of the primary keyword, so it does not comply with 2nf,
In this way, data redundancy exists first, because the number of warehouses may be small.
Second, if a record is missing when the repository address is changed, data inconsistency exists.
Once again, if the parts of a warehouse are finished, the address of the warehouse is lost, that is, this relationship does not allow the parts to be left in a warehouse.
We can use the projection decomposition method to eliminate part of the dependency, so that the relationship reaches the 2nf standard.
The method is to break down a new two-dimensional table from the relationship. All non-keywords in each two-dimensional table are completely dependent on their primary keywords.
We can break it down into two tables (part number, Warehouse number, and part quantity) and (Warehouse number, warehouse address ).
This completely conforms to 2nf.

Third Paradigm (
Third normal
Form)

Definition: If a link belongs to 2nf, and each non-Keyword is not transmitted dependent on the primary keyword, this relationship is 3nf.
Remove the transfer dependency from 2nf, that is, 3nf. For example, there is a table (name, salary level, salary amount) Where name is a keyword,
This relationship complies with 2nf, but because the wage level determines the wage amount, it is called transfer dependency, it does not comply with 3nf,
We can also use the projection decomposition method to break down two tables: (name, salary level ),
(Salary level, salary ).

In general, standardization to 3nf meets the needs. bcnf, 4nf, and 5nf are more normalized, because they are not commonly used and are not explained or discussed. Their lower layers are all upper-layer subsets. The standard method is 1nf à (to eliminate some function dependencies of non-primary attributes on keywords) à 2nf à (to eliminate the function dependency of non-primary attributes on key words) à 3nf à (removing the partial and transmit dependencies of primary attributes on keywords) à bcnf à (removing non-trivial and non-functional dependent multi-value dependencies) à 4nf à (eliminate join dependencies not implied by the candidate keyword) à 5nf.

Projection Decomposition
The projection decomposition method is mentioned above. the normalization process of the relational model is implemented through projection decomposition. In this way, the projection decomposition of low-level relationship patterns into several high-level relationship patterns is not unique, and three conditions should be met in the decomposition:
1.
Lossless connection decomposition, no information loss after decomposition
2. Each link obtained after decomposition is a high-level paradigm. do not perform similar or even low-level decomposition.
3.
The minimum number of shards is required. This is a perfect requirement and should be minimized.

Advantages and disadvantages of Standardization
One advantage has one disadvantage. The advantage of standardization is obvious. He avoids a large amount of data redundancy, saves space, and maintains data consistency,
If it reaches 3nf, you will not change the same value in more than one place. If your record changes frequently, this advantage will surpass all possible disadvantages!
The biggest disadvantage is that placing information in different tables increases the difficulty of operations and the cost of connecting multiple tables is huge.
("The theory of time space Interchange", which is fabricated by the author. Never take it as an argument! If time is saved, the cost of space is inevitable. If space is saved, the cost of time is inevitable. Time and space are in the computer field as a contradiction and they interact with each other, unity of Opposites ). Because the join operation between a table and a table is a two-relationship Cartesian Product (if one or more records exist and two or more M records exist in the table, if no join conditions exist, N * m records are connected together, and the number of records is unacceptable. Rather, a large number of tables are connected together.) A large number of useless or even invalid records are bound, the cost of performance is huge.

Denormalization)
Even if you spend all your lunch breaks and make a fully standardized database (your university professor can prove), it is still not perfect. You may not be able to afford the performance problems brought about by standardized design. In this case, you need to prepare for non-standardization. Non-standardization is the violation of standardized rules that you perform in order to gain performance benefits, and there is no magic in it. It is a performance benefit analysis, trial and re-Attempt and continuous re-evaluation process. It also has many methods, but most of them are related to actual applications, including copying attributes, copying external keywords, table merging, and table re-combination, you can select the most effective method based on the actual application. Introduction

The design paradigm of databases is the specifications that need to be met by database design. databases that meet these specifications are concise and have clear structures. At the same time, there will be no insert or delete operations) and update operations are abnormal. On the contrary, it is a mess, which not only creates troubles for database programmers, but also features an ugly face. It may store a large amount of unnecessary redundant information.

Is the design paradigm hard to understand? No, we certainly cannot understand and remember the mathematical formulas given to us in university textbooks. Therefore, many of us simply do not follow the paradigm to design databases.

In essence, the design paradigm can be clearly stated in an image and concise discourse, and it is clear. This article will give a general description of the paradigm, and explain how to apply these paradigms to practical engineering using the database of a simple forum designed by the author as an example.

Paradigm description

1nf: the fields in the database table are single attributes and cannot be divided into {personal understanding: Like a family, there are several sons, and other sons are composed of one part, only one son needs two parts, that is, this is not a normal family}
. This single attribute is composed of basic types, including integer, real number, complex type, logical type, and date type.

For example, the following database tables conform to the first paradigm:

Field1	Field2	Field3	Field4

Such database tables do not conform to the first paradigm:

Field1	Field2	Field3		Field4
		Field3.1	Field3.2

Obviously, 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 one column of the database table into two or more columns. Therefore, you want In DBMS, it is impossible to design a database that does not conform to the first paradigm.
.

2nf: There is no function dependency between non-Keyword fields and any candidate keyword fields in the database table.
(Some function dependencies refer to some fields in the composite keywords to determine non-key fields.
), That is, all non-Keyword fields are completely dependent on any set of candidate keywords. {Personal understanding: for example, in a family, any decision can only be counted after both the father and mother pass the same statement, which means it is normal.If a daughter can only decide what to do by her mother, this violates the principle and does not satisfy the agreement .}

Assume that the course selection relation table isSelectcourse (student ID, name, age, course name, score,
Credits), the keyword is a combination of keywords (student ID, course name), because of the following decision relationship:

(Student ID, course name) → (name, age, score,
Credits)

This database table does not meet the second paradigm because of the following decision relationships:

(Course name) → (credits)

(Student ID) →
(Name,
Age)

That is, fields in the combined keywords determine non-keywords.

Because 2nf is not met, this course selection relation table has the following problems:

(1)
Data redundancy:

The same course is selected by N students, and "Credits" are repeated for n-1 times. The same student takes m courses, and the name and age are repeated for m-1 times.

(2)
Update exception:

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.

(3)
Insertion exception:

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

(4)
Deletion exception:

Assuming that a group of students have completed their electives, 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.

Change the selectcourse table to the following three tables:

Student: Student (student ID, name,
Age );

Course: Course (Course name,
Credits); {personal understanding: You can addThe ID field is used as the primary key, because if the course name is changed in the future and the database has been running for 10 years and there are 10 million course selection records, you need to update these 10 million records, it is also a matter of resource fees. If you have an ID, no matter how your name is changed, only one current record will be affected}

Selectcourse (student ID, course name, score ). {Here it is changed:Selectcourse (student ID,
Course ID, score )}

Such database tables conform to the second paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.

In addition, all database tables with single keywords comply with the second paradigm, because it is impossible to have a combination of keywords.

Third Paradigm (3nf): Based on the second paradigm, if there is no transfer function dependency for any candidate keyword segment in the data table, it complies with the third paradigm. The so-called transfer function dependency refers
If the "A → B → C" Deciding relation exists, the C transfer function depends on. Therefore, database tables that meet the third paradigm should not have the following dependency:

Keyword segment → non-Keyword segment x
→ Non-Keyword field y

Assume that the student relationship table is student (student ID, name, age, school, school location,
College phone number), the keyword is a single keyword "student ID", because there is a decision relationship as follows:

(Student ID) → (name, age, school, school location,
College phone number)

This database complies with 2nf but does not comply with 3nf because of the following decision relationships:

(Student ID) → (school location,
College 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 can also cause data redundancy, update exceptions, insertion exceptions, and deletion exceptions. You can analyze and learn this information on your own.

The student relationship table is divided into the following two tables:

Student: (student ID,
Name, age, school );

School: (school, location,
Phone number ).

Such database tables conform to the third paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.

Bois-cell Paradigm (bcnf): Based on the third paradigm, if no field in the database table is dependent on the transfer function of any candidate keyword segment, it complies with the third paradigm. Assume that the warehouse management relationship table isStorehousemanage (warehouse ID, storage item ID, administrator ID,
Number), and an administrator only works in one warehouse. A warehouse can store multiple items. This database table has the following decision relationships:

(Warehouse ID, storage item ID)
→ (Administrator ID, quantity)

(Administrator ID, storage item ID) → (warehouse ID, quantity)

Therefore, (repository ID,
Storage item ID) and (administrator ID,
Storage item ID) is a candidate keyword for storehousemanage. The unique non-Keyword segment in the table is the number, which conforms to the third paradigm. However, the following decision relationships exist:

(Repository ID)
→ (Administrator ID)

(Administrator ID) →
(Repository ID)

That is, the keyword segment determines the keyword segment, so it does not conform to the bcnf paradigm. It has the following exceptions:

(1)
Deletion exception:

When the respiratory is cleared, all the "Storage item ID" and "quantity" information are deleted, and the "warehouse ID" and "administrator ID" information are also deleted.

(2)
Insertion exception:

When a Warehouse does not store any items, an administrator cannot be assigned to the warehouse.

(3)
Update exception:

If the repository is changed to an administrator, the administrator ID of all rows in the table must be modified.

Break down the warehouse management relationship table into two Relational Tables:

Warehouse Management: storehousemanage (warehouse ID,
Administrator ID );

Repository: storehouse (repository ID, storage item ID,
Quantity ).

Such database tables conform to the bcnf paradigm and eliminate deletion, insertion, and update exceptions. Paradigm Application

Let's get a forum database step by step, with the following information:

(1)
User: user name, email, home page, phone number, and contact address

(2) post: post title, post content, reply title, reply content

For the first time, we designed the database to only exist tables:

User Name

Email

Home Page

Phone number

Contact address

Post Title

Post content

Reply title

Reply content

This database table conforms to the first paradigm, but no set of candidate keywords can determine the entire row of the database table. The username of the unique keyword segment cannot completely determine the entire tuples. We need to add The "post ID" and "Reply ID" fields change the table:

User Name

Email

Home Page

Phone number

Contact address

PostID

Post Title

Post content

ReplyID

Reply title

Reply content

In this way, the keywords in the data table are(Username, post ID, reply ID) can determine the entire line:

(User name, post ID, reply ID)
→
(Email, homepage, phone number, contact address, post title, post content, reply title, reply content)

However, such a design does not conform to the second paradigm because of the following decision relationships:

(User name)
→ (Email, home page, phone number, and contact address)

(Post ID) → (post title, post content)

(Reply ID) →
(Reply title, reply content)

That is, some functions of non-Keyword fields depend on the candidate keyword fields. Obviously, this design will cause a large amount of data redundancy and operation exceptions.

We break down a database table into (underlined keywords ):

(1)
User information: user name, email, home page, phone number, contact address

(2) post information: Post ID, title, content

(3)
Reply information: reply ID, title, content

(4) post: User Name, post ID

(5)
Reply: Post ID, reply ID

This design meets the requirements of the 1st, 2, 3 and bcnf paradigms. But is this the best design?

Not necessarily.

We can see that there is a 1: n relationship between the "user name" and "post ID" in the 4th "posts, therefore, we can merge "Post" into "post information" of 2nd items; in 5th "reply ",
The relationship between "post ID" and "Reply ID" is also 1: N. Therefore, we can merge "reply" into "Reply information" of item 3rd. In this way, data redundancy can be reduced in a certain amount. The new design is as follows:

(1)
User information: user name, email, home page, phone number, contact address

(2) post information: User Name, post ID, title, content

(3)
Reply information: Post ID, reply ID, title, content

Database Table 1 clearly meets the requirements of all paradigms;

Database Table 2 contains some functional dependencies of non-Keyword segments "title" and "content" on the keyword segment "post ID", that is, it does not meet the requirements of the second paradigm, however, this design does not cause data redundancy and operational exceptions;

In database table 3, some function dependencies of non-Keyword segments "title" and "content" on the keyword segment "Reply ID" do not meet the requirements of the second paradigm, however, similar to database table 2, this design does not cause data redundancy and operation exceptions.

From this we can see that it is not necessary to forcibly meet the requirements of the paradigm. For a 1: n relationship, when one side is merged to the other side of N, the other side of N will no longer meet the second paradigm, but this design is better!

For M: N relationships, one or n sides of M cannot be merged to the other, which may result in non-compliance with the paradigm requirements, Operation exceptions and data redundancy.

For a relationship, we can merge 1 on the left or 1 on the right to the other side. This design does not meet the requirements of the paradigm, but does not cause operation exceptions and data redundancy.

Conclusion

The database design that meets the requirements of the paradigm is clear in structure, while avoiding data redundancy and operational exceptions. This means that the design that does not meet the requirements of the paradigm must be incorrect. In the case of a database table with a or 1: n relationship, rather than conforming to the requirements of the paradigm, the merger is reasonable.

When designing databases, we must always consider the requirements of the paradigm.