Shulou(Shulou.com)06/02 Report--

How to analyze the design of SQL Server database, I believe that many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

I. the necessity of database design

In the actual software project, if the system needs to store a large amount of data, need to design more tables, and the relationship between tables is more complex, then we need to set up a standardized database. If we do not go through the design of the database, the database we build is unreasonable and inappropriate, then the maintenance and operation efficiency of the database will have great problems. This will directly affect the operation and reliability of the project.

II. What is database design

Database design is actually a process of planning and structuring data objects in a database and the relationship between these data objects.

III. The importance of database design

An undesigned database or a poorly designed database is likely to lead to

1. Database operation efficiency is underground.

2. Problems with updating, deleting and adding data

A well-designed database

1. High execution efficiency

2. Make the application easier to develop

3. Good expansibility

4. Good maintenance

4. Data model

A data model is like an outline of the relationship between data, and the whole model is like a framework.

If the data model is classified according to the representation of the relationship between records, it can be divided into hierarchical model, mesh model and relational model. The first two are also called formatted data models. The quality of the data model directly affects the performance of the database, so the choice of the data model is the primary task of the database design.

Entity-relation (Emurr) data model

Emurr data model (Entity-Relationship data model), that is, entity-relational data model. The Emurr data model is different from the traditional relational data model. It is not implementation-oriented, but real-object-oriented.

Entity (Entity)

Data is used to describe objects in reality, and the objects described are all kinds of objects, including concrete, abstract, physical and conceptual. All things, things, concepts that can be distinguished from each other and can be recognized by people are abstracted into entities. Multiple entities of the same type can be called Entity set. Therefore, in the Emurr data model, there are also types and values; entities can be defined as types, and each entity can be its instance and value.

Attribute (Attribute)

Entities generally have a number of specific features, which are called attributes of entities. Each attribute has its own value range, which is called value set in the Emurr data model. In the same entity set, the attributes of each entity and its set of values are the same, but may take different values. Property corresponds to the columns of the database table.

Relationship (Relationship)

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

Database design steps Database design can be divided into the following stages

1. Requirements analysis phase: analyze customer's business needs, especially data requirements.

2. Outline design phase: draw the Emurr diagram of the database and confirm the correctness and completeness of the requirements documents. The Emurr diagram is an important credential for the designers, developers, testers and customers of the project.

3. Detailed design stage: convert the Emurr diagram of the outline design phase into database tables, carry out logical design, determine the primary and foreign key relationship between each table, use the three paradigms of the database for audit, and conduct technical review. Finally, we decide which kind of database (Oracle, SQLServer, MySQL) to build the database and table.

Requirement analysis phase: database system analysis

The focus of the elegant analysis phase is to investigate, collect and analyze customers' business data requirements, as well as data security and integrity requirements.

Requirements analysis steps:

1. Identify business requirements

2. Identify the relational entity

3. Identify the attributes of each entity

4. Confirm the relationship between entities

Outline design phase: drawing Emurr diagram

As a database designer, you need to share your design ideas with other members of the project team, discuss the rationality, security and integrity of the database design, and confirm whether it meets the customer's business needs. Then using the Emurr diagram, this graphical representation is the most intuitive.

Entities, attributes and relationships in Emurr Graph

The simple Emurr diagram above shows the relationship between users and income and expenditure. As can be seen in the above picture: a rectangle is used to represent an entity, and an entity is a general noun; an ellipse represents an attribute, which is generally a noun; and a diamond is a relation, usually a verb.

Mapping cardinality

The mapping cardinality represents the number of entities that can be related to it. For the binary relationship between entity sets An and B, the possible mapping cardinality is:

1. One-to-one: that is, the association of at most one B entity in An entity and one An entity at most in B entity. The Emurr diagram is used to show:

2. One-to-many: an entity can be associated with any number of B entities, and the entities in B can be associated with at most one entity in A. The Emurr diagram shows:

3. Many to one: an entity is associated with at most one B entity, while B entity can be associated with any number of An entities. The Emurr diagram shows:

4. Many-to-many: an entity can have multiple B entities, and B entity can have any number of An entities. The Emurr diagram shows:

Emurr graph

The Emurr diagram can graphically represent the entire logical structure of the database, which consists of:

1. A rectangle represents a set of entities

2. The ellipse represents the attribute

3. The diamond represents the relationship,

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

5. The straight line arrow represents the mapping cardinality between entity sets.

Detailed design phase: convert Emurr diagram to table

The steps are as follows:

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

2. Identify the primary key of each table

3. Convert the relationship between entities into the primary foreign key relationship between tables.

VI. Database design standardization problems that often occur in database design

1. Large data redundancy

2. Insert data exception

3. Delete exception

4. Update exception

Standard design

In a better relational database model, the attributes in each relationship must meet some inherent semantic conditions, that is, the relational model must be designed according to certain specifications, which is the standardization of design.

In database design, there are some special rules, called database design paradigm, and a good database can be created by following these rules.

The three famous paradigm theories of database:

1. The first normal form (1NF)

The first normal form is the most basic conditional normal form that the relational database model should follow. Each attribute in several relations must be an inseparable simple item, not an attribute combination, that is, the value of the attribute is an inseparable atomic value.

2. The second normal form (2NF)

The second paradigm is to ensure that each column in the table is related to the primary key on the basis of the first paradigm. It is defined as satisfying the second normal form if a relationship satisfies 1NF and all columns except the primary key relationship depend on the primary key.

3. The third normal form (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, not indirectly. It is defined as if a relationship satisfies 2NF and no column other than the primary key is passed dependent on that primary key.

Relationship between normalization and performance

In order to meet the three paradigms, the performance of the database may be degraded to some extent. Therefore, in the actual database design, we should not only try to meet the three paradigms, so as to avoid data redundancy and abnormal operation of various databases, but also consider the data access performance. Sometimes, in order to improve the efficiency of database access, it is the most suitable database design to allow a small amount of data redundancy.

After reading the above, have you mastered the method of how to analyze the design of SQL Server database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.