Database Review 11-relational model and paradigm, and database Review 11 paradigm

Database Review 11-relational model and paradigm, and database Review 11 paradigm
Database review CH11 relational model and paradigm 11.1 Relational Model Design

The Schema is the logical structure and feature description of all data in the database. The Schema of a relational database is also calledLink ModeIn my understanding, the relational model is in the database.Table Structure DefinitionAndLogical connection between multiple tables

According to a specific application, the design of the relational model uses the table form to represent the logical design process of the real world relations. The design of the nonstandard relational model will bring about the following problems:

• Data redundancy
• Update exception
• Insertion exception
• Deletion exception

The following table describes the instructor information (one teacher has one address, multiple courses can be taught, and only one teacher can be taught ):

Tname	Addr	C # (PK)	Cname
T1	A1	C1	N1
T1	A1	C2	N2
T1	A1	C3	N3
T2	A2	C4	N4
T2	A2	C5	N5
T3	A3	C6	N6

-Data redundancy: The address A1 of instructor T1 is recorded three times. Redundancy
-Update exception: the address A1 of the T1. The other two are not updated, and the DBMS does not detect inconsistency. This is a logical inconsistency, not a database inconsistency.
-Insertion exception: a new teacher without a course cannot be inserted because no C # violates primary key constraints.
-Deletion exception: Miss T3 does not take the course C6. When the tuples are deleted, the personal information of Miss T3, such as the address, is deleted.

The simplest way to solve the above problems isMode DecompositionTo separate the instructor information and course information, the following is a decomposition method:

R(Tname,Addr,C#(PK),Cname)     =>     R1(Tname(CK), Addr)    R2(C#(PK), Cname, Tname(FK))

Mode DecompositionThere is a set of standardized decomposition standards calledParadigm(For details about this chapter, see the following sections)

11.2 function dependency

Function Dependency (FD) refers to the many-to-one relationship between an attribute set and another attribute set in a relational mode, such as the course selection relationship.SC(S#, C#, Score)Given (S #, C #), only one Score corresponds to each other. The Score values of different (S #, C #) values are equal.

(1) Formal Definition

X and Y are relational modes.R(U)Subset of attribute set U,R(U)InstancerThe two tuples t1 and t2 in, ift1[X]==t2[X]Exportedt1[Y]==t2[Y]The Y function depends on X and is recordedX→Y

The same relational model may have different FD, FD, and application-related. FD is an assertion of the real world. To check the correctness of FD, we can only evaluate the meaning of attributes.

Formally define the link modeR(U, D, dom, F):

• R indicates the link mode name.
• U is an attribute set.
• D is the field from which the property value in U comes from.
• Dom is a set of mappings between attributes and fields.
• F is the dependency between attributes.

Relational pattern design is to find a minimum FD set T. Once T is implemented, all FD

(2) Ordinary function dependency

If X → Y and Y are subsets of X, X → Y is an ordinary FD (the subset must be dependent). Otherwise, it is called FD. Ordinary FD has no practical significance, you can reduce the FD set by eliminating the ordinary FD.

(3) function dependency Closure

Function dependencies have the following inference rules, called the Armstrong principle:

• Self-inverse law: if B is A subset of A, then A → B
• Augmented law: if A → B, AC → BC
• Transfer Law: If A → B and B → C, then A → C
• Self-contained Law: A →
• Decomposition law: if A → BC, then A → B and A → C
• Merge law: if A → B and A → C, then A → BC
• Combination law: if A → B and C → D, AC → BD

The entire set of function dependencies contained in the function dependency set F logic is calledFunction depends on the closure of F, Record as F +, and use a series of reasoning rules to obtain the closure of F and determine whether a function depends on X → whether Y can be introduced by F (that is, to determine whether X → Y belongs to F +)

(4) attribute Closure

Judging whether X → Y can be used to construct F + with the launch of F requires a large amount of computing. In fact, you only need to construct the ** closure of attribute X ** X +, X + is A set of all attributes that can be used by A (that is, the function depends on A's attribute set)

(5) Minimum function dependency set

The minimum function dependency set F must meet the following requirements:

• F has only one attribute on the right of each FD.
• F is not allowed, that is, every X → Y, F-{X → Y} In F is not equivalent to F.
• The left part of each FD of F cannot be correlated. That is, the F' after deleting any attribute on the left of FD is not equivalent to F.

To obtain the minimum function dependency set of a function dependency set, follow these steps:

11.3 Relationship Mode Decomposition

A decomposition p = {Ri (Ui)} of the relational mode R (U) satisfies U = detail {Ui}, and the mode decomposition must beLossless connectionAnd requiresMaintain function dependency

(1) lossless connection

Lossless connectionIt refers to the case r of a relational model divided into multiple relational r1 ,..., Rk, if r1 ,..., If the Join Operation of rk is equal to r, the mode decomposition is lossless.

(2) test lossless connections

The Chase method can detect completely lossless connections. Pattern R with n attributes is decomposed into k pattern RIS and has the following Chase process:

When the mode decomposition is a simple binary decomposition (that is, p = {R1, R2}), p is a lossless link decomposition when and only when one of the following FD is true:

• Intersection of the R1 and R2 modes → difference set of the R1 and R2 Modes
• Intersection of the R1 and R2 modes → difference set of the R2 and R1 Modes

(3) maintain function dependency

Maintain function dependencyIt means that the FD set F of relational mode R remains unchanged in the database mode after decomposition, which is the second condition of Schema decomposition.

After the formal definition is decomposed, the projection of F on the model Ri is:

π Ri (F) = {X → Y | X → Y ε F + ⋂ X, Y ⊂ Ri}

If p decomposition meets the following conditions, p ** maintains function dependency **:

(I = 1k π Ri (F) + = F +

11.4 Relationship Model paradigm

Paradigm XNF That is, a model that meets specific requirements. The process of converting the relationship patterns of the lower-level paradigm into a set of Relationship patterns of the higher-level paradigm is calledNormalization

The paradigm sequence is from low level to advanced: 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF. The high level paradigm is always the true subset of the Low Level paradigm.

According to the relational mode R's non-approximately FD set F, You can plot the nodeAttribute or attribute setThe edge is directed by the depended node to the dependent node.Directed GraphTo assist in the analysis of the relationship model, calledFunction dependency Diagram

Note: It is too late to sort out the paradigm examples in the time relationship ppt.

(1) 1NF

1NF requires that every instance R of relational mode meets the following requirements: Each attribute of every tuples t IN r has only one value, which is in relational mode.Basic Requirements

1NF not metThe link mode of has two meanings!

(2) 2NF

Assume that R only has one candidate code and the candidate code is the primary code.

Rε 1NF And every non-primary attribute of R (other attributes of non-candidate Code) isFull function dependencyWhen the primary code is used, Rε 2NF

A completely dependent on W refers to: W → A and A does not depend on the real subset X of any W. W is A primary key and may also contain multiple attributes {X, Y }, non-Primary attribute A cannotLocal function dependencyOn X or Y

2NF not satisfiedThe link mode of may include [insertion exception, deletion exception, update exception, and data redundancy]. A function dependency graph is drawn to lossless decomposition of non-2NF to obtain 2NF, but 2NF cannot completely eliminate the above problems.

(3) 3NF

Assume that R only has one candidate code and the candidate code is the primary code.

Rε 2NF And each non-primary attribute of R is notTransfer dependencyWhen the primary code is used, Rε 3NF

NameA transfer depends on YThere are: Y → X, X → A, and Y does not depend on X (that is, Y is not equal to X), and A is not A subset of X.

Not 3NFThe link mode of may also have [insertion, deletion, update, and data redundancy]. By breaking the transfer dependency chain, the link mode is divided into multiple sublink modes.

(4) BCNF

BCNF is an extension of 3NF processing R with Multiple candidate codes. If R has multiple candidate codes Rε 3NF Or [insertion exception, deletion exception, update exception, and data redundancy], which must be decomposed into BCNF paradigm.

If all the nontrivial and fully functional dependencies of the relational mode R are the deciding factors (the attribute set on the left ),Candidate code, Then Rε BCNF

If function dependency and lossless connection are required, the total number of connections can reach 3NF, but not necessarily BCNF. Because BCNF can achieve lossless connection, function dependency is not necessarily maintained.

11.5 relationship pattern decomposition into a paradigm decomposition algorithm (1) maintain function dependency decomposition R to 3NF

Algorithm steps:

(2) lossless connection and functional dependency decomposition R to 3NF

Algorithm steps:

(3) lossless join decomposition of R to BCNF

Algorithm steps:

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.