Database and database Learning

Database and database Learning
Database Design Overview

Database Design
Database Design refers to the construction (Design) of the database logical mode and physical structure for a given application environment, and the establishment of the database and its application system accordingly, it can effectively store and manage data to meet application requirements of various users, including information management requirements and data operation requirements.
Objective: To provide an information infrastructure and efficient operating environment for users and various application systems
Basic Rules of Database Construction
Three-point technology, seven-point management, and 12-point basic data
Management
Database Construction Project Management
Business Management of Enterprises (Application Departments)
Basic Data
Collection and warehouse receiving
Update new data
Combination of structure (data) design and behavior (processing) Design
Closely integrates database structure design and data processing design

Database Design Method

Combination of manual and experience
The design quality is directly related to the experience and level of the design personnel.
After the database runs for a period of time, various problems are often found to varying degrees, increasing the maintenance cost.
Standard Design Method
Basic Idea: Process iteration and refinement

New Orleans Method
Database Design is divided into several stages and steps
Database Design Method Based on E-R Model
Concept Design Stage widely used
Design Method of 3NF (third paradigm)
Effective methods available in the logic phase
ODL (Object Definition Language) Method
Object-Oriented Database Design Method
Computer Aided Design
ORACLE Designer 2000
SYBASE PowerDesigner

Basic Steps for Database Design

Database Design is divided into six phases
Requirement Analysis
Conceptual Structure Design
Logical Structure Design
Physical Structure Design
Database implementation
Database Operation and Maintenance
Requirement Analysis and conceptual design are independent of any database management system
Logical design and physical design are closely related to the selected DBMS.

I. Preparations for Database Design: persons selected to participate in the design
1. system analysts and database designers
Participate in Database Design from beginning to end
2. Users and Database Administrators
Mainly participate in requirement analysis and Database Operation and Maintenance
3. Application developers (programmers and operators)
Participate in the system implementation phase, responsible for programming and preparing the software and hardware environment
Ii. Database Design Process (six phases)
Pipeline requirement analysis stage
Accurately understand and analyze user requirements (including data and processing)
The most difficult and time-consuming step

Concept Structure Design Stage
Key to the entire database design
A conceptual model independent of a specific DBMS is formed by integrating, summarizing, and abstracting user requirements.

Pipeline Logical Structure Design Stage
Converts a conceptual structure to a data model supported by a DBMS.
Optimize it

Physical Design Stage
Select a physical structure (including storage structure and access method) that best suits the application environment for the Logical Data Model)

⒌ Database implementation stage
Use the database language (such as SQL) and host language provided by DBMS, based on the Logical design and physical design results
Create a database
Compile and debug applications
Organize data warehouse receiving
Test Run

Runtime and maintenance stage
The database application system can be put into operation after trial run
The database system must be constantly evaluated, adjusted, and modified during operation.

Requirement Analysis task

Investigate in detail the objects to be processed in the real world (organizations, departments, enterprises, etc)
Full understanding of the original system (manual system or computer system)
Clarify various user needs
Determine features of the new system
Fully consider possible future expansion and changes
The investigation focuses on "data" and "processing" to obtain users' database requirements.
Information requirements
Handling requirements
Security and Integrity requirements
Determine users' final requirements
The user lacks computer knowledge
The designers lack the professional knowledge of users.
Solution
Designers must constantly communicate with users in Depth
(1) Investigate organizational unit information
(2) investigate the business activities of each department.
(3) assists users in clarifying requirements for new systems based on familiarity with business activities.
(4) determine the boundaries of the new system

Common Survey Methods

(1) Follow-up assignments
(2) opening an investigation MEETING
(3) Please give a special introduction
(4) Inquiry
(5) enter the Design Survey Form
(6) query records
Structured Analysis (SA)
Start with the upper-level system organization
Top-down, layer-by-layer Analysis System
2. decomposition and processing functions and data
(1) decomposition processing function
Break down the specific content of the processing function into several sub-functions
(2) Data Decomposition
The processing function is gradually decomposed, and the data used is decomposed step by step to form a data flow chart of several layers.
(3) expression method
Processing logic: used to describe tables or decision trees
Data: Describes data in a data dictionary.
3. submit the analysis results to the user again, with the user's approval

Data Dictionary

Usage of Data Dictionary
Main results of detailed data collection and data analysis
Data Dictionary content
Data items
Data Structure
Data Stream
Data Storage
Handling process

Data items

Description of a data item as a data unit that cannot be further divided data item description = {data item name, description, alias, data type, length, value range, value meaning, the logical relationship with other data items and the relationship between data items}

Data Structure

The data structure reflects the combination of data. A data structure can be composed of several data items, several data structures, or a combination of several data items and data structures. Description of Data Structure Description = {Data Structure name, description, composition: {data item or data structure }}

Data Stream

Data streams are the path for data structures to be transmitted in the system. Description of data stream description = {data stream name, description, data stream source, data stream whereabouts, composition: {data structure}, average traffic, peak traffic}

Data Storage

Data storage is the place where the data structure stays or is stored, and also the source and destination of the data stream. Description of data storage description = {data storage name, description, number, input data stream, output data stream, composition: {data structure}, data volume, Access frequency, access Method}

Handling process

The specific processing logic is generally used to describe the processing process descriptive information using a decision table or decision tree. The processing process description = {processing process name, description, input: {data stream}, output: {data stream }, handling: {brief description }}

For example, the data dictionary of the student status management subsystem.

Data items. Take "student ID" as an example. Data items: Student ID Description: uniquely identifies each student.

Alias: Student ID
Type: Portable
Length: 8
Value Range: 00000000 to 99999999
Value description: The first two marks indicate the grade of the student,
The last six digits are numbered in order.
Logical Relationship with other data items:
Data structure, taking "student" as an Example

"Student" is a core data structure in the system: Data Structure: Student description Description: it is the main data structure of the student status management subsystem. It defines the composition of related information of a student: Student ID, name, gender, age, department, grade

　　
Data flow. The "health check result" can be described as follows:
Data Flow: Health Check Results
Note: Final Results of students performing physical examination
Data Flow Source: Health Check
Data Stream direction: Approval
Composition :......
Average Traffic :......
Peak traffic :......
Data storage. The "student registration form" can be described as follows:
Data storage: student registration form
Note: records the basic information of students.
Inbound Data Stream :......
Outbound data stream :......
Composition :......
Data volume: 3000 million records per year
Access Method: Random Access
The process of "assigning a dormitory" can be described as follows:
Handling process: allocate a dormitory
Student dormitories are allocated to all new students.
Input: Student, dormitory
Output: dormitory Arrangement
Handling: after the new student reports, all new students will be assigned student dormitories.
Only the same student in the same dormitory is required,
The same student can only be arranged in one dormitory.
The area of residence for each student is no less than 3 m² square meters.
Arrange for the dormitory to be processed within 15 minutes.
A data dictionary is a description of data in a database. It is metadata, not the data itself.
Data dictionaries are established in the demand analysis stage and are constantly modified, enriched, and improved during the database design process.
Designers should fully consider possible expansion and changes to make the design easy to change and the system easy to expand
User participation must be emphasized