Relational Database paradigm Constraints

Suddenly, I felt that my knowledge in the paradigm was almost forgotten. I found some information from the Internet, mainly combining some (negative) instances and having the right to back up the data.

Write your own understanding first. Relational databases are based on rigorous relational logic operations. E. F. codd first proposed a complete theory in 1970, So it won the 1981 Turing Award.

The basic features of acid restrict the development of productivity for High-concurrency read/write + mass data efficient storage + high scalability and availability (derived from SNS ).

Nosql (Cap and base theory) is booming. Although the paradigm is designed to reduce redundancy and prevent inconsistency caused by updates, deletions, and inserts, availability and partition toleratce are required, you have to give up consistency to a certain extent (Consistency) To obtain availability.

Proof of option 3 and option 2: Chinese Translation

Back to the question, I won't go over the concepts involved, such as codes, primary attributes, and full function dependencies.

1nf the so-called first paradigm (1nf) means that each column in the database table is an inseparable basic data item, that is, an attribute in the entity cannot have multiple values or duplicate attributes. In any relational database, the first paradigm (1nf) is the basic requirement for the relational model. databases that do not meet the first paradigm (1nf) are not relational databases.

E.g. The following table does not comply with 1nf:

Field 1	Field 2	Field 3		Field 4
		Field 3.1	Field 3.2

2nf

The second Paradigm (2nf) is based on 1nf, which requires that each instance or row in the database table be allowed to be uniquely divided. Introduce the primary attribute, primary key, and primary code.

E.g. The course selection relation table is selectcourse (student ID, name, age, course name, score, credits), and the keywords are combined keywords (student ID, course name), because the following decision relationships exist:

(Student ID, course name) → (name, age, score, credits)

This database table does not meet 2nf because of the following decision relationships:

(Course name) → (credits)

(Student ID) → (name, age)

That is, some fields in the composite keywords determine non-keywords, or some functions of non-primary attributes depend on the primary attributes.

3nf 3nf is based on 2nf, which requires Any non-primary attributeNone of them depend on candidate keys.

E.g. The student relationship table is student (student ID, name, age, school, school location, school phone number), and the keyword is single keyword "student ID", because the following decision relationship exists:

(Student ID) → (name, age, school, school location, school phone number)

This database complies with 2nf but does not comply with 3nf because of the following decision relationships:

(Student ID) → (school location, school phone number)

That is, the transfer function dependency of the non-Keyword section "school location" and "college phone" on the keyword section "student ID" exists.

Bcnfbcfc is based on 3nf, which requires Any attributeNone of them depend on candidate keys. Note:The difference between bcnf and 3nf in bold!

E.g. The Warehouse Management relationship table is storehousemanage (warehouse ID, storage item ID, administrator ID, quantity ),

One administrator only works in one warehouse, and one warehouse can store multiple items. This database table has the following decision relationships:

(Repository ID, storage item ID) → (administrator ID, quantity)

(Administrator ID, storage item ID) → (warehouse ID, quantity)

Therefore, both (repository ID, storage item ID) and (administrator ID, storage item ID) are candidate Keywords of storehousemanage, and the unique non-Keyword segments in the table are quantity, it conforms to the third paradigm.

However, the following decision relationships exist:

(Administrator ID) → (warehouse ID) → (administrator ID)

That is, the keyword segment determines the keyword segment, so it does not conform to the bcnf paradigm.

There is a relationship between the four paradigms:

Note: most of the content of this article comes from

For further examples, refer to here. It seems that this example is taken from Baidu's interview question and has a background information.

"The Forum has 300 million visits per day, and the number of posts updated is 10 million"

Next, we will study Master/Slave Mode/read/write splitting; sharding/partition