SQL three main paradigms three examples of

First Paradigm (1NF)

(Must have primary key, column is not divided)

Any field in a database table is a single attribute and cannot be divided

CREATE TABLE AA (ID int,nameage varchar (100))
Insert AA values (1, ' infinity-female ')
Did not reach the first paradigm

CREATE TABLE AA (ID int,name Varcahr (Ten), Age char (2))
Insert AA values (1, ' infinity ', ' female ')
Reach the first paradigm

Second Paradigm (2NF)

A non-critical field in a database table does not have a partial function dependency on any of the candidate key fields

(When a table is a composite primary key, the non-primary key field does not depend on the partial primary key (that is, it must depend on all primary key fields))

CREATE TABLE SCI (

Sno Int (+), CNO Int (+), Grade int (+), credit int (32),

Primary KEY Sno,cno

)

Course (CNO) 1---1 credits (credit)

Student (SNO) n---N Course (CNO)

Student + Course---> Score (grade)

Sci

Sno CNO Grade Credit

1 1 60 80

2 1 90 80

3 1 70 80

.   .      . .

.   .     . .

.   .     . .

In this way, credit has been repeatedly stored, data redundancy

If you want a course credit, you need to repeat it a lot.

If you want to add a new course, as Sno and CNO together as a primary key, you must have a candidate for the course when you join the new curriculum.

If a student has completed a course, the information of the student's other courses has also been deleted.

Anyway

This design is not very good, non-keyword attribute credit only function depends on CNO, that is, the credit part depends on the combination keyword (SNO,CNO) instead of fully dependent

Solve

Divided into two relational patterns SC1 (Sno,cno,grade), C2 (Cno,credit). The new relationship consists of two relational patterns, which are linked by the outer keyword CNO in the SC1, and then natural joins are needed to restore the original relationship

Third Paradigm (3NF)

All non-primary properties in relational mode R (U,F) do not have transitive dependencies on any candidate keywords

Example----S1 (Sno,sname,dno, dname, location)

The name of the Department of the school number is the address
The SNO keyword determines the individual properties. Because it is a single keyword, there is no partial dependency on the problem, certainly is 2NF. However, there must be a lot of redundancy in this relationship, and some of the properties of the student dno,dname,location will be stored, inserted, deleted and modified in a similar case.
Cause: There is a transitive dependency in the relationship. That is Sno 1->1 DNO. and DNO 1->n SNO but does not exist, and the location of DNO, so the key Liao SNO to the location function is determined by the transfer dependency SNO. In other words, Sno does not directly determine the location of the non-primary attribute.
Solution: No transitive dependencies can be left in each relational pattern.
Workaround: Divided into two relations S (Sno,sname,dno), D (dno,dname,location)
Note: The relationship S cannot have no outside keyword DNO. Otherwise, two relationships lose contact.

Boyce-Christie's Paradigm (BCNF)

On a 3NF basis, any field in the library table does not exist for the transfer function dependency of any of the candidate key fields

Warehouse Management Relationship table is storehousemanage (warehouse ID, store item ID, administrator ID, quantity), and Administrator 1---1 warehouse; warehouse 1---n items. The following decision relationships exist in this database table:
(Warehouse ID, store item id) → (Administrator id, quantity)
(Admin ID, store item id) → (warehouse ID, quantity)
So, (warehouse ID, store item ID) and (Administrator ID, store item ID) are the candidate keywords for storehousemanage, the only non-critical field in the table is the number, which is in accordance with the third paradigm. However, the following decision relationship exists:
(warehouse id) → (Administrator id)
(Administrator id) → (warehouse id)
That is, the key field determines the key field, so it does not conform to the BCNF paradigm

Solve:

Break down the Warehouse management relationship table into two relational tables:
Warehouse Management: Storehousemanage (warehouse ID, Administrator ID)
Warehouse: Storehouse (warehouse ID, store item ID, quantity)

SQL three main paradigms three examples of