Database standardization design

Time:2021-1-22

Abnormal operation

Modify exception, insert exception, delete exception

Data dependence

The connection between data

Functional dependence FD

Attribute is the most basic data dependency

If x is determined, thenThe only definite yY is said to depend on X, X – > y

If X – > y and Y is a subset of X, it is calledTrivial functional dependenceOrdinary FD

If X – > y and for any and any proper subset X ‘, there is x ′ y, then y is calledComplete functional dependenceIn X, denote as X → y

If the non primary attribute determines the non primary attribute, theTransfer function dependency

Standardized design

In a given application environment, the optimal database schema is constructed

normal form

The relationship model that meets the specific requirements is used to evaluate the advantages and disadvantages of the relationship model

Paradigms can be upgraded

 

1NF

If the value range of each attribute is atomic, then r belongs to 1NF

1NF has the problems of insertion exception, deletion exception, update exception and data redundancy

2NF

If R satisfies 1NF and each nonprimary attribute is completely dependent on the candidate key, it belongs to 2NF

Candidate keys: 1. All attributes can be determined; 2. Irreducible, that is, a subset of candidate keys cannot determine all attributes

Candidate keys can make a combination of multiple attributes

The solution is to divide the unsatisfied table into multiple tables

 

3NF

If R belongs to 2NF and each non primary attribute does not pass a candidate key, then r belongs to 3NF

That is, there is no case in which the non primary attribute determines the non primary attribute

Solution: construct the passed properties as a single table

 

Modified 3NF

All properties are not passed depending on the candidate key

Most of the 3NF are consistent with the modified 3NF

Counter example: postcode (city, address, postcode)

 

 

A relationship ifIt can’t be used without satisfying 3NF

3NF can reduce the abnormality and information redundancy in the original relationship to a certain extent

The higher the degree of standardization, the better

Normalization is a process of schema decomposition. The principles are: 1. Data equivalence: lossless decomposition; 2. Semantic equivalence: maintaining dependency