[SQL Server] paradigm

To improve the performance of the database application system, we introduce a traditional concept-relational database standardization theory.

Today, we will focus on the paradigm.

What is paradigm?

A relationship is called a paradigm when it meets different levels of requirements.To meet the minimum requirements, the first paradigm is 1nf.

In relational databases, the basic requirement for the relational model is 1nf, and 3nf is usually required in actual applications.

 

The standardization process of the Relationship Mode is achieved through the decomposition of the Relationship Mode. The lower-level relationship mode is decomposed into the higher-level relationship mode.

 

First paradigm:

If each attribute of the relational mode contains only a single value, the relational mode meets 1nf.

 

Student ID	Name	Course name	Score
090504001	Chen Li	Computer-Aided Education	89
		Course integration	90
090504002	Li Hui	Computer-Aided Education	85
		Course integration	92
090504003	Kunna	Computer-Aided Education	93
		Course integration	87

 

The course names and scores in the above table are repeated, not a single value-Each student has multiple scores. To satisfy 1nf, we can draw the following table:

 

Student ID	Name	Course name	Score
090504001	Chen Li	Computer-Aided Education	89
090504001	Chen Li	Course integration	90
090504002	Li Hui	Computer-Aided Education	85
090504002	Li Hui	Course integration	92
090504003	Kunna	Computer-Aided Education	93
090504003	Kunna	Course integration	87

 

 

Second paradigm:

If the relational mode satisfies 1nf, and all its non-primary attributes fully depend on the primary attributes, the relational mode satisfies 2nf.

 

To get the information of a column, do I need to use all primary keys or only some primary keys?

If you only need to use some primary keys, you must consider splitting the table so that all columns match all primary keys.

 

From the 1nf table, we can know the student's name, and the student's and course's name can be used to know the student's score. Therefore, the name depends entirely on the student's student ID, the score depends entirely on the student ID and Course name. Therefore, the following two tables meet 2nf:

 

Student ID	Name
090504001	Chen Li
090504002	Li Hui
090504003	Kunna

 

 

Student ID	Course name	Score
090504001	Computer-Aided Education	89
090504001	Course integration	90
090504002	Computer-Aided Education	85
090504002	Course integration	92
090504003	Computer-Aided Education	93
090504003	Course integration	87

 

 

Third paradigm:

If the relational mode meets 2nf, and none of its non-primary attributes depend on the primary attributes, the relational mode meets 3nf.

 

No data can be derived from other data in the table.

 

Each class has a fixed teacher, namely, the class teacher of high school and the university counselor. The relationship between teachers and students is as follows:

Teacher-student relationship (student ID, class, instructor name)

However, if a teacher is changed, the relationship between the student ID and the class should be disrupted. The relationship between the class and the teacher should be changed without changing the class and the student, to minimize data changes, you can obtain the following relationships:

Class (student ID, Class)

Teacher-student relationship (class, instructor name)

Now, if you change a teacher, you only need to change the relationship between the teacher and the class, but the relationship between the class and the student will not change.

The above summary is purely personal opinions. If there is anything wrong with it, please kindly advise the experts ~