Some concepts of MySQL

Hierarchical relationship of database to server and client

About the database

How you need to store data in your program:

1 variables (list, tuple, collection, dictionary, nested)

2 external memory (file) (*.ini)

3 tables, Excel (*.xls, *.xlsx, *.csv)

4 Structured database

Basic Concepts in the database

Record: A row in a database

Field: A column in a database

Entity: something that exists in reality and can be differentiated. For example: students, courses

Property (Attribute): The feature that the entity lock has. For example: For student entities, the attributes are: study number, name, age, gender

Relationship (Relationship): a dependency between an entity and an entity. Includes: A pair of one or one-to-many, many-to-many.

The above three (entity, attribute, relation) can compose ER diagram (Entity Relationship diagram)

To create a table from the Visio database Model diagram:

When you build a table, you need to consider paradigm constraints.

Generally have the following paradigm: 1NF 2NF 3NF BCNF 4NF 5NF

1NF: attribute is not divided

Name	Phone number		Age
	Landline	Cell phone
Tom	020-84444444	13777777777	23
John doe	020-85555555	13888888888	34

As above, because the phone attributes can be split into landline and mobile phones, so does not meet 1NF, modified as follows:

Name	Landline	Cell phone	Age
Tom	020-84444444	13777777777	23
John doe	020-85555555	13888888888	34

2NF: 1NF compliant. The non-primary attribute is completely dependent on the keyword

School Number	Name	Course Number	Exam Results
101	Tom	201	73
102	John doe	202	53

Dependency relationships in this table:

School Number--name

(School number, course number)--Test results

The key words of the table are: (School number, course number)

The name of the non-master attribute here depends on the number, which is part of the keyword, not all. So not satisfied 2NF

Need to split into two tables

School Number	Name
101	Tom
102	John doe

School Number	Course Number	Exam Results
101	201	73
102	202	53

This will satisfy the 2NF.

3NF: Conforms to 1NF and 2NF, and eliminates delivery dependencies

School Number	Course Number	Teacher	Title
101 (Zhang San)	201 (Advanced mathematics)	Zhang Lao Wet	Brick House
102 (John Doe)	202 (College English)	Professor Chen	Called the Beast
103 (Harry)	202 (College English)	Professor Chen	Called the Beast

Apparently satisfying 1NF

The key words of the table are: (School number, course number). The following non-primary attribute is completely dependent on the keyword, so 2NF is satisfied

(School number, course number)--Class teacher

Teacher--Job title

Like this a relies on the design of B,b relies on C, which is called transitive dependence, is not satisfied 3NF

Need to split into two tables

School Number	Course Number	Teacher
101 (Zhang San)	201 (Advanced mathematics)	Zhang Lao Wet
102 (John Doe)	202 (College English)	Professor Chen
103 (Harry)	202 (College English)	Professor Chen

Teacher	Title
Zhang Lao Wet	Brick House
Professor Chen	Called the Beast

That would satisfy the 3NF standard.

There are also bcnf, 4NF, 5NF behind. But we design a database to meet the general requirements of 3NF

Some concepts of MySQL