SQL Server Database Design

First, the necessity of database design

In the actual software project, if the system needs to store a large amount of data, the table needs to be designed more, the relationship between table and table is more complex, then we need to standardize the database settings. Without the design of the database, the database we build is unreasonable and inappropriate, so the maintenance and operation efficiency of the database will have great problems. This will directly affect the operational and reliability of the project.

Second, what is the database design

Database design is essentially the process of planning and structuring data objects in a database and the relationships between those data objects.

Third, the importance of database design

Ø non-designed databases or poorly designed databases are likely to cause

1, database operation efficiency Underground

2, UPDATE, delete, add data problems

Ø well-designed database

1. High execution efficiency

2. Making applications easier to develop

3. Good extensibility

4, the maintenance of good

Iv. Data Model

The data model is like a contour map of the data between the two, and the whole model is like a frame.

If the data model is classified according to the representation of the relationship between records, it can be divided into: Hierarchical model, network model and relational model. The first two are also called formatted data models. The data model has a direct impact on the performance of the database, so the choice of data model is the primary task of database design.

Ø Entity-relation (E-R) data model

The E-R Data Model (entity-relationship), the entity-relational data model. The E-R data model is different from the traditional relational data model, it is not oriented to implementation, but is oriented to real objects.

Ø Entities (Entity)

Data is used to describe objects in the real world, and the objects described are all kinds, concrete and abstract, physically and conceptually. All things, objects and concepts that can be distinguished from one another and can be recognized by people are abstracted as entities. More than one entity of the same type can be called an entity set. Thus, in the E-R data model, there are also types and values, entities can be defined as types, and each entity can be its instance and value.

Ø Properties (Attribute)

Entities are typically specific to certain characteristics, which are called attributes of an entity. Each property has its own range of values, called the value set in the E-R data model. In the same entity set, the properties and set of values for each entity are the same, but different values may be taken. Property corresponds to the column of the database table.

Ø Relationship (Relationship)

There are various relationships between entities, which are abstracted as connections. Not only can entities have attributes, but relationships can also have attributes.

V. Database design Steps

Ø database design can be divided into the following stages

1. Demand Analysis phase: Analyze the customer's business needs, especially the data requirements

2, Outline design stage: Draw the E-r diagram of the database, and confirm the correctness and completeness of the requirement document, E-r diagram is the Project Designer, developer, tester, and important credentials to communicate with the customer.

3, detailed design stage: The summary design phase of the E-r diagram into a database table, the logical design, to determine the main foreign key relationship between the tables, the use of the three-paradigm of the database audit, and technical review. Finally decide which database to choose (Oracle, SQL Server, MySQL) to build the library, build the table.

Ø Requirements Analysis Phase: Database system Analysis

The focus of the delicate analysis phase is to investigate, collect and analyze the customer's business data requirements, as well as the security and completeness requirements of the data.

Requirements Analysis Steps:

1. Confirm Business Requirements

2. Identify the relationship entity

3. Identify the attributes that each entity has

4. Confirm the relationship between entities

Ø Outline design phase: Draw E-r diagram

As a database designer, you need to share your design ideas with other members of the project team to discuss the rationality, security, integrity of the database design, and to confirm compliance with the customer's business needs. With the E-r diagram, this graphical representation is most intuitive.

* Entities, attributes, and relationships in the E-r diagram

The simple e-r diagram above shows the relationship between the user and the revenue. It can be seen in the following: The body is represented by a rectangle, the entity is a general noun; an ellipse denotes a property, usually a noun; a diamond denotes a relationship, usually a verb.

Map cardinality

The map cardinality represents the number of relationships that can be passed to the entity. For a two-tuple relationship between entity sets A and B, the possible mapping cardinality is:

1, one-to: that is, a entity has a maximum of only a B entity association, and a B entity has a maximum of one entity's Association. The E-r diagram shows:

2, one-to-many: a entity can be associated with any number of B entities, and entities in B are associated with at most a single entity in a. The E-r diagram shows:

3, many-to-one: a entity is associated with at most one B entity, whereas a B entity can be associated with any number of a entities. The E-r diagram shows:

4, many-to-many: a entity can have multiple B entities, and B entities can also have any number of a entities. The E-r diagram shows:

The E-r diagram can graphically represent the entire logical structure of a database, with the following components:

1. Rectangles represent entity sets

2. Ellipse Representation Property

3, diamond representation relations,

4. Line used to connect entity sets with attributes, entity sets, and relationships

5. Straight arrows indicate the mapping cardinality between entity sets

Ø Detailed design phase: Convert e-r diagram to table

The steps are as follows:

1. Convert each entity to the corresponding table and convert each attribute to the corresponding column

2. Identify the primary key for each table

3. Convert the relationship between entities to a primary foreign key relationship between tables

VI. Standardization of Database design

Ø frequently-occurring problems in database design

1. Large Data redundancy

2. Insert Data exception

3. Delete exception

4. Update exception

A better relational database model, its attributes in each relationship must satisfy some intrinsic semantic conditions, that is, to design a relational model according to certain norms, which is the standardization of design.

In the database design, there are some special rules, called the database design paradigm, following these rules, you can create a good database, the database famous three paradigm theory:

Ø Standard Design

A better relational database model, its attributes in each relationship must satisfy some intrinsic semantic conditions, that is, to design a relational model according to certain norms, which is the standardization of design.

In the database design, there are some special rules, called the database design paradigm, following these rules, you can create a good database, the database famous three paradigm theory:

1. The first paradigm (1NF)

The first paradigm is the most basic conditional paradigm that satisfies the relational database model, and each attribute in several relationships must be a simple item that cannot be re-divided, and cannot be an attribute combination, that is, the value of a property is an atomic value that cannot be split.

2. Second paradigm (2NF)

The second paradigm is based on the first paradigm, ensuring that each column in the table is related to the primary key. The definition is that if a relationship satisfies 1NF, and the other columns other than the primary key relationship depend on the primary key, the second normal form is satisfied.

3. The third paradigm (3NF)

The third paradigm is based on the second paradigm, and the goal of the third paradigm is to ensure that each column is directly related to the primary key column, rather than indirectly. It is defined as if a relationship satisfies 2NF and the other columns other than the primary key are not passed dependent on the primary key.

Ø Normalization and performance relationship

In order to meet the three paradigms, database performance may be reduced to a certain extent. Therefore, in the actual database design, we should try to meet the three main paradigms, so as to avoid data redundancy and the operation of various database anomalies, but also to consider the data access performance. Sometimes, in order to improve the efficiency of database access, appropriate to allow a small amount of data redundancy exists, is the most appropriate database design.

SQL Server Database Design