Electronic information system

Curriculum design report

Course: Database Course Design

Topic: Student Management System

Class: 09 Count

Members:

Instructor:

Date:

catalogue

Preface 3 of the first chapter

1. 1 Project Profile 3

1.2 Design Purpose 3

1.3 demand analysis 4

Chapter 2 Analysis and Application of Database Examples 4

2. 1 Theme and E-R Figure 4

2.2 Implementation of Database 5

2.3 Database Structure Attribute 8

2.3. 1 primary key (primary key constrains primary key; Index Settings) 8

2.3.2 Default values and rules of the database 13

2.3.3 Views and stored procedures 15

Trigger 17

Chapter III Summary Report 19

Reference 19

The first chapter preface

1. 1 project introduction

Database technology is one of the fastest developing and most widely used technologies in computer science and technology. It has been widely used in computer design, artificial intelligence, e-commerce, enterprise management, scientific calculation and many other fields, and has become the core technology and important foundation of computer information systems and applications.

This paper mainly introduces the database design of student achievement management system, and describes in detail the operation and maintenance of the database from demand analysis. The system is developed using SQL. The establishment of student achievement management system through SQL greatly facilitates and simplifies the query and processing of data. Administrators can add, delete, modify and query the data in the table through SQL language, and can also establish multiple users to allocate and reclaim their right to use. With the continuous progress of data processing and the rapid development of computer network, the database application system has undergone profound changes not only in function but also in structure, and has been applied to all aspects of life. By studying relational algebra, relational calculus, functional dependence, relational schema decomposition and relational schema standardization, we have established a solid theoretical foundation of relational database. On the basis of mastering the basic theory, it is particularly necessary to master the design method of relational database and the development method of modern information system. At present, SQL database is the most widely used relational database, and most of the language tools for developing databases are C++. Therefore, it is necessary for computer students to master the basic technology of database application and be familiar with the combination of programming language and SQL database. This course design is based on the student information management system as the simulation model, using C++ programming language combined with SQL database to develop the system.

1.2 design purpose

With the increasing number of students, the management requirements of the school for students are getting higher and higher. In order to better combine information technology with students' information and make students' achievement management more systematic and digital, we designed a student information management system. Guided by the database design method based on E-R model and the theory of relationship normalization, the analysis, design and final realization of the system are completed, and the student achievement management system is developed to complete all the functions of the student achievement management system. First, do a good job of demand analysis and complete the data flow diagram. Secondly, do conceptual analysis. Using the method of entity connection, the user requirements of requirements analysis are abstracted into an information structure, and an E-R diagram is obtained. Then, the logical structure is designed and the E-R diagram is transformed into a logical model supported by the computer system. Finally, a concrete example is completed in SQL language.

1.3 demand analysis

1. Question: In order to manage students efficiently, we decided to develop a student management system.

2. Functions to be completed:

(1) You can input, modify, query and output student file information, including student grades, courses and personal information.

(2) The establishment and use of triggers, indexes, constraints, rules, default values, views and stored procedures.

Chapter II Analysis and Application of Database Examples

2. 1 theme and E-R diagram

With the increasing number of students, the management requirements of the school for students are getting higher and higher. In order to better combine information technology with students' information and make students' achievement management more systematic and digital, we designed a student information management system. The following is the E-R diagram of middle school students' information management system, which further explains the structure and usage of the database in detail. Definition of entities and attributes:

List of students (student number, name, class number)

Class table (class number, class name, department number)

Department table (department number, department name)

List of teachers (teacher name, course number, department number)

Course schedule (course number, course name, credit, teacher, department number)

The following is an E-R diagram to further illustrate the functions and uses of the database:

2.2 the realization of the database

The physical structure of designing tables using SQL Server 2000 data is as follows:

Category table:

Student table:

Department table:

Course arrangement:

List of teachers:

Chart of each table:

The specific data to be filled in the design form is:

Category table:

Student table:

List of teachers:

Department table:

Course arrangement:

2.3 database structure attributes

2.3. 1 primary key (primary key constrains primary key; Index settings)

The 1. index is similar to a book catalog, which can quickly find the specified content. The index points to the records in the table through the key values in the record table, so that the database can locate the related records without scanning. The following is an implementation of indexing each table.

The student table is set as shown in the figure below:

The category table is designed as follows:

The teacher table is designed as follows:

The course design is as follows:

The department table is set as follows:

2. Constraints define the rules about which data is allowed to enter the database, which is an attribute assigned to a table or a column in a table. The purpose of using constraints is to prevent illegal data from appearing in columns and automatically maintain the data integrity of the database. The following are primary key constraints for class tables using enterprise manager:

2.3.2 Default values and rules of the database

1. Using default can realize that when a user inserts a new record in the data table, SQL Server will automatically enter a default value for a column if no input value is given. The following are the default functions to implement the category table:

Implement default values:

2. Rules are also one of the methods to realize data integrity, similar to CHECK constraints. When inserting or updating data into columns of a table, they are used to limit the range of input values. Let's use the curriculum to implement the rules:

Views and stored procedures

The 1. view is equivalent to a virtual table, which is a way for users to view the data in the database table, so that users can browse some or all of the data in the table in the required way, while the physical storage location of the data is still in the database table. By creating view management view and application view in enterprise manager, the function of view is explained more vividly and concretely.

Add a table to the view:

Add data and run:

Running results, specific view demonstration:

2. A stored procedure is a set of Transact-SQL statements compiled in a single execution plan, which concentrates some fixed operations and hands them over to the SQL-Server database server to complete a task. First, we create a stored procedure in the query manager:

And execute the stored procedure:

Stored procedures can also be included in Enterprise Manager:

trigger

Triggers are used to enforce business rules. SQL Server mainly provides two mechanisms to enforce business rules and data integrity: constraints and triggers. When the data in the specified table changes, triggers are called in response to insert, update or delete events. Triggers can query other tables and can contain complex statements. SQL Server treats the trigger and the statement that triggered it as a single thing that can be rolled back in the trigger. If a serious error is detected, the whole process will be automatically rolled back. First, we create a new trigger in the query manager:

New trigger:

Manage triggers:

Chapter III Summary Report

This course design is really difficult to do. I am deeply aware that making a software requires a lot of knowledge that we have not been exposed to. When we went to the library to look for books, we found that we had only learned the superficial knowledge, and there were still many things for us to explore. Even if we borrow a book to read, we will still find that there is still a lot of knowledge that we don't fully understand. This requires us to keep practicing, self-learning and discovering and thinking about problems.

After continuous testing and continuous improvement, many problems have been found. For the first time, I have no experience or even a way to start. I am still very grateful to my teachers and classmates for their help, and I have also learned some coding methods. Why do I write like this? Through discussion with my classmates, I found some methods that are not available in books, such as how to bind data, how to extract data from the database and put it in text boxes or labels. These things are what make up the interface. Although small, it can reflect the level of the whole software. In fact, there is no need to build many database tables, write many complicated stored procedures, and use database functions and triggers. But at least you have to understand that if you operate these things, you can clearly distinguish the functions.

After a period of research and practice, the student information management system has been basically developed. The system has the functions of adding, modifying, deleting, browsing, querying and outputting schedule information, and realizes the function of viewing the schedule according to the needs of users. As a personal schedule management system, the functions provided by this system are really too few, only some basic functions have been realized, and there are still many places that need to be expanded and improved.

People can't do things well without self-confidence, goals and confidence. When others are confused, we must be firm in our goals. After graduating from college, they are faced with finding jobs. From studying this major to engaging in this job in the future, they need constant study and practice. This practice can give us a wake-up call. We need these practical experiences when we are looking for a job after graduation. This is the biggest gain for me from this course design. Thank teachers for their hard guidance and education!

refer to

A concise course of SQL Server2000 edited by Huang Weitong.

Xu Renfeng and Zeng Jianhua edited SQL Server2000 Database and Application.