The relational paradigm uses a set of standards to regulate the dependencies between attributes in a database table structure to reduce data redundancy, SQL operation exceptions, and so on.

Relationship paradigms include first, second, third, BCNF, fourth and fifth paradigms.

At the same time, to reach the high-level relationship paradigm, one must first reach all the lower-level paradigms:

1NF ⊂ 2NF ⊂ 3NF ⊂ BCNF ⊂ 4NF...Copy the code

Modern databases can only reach third normal form at best. It’s not that the higher the norm, the better.

Functional dependencies

Relational paradigms are judged mainly by function dependence

Function dependent formal representation

X -> Y is called X determines Y or Y depends on X. That is, the non-primary attributes in the table structure depend on the primary attributes, and the primary attributes determine the non-primary attributes

X ! -> Y or X -\-> Y is written as X does not determine Y or Y does not depend on X

Complete function dependence

Definition: X and Y are the set of properties of the relation R, having X -> Y, and any proper subset X’ of X has X’! -> Y(any subset of x cannot determine Y by itself). It says that Y depends entirely on X or all subsets of X determine Y.

R(student ID, course ID, student course grade) has the following functional dependence in this relationship

(Student ID, course ID) -> Student's course score (Student ID)! -> Student course score (course number)! -> Student course scoresCopy the code

In other words, a subset of X X’ does not determine Y (a single student number or course number), but requires the entire set of attributes of X to determine Y (here we use student number and course number to determine a course grade).

Partial function dependence

Definition: there exists a proper subset of X’ that satisfies X’ -> Y. It’s called the Y part depends on X or a subset of X parts determines Y

Partial function dependence is the opposite of complete function dependence, that is, a subset of X determines Y

R(student number, ID number, name) has the following functional dependencies in this relationship (note: examples are not particularly appropriate)

1. The name of X (student id, id) - > 2 X 'names (student id) - > 3. X' (id) - > nameCopy the code

Here the X set is (student number, id number), and a subset of X (student number) can also determine the name attribute. So a subset of X, X prime, can determine Y, so there’s a partial function dependence

Transfer function dependence

Definition: X, Y, Z attribute set in the same relation R, X -> Y, Y -> Z, Y! -> X at this point Z transfer depends on X (X indirectly determines Y)

R(Teacher ID, course ID, name, course name) Suppose the relationship between course and teacher is 1: N, there are the following dependencies in the relationship:

Teacher Number -> Course Number Course Number -> Course Name Course number! - > teacherCopy the code

There is a course name transfer function that depends on the instructor number

Without Y! -> X this condition indicates that X <-> Y(interdependence), then X -> Z (Z directly depends on X), such as the example of partial function dependence: student number -> ID number, ID number -> name, ID number -> student number. In this case, the name depends directly on the student id, not on the transfer function.

Relationship normalization process (paradigm)

First Normal Form (1NF)

Definition: Attributes are not separable.

The first paradigm prescribes the most basic structure of a relationship. That is, to create a table in a relational database, if the table is operable, the table (relationship) must satisfy the first normal form.

Second normal Form (2NF)

Definition: all non-primary attributes in the same relationship are fully functional dependent on the primary attribute, and there is no partial function dependency.

There can be no properties that are unrelated to the main property

Third normal Form (3NF)

Definition: Non-primary attributes in the same relationship do not transfer functions dependent on candidate keywords

If the non-primary attribute is not directly dependent on the primary attribute (transitive), generally this intermediate attribute can be separated into the primary attribute of another relationship

BCNF(Boyce-Codd Normal Form)

Definition: the same relation F has X -> Y (X, Y is the set of attributes in F) where X must contain a candidate keyword or must be a supercode

How to get a set of relations with BCNF normal form

It can be seen that the second and third normal forms are used to specify the relationship between non-primary attributes and keys/codes, but in some cases, even if the relationship is in the third normal form, exceptions such as insertion and deletion are still possible.

BCNF: On the basis of reaching the third normal form, the complete function dependence and transfer function dependence between keys are specified. It can be considered that BCNF is optimized on the basis of the third normal form. So the paradigms that reach this level are not directly called fourth paradigms, but are based on optimization of third paradigms.