"Introduction to Database System"--6 relational data theory

Relational mode five tuples

R<u,d,dom,f>

r--Relationship Name

u--Properties (Group)

The domain from which the attributes in the d--u are derived

dom--attribute-to-domain mappings

Data dependencies in F--u (function dependencies, multivalued dependencies, connection dependencies, etc.)

Harm caused by poor data dependence

Data redundancy

Update exception

Insert exception

Delete exception

function dependency

The concept x, Y is a subset of R, there is no two tuples on the X value equal, and on the Y value of unequal, said Y function depends on X, as x→y.

Relationship Example Student (Sno,sname,cno,cname,sdept,dept_manager,grade)

Understand that there is not more than one x with multiple Y. Sno→sdept,sdept→dept_manager

Classification

The trivial function relies on x→y, Y? X. (sno,sname) →sname/* meaningless, not discussed */

The non-trivial function relies on x→y, Y? X. Sno→sname

Reciprocal functions depend on x→y, y→x. Sno→sname, sname→sno/* applies to cases where duplicate names are not allowed */

The full function relies on x→y, and Y does not depend on the any true subset of X.

Some functions rely on x→y, and Y can also depend on a true subset of X.

The transfer function relies on x→y, and Y does not depend on Z, y→z.

Code

Candidate code has a subset of K in R<u,f>,u, and K is the candidate code

When the main code has more than one candidate code, choose one as the main code

Attributes in any one of the main properties of a candidate code, all primary properties

Full code u does not exist subset candidate code, u only determine itself, then the full code

Multi-valued dependency

Defined

For R<u,f>,u has a subset of X, Y, Z, and Z=u-x-y, if the value of the corresponding set of Y values for a given T (x,z) value is only related to the X value, and is not related to the change in Z value, then the Y multi-value depends on X, which is recorded as X→→y

If z is a null value, it is called trivial multi-valued dependency

Example

Teaching (Course, Teacher, Course_book)

/* Course-Teacher-textbook book */

Understand

A teacher can take many courses, a textbook can be used by multiple courses (such as "data structure" can be used in mathematics, computer use), to solve the 2NF transmission dependence

Each course has a fixed group of teachers, there is a fixed use of textbooks, so the same course, the same teaching materials corresponding to the teacher group, will not be changed because of the textbook

Properties

Symmetry: If x→→y, then x→→z

Transitivity: If x→→y, y→→z, then x→→z-y

function dependency is a special multi-valued dependency

If x→→y,x→→z, then x→→yz,x→→y∩z,x→→y-z,x→→z-y

1NF

Defines that each component in the r<u,f> must be a non-re-divided data item

2NF

Define R<U,F>∈1NF, and each non-primary attribute is completely dependent on any one candidate code

Understand that you cannot have a non-primary attribute that is partially dependent on any candidate code

Counter Example s-l-c (Sno, Sdept, Cno, Cname, Grade)

Understanding Candidate Code: (SNO,CNO)

Because of Sno→sdept, the sdept part of the non-primary attribute depends on the candidate code (SNO, Cno)

3NF

Define a non-primary property an undeliverable function relies on any candidate code

Understand that there is no dependency between non-primary attributes

Counter Example S-l (Sno, sdept, saddress)/*saddress for the student Dormitory building, a department building * *

Understanding Candidate Code: Sno

Because Sno→sdept, sdept→saddress, so

BCNF

Define R<U,F>∈1NF, if X→y and y? x, x must contain candidate code

Others were proposed by Boyce, Codd, on the basis of the third paradigm, and therefore also called the amended third paradigm/augmented third paradigm

The decision attribute (group) that understands all non-trivial function dependencies must contain the candidate code

Example STC (Student, Teacher, Course)/* Each teacher teaches a course, each class is taught by multiple teachers, and each student has multiple courses selected * *

Understanding candidate codes (Student, Teacher, Course)

Because of Teacher→course, but teacher is not a candidate code.

4NF

Define R<U,F>∈1NF, if for each non-trivial multivalued dependency of R x→→y (Y? x), x must contain candidate code

The decision attribute (group) that understands all non-trivial multivalued dependencies must contain the candidate code

Anti-case WSC (Warehouse, Saver, commodity)/* warehouse, custodian, goods */

Understand Warehouse→→saver, but Warehouse is not candidate code

Graphic

"Introduction to Database System"--6 relational data theory