1. System analysis

Demand function analysis

The task of demand analysis is to investigate the application field, analyze the information requirements and operational requirements of the application field in detail, and form a demand analysis manual. The focus is on investigating, collecting and analyzing the user's information requirements, processing requirements, data security and integrity requirements in data management.

In order to complete the task of requirements analysis, the database application department to be developed should be investigated in detail, the work overview of the original system should be understood, the various needs of users should be analyzed, and the functions of the new system should be determined on this basis. The new system must consider future extensions and changes, not just design the database based on current application requirements.

The curriculum arrangement management system needs to manage the information of school teachers, classrooms, classes, and courses, and to manage the curriculum arrangements of colleges and universities. It mainly realizes the following functions：

Enter various class information, including class number, class name, and class size.

Enter various teacher information, including teacher number, teacher name, title, and gender.

Add, delete, and modify school curricula.

Develop a school curriculum plan.

Test whether designated teachers and designated programs have classes；

Generate the class schedule of the specified class；

Generate the class schedule of the designated teacher；

Generate the class schedule of the specified student；

 

 

2. System database design

1． Conceptual design

The class scheduling management system involves four entities: students, classes, teachers, and courses.

The class has a one-to-many relationship with the students；

Many-to-many classes and courses；

Classes and teachers are many to many；

Curriculum and teachers are a many-to-many relationship；

Figure 1 Student entity E-R diagram Figure 2 Class entity E-R diagram Figure 3 Teacher entity E-R diagram Figure 4 Course entity E-R diagram Figure 5 E-R Students have four attributes: student ID, name, gender, and class ID. The student ID is the primary key, and the class ID is the foreign key. Teachers have four attributes: ID, name, gender, and age. The teacher ID is the primary key. The class has two attributes: ID and class name. The class ID is the primary key. The course has two attributes: the course ID and the course name. The course ID is the primary key. There is 1 contact, and the contact can also be converted into an entity. Therefore, class scheduling produces a class schedule. The course has six attributes: class ID, teacher ID, course ID, week, festival, and remarks. The time of week is the main key, and the class ID, teacher ID, and course ID are the foreign keys.

2． Logic design

Write the relational model of the above-mentioned entities to realize the E-R transformation graph to relational model.

3． Physical design

Transform the above entities into relationships.

3. Implement the system database

1. Create databases, tables, views, stored procedures, functions, and trigger SQL statements

–Create a database

create database lesson

go

use lesson

go

–Create class table

create table class

(

classID int pri ** ry key,

classname char(20)

)

Go

–Create student table

create table student

(

studentID int pri ** ry key,

name char(10) not null,

**     char(2)  not null check( ** =’F’ or  ** =’M’),

classID int references class(classID)

)

Go

–Create a teacher table

create table teacher

(

teacherID int pri ** ry key,

name char(10) not null,

**     char(2)  not null check( ** =’F’ or  ** =’M’),

age int not null

)

Go

–Create course table

create table course

(

courseID int pri ** ry key,

coursename char(20)

)

–Create class_schdule table

use lesson

go

create table course_schdule

(

classID int,

teacherID int,

courseID int,

weeks      int not null check(weeks>0 and weeks<8),

lessons    int not null check(lessons>0 and lessons<9),

re ** rk char(100),

constraint pk_schdule pri ** ry key(classID,teacherID,courseID,weeks,lessons),

constraint fk_classID foreign key (classID) references class(classID),

constraint fk_teacherID foreign key (teacherID)references teacher(teacherID),

constraint fk_courseID foreign key (courseID)references course(courseID)

)

2. System data table structure relationship

As shown in Figure 6.

Figure 6 System data sheet structure diagram

4. System data query and data maintenance

1. Insert test data (implemented using the Insert statement)

–Add data to the table to class

use lesson

go

insert into class values(’01’,’物联网’)

insert into class values(’02’,&#39；营销’)

insert into class values(’03’,’机电’)

insert into class values(’04’,’电气’)

insert into class values(’05’,’软件’)–向student将数据添加到表中

use lesson

go

insert into student values(‘1201′,’白某’,’M’,’01’)

insert into student values(‘1202′,’贾某’,’M’,’01’)

insert into student values(‘1203′,’王某’,’M’,’02’)

insert into student values(‘1204′,’龚某’,’F’,’02’)

insert into student values(‘1205′,’邢某’,’M’,’03’)

insert into student values(‘1206′,’戴某’,’M’,’03’)

insert into student values(‘1207′,’刘某’,’F’,’04’)

insert into student values(‘1208′,’陈某’,’F’,’04’)

insert into student values(‘1209′,’韩某’,’M’,’05’)

insert into student values(‘1210′,’袁某’,’F’,’05’)–向teacher将数据添加到表中

use lesson

go

insert into teacher values(‘001′,’韩韩’,’M’,’35’)

insert into teacher values(‘002′,’周周’,’F’,’40’)

insert into teacher values(‘003′,’石石’,’M’,’45’)

insert into teacher values(‘004′,’费费’,’M’,’43’)

insert into teacher values(‘005′,’胡胡’,’M’,’37’)

insert into teacher values(‘006′,’田田’,’F’,’43’)

insert into teacher values(‘007′,’姚姚’,’M’,’45’)

insert into teacher values(‘008′,’喻喻’,’F’,’36’)

insert into teacher values(‘009′,’刘刘’,’F’,’30’)

insert into teacher values(‘010′,’李李’,’M’,’31’)–向course将数据添加到表中

use lesson

go

insert into course values(‘0001′,’复变’)

insert into course values(‘0002′,’模电’)

insert into course values(‘0003′,’信号’)

insert into course values(‘0004′,’电拖’)

insert into course values(‘0005′,’概率’)

insert into course values(‘0006′,’英语’)

insert into course values(‘0007′,’口语’)

insert into course values(‘0008′,’数据库’)

insert into course values(‘0009′,’电路’)

insert into course values(‘0010′,’大物’)

insert into course values(‘0011′,’马原’)–向course_schdule将数据添加到表中

use lesson

go

insert into course_schdule values(‘1′,’001′,’0004′,1,5,&#39；第一周到第五周；’)

insert into course_schdule values(‘1′,’001′,’0004′,1,6,&#39；第一周到第五周；’)

insert into course_schdule values(‘1′,’002′,’0007′,1,1,&#39；第一周到第六周；’)

insert into course_schdule values(‘1′,’002′,’0007′,1,2,’第1周到第6周’)

insert into course_schdule values(‘1′,’003′,’0001′,3,3,’第1周到第5周’)

insert into course_schdule values(‘1′,’003′,’0001′,3,4,’第1周到第5周’)

insert into course_schdule values(‘1′,’004′,’0008′,5,7,’第5周到第10周’)

insert into course_schdule values(‘1′,’004′,’0008′,5,8,’第5周到第10周’)

insert into course_schdule values(‘2′,’001′,’0004′,1,3,’第1周到第5周’)

insert into course_schdule values(‘2′,’001′,’0004′,1,4,’第1周到第5周’)

insert into course_schdule values(‘2′,’005′,’0011′,2,1,’第2周到第3周’)

insert into course_schdule values(‘2′,’005′,’0011′,2,2,’第2周到第3周’)

insert into course_schdule values(‘2′,’005′,’0011′,2,3,’第2周到第3周’)

insert into course_schdule values(‘2′,’005′,’0011′,2,4,’第2周到第3周’)

2． Examples of simple data queries

Check all course information

select * from course

go

 

3． Connection query example

Query the student information and ask the classID to be replaced by the class name.

select student. StudentID Student ID,student.name Student name, student. ** Gender, class. class name class name

from student,class

where student. classID=class. classID

 

4． Examples of subqueries

Query the information of which classes the teacher has given classes to, and ask the classID to be replaced by the class name to display.

use lesson

go

select distinct teacher. TeacherID Teacher ID,teacher.name Teacher name, class. class name class name

from teacher

join course_schdule on teacher. teacherID=course_schdule. teacherID

join class on course_schdule. classID=class. classID

5． Examples of data modification

Modify a class schedule information

use lesson

go

update course_schdule

set teacherID=’006′,courseID=’0001′

where classID=’1′ and weeks=1 and lessons=6

 

6． Examples of data deletion

Delete a class schedule information

use lesson

go

delete from course_schdule

where classID=’1′ and weeks=1 and lessons=6

7． Example of creating a function

Create a function to calculate the number of courses in the formulated class

create function dbo. countcourses (@classid int)

returns int

as

begin

declare @count int

select @count=count(distinct courseID)

from course_schdule

where course_schdule. classID=@classid

return @count

endprint convert(varchar(16),dbo. countcourses(1))

8． Example of creating a stored procedure

Create a stored procedure to generate a class schedule for the specified class

use lesson

go

create proc up_class(@classID int)

as

select class. class name class name,teacher.name Teacher name, course. coursename course name, course_schdule. weeks, course_schdule. lessons section,course_schdule.re ** rk Remarks

from course_schdule

join class on course_schdule. classID=class. classID

join teacher on course_schdule. teacherID=teacher. teacherID

join course on course_schdule. courseID=course. courseID

where course_schdule. classID=@classID

order by course_schdule.weeks asc,course_schdule.lessons ascexecute up_class ’01’

 

Create a stored procedure to generate the class schedule of the specified teacher

use lesson

go

create proc up_teacher(@teacherID int)

as

select teacher.name Teacher name, class. Class name class name, course. coursename course name, course_schdule. weeks, course_schdule. lessons section,course_schdule.re ** rk Remarks

from course_schdule

join class on course_schdule. classID=class. classID

join teacher on course_schdule. teacherID=teacher. teacherID

join course on course_schdule. courseID=course. courseID

where course_schdule. teacherID=@teacherID

order by course_schdule.weeks asc,course_schdule.lessons ascexecute up_teacher ‘0001’

Create a stored procedure to generate the class schedule of the specified student

use lesson

go

create proc up_student(@studentID int)

as

begin

declare @classid int

select @classid=student. classID

from student

where student. studentID=@studentID

select student. StudentID Student ID,student.name Student name,teacher.name Teacher name, class. Class name class name, course. coursename course name, course_schdule. weeks, course_schdule. lessons section,course_schdule.re ** rk Remarks

from course_schdule

join class on course_schdule. classID=class. classID

join teacher on course_schdule. teacherID=teacher. teacherID

join course on course_schdule. courseID=course. courseID

join student on student. classID=@classid

where course_schdule. classID=@classid and student. studentID=@studentID

order by course_schdule. weeks asc,course_schdule. lessons asc

endexecute up_student ‘1203’

 

Create a stored procedure to detect whether there are classes for the specified teacher and the specified class；

use lesson

go

create proc up_search(@teacherID int,@week char(20),@lesson char(20))

as

begin

declare @teachername char(10)

select @teachername=teacher.name

from teacher

where teacher. teacherID=@teacherID

if exists(select *

from course_schdule

where course_schdule. teacherID=@teacherID and course_schdule. weeks=@week and course_schdule. lessons=@lesson )

print convert(varchar(4),@teachername)+’老师在星期’+convert(varchar(2),@week)+’的第’+convert(varchar(2),@lesson)+’节 有课！’

else

print convert(varchar(4),@teachername)+’老师在星期’+convert(varchar(2),@week)+’的第’+convert(varchar(2),@lesson)+’节 没课！’

endexecute up_search ‘003’,’3′,’4’execute up_search ‘001’,’2′,’5′

9． Example of creating a trigger

Create a trigger to detect whether the added schedule information conflicts with the existing schedule；

use lesson

go

create trigger T_insert

on course_schdule

instead of insert

as

if exists(select *

from course_schdule,inserted

where course_schdule. teacherID=inserted. teacherID and course_schdule. weeks=inserted. weeks and course_schdule. lessons=inserted. lessons and course_schdule. classID=inserted. classID and course_schdule. courseID=inserted. courseID)

begin

raiserror(‘该节次已经有课了，请重新添加排课信息！’,16,1)

rollback

endinsert into course_schdule values(‘2′,’005′,’0011′,2,4,’第2周到第3周’)insert into course_schdule values(‘3′,’006′,’0009′,2,4,’第2周到第3周’)

10． Example of creating a view

Create a view, through which you can display the student's ID, name, course name, teacher name, class name, and course week, section, and remarks.

use lesson

go

create view v_stu

as

select student. StudentID Student ID,student.name Student name,teacher.name Teacher name, class. Class name class name, course. coursename course name, course_schdule. weeks, course_schdule. lessons section,course_schdule.re ** rk Remarks

from course_schdule

join class on course_schdule. classID=class. classID

join teacher on course_schdule. teacherID=teacher. teacherID

join course on course_schdule. courseID=course. courseID

join student on student. classID=class. classIDselect *

from v_stu

where 学生ID=’1201’C#程序应用举例

Use C# to log in to the SQL database

using System;

using System. Collections. Generic;

using System. ComponentModel;

using System. Data;

using System. Drawing;

using System. Linq;

using System. Text;

using System. Threading. Tasks;

using System. Windows. Forms;

using System. Data. SqlClient;

namespace login

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void label1_Click(object sender, EventArgs e)

{

}

private void Form1_Load(object sender, EventArgs e)

{

}

private void button1_Click(object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection(“server=.;database=lesson;uid=sa;pwd=123”);

bool error = false;

int num = 0;

try

{

string sql = string.For ** t(“select count(*) from stu where sname='{0}’ and password='{1}’”, textBox1.Text.Trim(), textBox2.Text.Trim());

conn. Open();

SqlCom ** nd com ** nd = new SqlCom ** nd(sql, conn);

num = Convert. ToInt32(com ** nd. ExecuteScalar());

}

catch (Exception ex)

{

error = true;

MessageBox. Show(ex. Message);

}

finally

{

conn. Close();

}

if (! error && (num == 1))

{

MessageBox.Show(“登录成功！”);

}

else

{

MessageBox.Show(“输入的用户名或密码有误！”, “登录提示”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

}

}

}

 

 

5. System database maintenance and management

1． System database backup and restore scheme

(1) Backup method

In view of the actual situation of different database systems, SQL server 2005 proposes four backup methods.

Full library backup: Back up a copy of the entire database Log backup: You can restore all submitted things in the event of an accident. Differential backup: Back up the data pages that have been modified since the last full library backup. File and filegroup backup: Separate backup of the files or filegroups that make up the database.

(2) Backup and restore scheme

Backup plan

Perform full backups regularly according to the actual situation of the operation of the textbook management system, every night or once a week; secondly, perform differential backups at smaller time intervals, every few hours; and finally, perform transaction log backups between two adjacent differential backups, such as every 5 minutes..

Restore scheme

First, use the most recent full-library backup to restore the full-library backup; secondly, restore the last differential backup; and finally restore the transaction log backup in chronological order.

2． System database security management

(1) The security architecture of SQL server can be divided into 4 levels：

The security of the guest operating system, the security of the login of SQL server, the security of the database, the security of the database object

(2) Client security certification

Log in to the user with the windows operating system to achieve the security of the client.

(3) Server security authentication

The SQL server security authentication mode is divided into two methods: windows authentication and SQL server authentication. When creating a login account, it is also divided into two modes of account.

(4) Database security certification

Access to the database is achieved by mapping the relationship between the user of the database and the login account. Database users are used to indicate which person can access which database. When the login account is authenticated, the database user must be set up to operate on the database and its objects. A login account can be mapped to different database users in different databases, which can have different permissions.

(5) Database object security authentication

The security of database objects is achieved through the roles and permissions of database users.

role

Roles are divided into server roles and database roles. Server roles are the permissions that SQL server 2005 gives users to server operations, which are divided into 8 types. Server roles are fixed and cannot be added or deleted. Database roles are the permissions that SQL server 2005 gives users to operate on databases. There are ten fixed database roles in SQL server. In addition to the 10 fixed database roles, SQL server allows the creation of new database roles.

Authority

There are three types of permissions: object permissions (which refer to the user's operation permissions on tables, views, stored procedures and other objects in the database), statement permissions (which refer to the permission to execute data definition statements), and implied permissions (which refer to the system's pre-defined server roles, database owners, and database object permissions).