Database Design Specifications and skills Series 1

I. Database Design Process Database technology is the most effective means of information resource management. Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements. Each stage of Database Design: A. Demand analysis stage: Comprehensive

I. Database Design Process Database technology is the most effective means of information resource management. Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements. Each stage of Database Design: A. Demand analysis stage: Comprehensive

I. Database Design Process

Database technology is the most effective means of information resource management.

Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements.

Database Design phases:

A. Demand analysis stage: comprehensive application requirements of various users (real-world requirements ).

B. In the conceptual design stage: A Conceptual Model (information world model) independent of machines and various DBMS products is formed, which is described by a E-R diagram.

C. In the logic design stage: converts the E-R diagram to the data model supported by the specific database product, such as the relational model, to form the database logic model. Then, based on the user's processing requirements and security considerations, the necessary VIEW is created on the basis of the basic table to form an external data mode.

D. In the physical design stage: arranges physical storage and designs indexes based on the characteristics and processing needs of DBMS to form the internal database mode.

　　1. Demand analysis stage

Requirement collection and analysis: the data requirement described by the data dictionary is obtained (and the processing requirement described by the data flow diagram ).

Requirements Analysis focuses on investigating, collecting, and analyzing users' information requirements, handling requirements, and security and integrity requirements in data management.

Requirement Analysis Methods: Investigate the organization and organization, the business activities of each department, assist the user to clarify various requirements for the new system, and determine the boundaries of the new system.

Common survey methods include: follow-up operations, opening survey meetings, inviting special personnel to introduce, ask, and designing surveys. Users are requested to fill in and check records.

The methods for analyzing and expressing user requirements mainly include top-down and bottom-up methods. The top-down Structured Analysis method (SA) starts with the upper-level system organization and analyzes the system layer by layer, the data flow diagram and data dictionary are used to describe each layer.

The data flow chart shows the relationship between the data and the processing process. The Data in the system is described using the Data Dictionary (DD.

　　2. Conceptual Structure Design Stage

A conceptual model independent of a specific DBMS is formed through the synthesis, induction and abstraction of user requirements, which can be expressed by a E-R diagram.

The conceptual model is used for modeling the information world. The conceptual model does not depend on the data model supported by a DBMS. A conceptual model can be converted to a specific data model supported by a DBMS on a computer.

Concept model features:

(1) Strong semantic expression ability, which can easily and directly express various semantic knowledge in applications.

(2) It should be simple, clear, and easy to understand. It is a language for communication between users and database designers.

A common method for conceptual model design is the IDEF1X method, which applies the entity-contact method to a Semantic Modeling Technology in the semantic data model and is used to establish a system information model.

The steps to create a E-R model using the IDEF1X method are as follows:
2.1 Step 1 -- initialize the project

In this phase, the task starts from the description of the purpose and scope, determines the modeling target, develops the modeling plan, organizes the modeling team, collects the source material, and develops constraints and specifications. Collecting source materials is the focus of this phase. Through investigation and observation results, business processes, input and output of the original system, various reports, and collection of raw data, a basic data table is formed.

2.2 Step 1 -- Define an object

Entity integrators have a common set of features and attributes that can identify most entities directly or indirectly from the collected source material-basic data tables. Mark the entities represented by the terms in the source material name table that indicate things and terms that end with "code", such as customer code, agent code, and product code, in this way, potential entities are preliminarily identified to form a preliminary entity table.

2.3 Step 2 -- Define contact

Only binary connections are allowed in the IDEF1X model. n-element connections must be defined as n binary connections. Based on actual business requirements and rules, the entity contact matrix is used to identify the binary relationship between entities. Then, the potential, Link name, and description of the connection relationship are determined based on the actual situation, and the link type is determined, whether it is an identity link, a non-identity Link (mandatory or optional), a non-deterministic link, or a classification link. If each instance of the Child body needs to be identified by the relationship with the parent object, the relationship is identified, otherwise it is not identified. In a non-identity relationship, if the instance of each child entity is associated with only one parent entity, it is mandatory; otherwise, it is not mandatory. If the parent entity and the Child entity represent the same real object, they are classification relationships.

Step 3-definition code

Remove the non-deterministic relationship generated in the previous stage by introducing the cross entity, and then identify the Hou code attribute from the non-cross entity and the independent entity to uniquely identify the instance of each entity, then confirm the master code from the candidate code. To determine the validity of the master code and link, the non-empty rules and non-multi-value rules are used to ensure that an attribute of an object instance cannot be null, you cannot have more than one value at the same time. Identify the identified relationship by mistake, break down the entity, and finally construct the key base view of the IDEF1X model (KB chart ).

2.5 Step 4 -- Define attributes

Extract descriptive terms from the source data table to develop an Attribute Table and determine the attribute owner. Defines non-primary Code attributes and checks non-null and non-multi-value rules of attributes. In addition, check fully dependent function rules and non-pass dependency rules to ensure that a non-primary code attribute must depend on the primary code, the entire primary code, and only the primary code. In this way, the full attribute view of the improved IDEF1X model, which is at least in line with the third paradigm of the relational theory, is obtained.

Step 5: Define other objects and rules

Defines the data type, length, precision, non-null, default value, and constraint rules of an attribute. Defines trigger, stored procedure, view, role, synonym, sequence, and other object information.

　　3. Logical Structure Design Stage

Converts a conceptual structure to a data model (such as a relational model) supported by a DBMS and optimizes it. To design the logical structure, select the data model that best suits to describe and express the corresponding conceptual structure, and then select the most appropriate DBMS.

To convert a E-R diagram to a relational model is to convert the relationship between the object, the object attributes, and the object into a relational model. This conversion generally follows the following principles: A real shape is converted into a relational model. An object attribute is a link attribute. The entity code is the link code.

Optimize the data model, determine data dependencies, eliminate redundant connections, and determine the relationship patterns that belong to the nth paradigm. Determine whether to merge or break them down. In general, the relationship is divided into 3NF criteria, namely:

Each value in the table can be expressed only once.

Each row in the table should be uniquely identified (with a unique key ).

The table should not store non-key information that depends on other keys.

4. Physical database design phase

Select the physical structure (including the storage structure and access method) that best suits the application environment for the Logical Data Model ). Arranges physical storage and designs indexes based on DBMS features and processing needs to form the internal database mode.

　5. database implementation stage

Use the Data Language (such as SQL) provided by DBMS and its host language (such as C) to establish a database based on the logic design and physical design results, compile and debug applications, and organize the data warehouse receiving, and run the test. Database implementation mainly includes the following tasks: defining the database structure with DDL, organizing data warehouse receiving, compiling and debugging applications, and database trial run (DataDefinition Language (DDL Data Definition Language) used to open a new data table, set fields, delete data tables, delete fields, and manage all related database structures)

● Create (DDL is used to add something about the database structure)

● Drop (delete something about the database structure, DDL)

● Alter (Alter structure, DDL)

　　6. Database Operation and Maintenance

During the operation of the database system, it must be constantly evaluated, adjusted, and modified. The content includes: Database dumping and recovery, database security, integrity control, database performance monitoring, analysis and improvement, and database re-organization and re-construction.

　　7. Use of modeling tools

To speed up database design, there are currently many database auxiliary tools (CASE tools), such as Rational Rose, Erwin and Bpwin of CA, sybase PowerDesigner and Oracle Designer.

ERwin is mainly used to establish conceptual and physical models of databases. It can describe the attributes of objects, links, and objects in a graphical manner. ERwin supports the IDEF1X method. By using the ERwin modeling tool to automatically generate, change, and analyze the IDEF1X model, we can not only obtain excellent business functions and data requirement models, but also transform the IDEF1X model to the physical design of the database. The model drawn by ERwin tool corresponds to two types: Logical Model and physical model. In the logical model, the IDEF1X toolbox can be used to easily create and draw object links and attributes in a graphical manner. In the physical model, ERwin can define the corresponding tables and columns and automatically convert them to appropriate types for various database management systems.

Designers can select appropriate database design modeling tools as needed. For example, after the requirement analysis is complete, the designer can use Erwin to draw an erdiagram, convert the erdiagram into a relational data model, generate a database structure, draw a data flow diagram, and generate an application.
Author: Xiao Ling (not myself)