SQL Note---Database design steps (GO)

Directory

Overall design process
Demand analysis
Conceptual structure Design
Logical Structure Design
Physical Design of database
Database implementation
Database operation and Maintenance

Overall design process

Database Design steps:

Design Description:

Database design at different stages of the database model:

Features of database design:

Demand analysis

Analyze and express user needs:

• the first one is to abstract any system into :

• decomposition processing capabilities and data :
  • decomposition processing function :
    • Decompose the specific contents of the processing function into several sub-functions
  • 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
  • Expression Method :
    • processing logic : Using a decision table or decision tree to describe
    • data : Using a data dictionary to describe
• Submit the results of the analysis to the user and obtain the user's approval

Task:

• through surveys, collect and analyze data to obtain user requirements for data :
  • Information Requirements :
    • refers to the content and nature of the information that the user needs to obtain from the database, and then the data requirements are exported.
  • Processing Requirements :
    • What processing function does the user want to complete, what are the requirements for the first response time, whether the processing is batch processing or online processing
  • Security and integrity requirements

Requirements Analysis Process:

Streaming chart

• Symbol Description:

• Example:

Data dictionary:

• The difference from the streaming chart
  • Data Flow Diagram ------expressing the relationship between information and processing
  • Data Dictionary --a collection of data descriptions in the system
• Composition
• Data item:
  • form :

    • Data item Description ={data item name, data item meaning description, alias, data type, length, value range, value meaning, logical relationship of other data items, data item connection}

  • Example : Data item, take "study number" as an example:
    • data Item : School Number
    • meaning Description : uniquely identifies each student
    • alias : Student number
    • type : Character type
    • Length : 8
    • range of Values : 00000000 to 99999999
    • value meaning : first two digits the student's grade, the last six digits numbered sequentially
• data structure:
  • form :
    • data structure description ={data structure name, meaning description, composition: {Item or structure}}
  • example : Data structures, taking "students" as the student "is a core data structure in the system:
    • data Structure : Student
    • meaning description : It is the main body data structure of the school student Management subsystem, and defines the relevant information of a students
    • composition : Student number, name, gender , age, department, Grade
• Data flow:
  • form /strong>:
    • Data flow description ={data stream name, description, data stream source, data flow whereabouts, composition: {Data structure}, average traffic, peak traffic} /li>
  • example data flow, "medical results" can be described as follows:
    • Data Flow : Medical Results
    • Description : Final results of student participation in physical examination
    • Data Flow source : Medical Checkup
    • Data flow to : Approve
    • make up : ...
    • Average traffic : ...
    • Peak traffic : ...
• data store:
  • form :
    • data store description ={data store name, description, number, input data stream, output data stream, composition: {Data Structure}, volume, access frequency, access Way}
  • example : Data storage, "Student Registration form" can be described as follows:
    • Data storage : Student Registration Form
    • description : Record students ' basic situation
    • incoming Data flow : ...
    • Outgoing data stream : ...
    • consists of : ...
    • Data Volume : 3000 sheets per year
    • access Method : Random access
• Processing process:
  • form :
    • Process description ={Process name, description, input: {Data flow}, output: {Data flow}, processing: {process}}
  • Example : 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. The same dormitory can only be arranged in one dormitory for students with the same sex. Each student's living area is not less than 3 square meters. The processing time of the new dormitory should not exceed 15 minutes.

Conceptual structure Design

Characteristics:

• Can truly and fully reflect the real world
• Easy to understand
• Easy to change
• Easy to transform into relational, mesh, hierarchical, and various data models

Four types of methods:

• From top to bottom:
  • Defined:
    • That is, the framework of the global conceptual structure is defined first and then gradually refined
  • Graphic:

• Bottom-up:
  • Defined:
    • That is, first define the conceptual structure of the local application, and then assemble them to get a global concept.
  • Steps:
    • 1th Step: Abstract data and design a partial view
    • 2nd step: Integrate the local view to get a global conceptual structure
  • Graphic:

• Gradual expansion:
  • definition :
    • First, define the most important core conceptual structure, then scale out, and gradually generate other conceptual structures by rolling the ball up to the overall conceptual structure
  • Diagram :

• Hybrid strategy:
  • definition :
    • By combining top-down and bottom-up, a global conceptual framework is designed with a top-down strategy, which integrates a local conceptual structure designed by the bottom-up strategy for the skeleton.
  • Diagram :

Three common abstractions:

• Classification (classification):
  • Define a class of concepts as a type of object in the real world
  • Abstract the semantics of "is member of" between object values and types
  • legend :

• Aggregation (Aggregation):
  • Define a component of a type
  • Abstract the semantics of "is part of" between the object's inner type and the composition
  • Complex aggregation, the composition of a certain type is still a gathering
  • legend :

• Summary (generalization):
  • Define a subset of types to contact
  • Abstract the semantics of "is subset of" between types
  • Inheritance :

E-r Chart:

• Task :
  • Extract data from the data dictionary for each local application
  • According to the flow chart, the attributes of entities and entities in each local application are calibrated, and the codes for identifying entities
  • Determine the relationships between entities and their types (1:1,1:N,M:N)
• Two guidelines :
  • Properties can no longer have properties that need to be described. That is, the attribute must be an irreducible data item and no longer be made up of another property
  • A property cannot be associated with another entity. Contact only occurs between entities

View Integration:

• category :
  • Integration of multiple sub-e-r graphs at one time
  • Typically used when a detail view is relatively simple
• Progressive Integration :
  • Integrate two sub-e-r graphs at a time in an additive manner
• Diagram :

• Conflict:
  • Two types of attribute conflicts:
    • Attribute domain conflict:
      • Type of property value
      • Range of values
      • Different collection of values
    • Property Value Unit Conflict
  • Two types of naming:
    • conflicts with the same name: objects of different meanings have the same name in different local applications
    • synonyms (more than one name ): Objects of the same meaning have different names in different local applications
  • Three types of structural conflicts:
    • The same object has different abstractions in different applications
    • The same entity has not exactly the same number of attributes and attributes in different e-r charts
    • Relationships between entities render different types in different detail views
• Basic tasks:
  • Eliminate unnecessary redundancy, design and generate basic E-r diagram
• Steps:
  • combine the E-r diagram to generate a preliminary e-r diagram :
    • Conflict elimination
    • Attribute conflicts
    • Naming conflicts
    • Structural conflicts
  • Modify and refactor:
    • Eliminate unnecessary redundancy, design and generate basic E-r diagram
    • Analysis method
    • Normalization theory

To verify the conceptual structure:

• There must be consistency within the conceptual structure of the whole, there is no contradictory expression
• The overall conceptual structure can accurately reflect the original structure of each view, including the relationship between attributes, entities and entities
• Overall conceptual structure to meet all requirements identified during the analysis phase

Logical Structure Design

E-r diagram and Relationship model transformation:

• Convert content :
  • Convert relationships between entities, attributes of entities, and entities to relational mode
• Conversion principle :
  • Converting an entity to a relational schema
  • The attribute of the entity is the property of the relationship
  • The code of the entity is the code of the relationship

E-r the relationship between the entity type has the following different situations:

• A 1:1 connection can be converted to a separate relational pattern, or it can be combined with a relational schema at either end :
  • Convert to a separate relational pattern
  • Merge the relationship pattern corresponding to one end entity
• A 1:n connection can be converted to a separate relational schema, or it can be combined with a relational pattern corresponding to the N-terminal :
  • Convert to a separate relational pattern
  • Merging relationship patterns with N-end
• A m:n connection is converted to a relational schema
• A multi-dimensional relationship between three or more entities is transformed into a relational pattern
• Relational patterns with the same code can be combined:
  • Objective: To reduce the number of relationships in the system
  • Merge method: Add all the properties of one of the relational schemas to another relationship pattern, and then remove the synonyms (possibly with the same name) and adjust the order of the attributes appropriately

Optimizing the data Model approach:

• Determine data dependencies
• Eliminate redundant connections by minimizing data dependencies between relational schemas.
• The model of each relationship is defined as the first paradigm respectively.
• Analyze whether these patterns are appropriate for your application environment, and determine whether you want to merge or decompose them.
• Perform necessary decomposition or merging of relational schemas

To design a user sub-mode:

• Use aliases that are more user-compliant
• Different external modes are defined for different levels of users to meet the security requirements of the system.
• Simplifies user use of the system

Task:

• Translating a conceptual structure into a specific data model
• The steps of logical structure design
• Transform the conceptual structure into a general relational, mesh, and hierarchical model
• Transform a transformed relationship, mesh, and hierarchy model into a data model supported by a specific DBMS
• Optimize your data model
• Design User sub-mode

Logical structure design is 3 steps:

Physical Design of database

Steps:

• Determine the physical structure of a database, mainly referring to the access method and the storage structure in the relational database
• Evaluation of physical structure is focused on time and space efficiency

Index Access:

• general rules for choosing an indexed access Method :
  • If a (group of) attribute is often present in a query condition, consider indexing on this (group) attribute (composite index)
  • If an attribute is often used as a parameter to a clustered function such as the maximum and minimum values, consider indexing on this property
  • If a (or group of) attribute is often present in the join condition of the join operation, consider indexing on this (or set) attribute
• an excessive number of indexes defined on a relationship can result in more overhead :
  • Cost of maintaining indexes
  • Cost of finding indexes

Cluster:

• Use :
  • Greatly improve the efficiency of query by cluster code
  • Save storage space
• Limitations :
  • Clustering can only improve the performance of specific applications
  • The overhead of building and maintaining a cluster is quite large
• Scope of application :
  • Suitable for single-relationship independent clustering and multi-relationship combination clustering
  • When accessing or connecting via a clustered code is the primary application of the relationship, when other accesses that are not related to the cluster code are small or minor, clustering can be used

Database implementation

Data loading method:

• Manual method
• Computer-assisted data warehousing

Main work:

• Functional Testing : Actually run the database application, perform various operations on the database, test whether the application's functionality meets the design requirements, and if not, modify and adjust the parts of the application until the design requirements are met
• Performance Testing : Measurement system performance indicators, analysis whether to achieve the design goals, if the test results and design goals do not match, you want to return to the physical design phase, re-adjust the physical structure, modify the system parameters, in some cases even return to the logical design phase, modify the logical structure

Emphasize two points:

• Organize data warehousing in batches
  • Re-engineering physical structures and even logical structures can cause data to be re-put into storage.
  • Enter small batches of data for debugging purposes first :
    • After the test run basic qualified and then bulk input data
    • Gradually increase the amount of data, and gradually complete the operation evaluation
  • Because the data storage workload is too large, time-consuming, laborious, so should be organized in batches of data warehousing
• Dumps and restores of databases
  • In the database trial run phase, the system is still unstable, hard, software failure can occur at any time
  • The operator of the system is not familiar with the new system, and the misoperation is unavoidable.
  • Therefore, it is necessary to do a good job of database dump and recovery to minimize the damage to the database.

Database operation and Maintenance

DBA maintains database work:

• Dumps and restores of databases
• Security and integrity control of the database
• Monitoring, analysis and improvement of database performance
• Re-organization and re-structuring of databases

Re-organization:

• form :
  • All re-organization
  • Partial re-organization (only re-organization of frequently added and deleted tables)
• Target :
  • Improve system performance
• work :
  • according to the original design requirements :
    • Reschedule Storage Locations
    • Recycling waste
    • Reduce the pointer chain
  • The reconfiguration of the database does not change the logical and physical structure of the original design data

Re-construction:

• Adjust the database mode and internal mode according to the new environment :
  • Add a new data item
  • Change the type of the data item
  • Changing the capacity of a database
  • Add or remove an index
  • Modify integrity Constraint conditions

SQL Note---Database design steps (GO)