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.
SQL Server Design three-paradigm