Database and database Learning

Database and database Learning

Logical design of Relational Database
How to construct a data mode suitable for specific problems
Tools for database Logic Design-Standardization Theory of relational databases
The link mode consists of five parts:

R (U, D, DOM, F) R: attribute-to-domain image set F: The dependency set of data between attributes

Data Dependency

Constraints between internal attributes of a link
Abstraction of associations between real-world attributes
Inherent nature of data
Semantic embodiment
2. Data dependent types
Function Dependency (FD)
Multivalued Dependency)
Others

The relational mode R (U, D, DOM, F) is simplified to a triple: R (U, F) When and only when a relational r on U satisfies F, r is a relation of the relational model R (U, F ).

Influence of data dependency on the relational model

[Example 1] Create a database describing the educational administration of a school: Student's student ID (Sno), Department Head name (Mname), course name (Cname) score (Grade) single link mode: Student <U, F> U = {Sno, Sdept, Mname, Cname, Grade}

Function dependency F: F = {Sno → Sdept, Sdept → Mname, (Sno, Cname) → Grade}

Problems in the Relationship Mode Student (U, F)
1. data redundancy is too large
2. Update Anomalies)
3. Insertion Anomalies)
4. Deletion Anomalies)
Conclusion:
The Student relationship mode is not a good mode.
"Good" Mode:
No insertion, deletion, or update exceptions occur,
Minimize data redundancy
Cause: it is caused by some data dependencies that exist in the mode (this is also the relationship
The root cause of pattern decomposition)
Solution: Eliminate the inappropriate data dependency through the decomposition Relationship Mode.

Decomposition Relationship Mode

This single mode is divided into three relational modes: S (Sno, Sdept, Sno → Sdept); SC (Sno, Cno, Grade, (Sno, Cno) → Grade ); DEPT (Sdept, Mname, Sdept → Mname)

Normalization

The normalization theory is used to transform the relational model. It eliminates inappropriate Data Dependencies by decomposing the relational model to solve insertion, deletion, update, and data redundancy problems. Finally, the relationship modes are separated to some extent, that is, the "one thing, one place" Pattern Design Principle

Function dependency

Function Dependency (FD)
Dependencies between ordinary functions and non-ordinary functions
Full function dependency and partial function dependency
Pass function dependency

Set R (U) to the relational mode on the property set U, and X and Y to the subset of U. If any possible relationship between R (U) is r, the attribute values of two tuples on X cannot be the same in r, but the attribute values on Y cannot be the same, it is called "X function determines Y" or "Y function depends on X" and is recorded as X → Y.

There is a relational mode R (U). X and Y are the subsets of the property set U. function dependency (FD) is a proposition in the form of X → Y, for any possible R (U) Relationship, for any two tuples t and s in r, t [X] = s [X] contains t [Y] = s [Y]. Therefore, fd x → Y is established in relational mode R (U.

For example, the function name → age depends only on the department
Set up with the same celebrity

Dependencies between ordinary functions and non-ordinary functions

In the relational mode R (U), for the subset X and Y of U, if X → Y, but y x, X → Y is a non-trivial function dependency. If X → Y, but y x, X → Y is an ordinary function dependency example: In the relation SC (Sno, Cno, in Grade), non-trivial function dependencies: (Sno, Cno) → Grade ordinary function dependencies: (Sno, Cno) → Sno (Sno, Cno) → Cno

If X → Y, X is called the deciding Attribute Group of the function dependency, also known as the deciding factor (Determinant ). If X → Y, Y → X, it is recorded as X → Y. If Y does not depend on X, it is recorded as X → Y.

Full function dependency and partial function dependency

In R (U), if X → Y has x' Y for any real subset of X, then Y is fully functional dependent on X, x f y. If X → Y, but Y is not fully dependent on X, Y is called the dependency on X function, which is recorded as x p y.

[Example 1] (Sno, Cno) → Grade is fully functional dependency, (Sno, Cno) → Sdept is part of function dependency because Sno → Sdept is established, and Sno is (Sno, cno) Real subset

Pass function dependency

In R (U), if X → Y, (y x), Y → x y → Z, Z ∈ Y, then Z is called the function dependency on X. NOTE: If Y → X, that is, X then → Y, Z is directly dependent on X. For example, in the relational Std (Sno, Sdept, Mname), the transfer functions of Sno → Sdept, Sdept → Mname depend on Sno.