Database design (i)--Database design

Database design (i)--Database design Introduction

According to the standard design, the database design process is divided into six stages:
A, the system requirements analysis phase
B. Conceptual structure Design phase
C, logical structure design phase
D, physical structure design phase
E, database implementation phase
F, database operation and Maintenance phase
Requirements analysis and conceptual structure design are independent of any database management system.

Second, the system needs Analysis 1, the task of demand analysis

Requirements analysis of the task: the real world to deal with the object of a detailed investigation, through the original system of understanding, to collect support for the new system of basic data and processing, on the basis of the new system to determine the function.
A. Survey and analysis of user activities
B. Collect and analyze requirements data, identify system boundary information requirements, address requirements, security and integrity requirements
C. Prepare the System Analysis report

2, the method of demand analysis

There are two ways to analyze requirements: top-down, bottom-up
A, top-down
The top-down method is based on the topmost system organization, and the system is analyzed by layers decomposition.
Describe the system with Data Flow diagram and dictionary
Data Flow diagram: Describe the transformation process of input data to output
Data flow: Consists of a set of fixed component data that represents the flow direction of the data
Processing: Describes the transformation of the input data to the output data
Files: for storing data
Source or host: A person or organization that exists outside the system, representing the source of the system's input data and the whereabouts of the output data
B, bottom-up

3. Example

Teaching Management System
Basic requirements:
A school design school teaching management system, student entities including school number, name, gender, birthday, nationality, birthplace, resume, enrollment date, each student choose a major major, professional including professional number, name, category, a professional belongs to a college, a college can have multiple majors. College information to store the school number, college name, Dean. The teaching management should manage the timetable and the Student achievement table. Courses include course numbers, course names, credits, and each course is offered by a college. Each course that students take is a result.

III. Conceptual Structure Design 1. Introduction to Conceptual structure design

The goal of conceptual structure design is to design the E-R model diagram of the database to confirm the correctness and completeness of the requirement information. Specifically, it is to find the entity from the requirement analysis, confirm the entity's attribute, confirm the relation of the entity, draw the ER diagram.

2, the concept of structural design steps

The first step, data abstraction and local E-R model design
A, Data abstraction
Select an appropriate level in the multi-tier data stream as the starting point for designing the E-r diagram.
Determine which entities each local app contains, what attributes the entity contains, and how the entities relate to each other
Methods for dividing entities and attributes
Classification: Abstract a set of objects with certain common characteristics and behaviors into one entity.
Aggregation: Abstracts the constituent components of an object type as attributes.
B, local e-r model design
The principle of local E-R model design is that a property must be an irreducible data item, which can no longer be made up of other attributes, and the property cannot be associated with other entities, and the contact can only occur between entities.
In order to simplify the E-r diagram, all can be treated as attributes as attributes.
The second step, the global e-r model design
Integrate each local e-r model to form a global model. There are two ways to view integration:
A, multivariate integration method: a single time to combine multiple local e-r graphs into a global e-r graph.
B, two Yuan integration method: First integrate two important local e-r diagram, and then use the cumulative method to gradually integrate a new e-r diagram.
Merge:
Merge the local e-r diagram, eliminate the conflict, and generate the E-r diagram preliminarily. The key of merging is to eliminate the conflicts of local e-r graphs rationally.
The conflict classifications are as follows:

Optimization:
Eliminate unnecessary redundancy in the preliminary e-r diagram and generate a basic e-r diagram.
Redundant data: Data that can be exported from basic data.
Redundant links: Links that can be exported by basic links.

3. Example

E-r diagram of teaching management system
Entities: Students, majors, colleges, courses
Attributes to be logged by the entity table:
Students (student number, name, gender, birthday, birthplace, ethnicity, resume, date of entry)

Professional (professional number, professional name, category)
College (college, college name, dean)
Course (Course number, course name, credits)

Teaching Management ER diagram:

Four, logical structure design 1, the logical Structure design Brief introduction

The task of logical structure design is the process of translating the entity model completed in the conceptual structure design phase into a data model supported by a particular DBMS. The purpose of the logical structure design is to transform the entities, attributes, and connections in the E-r diagram into relational patterns.

2. Initial relationship model design

(1) The principle of inter-Entity Relationship transformation follows:
An entity is converted to a relational schema, and the attribute of the entity is the attribute of the relationship, and the key of the entity is the key of the relationship.
A connection is converted to a relational schema, and the keys of each entity connected to the contact and the properties of the contact are converted to the properties of the relationship.
There are three ways to contact a key:
If the contact is 1:1, the key for each entity is the candidate key for the relationship
If you contact the key of an insight relationship for a 1:n,zen-side entity
If the contact is n:m, then the combination of keys for each entity is the key of the relationship
Special case: Multiple connections
When a multivariate connection is converted into a relational pattern, the properties of the primary key and the contact itself of each entity connected to the multivariate connection are converted to the properties of the relationship, and the primary key of the resulting relationship is the combination of the entity keys.
(2) Transformation rules for inter-entity relationships:
A, a 1:1 relationship can be converted to a separate relational schema, or it can be combined with a relational schema at either end.

The original entity correspondence pattern is:
Class (class number, major, number)
Monitor (school number, name, specialty)
After merging the relationship "management" into the corresponding pattern for the entity "class":
Class (class number, major, number, squad number)
Monitor (school number, name, specialty)
The relationship "management" can also be merged into the entity "monitor" corresponding to the model, the relationship "management" merged into the entity "class" corresponding to the pattern:
Class (class number, major, number)
Monitor (school number, name, specialty, class number)
B, a 1:n relationship can be converted into an independent relational schema, or it can be combined with the relational schema corresponding to the N-terminal.

Entity-corresponding relationship patterns
Department (department number, department name, Dean, telephone)
Teacher (teacher number, name, professional, title, gender, age)
Relational patterns of relationships
Management (teacher number, department number)
After merging to the entity "teacher" (the relationship model can only be merged to one end of "many"):
Teacher (teacher number, name, professional, title, gender, age, Department number)
C, a m:n relationship is transformed into a relational schema. The method of conversion is: the code of each entity connected to the relationship and the properties of the relationship itself are converted to the properties of the relationship, the code of the new relationship is a combination of two connected entity codes.
The relationship can only be converted to standalone mode, and the properties of the pattern are composed of the attributes of the relationship itself and the keys of the two entities; The primary key is composed of the keys of the two entities.

Course (Course number, course name, hours, category) entity table
Student (school number, name, gender, professional, date of birth, photo) entity table
Elective (school number, course number, score) Relationship table
The multivariate relationship between D, three, or more than three entities is transformed into a relational pattern.
Properties of a relationship: the code of each entity connected to the multivariate relationship and the properties of the relationship itself
The code of the relationship: the combination of each entity code
The "lecturing" relationship is a ternary relationship, which can be converted into the following relationship pattern, where the course number, the employee number, and the ISBN are the combination codes of the relationship:
Lectures (Course number, employee number, ISBN)

3, the Relationship mode normalization

The initial relational model is optimized by using the paradigm theory of database design. The three main paradigms of database design are as follows:
The first paradigm each of the classifications must be an irreducible data item. Attributes are not re-divided to ensure the atomicity of each column.
The second paradigm requires that each table describe only one thing, and each record has a unique identity column.
The third normal Form database table does not contain non-primary key information that has already been included in other tables.
The normalization process for relational schemas is as follows:
A. Determine the paradigm level
Investigate the function dependence of the relational pattern and determine the paradigm level.
B, the implementation of standardized processing
Normalization method and theory are used to standardize the relationship pattern.
C, pattern improvement
Merge:
Merging tables with the same primary key for the associated query to improve query efficiency
Decomposition:
Horizontal decomposition, the relationship of the tuple into a number of subsets, improve query efficiency; Vertical decomposition, the relationship is often used in conjunction with the attribute decomposition, forming a sub-relationship, improve the efficiency of execution. Maintain lossless connections and function dependencies when decomposing.

4. Example

Teaching Management System
The relational model that is transformed from the ER model:
Entity Table of students (student number, name, gender, birthday, hometown, nationality, entry date, professional number)
Professional (professional number, professional name, category, college number) entity table
College (college, college name, Dean) entity table
Course (Course number, course name, credit, college number) entity table
Table of achievement (school number, course number, grade) relationship
When you convert to a relational model, a one-to-many connection adds a foreign key to the relationship of the corresponding multiparty entity.
Increase in demand:
If the teaching management system also manages the teacher teaching arrangement, the teacher includes the number, the name, the age, the title, a teacher can only belong to one college, a teacher may take several courses, a course can have more than one teacher to come on, each teacher each course has a class number and the number of lessons.
Er diagram of the teacher entity:

Teaching Management System ER diagram:

Relational table Many-to-many
Score Table (School number, course number, score, time, location)

Five, physical structure design 1, physical structure Design Brief introduction

Physical Structure design: For a given logical data model, choose a physical structure that best suits the application environment. The physical structure design of a database is divided into two steps:
A. Determine the physical structure: access method and storage structure
B. Evaluation of physical Structure: evaluation focuses on time and space efficiency
According to the various storage structures and access methods provided by the database management system and other physical design measures depending on the specific computer structure, the most suitable physical storage structure (data type index primary key) is selected for the specific application task.

2. Determine the physical structure

(1) Design of storage structure
In a physical structure, the basic unit of access to data is the storage record.
A collection of all stored records of a type is called a file.
Consider the three factors of access time, storage space utilization and maintenance cost when determining the database storage structure. Eliminating all redundant data, for example, can save storage space, but often leads to an increase in retrieval costs, so tradeoffs must be made to choose a compromise.
(2) Design of data access path
In a relational database, choosing an access path primarily means determining how the index is built. For example, which fields should be used as sub-code to establish sub-index, the establishment of a single-code index or composite index, how many to establish the appropriate, whether to establish a clustered index, etc.
(3) Design of data storage location
To improve performance, the volatile part of the data, the stable part, the regular access section, and the lower part of the storage frequency can be stored separately.
(4) Design of system configuration
DBMS products typically provide storage allocation parameters that designers and DBAs can use to physically optimize the database. Initially, the system assigns reasonable defaults to these variables, but these values are not necessarily suitable for each application environment, and they need to be re-assigned to improve the performance of the system in the case of physical design.

3. Evaluation of physical structure

In the process of physical structure design, it is necessary to weigh the time efficiency, space efficiency, maintenance cost and various user requirements, the result can produce many kinds of schemes, and the Database Designer must evaluate the scheme carefully and choose a better scheme as the physical structure of the database.
The method of evaluating the physical database relies entirely on the DBMS chosen, mainly starting from the quantitative estimation of storage space, access time and maintenance cost of various schemes, and comparing the estimation results, and choosing a better reasonable physical structure.

4. Example

Teaching Management System
Table 1-1 College

Table 1-2 Professional

Table 1-3 Students

Table 1-4 Courses

Table 1-5 Transcript

VI. implementation of database 1, database implementation process

Database implementation: The process of establishing the actual database structure, loading data, testing and commissioning according to the result of logical design and physical design.

2. Example

Student table:

Curriculum:

Professional table:

College table:

Score Table:

Vii. database operation and Maintenance

Key tasks for database operation and maintenance include:
A, maintain the security and integrity of the database
B. Monitor and improve database performance
C. Reorganize and construct the database
Only the database system is running, it needs to be constantly modified, adjusted and maintained.

Database design (i)--Database design