Shulou(Shulou.com)06/01 Report--

Database Design (1)-- Database Design (1) introduction to Database Design

According to the standard design, the design process of the database is divided into six stages:

A. system requirements analysis phase

B. Conceptual structure design stage

C, logical structure design stage

D, physical structure design stage

E, database implementation phase

F, database operation and maintenance phase

Requirements analysis and conceptual structure design are independent of any database management system.

Second, system requirement analysis 1. The task of requirement analysis

The task of needs analysis: conduct a detailed survey of the objects to be dealt with in the real world, through the understanding of the original system, collect and process the basic data that support the new system, and determine the function of the new system on this basis.

A. investigation and analysis of user activities

B. collect and analyze requirements data, determine system boundary information requirements, deal with requirements, security and integrity requirements

C. Prepare the system analysis report

2. The method of requirement analysis

There are two methods of demand analysis: top-down and bottom-up

A. from top to bottom

The top-down method starts with the top-level system organization and analyzes the system by layer-by-layer decomposition.

Describe the system with data flow diagram and data dictionary

Data flow diagram: describes the transformation process from input data to output data

Data flow: consists of a set of fixed components of data that represent the direction of data flow

Processing: describes the transformation from input data to output data

Files: for storing data

Source or destination: a person or organization that exists outside the system, indicating the source of the system's input data and the destination of the output data

B, bottom up

3. Examples

Teaching management system

Basic requirements:

A certain school designs the teaching management system of the school, the student entity includes student number, name, gender, birthday, nationality, place of origin, resume, admission date, each student chooses a major, including major number, name, category, a major belongs to a college, a college can have multiple majors. College information should be stored in the college number, the name of the college and the dean. Teaching management should manage the curriculum and students' scores. Courses include course number, course name and credits. Each course is offered by a college. Students get a grade for each course they take.

Conceptual structure design 1. A brief introduction to conceptual structure design

The goal of conceptual structure design is to design the Emurr model diagram of the database to confirm the correctness and integrity of the requirement information. Specifically, it is to find the entity from the requirement analysis, confirm the attributes of the entity, confirm the relationship of the entity, and draw the ER diagram.

2. Steps of conceptual structure design

The first step is data abstraction and local Emurr model design.

A, data abstraction

Select an appropriate level in the multi-layer data flow as the starting point for the design of the Emurr diagram.

Determine which entities each local application contains, which attributes it contains, and the relationships between entities.

The method of dividing entities and attributes

Classification: abstracts a group of objects with some common characteristics and behavior into an entity.

Aggregation: abstracts the components of an object type into attributes.

B. Design of local Emurr model

The principle of local Emurr model design is that attributes must be inseparable data items, can no longer be composed of abandoning other attributes; attributes can not be related to other entities, the relationship can only occur between entities.

In order to simplify the Emurr diagram, anything that can be treated as an attribute should be treated as an attribute as far as possible.

The second step is the design of global Emurr model.

Integrate each local Emurr model to form a global model. There are two ways to integrate views:

A. Multivariate integration method: multiple local Emurr graphs are merged into one global Emurr graph at one time.

B. binary integration method: firstly, two important local Emurr graphs are integrated, and then a new Emurr graph is integrated step by step by accumulating method.

Merge:

Merge the local Emurr graph, eliminate the conflict, and preliminarily generate the Emurr graph. The key to merging is to reasonably eliminate the conflicts among local Emuri R graphs.

The categories of conflicts are as follows:

Optimization:

The unnecessary redundancy in the preliminary Emurr diagram is eliminated and the basic Emurr diagram is generated.

Redundant data: data that can be derived from basic data.

Redundant contact: a contact that can be derived from a basic contact.

3. Examples

Emurr Diagram of Teaching Management system

Entities: students, majors, colleges, courses

Attributes to be recorded by the entity table:

Students (student number, name, gender, birthday, place of origin, nationality, resume, date of admission)

Major (professional number, professional name, category)

College (college number, college name, dean)

Course (course number, course name, credits)

ER diagram of teaching management:

Fourth, logical structure design 1. Brief introduction of logical structure design

The task of logical structure design is to transform the entity model completed in the conceptual structure design phase into the data model supported by a specific DBMS. The purpose of logical structure design is to transform the entities, attributes and relationships in the Emurr diagram into relational schemas.

2. The design of initial relation model.

(1) the principles to be followed in the transformation of relations between entities:

An entity is transformed into a relational schema, the attribute of the entity is the attribute of the relationship, and the key of the entity is the key of the relationship.

A relationship is converted into a relationship schema, and the keys of each entity connected to the connection and the attributes of the connection are converted into the attributes of the relationship.

There are three situations in which the keys of a contact relationship are:

If the contact is 1:1, the key of each entity is a candidate key for the relationship

If the connection is the key of the insight relationship of the entity at the end of 1Plul

If the association is nRom, then the combination of the keys of each entity is the key of the relationship

Special circumstances: multiple connections

When a multivariate relation is transformed into a relational model, the primary key of each entity connected to the multivariate relation and the attribute of the connection itself are converted into the attribute of the relationship, and the primary key of the relationship obtained after the transformation is the combination of the entity keys.

(2) the transformation rules of the relationship between entities:

A 1:1 relationship can be transformed into an independent relationship schema, or it can be merged with the corresponding relationship schema at either end.

The corresponding models of the original entities are as follows:

Class (class number, major, number)

Monitor (student number, name, specialty)

After merging the relationship "management" into the corresponding model of the entity "class" is:

Class (class number, major, number of students, monitor student number)

Monitor (student number, name, specialty)

Relationship "management" can also be merged into the corresponding mode of entity "monitor". After merging relationship "management" into the corresponding mode of entity "class", it is as follows:

Class (class number, major, number)

Monitor (student number, name, specialty, class number)

B, a 1Rom-n relationship can be transformed into an independent relation pattern, or it can be merged with the corresponding relation pattern of the n-end.

Relationship pattern corresponding to entity

Department (Department number, Department name, Dean, telephone)

Teacher (teacher number, name, major, professional title, gender, age)

Relation pattern corresponding to relation

Management (teacher number, department number)

After merging into the entity "teacher" (a relational model that can only be merged into the "many" side):

Teacher (teacher number, name, major, professional title, gender, age, department number)

C, an mpurn relationship is transformed into a relational schema. The method of transformation is as follows: the codes of each entity connected to the relationship and the attributes of the relationship itself are converted into the attributes of the relationship, and the code of the new relationship is a combination of two connected entity codes.

A relationship can only be transformed into an independent schema, and the attributes of the pattern are composed of the attributes of the relationship itself and the keys of two entities; the primary key is composed of the keys of the entities at both ends.

Physical list of courses (course number, course name, class hours, category)

Student (student number, name, gender, major, date of birth, photo) physical table

Elective (course number, course number, score) relation table

D, the multiple relationships between three or more entities are transformed into a relational model.

The attribute of the relationship: the code of each entity connected to the multivariate relationship and the attribute of the relationship itself

Relational codes: the combination of entity codes

The "lecture" relationship is a ternary relationship, which can be transformed into the following relationship model, in which the course number, employee number and book number are the combined codes of the relationship:

Lecture (course number, employee number, book number)

3. Standardization of relational model.

The paradigm theory of database design is applied to optimize the initial relational model. The three paradigms of database design are as follows:

In the first paradigm, each category must be an inseparable data item. Properties are not divisible to ensure the atomicity of each column.

The second paradigm requires each table to describe only one thing, and each record has a unique identity column.

The third paradigm database table does not contain non-primary keyword information that has been included in other tables.

The normalization process of the relational schema is as follows:

A. determine the level of paradigm

Examine the functional dependency of the relational schema and determine the paradigm level.

B. implement standardized processing

Normalization method and theory are used to standardize the relational model.

C, mode improvement

Merge:

Merging tables with the same primary key for associative queries can improve query efficiency

Decompose:

Horizontal decomposition divides the tuples of the relationship into several subsets to improve the query efficiency; vertical decomposition decomposes the attributes that are often used together in the relationship to form a sub-relationship to improve the execution efficiency. Keep lossless connections and functional dependencies when decomposing.

4. Examples

Teaching management system

The relational model transformed from the ER model:

Physical table of students (student number, name, gender, birthday, place of origin, nationality, date of admission, professional number)

Professional (professional number, professional name, category, college number) entity table

College (college number, college name, dean) entity table

Physical list of courses (course number, course name, credits, college number)

Grade sheet (student number, course number, grade) relation table

When converted to a relational model, one-to-many relationships add a foreign key to the corresponding multi-entity relationship.

Increased demand:

If the teaching management system also manages teachers' teaching arrangements, including teachers' numbers, names, ages and professional titles, a teacher can only belong to one college, a teacher can take several courses, and a course can be attended by multiple teachers. each teacher has a class number and class hours for each course.

ER diagram of the instructor entity:

ER diagram of teaching management system:

Relational table many-to-many

Transcript (student number, course number, grade, time, place)

Physical structure design 1. A brief introduction to physical structure design

Physical structure design: for a given logical data model, select a physical structure that is most suitable for the application environment. The physical structure design of the 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 physical design measures that depend on the specific computer structure, such as a variety of storage structures and access methods provided by the specific database management system, the most appropriate physical storage structure (data type index primary key) is selected for specific application tasks.

2. Determine the physical structure

(1) the design of storage structure.

In the physical structure, the basic access unit of data is to store records.

The collection of all stored records of a certain type is called a file.

When determining the database storage structure, we should comprehensively consider three factors: access time, storage space utilization and maintenance cost. For example, eliminating all redundant data can save storage space, but it often leads to an increase in the cost of retrieval, so it is necessary to make a tradeoff and choose a compromise.

(2) Design of data access path

In a relational database, choosing an access path mainly refers to determining how to build an index. For example, which fields should be used as secondary codes to establish a secondary index, a single code index or a combined index, how many are appropriate, whether to build a clustered index, and so on.

(3) the design of data storage location.

In order to improve the performance, the variable part, the stable part, the frequently accessed part and the lower frequency part of the data can be stored separately.

(4) the design of system configuration

DBMS products generally provide some storage allocation parameters for designers and DBA to physically optimize the database. In the initial case, the system gives reasonable default values to these variables, but these values are not necessarily suitable for every application environment. In physical design, it is necessary to re-assign these variables to improve the performance of the system.

3. Evaluate the physical structure.

In the process of physical structure design, time efficiency, space efficiency, maintenance cost and various user requirements need to be weighed, and the results can produce a variety of schemes, and database designers must carefully evaluate the scheme. choose a better scheme as the physical structure of the database.

The method of evaluating the physical database completely depends on the selected DBMS, which mainly starts with the quantitative estimation of the storage space, access time and maintenance cost of various schemes, weighs and compares the estimated results, and selects a better and reasonable physical structure.

4. Examples

Teaching management system

Table 1-1 College

Table 1-2 majors

Table 1-3 students

Table 1-4 courses

Table 1-5 transcripts

Database implementation 1. The process of database implementation

Database implementation: the process of establishing the actual database structure, loading data, testing and trial operation on the computer according to the results of logical design and physical design.

2. Examples

Student form:

Course schedule:

Professional table:

College table:

Transcript:

VII. Database operation and maintenance

The main tasks of 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

As long as the database system is running, it needs to be constantly modified, adjusted and maintained.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.