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’,';营销’)
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,';第一周到第五周;’)
insert into course_schdule values(‘1′,’001′,’0004′,1,6,';第一周到第五周;’)
insert into course_schdule values(‘1′,’002′,’0007′,1,1,';第一周到第六周;’)
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).
Leave a Reply