Chapter I.
Design of the database
A good database design
1
, save the database storage space
2
, to ensure the integrity of the data
3
, facilitate the development of database application system
Poor database design
1
, low efficiency
2
, updating, and retrieving data, there are many problems
Steps to design a database
1
, demand analysis phase
Analyze customer's business and data processing needs
⑴
Collect information
⑵
Identifying objects
(
Entity
)
⑶
Identify the details that each object needs to store
⑷
Identify the relationships between objects
2
, Outline design phase
Drawing the Database
E-r
Model Diagram
(
Entity Relationship Diagram
)
3
, Detailed design phase
Will
E-r
Graph converted to multiple tables
,
Make a logical design
,
and apply the three major specifications of database design into
Line Design
4
, Code authoring phase
Map cardinality
1
One
2
One-to-many
3
Many-to-one
4
Many-to-many
Entity Relationship Diagram
Rectangles represent entity sets
Ellipse Representation Property
Diamond represents a relationship set
Straight lines are used to connect
Designing Database Problems
1
Information duplication
2
Update exception
3
Insert exception
(
Unable to represent certain information
)
4
Delete exception
(
Loss of useful information
)
Three normative theories of database design
One
The objective of the first paradigm is to ensure that
The atomicity of each column
Two
The objective of the second paradigm is to ensure that
Each column in the table is associated with the primary key
Three
The objective of the third paradigm is to ensure that
Each column is directly related to the primary key
,
Rather than indirectly related
(
Cannot pass dependent
)
The relationship between normalization and performance
In case of necessity
Allow appropriate data redundancy
The study of SQL S2 semester