Detailed explanation of database principle, save your final exam four parts [four]: database design

Detailed explanation of the database principle, save your final exam four parts [4]: ​​Chapter 7

Chapter VII

1. Overview of database design

1. Database design

It is defined : for a given application environment , optimized structure database logical and physical structure of the model , and accordingly to establish the database and its applications , making it possible to effectively store and manage data , to meet various types of user applications .

Development of design methods : manual design method → ​​standard design method (New Orleans method) → database design method based on ER model, 3NF (third normal form) design method, object-oriented design method, unified modeling language (UML) method , Computer Aided Design Method and Automated Design Method

The six stages of database design :

  • demand analysis
  • Conceptual structure design
  • Logical structure design
  • Physical structure design
  • Database implementation
  • Database operation and maintenance

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-sOs790AB-1622815006527) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604212036307.png)]

2. Demand analysis

1. Task

Definition : Investigate in detail the objects to be processed in the real world, fully understand the original system, clarify the various needs of users, determine the functions of the system on this basis, and consider the possible expansion and changes of the system in the future.

The focus of the survey : data and processing

Through investigation, collection and analysis, the user's requirements for the database are obtained :

  • Information request
  • Processing requirements
  • Security and integrity requirements

Specific steps to investigate user needs :

  • Investigate the situation of the organization
  • Investigate the business activities of various departments
  • On the basis of familiarity with business activities, assist users to clarify various requirements for the new system
  • Determine the boundaries of the new system

Investigation difficulty and resolution : Users lack computer knowledge and can hardly express their needs accurately. In-depth exchanges are required to determine gradually.

2. Method

  • Follow-up work: personal experience of business activities
  • Open investigation meeting: understand through meeting
  • Please introduce
  • ask
  • The design questionnaire asks users to fill in
  • View records

3. Data dictionary

Definition : The data dictionary is about the description of the data in the database, that is, metadata, not the data itself. The data dictionary is established during the requirement analysis stage and is continuously improved during the database design process.

A data dictionary usually includes data items, data structures, data streams, data storage and processing, where data items are the smallest unit of data.

① Data item
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-UN2PGu0c-1622815006529) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214012761.png)]
② Data structure
[External link image transfer failed. The origin site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Esk33QaB-1622815006530) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214037490.png)]
③ Data flow
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ITUBWdMw-1622815006531) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214055803.png)]
④ Data storage
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-fZt1G3sb-1622815006533) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214114605.png)]
⑤ Processing process
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-BpB2BZZl-1622815006533) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214139441.png)]

3. Conceptual structure design

1. Conceptual model

Features :

  • Truth fully reflects the real world
  • Easy to understand and change
  • Easy to convert to relational, mesh, hierarchical and other data models

Tool for describing conceptual models : ER model

2. ER model

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-kgHl8rvc-1622815006534) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214308325.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-se2WSHp0-1622815006535) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214330589.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-IxCY6Ma8-1622815006536) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214345877.png)]

3. ER model example

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-nYjot9w7-1622815006536) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214428705.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-i4L7LWkp-1622815006537) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214438796.png)]

4. ER diagram construction method

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-CniaCLK2-1622815006537) (C:\Users\Leizi of the official second\Desktop\Future Village Chief\image -20210604214540980.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-OmQWYQzR-1622815006538) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214550954.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-AsqAcZa3-1622815006539) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214606652.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-jovBNOPo-1622815006539) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214621514.png)]

5. Key points of ER diagram construction

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-GDmGxsU3-1622815006540) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214717990.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-quL4cvvE-1622815006540) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214726959.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-lvgkooTh-1622815006541) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214740198.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Vn4e8dXk-1622815006542) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214751874.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-UKXpUxtg-1622815006543) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214806128.png)]

6. Integration of ER diagram

Divided into two steps: merge, modify and refactor

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-SdBneiug-1622815006544) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214933494.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-qZ5oDaKV-1622815006545) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214942867.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-tiTBj1jZ-1622815006546) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214948528.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-6jgk7n8L-1622815006546) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604214959906.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-BXVH44Z6-1622815006547) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215031501.png)]

Four, logical structure design

1. Conversion of ER diagram to relational model

① Conversion content
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-CVuTKcnW-1622815006548) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215052938.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-MajwLmbn-1622815006549) (C:\Users\Official Er's Leizi\Desktop\Future Village Chief\image -20210604215108668.png)]
② Principle of conversion
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-wSY7kdrp-1622815006549) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215125087.png)]
③ Specific conversion method
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-4rN6cl2u-1622815006550) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215136042.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-6lI5ZM9b-1622815006551) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215318008.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-KSrUO9jE-1622815006551) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215335143.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-DWZpscV9-1622815006552) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215345287.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-PiLa6ruh-1622815006552) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215354935.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ssNNRenr-1622815006553) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215402782.png)]

2. Optimization of data model

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-S4FAWmW2-1622815006554) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215438173.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-vj6gPFHS-1622815006554) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215453802.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-vdPXDQRH-1622815006555) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215502323.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-RqRJyyuX-1622815006555) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215509281.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-91eHeR5P-1622815006557) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215516433.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-tWalaAYf-1622815006557) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215524038.png)]
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-OAJoqjeO-1622815006558) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215530138.png)]

3. Design user sub-modes

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-7GbMmgMm-1622815006559) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215545145.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-fmZ9DB3N-1622815006560) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215550543.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-x6kX8OoB-1622815006562) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604215558391.png)]