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