Database paradigm and instance, database paradigm instance

Database paradigm and instance, database paradigm instance

When designing a relational database, a rational relational database is designed according to different specifications. These different specifications and requirements are called different paradigms and various paradigm submission specifications, the higher the standard database, the smaller the redundancy.

Currently, relational databases have six paradigms: 1NF, 2NF, 3NF, BCNF, and 4NF) and the fifth Paradigm (5NF, also known as perfect paradigm ).

In practice, the first three methods can meet the general database design requirements.

1NF)

The first paradigm (1NF) refers to a standard requirement for adding domains in a relational model. All domains should be atomic, that is, each column in the database table is an atomic data item that cannot be a set, array, record, or other non-atomic data item. That is, when an attribute in an object has multiple values, it must be split into different attributes. In the 1NF table, each domain value can only be one attribute or a part of an object. In short, the first paradigm is a non-repetitive domain.

Note: The 1NF field cannot be divided.

Example of 1NF non-conformity:

 

 

Student ID	Name	Age	Date of birth	Parents	Course	Credits	Score	Level
001	Zhang San	20	1996-02	Parent: Zhang Wuji, Mother: Han Meimei	Mathematics	5	80	Good

When you observe the table above, it is easy to find that the parent can be divided into the parent and the mother. The design is as follows:

Example of 1NF compliance:

 

Student ID	Name	Age	Date of birth	Father	Mother	Course	Credits	Score	Level
001	Zhang San	20	1996-02	Zhang Wuji	HAN MEIMEI	Mathematics	5	80	Good

2NF)

The second Paradigm (2NF) requires that the attributes of an object fully depend on the primary keyword. The so-called full dependency refers to the fact that there cannot be an attribute that only depends on a part of the primary keyword. If so, this attribute and this part of the primary keyword should be separated to form a new entity, the relationship between the new object and the original object is one-to-multiple. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. In short, the second paradigm is based on the first paradigm that attributes fully depend on primary keys.

Note: 2NF has a primary key. non-primary key fields depend on the primary key.

Example of 2NF non-conformity:

Student ID	Name	Age	Date of birth	Father	Mother	Course	Credits	Score	Level
001	Zhang San	20	1996-02	Zhang Wuji	HAN MEIMEI	Mathematics	5	80	Good
001	Zhang San	20	1996-02	Zhang Wuji	HAN MEIMEI	Chinese	5	90	Excellent

2NF-compliant example:

 

Student ID	Name	Age	Date of birth	Father	Mother
001	Zhang San	20	1996-02	Zhang Wuji	HAN MEIMEI

 

No.	Course	Credits
2001	Mathematics	5
2002	Chinese	5

 

No.	Student ID	Course	Score	Level
1001	001	2001	80	Good
1002	001	2002	90	Excellent

3NF)

On the basis of 1NF, any non-primary attribute does not depend on other non-primary attributes [eliminate the transfer dependency on 2NF]. 3NF is a subset of 2NF, that is, 3NF must satisfy 2NF ). It requires that a link does not contain information about non-primary keywords contained in other links.

Note: 3NF non-primary key fields cannot depend on each other.

Example of 3NF non-conformity:

Student ID	Name	Age	Date of birth	Father	Mother
001	Zhang San	20	1996-02	Zhang Wuji	HAN MEIMEI

 

No.	Student ID	Course	Score	Level
1001	001	2001	80	Good
1002	001	2002	90	Excellent

Example of 3NF:

 

Student ID	Name	Date of birth	Father	Mother
001	Zhang San	1996-02	Zhang Wuji	HAN MEIMEI

 

No.	Student ID	Course	Score
1001	001	2001	80
1002	001	2002	90

 

No.	Level	Score Line
3001	Excellent	90
3002	Good	80