Design Database System Design

This article from http://www.cnblogs.com/jiapengliang2005/articles/1632745.html

 

 

1 Database Design Overview

Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, so that it can effectively store data.
Basic Steps for Database Design:

• Requirement Analysis

• Conceptual Structure Design

• Logical Structure Design

• Physical Structure Design

• Database creation and Testing

• Database operation and maintenance.

Database Stage Design Description

2. Conceptual Structure Design

In the early stage of database design, after the demand analysis stage, the logical structure is directly designed. At this time, we must consider the relationship and characteristics of real-world information and meet the constraints of specific database systems. Therefore, the description of the objective world is limited. At the same time, the design work is also complicated because multiple aspects should be taken into account during design. In 1976, p. p.s. Chen proposed to design a conceptual model before the logical structure design, and proposed the entity-association method (entity-relationship approach) of the database design ). This method does not include deep theories, but provides a simple and effective method. It is currently a common tool in database design.

There are a lot of commercial software to support E-R models, such as Sybase powerdesigner dataarchitect (the latest version of v9.5.1 for Windows), Microsoft infomodeler (visiomodeler) and so on.

Figure S-designer data‑ect 5.1 E-R Model

The Design of Conceptual Model Using E-R model is usually divided into two steps, first is to establish a local conceptual model, and then integrate the local conceptual model to become a global conceptual model.

2.1 E-R Model Basic symbols

• Object Representation: Use a rectangle

• Description of the contact: Diamond, 1: n (m: 1), (M: N)

• Attribute representation: uses an elliptical shape.

A E-R diagram has the following features:

• A contact set can be defined on two or more entity sets, such as the teacher-student-course contact set S-T-C, is defined on three entities.

• A contact set can also be defined on an entity set. For example, a part is divided into sub-parts, and each part can be composed of M sub-parts, each sub-part can be combined into N parts.

• For a given object set, you can define more than one contact set. For example, for a project, a worker can define two contact sets, one of which indicates the connection between a project and a worker, the other is the contact between the project owner of the project and the worker. The former is N: m, and the latter is.

• An entity contact diagram can indicate the dependency of an entity type on the existence of another entity type. For example, a worker instance reflects the relationship between the owner and the dependent entity, which is indicated by an arrow, it indicates that the existence of the owner entity depends on the entity corresponding to the worker.

2.2 preliminary E-R diagram design

On the basis of data analysis, we can design the conceptual structure. Steps to design a preliminary E-R diagram:

• First design the local E-R diagram, also known as the user view

• Combining the local E-R diagram to form the overall E-R diagram, that is, the integration of the user view.

In the design of the preliminary E-R diagram, we should try to fully set up the requirements of all departments in the organization for information, without the need to consider data redundancy.

The design of a local conceptual model is based on the user's point of view to design a conceptual structure that meets the user's needs. The local conceptual model is designed to organize and classify the collected data items and determine which data items are used as entities, which data items are used as attributes, and which data items are attributes of the same object.Principles for determining entities and attributes:

• Attribute can be regarded as an attribute as far as possible rather than as an entity;

• The link between the data element as an attribute and the described object can only be 1: N;

• Data items as attributes cannot be described by other attributes or be associated with other entities or attributes.

Example 1: A simple management system for a mechanical manufacturing plant. First, design two partial E-R diagrams Based on the factory technical department and the factory supply department. The factory technical department is concerned with the product performance parameters, components, parts, materials, and consumption. The factory supply department is concerned with the product price, price and inventory of materials used.

Example 1 Local E-R Diagram

The preliminary E-R diagram is obtained by combining the two E-R charts.

Example 1 Diagram preliminary E-R Diagram

The preliminary E-R diagram is a pure representation of the real world, which may be associated with redundant data and inter-entity redundancy. The so-called redundant data refers to the data that can be exported from the basic data, and the redundant contact refers to the connection that can be exported from the basic contact.

2.3 Basic E-R diagram design

Preliminary E-R diagram due to the existence of redundant information, will damage the integrity of the database, to the database management trouble, and even cause data inconsistency error. Therefore, data redundancy and joint Redundancy must be eliminated to eliminate the redundant E-R diagram, calledBasic E-R Diagram.

The Redundant method can be analyzed.

Example 1 Figure basic E-R Diagram

Example 2: A bookstore purchase summary registration form, which is obtained through 1nf, 2nf, and 3nf analysis.

Corresponding BASIC E-R Diagram

Example 2 Figure basic E-R of the book summary Registration Form

The basic E-R diagram meets the requirements of the third paradigm.

The analysis method of Case 1 is difficult for a large complex system. It can convert the preliminary E-R diagram Into the function dependency, it is divided into multiple functional dependencies that conform to the third paradigm, and then constitute the basic E-R diagram.

3. Logical Structure Design

Task: convert a basic E-R diagram Into a logical structure that matches the data model supported by the selected DBMS product.
Process:

• Converts a conceptual structure to a data model in a relational, mesh, or hierarchical model supported by the existing DBMS;

• Evaluate the transformed model based on functional and performance requirements to see if it meets user requirements;

• Optimize the data model

3.1 conversion of E-R graph to Relational Model

Principles:
(1) A real shape is converted into a relational model. The attributes of an object are the attributes of a link, and the keys of an object are the keys of a link;
(2) A contact is converted to a link mode. The keys and attributes of each solid type connected to the contact are converted to the link attributes. The keys of this link are divided into three different situations:

• If the link is, the keys of each solid type are the optional keys in the link mode.

• If the contact is 1: N, the key of the link mode corresponding to the contact is the key of the solid shape of the N end.

• If the contact is M: N, the key of the connection mode is the combination of the solid types of keys involved in the contact.

When the figure contact is

When the figure contact is 1: N

When the figure contact is M: N

(3) handling of some special contacts can be divided into the following situations:

• When an entity is dependent on the existence of another entity, the relationship between the two entities represents a kind of relationship between the two entities.

• When a contact is defined on the same type of entity, the contact is converted to a relational mode. The key of the real type connected to the contact and the attribute of the contact are converted to the attribute of the relational mode.

Graph object dependency

The graph contact is defined on the same type of entity.

3.2 model evaluation:Function evaluation and performance evaluation

3.3 Model Optimization:Reduces connection operations, vertical and horizontal segmentation, and uses snapshots to reduce data footprint

4. Physical Structure Design

5 database creation and Testing

6. Database Operation and Maintenance

7. Database Protection