MySQL Note 8_ database design steps

Step1:

Collect information, communicate with people who talk about the system, and fully understand the tasks that the database needs to complete.

Example: Blog system

Basic functions: Publish and edit articles, multi-user support, full text search, RSS support, image collection, management station short message.

Step2:

Identifying objects (Entity entities)

Identify the key objects or entities to be managed by the database.

Entities are generally nouns:

Bo Master account: Publish and manage articles, image management.

Article category

Article

Article reply

Image category

Image

Connection classification

Connection

Short Message

Step3:

Identify the properties of each entity

E-r Chart (entity-relationship)

|      Symbols | meaning |

|   Rectangle | An entity, usually a noun |

|   Ellipse | attribute, usually a noun |

|   Diamond | A relationship, usually a verb |

STEP4:

Convert the E-r diagram to the corresponding table

Step1. Convert each entity to a corresponding table.

Step2. Convert each attribute to a column corresponding to a table.

Step3. Identifies the primary key column for each table.

Note: Tables that do not have a primary key are adding the ID number column as the primary key. It has no practical meaning.

Step4. Establish a primary foreign key between the tables, reflecting the mapping relationship between entities.

A one-to-many relationship is associated with a primary foreign key.

A many-to-many relationship adds an intermediate table to handle association relationships.

3 Paradigm of database design:

The first paradigm (1NF): In each specific relationship pattern, there must be a primary key, and each attribute value is the smallest unit of data that cannot be re-divided. is called the first canonical relationship.

Second Normal (2NF): If all non-primary attributes in the relational schema are completely dependent on the primary key, it is called the second paradigm.

Third paradigm (3NF): A non-primary key in a relational schema cannot rely on other non-primary keywords, that is, a function (transitive) dependency between non-primary keywords, which is called the third paradigm.

Hidden trouble:

Data redundancy

Update exception

Insert exception

Delete exception