SQL paradigm: 1NF, 2NF, 3NF, BCNF (function dependent)

First Paradigm (1NF)

Each property is an irreducible basic data item. (Must have primary key, column is not divided)

Eg: Table of non-first normal form: (column can be divided)

College Name	Number of senior titles
	Professor	Associate professor
School of letters and electricity	3	34
School of Management	5	23
College of Foreign Languages	3	12

Table satisfying the first paradigm

College Name	Professor	Associate professor
School of letters and electricity	3	34
School of Management	5	23
College of Foreign Languages	3	12

Second Paradigm (2NF)

1NF on the basis of eliminating part of the function dependence, eliminate data redundancy and increase, delete, change the anomaly.

Each non-primary property is fully functional dependent on the primary key

Examples of 2NF relationships:

Not satisfied with 2NF relationship:

(School number, course name) → (name, age, score, credits)

To meet 2NF relationships:

(School number, course name) → (score) (course name) → (credit) (school number) → (name, age) PS:

( full function dependency : In relational mode R (U), X, Y is a subset of u, the Y function depends on X and Y is not a function dependent on a subset of X, then the Y full function depends on X.) X F >y)

( Partial function dependency : In relational mode R (U), X, Y is a subset of U, y function depends on X and Y function depends on a subset of X)

( function Dependency : When a property set determines another property set, such as student number attribute set SNO determines student name attribute set sname, called Sname function depends on sname)

( trivial function dependency : y function depends on x, and y is contained in x, e.g. (Sno), (Sno), (Sno, Sname), (Sno))

( non-trivial function dependency : y function depends on x, and Y is not included in X, for example (Sno,sname), (Ssex))

Third Paradigm (3NF)

On a 2NF basis, the transfer function dependency is eliminated, and the attribute set is directly dependent on the primary key.

Non-third Paradigm table (Sno->bounslevel,bounslevel->bounsmoney)

Sno	Sname	Sage	Bounslevel	Bounsmoney
1	Carrie	21st	A	2000
2	Cherry	20	B	1500

Third Paradigm Table: (Elimination of transfer function dependencies)

Sno	Sname	Sage	Bno
1	Carrie	21st	1
2	Cherry	20	2

Bno	Bounslevel	Bounsmoney
1	A	2000
2	B	1500

( transitive function dependency : The Y function relies on the X,Z function dependent on y, and X does not contain a y,x non-function dependent on y, then the z-transfer function depends on X.) X T >z)

Boyce-Code (Bath) Paradigm (BCNF)

3NF eliminates the primary attribute dependency, that is, there is no case where critical fields determine key fields.

(Non-primary property: A property that is not included in the main code is called a non-primary attribute.) ）

Counter Example: (Sid->gid,gid->sid the presence of keywords determines keywords)

SID (Warehouse ID)	GOODSID (Product ID)	MID (Admin id)	Goodsnum (number of items)
001	20170510	1	200

Complies with BCNF

Storehouseid (Warehouse ID)	GOODSID (Product ID)	Goodsnum (number of items)
001	20130104	200

Storehouseid (Warehouse ID)	ManagerID (Administrator ID)
001	1

SQL paradigm: 1NF, 2NF, 3NF, BCNF (function dependent)