relational database theory

This paper mainly introduces the theoretical basis of relational database design which is closely related to the establishment of relational model (i.e. relational table structure), which is the basic knowledge of relational paradigm. The paradigm theory provides a theoretical standard for judging the relationship model, and is an important basis for database designers to design the database.

1. Database design

Database design is an important part in the process of application system development, which is designed by database designers according to information needs, processing needs, DBMS specifications, operating system and hardware environment instructions. The design process is divided into four stages: need analysis and data analysis stage, concept design stage, logic design stage and physical design stage.

(1) Needs analysis and data analysis

The needs analysis and the data analysis mainly refers to investigates the object which will be processed, obtains the user to the database information request, the processing request as well as the security and the integrity request from the survey.

(2) Conceptual design

Conceptual design refers to the information that will be processed in the form of the user's easy to understand, on the basis of analysis and data analysis.

(3) Logical Structure design
Logical structure design refers to the transformation of the conceptual structure of a database into the logical structure of the database supported by the selected DBMS. The results of the logical structure design of the database are not unique. To optimize the database structure, we can use the normalization theory to design the database logic. This article comes from the programming gateway

(4) Physical design of database

The physical structure of a database mainly refers to the storage structure and access method of the database on the physical device. The process of choosing a physical structure that best suits the application environment for a given database logical structure is called the physical design of the database.

2. Function dependency
In the actual database design, in addition to the entity set there is a relationship between the attributes (that is, between the data items) there is a certain dependency, which introduces the function of the attribute dependencies. Function dependency is an important concept in database design, we give the definition of function dependency.

Set R (U) is a relational pattern on the attribute set U, X,y is a subset of U. For any possible relationship R (that is, for the content of the relational schema r in the database for each moment) for R (U), no two tuples in R can be equal in the attribute component of X, while the attribute component values in Y are unequal, then the X function is called Y, or the Y function is dependent on X. Remember as X->y. In layman's parlance, for each value of an attribute or attribute group X in R, Y has only one value in R.

3.Armstrong Axiom

In 1974, W.w.armstrong first proposed a function-dependent axiom system called the Armstrong Axiom. For a set of known function dependencies, this axiom can be used to derive the function dependencies that are contained.

Armstrong Axiom:

Set U as an attribute population set, F is a set of function dependencies on U, and the following inference rules for relational mode R (U,F):

(1) Reflexive law

If Y is contained in the x,x contained in U, then x->y is contained in F.

(2) Augmentation law

If the x->y is contained in F and Z is included in U, then x*z->y*z is contained in F.

(3) Transfer law

If the x->y,y->z is contained in F, then the x->z is contained in F.

4. Standardized Design method

A relational pattern that satisfies a certain condition is called a paradigm. From 1971 to 1972, E.f.codd systematically proposed the first paradigm (1NF), the second normal form (2NF) and the third paradigm (3NF) concept, 1974 Codd and Boyce jointly presented the bcnf,1976 year Fagin presented 4NF, Then someone proposed the 5NF. The relational pattern of a low-level paradigm, which is transformed into a set of relational schemas of several higher-level paradigms, is called normalization.

▲ First Paradigm: If a relational pattern, which each component is an irreducible data item, then this relational pattern is the first normal form.

▲ Second Normal form: If a relationship r∈1nf, and each non-primary attribute full function relies on code, then R∈2NF.

▲ the third normal form: if the relation mode R (U,F) is the first normal form, and does not exist the transfer dependence of the non-main attribute to the code, then it is called R (U,f) as the third normal form.

▲BCNF Paradigm: The BCNF paradigm is the third normal form of revision, sometimes called the third paradigm. The bcnf eliminates partial function dependencies and transitive function dependencies that may exist in 3NF in the main property pair code.

In the paradigm, 3NF and bcnf are commonly used. When designing a database, you should consider it synthetically, because although the decomposition relational pattern can reduce the data redundancy, but also can overcome the problem such as the data operation is easy to be abnormal, but then the complexity of the database operation is improved, the system pays more for the operation of the multi-relational table.

Back to the database basics ABC Directory