Database-database Design

Database Design Overview

Database design
Database design is to construct (design) optimized database logical mode and physical structure for a given application environment, and establish database and its application system, so that it can effectively store and manage data, satisfy various users ' application needs, including information management requirements and data operation requirements.
Objective: To provide an information infrastructure and efficient operating environment for users and various application systems.
The Basic Law of database construction
Three-point technology, seven-point management, more than basic data
Management
Database construction Project Management
Business management of the enterprise (i.e., the application Department)
Basic data
Collection, warehousing
Update the new data
Combination of structure (data) design and behavioral (processing) design
Integrate database structure design and data processing design closely

Database Design Methodology

Combination of manual and Empirical methods
The design quality is directly related to the experience and level of the designers.
The database has been running for a period of time, often varying degrees to identify problems, increasing maintenance costs
Standard Design Method
Basic idea: Process iteration and gradual refinement

New Orleans (New Orleans) method
Divide the database design into stages and steps
A database design method based on E-R model
The concept design stage is widely adopted
3NF (third paradigm) Design method
An effective method to be adopted in the logic phase
ODL (Object Definition Language) method
Object-oriented Database design method
Computer aided design
ORACLE Designer 2000
SYBASE PowerDesigner

Basic Steps in database design

6 Stages of database design
Demand analysis
Conceptual structure Design
Logical Structure Design
Physical Structure Design
Database implementation
Database operation and Maintenance
Requirements analysis and conceptual design independent of any database management system
Logical design and physical design are closely related to selected DBMS

I. Preparation of database design: Selected persons to participate in the design
1. System Analyst, Database Designer
Participate in database design throughout
2. User and database administrator
Participate in requirement analysis and database operation and maintenance mainly.
3. Application developers (programmers and operators)
Participate in the system implementation phase, responsible for programming and preparing the hardware and software environment
II. process of database design (six phases)
⒈ Requirements Analysis Phase
Accurate understanding and analysis of user needs (including data and processing)
The most difficult and time-consuming step

⒉ Conceptual Structure Design phase
The key to the entire database design
To form a conceptual model independent of the specific DBMS by synthesizing, summarizing and abstracting the user's needs

⒊ Logical Structure design phase
Transform a conceptual structure into a data model supported by a DBMS
To optimize it

⒋ Database Physical Design phase
Select a physical structure (including storage structure and access methods) that best suits the application environment for the logical data model

⒌ Database Implementation phase
Using DBMS-provided database languages (such as SQL) and host languages, based on the results of logical design and physical design
Set up a database
Compiling and debugging applications
Organize data warehousing
Run a pilot

⒍ database operation and Maintenance phase
Database application system can be put into operation after trial operation
It must be evaluated, adjusted and modified continuously during the operation of database system.

Tasks for demand Analysis

Detailed survey of the real-world objects to be addressed (organization, Department, Enterprise, etc.)
Fully understand the original system (manual or computer system)
Identify the needs of users
Determine the functionality of the new system
Full consideration of possible future expansions and changes
The focus of the survey is "data" and "processing" to obtain user requirements for the database
Information requirements
Processing requirements
Security and integrity requirements
Determine End-user requirements
User is missing computer knowledge
Designers lack the user's expertise
Workaround
Designers must keep in-depth communication with users
⑴ Investigation of Organizational structure
⑵ investigates the operational activities of various departments.
⑶ is familiar with the business activities on the basis of helping users to clear the requirements of the new system.
⑷ Determining the boundaries of a new system

Common investigative methods

(1) Valet work
(2) Open an investigation meeting
(3) Please introduce
(4) Inquiry
(5) Design Questionnaire please fill in the user
(6) Access to records
Structured analytical methods (structured analysis, abbreviated SA method)
Start with the top-level system organization
Top-down, layered decomposition analysis system
2. Decomposition processing capabilities and data
(1) Decomposition processing function
Decompose the specific contents of the processing function into several sub-functions
(2) Decomposition data
The processing function is decomposed at the same time, stepwise decomposition of the data used to form a number of layers of flow chart
(3) Expression method
Processing logic: Using a decision table or decision tree to describe
Data: Using a data dictionary to describe
3. Submit the results of the analysis to the user and obtain the user's approval

Data dictionary

The purpose of the data dictionary
Key results for detailed data collection and data analysis
The contents of the data dictionary
Data item
Data
Data flow
Data storage
Processing process

Data item

 数据项是不可再分的数据单位 对数据项的描述      数据项描述＝｛ 数据项名，数据项含义说明，别名，                                数据类型，长度，取值范围，取值含义，                与其他数据项的逻辑关系，数据项之间的          联系 ｝

Data

数据结构反映了数据之间的组合关系。 一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构混合组成。 对数据结构的描述      数据结构描述＝｛数据结构名，含义说明，                                   组成:｛数据项或数据结构｝｝

Data flow

数据流是数据结构在系统内传输的路径。 对数据流的描述　      数据流描述＝｛ 数据流名，说明，数据流来源，                               数据流去向，组成:｛数据结构｝，                               平均流量，高峰期流量｝

Data storage

数据存储是数据结构停留或保存的地方，也是数据流的来源和去向之一。对数据存储的描述　 数据存储描述＝｛数据存储名，说明，编号，                输入的数据流 ，输出的数据流 ，                组成:｛数据结构｝，数据量，存取频度，         存取方式｝

Processing process

具体处理逻辑一般用判定表或判定树来描述处理过程说明性信息的描述　处理过程描述＝｛处理过程名，说明，输入:｛数据流｝，                                  输出:｛数据流｝，处理:｛简要说明｝｝

Example: The data dictionary of the Student Status management subsystem.

数据项，以“学号”为例：数据项：　学号含义说明：唯一标识每个学生

Alias: Student number
Type: Character type
Length: 8
Range of values: 00000000 to 99999999
Value meaning: The first two digits of the student's grade,
Post six digits numbered sequentially
Logical relationship to other data items:
Data structure, taking "students" as an example

“学生”是该系统中的一个核心数据结构：数据结构：　学生含义说明：　是学籍管理子系统的主体数据结构，                      定义了一个学生的有关信息组成：　　　学号，姓名，性别，年龄，所在系，年级

　　
Data flow, "medical results" can be described as follows:
Data flow: Medical results
Description: The final result of a student's physical examination
Data stream Source: physical examination
Data flow to: approval
Composition: ...
Average flow rate: ...
Peak traffic: ...
Data storage, "Student Registration form" can be described as follows:
Data storage: Student Registration Form
Description: Record the student's basic situation
Flow into the data stream: ...
Flow out of data stream: ...
Composition: ...
Data Volume: 3000 sheets per year
Access mode: Random access
The process of "assigning quarters" can be described as follows:
Process: allocation of dormitories
Description: Assign student dormitories to all new students
Input: Student, dorm
Output: Dormitory arrangement
Treatment: The student dormitory is assigned to all new students after registration.
Requiring students of the same sex to be arranged in the same dormitory,
The same student can only be arranged in one dormitory.
Each student's living area is not less than 3 square meters.
The processing time of the new dormitory should not exceed 15 minutes.
A data dictionary is a description of the data in a database, which is metadata, not the data itself
The data dictionary is established in the requirement analysis stage, and it is constantly modified, enriched and perfected in the process of database design.
Designers should take full account of possible expansions and changes, make the design easy to change, the system is easy to expand
Users ' participation must be emphasized

Database-database Design