SQL Server Learning 2--database design

Database design is a relatively important part of database knowledge, we need to understand the basic steps of database design, e-r drawing.

A basic overview of database design

The standard of testing a database design is to see if he can easily perform various data retrieval and processing operations, and is conducive to the control of data management and maintenance.

Database design Steps

The more famous design database specification is the "New Orleans method" (This is not the way to do New Orleans chicken legs), now Orleans, a total of 4 stages:

• Requirements analysis (analysis of user requirements, production of flow chart and decision diagram)
• Conceptual structure Design (information analysis and definition, making e-r diagram)
• Logical Structure design (design implementation, relational model, or non-relational model)
• Physical Structure Design (physical database design, storage scheduling, storage path creation)

Later, many scientists have improved and added 2 more steps in the end:

• Database implementation phase (load data, database test run)
• Database operation and maintenance (performance detection, backup, recovery, database refactoring)

E-r diagram of the design phase

E-r diagrams are entities-contact diagrams (Entity-relationship Diagram) that describe the conceptual model of the real world, representing entity types, data, and relationships.

Entity

Can be anything in the real world that can be understood, can be people or things, can be abstract concepts, such as a student, a computer.

Property

A series of characteristics between entities, such as the properties of a computer can have a product name, brand, model, price.

Contact

The internal linkages between entities can be divided into 3 categories:

One-on-one (1:1)

For entity set A, there is a maximum of one in entity set B that is associated with a (can not), and vice versa, that is, the one by one correspondence. For example, a car a has a license plate number B, a license plate B corresponds to a car a.

One-to-many (1:n)

For entity set A, entity set B has multiple object contacts, which is a one-to-many relationship, and a corresponds to more than one B. For example, a model A can have a lot of cars, many cars can also be the same model A.

Many-to-many (M:N)

For entity collection A, entity set B has more than one object contact, and vice versa. For example, a person a can buy many kinds of goods, a kind of goods B can also buy a lot of individuals.

E-r Legend Description

Rectangle: Represents an entity

Diamond: Indicates contact

Ellipse: An attribute that represents an entity or contact

Wiring: The use of straight lines between the various graphics connection, and the line of contact type, one-to-one (in the connection 2 sides are written 1), a pair of more (on the side of the line to write 1, many side write N), many-to-many (on the connection side write N, the other side write m)

Logical structure to relational model

Like one-on-one relationships (1:1):

Company ( Company code , name, address)

Manager ( employee number , name, job title, Company Code )

For example, a one-to-many relationship (1:N):

Scenario 1

Type ( type number , type name, level)

Product ( Product code , product name, brand)

Attribution ( type number , item number )

Scenario 2

Type ( type number , type name, level)

Product ( Product code , product name, brand, type number )

As can be seen here, Scenario 1, although also in line with the concept of design, but too many relationships, management is not convenient, less than the relationship 2 concise.

For example, many-to-many relationships (M:N):

Buyer ( buyer number , buyer name, phone)

Product ( Product code , product name, brand)

Sales ( buyer number , item number , date of sale)

Database implementation phase

Database implementation is mainly database definition, data warehousing, database test run. The designer uses the number definition language provided by the DBMS to describe the logical structure and the physical structure of the database, which is the source code that the DBMS can accept, the database structure, the organization data warehousing, the data storage is the most important work of the implementation, usually the data volume is large and time-consuming. Then the trial run phase is to actually run the application, store operations on the database, and test whether the application functionality meets the requirements.

In the test run phase, the actual measurement and evaluation of system performance indicators.

Database operation and Maintenance

There are 4 main jobs:

Backup and recovery of databases

Security and integrity control of the database

Database performance detection, analysis, transformation database

Rebuilding the database

SQL Server Learning 2--database design