General steps and examples of designing a database-Mysql Tutorial-php.cn

MySQL will definitely be used in current program development. MySQL is an open source small relational database management system. The developer is the Swedish MySQL AB company. MySQL is currently widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership. In this article, we will talk about the general steps and examples of designing a database.

1. General process of database design

1. Overview

Including course design topic selection, project background, purpose of writing the course design report, organization of the course design report, etc. .

2. Requirements analysis of course design tasks

2.1 Design tasks

2.2 Design requirements

2.3 Specification documents for requirement description

3. Conceptual structure design

3.1 Conceptual structure design tool (E-R model)

3.2XXX subsystem (partial)

3.2.1 Subsystem description

3.2.2 Points E-R Diagram

3.2.3 Description

3.3YYY Subsystem

3.3.1 Subsystem Description

3.3.2 Points E-R Figure

3.3.3 Description

……

3.X Overall E-R diagram

3.X.1 Integration of E-R diagram

3.X.2 Overall E-R diagram

4. Logical structure design

4.1 Relational data model

4.2 View design

4.3 Optimization

5. Database physical design and implementation

5.1 Introduction to the hardware and software environment of database application

5.2 Physical structure design

5.3 Index design

5.4 Establish database

5.5 Load database test data

6. Data operation requirements and implementation

6.1 Data query operation

6.2 Data update operation

6.3 Data maintenance operations

6.4 Others

7. Implementation of database application system

8. Design experience

9. References

2. Example: Student Course Selection Management System

In the teaching management system of colleges and universities, student course selection management is a very important functional module.

Requirement analysis:

The system should be able to manage the school’s teacher information, student information, professional information, all course information offered by the school, information on student elective courses, etc. The course selection system mainly meets the requirements of three types of users, namely system administrators, teachers and students of the Academic Affairs Office. The operation permissions and operation contents they have are different. The specific demand analysis is as follows:

System Administrator

Maintain students' basic personal information and realize the addition, deletion and modification of students' personal information.

Student information includes...

Maintain the basic personal information of teachers and realize the addition, deletion and modification of teachers' personal information.

Teacher information includes...

Maintain the basic personal information of the course and realize the addition, deletion and modification of the personal information of the course.

Course information includes...

Student users

Query and modify personal information.

Perform course selection operations

Students can view the course information they selected and the grade information of previously selected courses.

Teacher User

Query and modify personal information

After the course is over, register results

Teachers can check their teaching arrangements.

Database conceptual structure design:

The common tool for conceptual structure design is the ER diagram. Complete it in the following steps.

Data abstraction (abstract entities). Draw a diagram of the properties of each entity.

Design ER diagram. Find the entities and their connections and draw an ER diagram.

Merge the separate ER diagrams to generate a preliminary ER diagram.

Global ER diagram. Add the attributes of each entity to the preliminary ER diagram to eliminate possible local conflicts (including attribute conflicts, naming conflicts, and structural conflicts) to form a global ER diagram.

Database logical structure design:

First, convert the entities and relationships in the conceptual model ER diagram into a data model. In RDBMS, it is converted into a relational schema, and the attributes and attributes of the relational schema are determined. Master code.

The basic rules for converting ER diagrams to relational data models are as follows:

When an entity is converted into a relational schema, the attributes of the entity are the attributes of the relationship, and the keys of the entities are the keys of the relationship.

If the relationship between entities is 1:1, the relationship is not converted into a relationship model separately. The key of the other relationship model and the relationship need to be added to any one of the relationship models corresponding to the two entities. Attributes.

If the relationship between entities is 1:n, the relationship is not converted into a relationship schema separately. The key of the relationship schema corresponding to the 1-side entity and the attributes of the relationship need to be added to the relationship schema corresponding to the n-side entity. .

If the relationship between entities is m:n, the relationship is also converted into a relationship mode. Its attributes are the codes of the entity types at both ends plus the attributes of the relationship, and the keys of the relationship are the entities at both ends. combination of codes.

Secondly, perform the necessary merging of relationship patterns with the same primary key.

Relationship Optimization

Use the standardization theory as a guide to optimize the relational data model. Normalized to third normal form.

Physical design and implementation of database:

Mainly includes the following work:

Create database

Create basic tables, set constraints, and manage basic tables.

Create and manage indexes. (DBMS will automatically create an index for the primary key. Create an index to improve query efficiency.)

Create and manage views.

Enter data into the database.

Use SQL statements to perform operations such as data query, modification, and deletion. (You can think about how to manipulate the database first, and then implement it later)

Write stored procedures, triggers, etc., and pass debugging. (For example, create a trigger to automatically delete the course selection record in the course selection table when a student with a certain student number is deleted from the student table)

Database operation and maintenance:

Mainly includes the following content

Database backup and recovery

Database security and integrity control

Database performance monitoring, analysis and improvement

Access the database through The following statements manipulate data.

Add constraints to the gender and age fields of the student table.

Add an attribute column "Enrollment Time".

Modify the "professional" data type.

Add the constraint that the course name must have a unique value.

Create an index on the columns "student number" and "course number" in the course selection table.

Create an index on the "job number" and "course number" in the course selection table.

Assume that there are 4 departments in the student course selection system. In order to facilitate the teaching management personnel of each department to view the student information of the department, each department creates a student view.

Create a view that reflects students' course selection.

Students can check their course selection information by their student number or name.

Students can check their basic information through their student number or name.

List the transcripts of a certain course taught by a teacher, and display them in ascending or descending order.

List all course information taught by a teacher.

Query the credit information a student has obtained.

Statistics on the average score, highest score, and lowest score of a certain teacher's course.

Add a new course.

Modify the credits of a certain course.

Create a deletion trigger. When a student with a "student number" in the student table is deleted, the student's course selection record in the course selection table will be automatically deleted. That is, a deletion trigger will be created for the student table to achieve Cascade deletion of student table and course selection table.

Create a deletion trigger. When a course with a certain "course number" in the course schedule is deleted, all records for that course in the course selection list will be automatically deleted, that is, a deletion trigger will be created for the course schedule. Implement cascade deletion of student table and course selection table.

Create an insert trigger. When inserting a record into the course selection table, query whether there is a student with the student number in the student table, and whether there is a course with the course number in the course table. If there is a record, it can be inserted. Otherwise the insertion is refused.

Back up the "Student Course Selection System" database to the BACKUPDB folder under the E drive of the local disk.

sp_addumpdevice 'disk','Student Course Selection System_bak','E:\BACKUPDB\Student Course Selection System_bak'

BACKUP DATABASE Student Course Selection System TO DISK='Student Course Selection System_ bak'

Book borrowing management system

Requirements analysis:

The system should implement the following functions: librarians can maintain book information, including adding new books, modifying book information, and processing Book borrowing registration, return registration, expired book processing, lost book processing, and maintenance of reader borrowing certificate information, etc. Readers can borrow books, return books, check book information, borrow book information, etc. The specific requirements are as follows:

Book information management: enter each book information, maintain book information, etc.

Reader information management: Maintain reader information and modify, update, and delete readers according to actual needs

Borrowing management: including borrowing books, returning books, and returning expired books, etc.

The book lending management system mainly has two types of users.

Administrator: Maintain basic book data, including book types, update book information, and process readers' book borrowing and return.

Reader users: can check book information and borrow books.

Semantics: The book situation and management regulations of the library. Each type of book has many different books, and the same book can be read multiple times; each book can be borrowed multiple times, and each reader can borrow multiple times. This book. The borrowing period for each book is one month.

Database conceptual structure design:

Completed in 3 steps: 1) Abstract the entity 2) Abstract and connect with reality) Determine the attributes and keys of the entity

Book type, including : Book category number, book category name, description information.

Books, including: book number, book title, author, publisher, price.

Readers, including: ID number, name, gender, department, class, ID status (including valid and invalid), contact information, etc.

The relationship between readers and books is m:n, the relationship between book types and books is 1:n, and there is no connection between readers and book types.

Database logical structure design:

Convert the E-R diagram into a relational model.

Optimize the relational model

Normalize to the third normal form

Physical design and implementation of database:

Mainly include the following work:

Create database

Create basic tables, set constraints, and manage basic tables.

Create and manage indexes. (DBMS will automatically create an index for the primary key. Create an index to improve query efficiency.)

Create and manage views.

Enter data into the database.

Use SQL statements to perform operations such as data query, modification, and deletion. (You can think about how to manipulate the database first, and then implement it later)

Write stored procedures, triggers, etc., and pass debugging.

Manipulate the data in the database

In order to facilitate the administrator's classification management, it is now necessary to create views for multiple book types.

Create a reader borrowing status table.

Create a combined sub-index for the document number and borrowing date in the borrowing table.

Create indexes for the book classification number, book title, and publisher fields in the book table.

Create an insertion trigger for the borrowing table to ensure that the "certificate number" inserted into the borrowing table exists in the reader table. If it does not exist, the borrowing information record will not be inserted into the borrowing table.

Create an update trigger in the borrowing table and monitor the "Borrowing Date" column of the borrowing table so that it cannot be modified manually.

Create a delete trigger in the reader table to implement cascade deletion of the reader table and borrowing table.

Sports Games Management System

Requirements Analysis:

Introducing computers to manage sports games. Athletes can check their own competition events and results, referees or staff in the sports meeting management system. The system can record athletes' results in various competitions and perform performance statistics, etc. The sports meeting management system saves the participation data of athletes, competition events and different types of competitions.

The main management objects of the sports meeting management system include representative teams, athletes, competition events, etc.

In this system, the basic regulations are as follows: a representative team contains multiple athletes, and an athlete can only belong to one representative team; an athlete can participate in multiple competition events, and a competition event can be participated by multiple athletes; Assume that after each competition event is completed, the rankings will be ranked from high to low, and the top three athletes will be given corresponding points. For example, the points for the 1st, 2nd, and 3rd place will be 3, 2, and 1 points respectively, and other rankings will be given. The athlete's points are zero points. The points of all members of the representative team are accumulated to obtain the total points of each representative team. The total points are sorted from high to low to obtain the ranking of each representative team.

In order to simplify the system requirements, this sports meeting management system only considers the management of track and field games, only considers the relationship between representative teams, competition events, and does not consider the arrangement of referee scores, competition time, location, etc.

Database conceptual structure design:

Representative team: representative player, team name, team leader, total points, total ranking

Athlete: athlete number, name, gender, age

Competition items: project number, project category, project name, performance unit.

Database logical structure design:

4 tables,

competition details, including results, points, and rankings

The above are the general steps for designing a database and With all the examples, I believe you already have a general idea of how to design a database.