The following contents are for reference only. If there are errors, please correct them.
Design requirements
Realize the management of student information, classes, departments and majors;
Realize the management of course and student achievement information;
Realize the information management of students' rewards and punishments;
Create rules to restrict gender items. Only "male" or "female" can be entered;
Create a view to query the student number, name, class, major and Department of each student;
Create a stored procedure to query the transcript of the specified student;
Create a trigger to automatically modify the number of students in the corresponding class when adding, deleting students and modifying student class information;
Establish referential integrity constraints between database related tables.
catalogue
1.1 introduction to project demand analysis
2 database conceptual structure design
3 database logical structure design
4 database physical structure design
4.2 create data table (and create relationship integrity)
8 implementation and maintenance of database
Demand analysis
Introduction to project demand analysis
The users of this database are mainly students. Through the collection and analysis of user needs, the following requirements of users for the database are obtained.
(1) Student information: including student number, name, gender, date of birth, native place, etc;
(2) Class information: including class number, counselor name, etc;
(3) Professional information: including professional number, professional name, etc;
(4) Department information: including department number, department name, etc;
(5) College information: including college name, President's name, etc;
(6) Course information: including course number, course name, lecturer, etc;
(7) Reward and punishment information: including reward and punishment record number, reward and punishment level, reward and punishment record time, reward and punishment details, etc.
2. Processing requirements
College student status management system is mainly composed of student information management, course selection and performance management, student reward and punishment information management and other modules.
Student information module: including students' basic information and their classes, majors, departments and colleges;
Course selection and score management module: including course number, course name, score, etc;
Student reward and punishment information management module: including reward and punishment record number, reward and punishment level, reward and punishment record time, reward and punishment details, etc.
In order to ensure the security of information and the authority of data, the use authority of information is specified as follows:
| Student status information | Student course selection information | Student achievement information | Course information | Student reward and punishment information | |
| student | a | ab | a | a | a |
| instructor | ab | a | a | ab | ab |
| Instructor | a | a | ab | a | a |
Note: a. query b. modify
3. Operation requirements:
The language of personal information management system is python, which is completed with SQL Sever 2008 R2 database. It has the following functions:
Good authority management;
The modification of original data is simple and convenient, and supports multi condition modification;
Convenient data query and support multi condition query.
Data dictionary
Data item:
1. Data item: student number
Data item meaning: uniquely identifies each student
Data type: character type
Length: 8
Value range: 00000000 ~ 9999999
2. Data item: name
Data item meaning: Student Name & & Class counselor name & & college president name & & course teacher name
Data type: character type
Length: 20
Value range: all Chinese characters
3. Data item: Gender
Data item meaning: student gender
Data type: character type
Length: 2
4. Data item: Date
Data item meaning: Student birth date & & reward record date & & punishment record date
Data type: date
5. Data item: Location
Data item meaning: Student native place information
Data type: character type
Length: 50
Value range: all Chinese characters
6. Data item: No
Meaning of data item: class number & & major number & & department number & & course number
Data type: character type
Length: 4
Value range: 0000 ~ 9999
7. Data item: name
Data item meaning: College name & & major name & & Department Name & & course name
Data type: character type
Length: 50
Value range: all Chinese characters
8. Data item: record number
Data item meaning: reward record No. & & punishment record No
Data type: character type
Length: 8
Value range: 00000000 ~ 9999999
9. Data item: Grade
Data item meaning: reward level & & punishment level
Data type: integer
10. Data item: record number
Data item meaning: reward record & & punishment record
Data type: character type
Length: 1000
Value range: all Chinese characters
11. Data item: number of people
Data item meaning: class number
Data type: integer
data structure
1. Data structure: Student
Meaning Description: it is the main data structure of university student status management system, which defines the relevant information of a student.
Composition: student number, name, gender, date of birth, native place
2. Data structure: Class
Meaning Description: it is the main data structure of college student status management system and defines the information about classes.
Composition: class number, counselor name, class number
3. Data structure: Major
Meaning Description: it is the main data structure of university student status management system, which defines the information of relevant majors.
Composition: Discipline number and discipline name
4. Data structure: Faculty
Meaning Description: it is the main data structure of university student status management system and defines the information about the Department.
Composition: department number and department name
5. Data structure: College
Meaning Description: it is the main data structure of university student status management system and defines the information about the college.
Composition: College name, President name
6. Data structure: Course
Meaning Description: it is the main data structure of university student status management system and defines the information about courses.
Composition: course number, course name and lecturer
7. Data structure: Course Selection
Meaning Description: it is the main data structure of university student status management system and defines the information about course selection.
Composition: student number, course number and grade
8. Data structure: reward
Meaning Description: it is the main data structure of university student status management system and defines the information about rewards.
Composition: reward record number, reward level, reward record time and reward detail description
9. Data structure: penalty
Meaning Description: it is the main data structure of university student status management system and defines the information about punishment.
Composition: punishment record number, punishment level, punishment record time and punishment detail description
Database conceptual structure design
Data flow chart
The flow chart of university student status management system is shown in Figure 2-1.
Figure 2-1 flow chart
System E-R diagram
In the database conceptual structure design stage, complete the E-R diagram design, as shown in Figure 2-2.
Figure 2-2 general E-R diagram of university student status management system
Database logical structure design
1. Relational model of
Student: (student number, name, gender, date of birth, native place, class number, college name)
Class: (class number, counselor's name, class number, major number)
Major: (major number, major name, department number)
Department: (department number, department name, college name)
College: (College name, President's name)
Course: (course number, course name, instructor)
Course selection: (student number, course number, grade)
Reward: (reward record number, student number, reward level, reward record time, reward details)
Punishment: (punishment record number, student number, punishment level, punishment record time, punishment detail description)
2. University student status management system database
(1) Student number is the primary key of students. Each student has a unique student number. The information of each student includes student number, name, gender, date of birth and native place. A student can have multiple reward and punishment records at the same time. Each reward and punishment record number corresponds to only one reward and punishment record of a student.
(2) A student can choose multiple courses, and a course can also be selected by multiple students. The course selection relationship "course selection" is used as the connection relationship, and its main code is the combination of student number and course number.
(3) the corresponding relationship between the agreed value of reward level and reward category is as follows:
1. National Scholarship
2. Professional first-class scholarship
3. Professional second-class scholarship
4 other scholarships
The corresponding relationship between the agreed value of punishment level and punishment category is as follows:
1. Withdrawal punishment
2. Punishment of major demerit recording
3. Severe warning
4. Warning
3. Nine two-dimensional tables of the database
The student table is used to record the basic information of students. The structure of the student table is shown in table 3-1.
Table 3-1 student list
| Field name | data type | Special properties | describe |
| Student number | Char(8) | Primary key | Student number |
| full name | Varchar(20) | Non empty | full name |
| Gender | Char(2) | Male / female | Gender |
| date of birth | date | Non empty | date |
| Native place | Varchar(50) | Non empty | place |
| Class number | Char(4) | Refer to class table | number |
| School name | Varchar(50) | Refer to college table | name |
The class table is used to record the basic information of the class. The structure of the class table is shown in table 3-2.
Table 3-2 class table
| Field name | data type | Special properties | describe |
| Class number | Char(4) | Primary key | number |
| Counselor name | Varchar(20) | Non empty | full name |
| Class size | Int | Non empty | Number of people |
| Discipline number | Char(4) | Refer to professional table | number |
The specialty table is used to record the basic information of the specialty. The structure of the specialty table is shown in table 3-3.
Table 3-3 discipline
| Field name | data type | Special properties | describe |
| Discipline number | Char(4) | Primary key | number |
| Professional name | Varchar(50) | Non empty | name |
| Department No | Char(4) | Refer to department table | number |
The Department table is used to record the basic information of the Department, as shown in table 3-4.
Table 3-4 department table
| Field name | data type | Special properties | describe |
| Department No | Char(4) | Primary key | number |
| Department name | Varchar(50) | Non empty | name |
| College name | Varchar(50) | Refer to college table | name |
The college table is used to record the basic information of the college, as shown in table 3-5.
Table 3-6 list of Colleges
| Field name | data type | Special properties | describe |
| College name | Varchar(50) | Primary key | name |
| Principal's name | Varchar(20) | Non empty | full name |
The course schedule records the basic information of the course. The structure of the course schedule is shown in table 3-6.
Table 3-6 course schedule
| Field name | data type | Special properties | describe |
| Course number | Char(4) | Primary key | number |
| Course name | Varchar(50) | Non empty | name |
| Instructor | Varchar(20) | Non empty | full name |
The course selection table records students' course selection and their scores. The structure of the course selection table is shown in table 3-7.
Table 3-7 course selection schedule
| Field name | data type | Special properties | describe |
| Student number | Char(4) | Primary key | Student number |
| Course number | Char(4) | Primary key | number |
| fraction | Int | Non empty | fraction |
The reward table records the reward information of students. The structure of the reward table is shown in table 3-8.
Table 3-8 reward table
| Field name | data type | Special properties | describe |
| Reward record No | Char(8) | Primary key | Record number |
| Student number | Char(8) | Refer to student table | Student number |
| Reward level | Int | Non empty | Grade |
| Reward time | date | Non empty | time |
| Reward details | Varchar(1000) | nothing | record |
The punishment table records the punishment information of students. The structure of the punishment table is shown in table 3-9.
Table 3-9 penalty table
| Table name | Punishment (penalty table) | ||
| Field name | data type | Special properties | describe |
| Penalty record number | Char(8) | Primary key | Record number |
| Student number | Char(8) | Refer to student table | Student number |
| Penalty level | Int | Non empty | Grade |
| Penalty time | date | Non empty | time |
| Penalty details | Varchar(1000) | nothing | record |
Database physical structure design
Create database
CREATE DATABASE school_manage;
Create data table (also create relationship integrity)
(1) Student list
CREATE TABLE Student list ( Student number CHAR(8) PRIMARY KEY, full name VARCHAR(20) NOT NULL, Gender CHAR(2) DEFAULT 'male' , date of birth DATE NOT NULL, Native place VARCHAR(50) NOT NULL, Class number CHAR(4) REFERENCES Class table(Class number), School name VARCHAR(50) REFERENCES College table(College name), FOREIFN KEY(Class number) REFERENCES Class table(Class number) ON DELETE SET NULL ON UPDATE CASCADE, FOREIFN KEY(School name) REFERENCES college(School name) ON DELETE SET NULL ON UPDATE CASCADE, CHECK((Gender='male') OR (Gender='female')) );
(2) Class table
CREATE TABLE Class table ( Class number CHAR(4) PRIMARY KEY, Counselor name VARCHAR(20) NOT NULL, Class size INT NOT NULL, Discipline number CHAR(4) REFERENCES Professional table(Discipline number), FOREIGN KEY(Discipline number) REFERENCES Professional table(Discipline number) ON UPDATE CASCADE ON DELETE SET NULL );
(3) Professional table
CREATE TABLE Professional table ( Discipline number CHAR(4) PRIMARY KEY, Professional name VARCHAR(50) NOT NULL, Department No CHAR(4) REFERENCES Department table(Discipline number), FOREIGN KEY(Department No) REFERENCES Department table(Department No) ON UPDATE CASCADE ON DELETE SET NULL );
(4) Department table
CREATE TABLE Department table ( Department No CHAR(4) PRIMARY KEY, Department name VARCHAR(50) NOT NULL, College name VARCHAR(50) REFERENCES College table(College name), FOREIGN KEY(College name) REFERENCES College table(College name) ON UPDATE CASCADE ON DELETE SET NULL );
(5) College table
CREATE TABLE College table ( College name CHAR(4) PRIMARY KEY, Principal's name VARCHAR(20) NOT NULL );
(6) Curriculum
CREATE TABLE Class Schedule Card ( Course number CHAR(4) PRIMARY KEY, Course name VARCHAR(50) NOT NULL, Instructor VARCHAR(20) NOT NULL );
(7) Course selection table
CREATE TABLE StudentCourse ( Student number CHAR(8) REFERENCES Student list(Student number), Course number CHAR(4) REFERENCES Class Schedule Card(Course number), fraction INT NOT NULL, PRIMARY KEY(Student number,Course number), FOREIGN KEY(Student number) REFERENCES Student list(Student number) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY(Course number) REFERENCES Class Schedule Card(Course number) ON UPDATE CASCADE ON DELETE CASCADE );
(8) Reward form
CREATE TABLE Reward form ( Reward record No CHAR(8) PRIMARY KEY, Student number CHAR(8) REFERENCES Student list(Student number), Reward level INT NOT NULL, Reward time DATE NOT NULL, Reward details VARCHAR(1000), CHECK ((Reward level = 1)OR(Reward level = 2)OR(Reward level = 3)OR(Reward level = 4)OR(Reward level = 5)), FOREIGN KEY(Student number) REFERENCES Student list(Student number) ON UPDATE CASCADE ON DELETE SET NULL );
(9) Penalty table
CREATE TABLE Penalty table ( Penalty record number CHAR(8) PRIMARY KEY, Student number CHAR(8) REFERENCES Student list(Student number), Penalty level INT NOT NULL, Penalty time DATE NOT NULL, Penalty details VARCHAR(1000), CHECK ((Penalty level = 1)OR(Penalty level = 2)OR(Penalty level = 3)OR(Penalty level = 4)OR(Penalty level = 5)), FOREIGN KEY(Student number) REFERENCES Student list(Student number) ON UPDATE CASCADE ON DELETE SET NULL );
view
Create a view to query the student number, name, class, major and Department of each student:
(1) Create view:
CREATE VIEW v_student_chk(Student number,full name,class,major,Faculty) AS SELECT Student number,full name,Student list.Class number,Professional name,Department name FROM Student list,Professional table,Department table,Class table WHERE Student list.Class number = Class table.Class number AND Class table.Discipline number = Professional table.Discipline number AND Professional table.Department No = Department table.Department No ORDER BY Student number;
(2) View view:
Figure 5-1 view query table
stored procedure
Create a stored procedure to query the transcript of the specified student:
(1) Create stored procedure:
DELIMITER@@ USE school_manage@@ DROP PROCEDURE IF EXISTS student_p@@ CREATE PROCEDURE student_p(IN p_id VARCHAR(8)) BEGIN SELECT Student list.Student number,Student name,Course name,fraction FROM Student list,Class Schedule Card,StudentCourse WHERE Student list.Student number = StudentCourse .Student number AND Class Schedule Card.Course number = StudentCourse .Course number AND Student list.Student number = p_id ORDER BY Student number; END@@ DELIMITER；
(2) Query statement:
CALL student_p(20020701);
Figure 6-1 stored procedure query table
trigger
Create a trigger to automatically modify the number of students in the corresponding class when adding, deleting students and modifying student class information:
Student table insert
(1) Create:
CREATE TRIGGER c_trigger AFTER INSERT ON Student list FOR EACH ROW UPDATE Class table SET Class size = Class size + 1 WHERE Class number = new.Class number;
Student table deletion
(1) Create:
CREATE TRIGGER s_trigger AFTER DELETE ON Student list FOR EACH ROW UPDATE Class table SET Class size = Class size - 1 WHERE Class number = old.Class number;
Student form update
(1) Create:
CREATE TRIGGER u_trigger AFTER UPDATE ON Student list FOR EACH ROW UPDATE Class table SET Class size = Class size + 1 WHERE Class number = new.Class number; CREATE TRIGGER z_trigger AFTER UPDATE ON Student list FOR EACH ROW UPDATE Class table SET Class size = Class size - 1 WHERE Class number = old.Class number;
Implementation and maintenance of database
Loading of data
Loading of data
(1) Student information sheet
The student table information is shown in Figure 8-1.
Figure 8-1 data loaded in student table
The rest are omitted.
Database backup
Use the mysqldump command to back up data. The file is backups_sql.sql.
summary
Through this course design, I have more solid knowledge about database. Although I encountered some problems in the design process, after continuous thinking and inspection, I finally found out the reason, and exposed my lack of knowledge and experience in this field in the early stage. This course design makes me learn a lot of knowledge that I haven't learned in class. It also makes me more proficient in common drawing software, document editing and other knowledge. Although the course design has been completed, there are still some deficiencies in the system, such as the system is very simple and the security problem has not been well solved. Therefore, in the future study, I will continue to strive to improve myself. The help of my classmates, the attentive guidance of my instructors and the careful arrangement of the school will make this topic go smoothly. However, due to the lack of time and my limited level, there are still many imperfections in this system. I hope it can be continuously optimized in the future study.
:
[1] Li Yuejun, Fu Liangting, database principle and application [M] Beijing: Tsinghua University Press, October 2019