Database Design--three paradigm concept + combat

In the use of the three-paradigm design database, always thought is the first to draw the ER diagram, and then export the relationship model, and finally use three paradigms to test the database design is reasonable, but not! When we first draw the ER diagram, we should relate to the three paradigms and destroy the mistakes at the source. In order to be able to detect the earliest errors, we need to transform the ER diagram into a relational pattern of the algorithm and the three-paradigm is how to eliminate redundancy, to avoid a deep understanding of conflict, to know how to find the first error.

This paper mainly takes some things in the database design of the computer room charge system as an example, and combines the three paradigm concepts to describe the next three paradigms.

One, 1NF

definition :

If the relationship pattern R of each relationship R attribute values are non-divided atomic values, then the name R is the first paradigm.

To put it simply, the first paradigm requires attributes to be non-re-divided to see a more common example:

For example, in the computer room charge system, for example, we will encounter the date time is to write a property or separate writing it? At this point we are going to make a judgment based on the first paradigm, because the first paradigm requires that the attribute values be non-divided, so we should:

Two,2NF

Defined:

If the relationship pattern R is a 1NF , and each non-principal attribute full function relies on the candidate key, then the name R is the second paradigm.

To understand the second paradigm, the first thing to understand is what is full function dependency?

Full function dependencies:

in theR(U), ifX→Y, and forXany subset ofx,all havex→is not established, it is saidYtheXfull function dependency.

, (X1,X2)→Y,and,X1→Ynot established,X2→Ynot set up.

However, in the case of a complete function dependency, ifX→Y, butYThe incomplete function is dependent onX, it is saidYtheXPartial function dependency.

For example, at this point, X1 → established, then Y partially dependent on x1x2;

In conclusion, we can conclude that in order to satisfy the second paradigm, each non-primary key attribute must be fully dependent on the primary key.

For example, when we design a database in the computer room charge system, we may have such a design for the student table, at the very beginning, we make the student table and card-table do this for convenience of inquiry:

But when we refactor the database again, we're going to have to judge it by three paradigms. For example, the amount in the card depends on the primary key number and card number, and the card amount is also dependent on the card number, here will not meet the 2NF , We will be two of tables to be taken apart.

Three,3NF

definition :

R Meet 1NF , and each non-primary property does not pass dependent R The entity is called the candidate key E when the third paradigm.

What is a non-master attribute pass-through code?

, which is dependent on the transfer function:

For example

Or the table above, where we chose the study number as the primary key, the table has:

School Number → Card number → The in-card amount, at which time a delivery dependency occurs.

Analysis:

2NF and the 3NF , whether partial dependence or transmission dependence, is to decompose the relationship pattern, to investigate the root cause of the error, feeling or E-r The picture is not good for the sake of drawing.

Summary:

1NF the properties are split into atomic values; 2NF and the 3NF are designed to eliminate redundancy and abnormal problems;

Therefore, the three paradigms have two functions: one is to measure the quality of a database, the second is a basic principle of database design.

Database Design--three paradigm concept + combat