计算机与控制工程学院
数据库原理及应用
课程设计报告
题目名称： 选课系统
专业： 计科 班级： 计121-1
学号： 201258501149 姓名：李利娟
指导教师： 毕远伟
2015 年 01月 11 日
课程设计评语
班级：计121-1 学号： 201258501149 学生姓名： 李利娟
综合成绩：
目录
一、需求分析：............................................................................................................... 4
1、设计目的.............................................................................................................. 4
2、意义..................................................................................................................... 4
二、 系统设计................................................................................................................. 4
1、 设计分析............................................................................................................ 4
2、 方案分析............................................................................................................ 5
3、概念设计.............................................................................................................. 6
4、逻辑设计.............................................................................................................. 7
三、系统实现（功能模块实现）....................................................................................... 7
1、源代码：.............................................................................................................. 7
2、建立存储过程..................................................................................................... 17
3、建立视图............................................................................................................ 21
4、建立触发器........................................................................................................ 22
四、测试（含最终结果分析）......................................................................................... 24
1、 添加................................................................................................................. 24
（1）添加院系................................................................................................. 24
（2）添加专业................................................................................................. 24
（3）添加学生................................................................................................. 25
（4）添加教师................................................................................................. 25
（5）添加课程................................................................................................. 26
（6）添加管理员.............................................................................................. 26
五、结论（设计体会）................................................................................................... 27
一、需求分析：
1、设计目的
2、意义
二、系统设计
1、设计分析
2、方案分析
3、概念设计
4、逻辑设计
三、系统实现（功能模块实现）
1、源代码：
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2005 */
/* Created on: 2015/1/10 23:08:20 */
/*==============================================================*/
create database 选课系统
use 选课系统
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Course') and o.name = 'FK_COURSE_开设_TEACHER')
alter table Course
drop constraint FK_COURSE_开设_TEACHER
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Course') and o.name = 'FK_COURSE_管理C_MANAGER')
alter table Course
drop constraint FK_COURSE_管理C_MANAGER
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Major') and o.name = 'FK_MAJOR_属于_DEPT')
alter table Major
drop constraint FK_MAJOR_属于_DEPT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Student') and o.name = 'FK_STUDENT_属于_DEPT')
alter table Student
drop constraint FK_STUDENT_属于_DEPT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Student') and o.name = 'FK_STUDENT_属于_MAJOR')
alter table Student
drop constraint FK_STUDENT_属于_MAJOR
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Student') and o.name = 'FK_STUDENT_管理S_MANAGER')
alter table Student
drop constraint FK_STUDENT_管理S_MANAGER
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Teacher') and o.name = 'FK_TEACHER_开设_COURSE')
alter table Teacher
drop constraint FK_TEACHER_开设_COURSE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Teacher') and o.name = 'FK_TEACHER_管理T_MANAGER')
alter table Teacher
drop constraint FK_TEACHER_管理T_MANAGER
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('选修') and o.name = 'FK_选修_选修_STUDENT')
alter table 选修
drop constraint FK_选修_选修_STUDENT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('选修') and o.name = 'FK_选修_选修_COURSE')
alter table 选修
drop constraint FK_选修_选修_COURSE
go
if exists (select 1
from sysindexes
where id = object_id('Course')
and name = '管理C_FK'
and indid > 0
and indid < 255)
drop index Course.管理C_FK
go
if exists (select 1
from sysindexes
where id = object_id('Course')
and name = '开设_FK'
and indid > 0
and indid < 255)
drop index Course.开设_FK
go
if exists (select 1
from sysobjects
where id = object_id('Course')
and type = 'U')
drop table Course
go
if exists (select 1
from sysobjects
where id = object_id('Dept')
and type = 'U')
drop table Dept
go
if exists (select 1
from sysindexes
where id = object_id('Major')
and name = '属于_FK'
and indid > 0
and indid < 255)
drop index Major.属于_FK
go
if exists (select 1
from sysobjects
where id = object_id('Major')
and type = 'U')
drop table Major
go
if exists (select 1
from sysobjects
where id = object_id('Manager')
and type = 'U')
drop table Manager
go
if exists (select 1
from sysindexes
where id = object_id('Student')
and name = '属于_FK'
and indid > 0
and indid < 255)
drop index Student.属于_FK
go
if exists (select 1
from sysindexes
where id = object_id('Student')
and name = '属于_FK'
and indid > 0
and indid < 255)
drop index Student.属于_FK
go
if exists (select 1
from sysindexes
where id = object_id('Student')
and name = '管理S_FK'
and indid > 0
and indid < 255)
drop index Student.管理S_FK
go
if exists (select 1
from sysobjects
where id = object_id('Student')
and type = 'U')
drop table Student
go
if exists (select 1
from sysindexes
where id = object_id('Teacher')
and name = '管理T_FK'
and indid > 0
and indid < 255)
drop index Teacher.管理T_FK
go
if exists (select 1
from sysindexes
where id = object_id('Teacher')
and name = '开设_FK'
and indid > 0
and indid < 255)
drop index Teacher.开设_FK
go
if exists (select 1
from sysobjects
where id = object_id('Teacher')
and type = 'U')
drop table Teacher
go
if exists (select 1
from sysindexes
where id = object_id('选修')
and name = '选修_FK'
and indid > 0
and indid < 255)
drop index 选修.选修_FK
go
if exists (select 1
from sysindexes
where id = object_id('选修')
and name = '选修_FK'
and indid > 0
and indid < 255)
drop index 选修.选修_FK
go
if exists (select 1
from sysobjects
where id = object_id('选修')
and type = 'U')
drop table 选修
Go
----创建表
/*==============================================================*/
/* Table: Course */
/*==============================================================*/
create table Course (
CourseNum char(10) not null,
--TeacherNum char(10) null,
ManagerNum char(10) null,
CourseName varchar(20) not null,
Time1 char(10) not null,
CourseTime2 char(20) not null,
CoursePlace char(10) not null,
-- Number1 int not null,
--Number2 int not null,
constraint PK_COURSE primary key nonclustered (CourseNum)
)
go
/*==============================================================*/
/* Index: 开设_FK */
/*==============================================================*/
create index 开设_FK on Course (
TeacherNum ASC
)
go
/*==============================================================*/
/* Index: 管理C_FK */
/*==============================================================*/
create index 管理C_FK on Course (
ManagerNum ASC
)
go
/*==============================================================*/
/* Table: Dept */
/*==============================================================*/
create table Dept (
DeptNum char(10) not null,
DeptName varchar(20) not null,
DeptChairman varchar(10) not null,
DeptTel varchar(15) not null,
constraint PK_DEPT primary key nonclustered (DeptNum)
)
go
/*==============================================================*/
/* Table: Major */
/*==============================================================*/
create table Major (
MajorNum char(10) not null,
DeptNum char(10) null,
MajorName varchar(20) not null,
MajorAssistant varchar(10) not null,
MajorTel varchar(15) not null,
constraint PK_MAJOR primary key nonclustered (MajorNum)
)
go
/*==============================================================*/
/* Index: 属于_FK */
/*==============================================================*/
create index 属于_FK on Major (
DeptNum ASC
)
go
/*==============================================================*/
/* Table: Manager */
/*==============================================================*/
create table Manager (
ManagerNum char(10) not null,
ManagerName varchar(10) not null,
ManagerSex char(2) not null,
ManagerPassword varchar(20) null,
constraint PK_MANAGER primary key nonclustered (ManagerNum)
)
go
/*==============================================================*/
/* Table: Student */
/*==============================================================*/
create table Student (
StudentNum char(10) not null,
DeptNum char(10) null,
ManagerNum char(10) null,
MajorNum char(10) null,
StudentName varchar(10) not null,
StudentSex char(2) not null,
StudentPassword varchar(20) not null,
constraint PK_STUDENT primary key nonclustered (StudentNum)
)
go
/*==============================================================*/
/* Index: 管理S_FK */
/*==============================================================*/
create index 管理S_FK on Student (
ManagerNum ASC
)
go
/*==============================================================*/
/* Index: 属于_FK */
/*==============================================================*/
create index 属于_FK on Student (
MajorNum ASC
)
go
/*==============================================================*/
/* Index: 属于_FK */
/*==============================================================*/
create index 属于_FK on Student (
DeptNum ASC
)
go
/*==============================================================*/
/* Table: Teacher */
/*==============================================================*/
create table Teacher (
TeacherNum char(10) not null,
CourseNum char(10) null,
ManagerNum char(10) null,
TeacherName char(10) not null,
TeacherSex char(2) not null,
Title varchar(20) not null,
TeacherPassword varchar(20) not null,
constraint PK_TEACHER primary key nonclustered (TeacherNum)
)
go
/*创建索引*/
/*==============================================================*/
/* Index: 开设_FK */
/*==============================================================*/
create index 开设_FK on Teacher (
CourseNum ASC
)
go
/*==============================================================*/
/* Index: 管理T_FK */
/*==============================================================*/
create index 管理T_FK on Teacher (
ManagerNum ASC
)
go
/*==============================================================*/
/* Table: 选修 */
/*==============================================================*/
create table 选修(
StudentNum char(10) not null,
CourseNum char(10) not null,
constraint PK_选修primary key (StudentNum, CourseNum)
)
go
/*==============================================================*/
/* Index: 选修_FK */
/*==============================================================*/
create index 选修_FK on 选修(
StudentNum ASC
)
go
/*==============================================================*/
/* Index: 选修_FK */
/*==============================================================*/
create index 选修_FK on 选修(
CourseNum ASC
)
go
alter table Course
add constraint FK_COURSE_开设_TEACHER foreign key (TeacherNum)
references Teacher (TeacherNum)
go
alter table Course
add constraint FK_COURSE_管理C_MANAGER foreign key (ManagerNum)
references Manager (ManagerNum)
go
alter table Major
add constraint FK_MAJOR_属于_DEPT foreign key (DeptNum)
references Dept (DeptNum)
go
alter table Student
add constraint FK_STUDENT_属于_DEPT foreign key (DeptNum)
references Dept (DeptNum)
go
alter table Student
add constraint FK_STUDENT_属于_MAJOR foreign key (MajorNum)
references Major (MajorNum)
go
alter table Student
add constraint FK_STUDENT_管理S_MANAGER foreign key (ManagerNum)
references Manager (ManagerNum)
go
alter table Teacher
add constraint FK_TEACHER_开设_COURSE foreign key (CourseNum)
references Course (CourseNum)
go
alter table Teacher
add constraint FK_TEACHER_管理T_MANAGER foreign key (ManagerNum)
references Manager (ManagerNum)
go
alter table 选修
add constraint FK_选修_选修_STUDENT foreign key (StudentNum)
references Student (StudentNum)
go
alter table 选修
add constraint FK_选修_选修_COURSE foreign key (CourseNum)
references Course (CourseNum)
Go
2、建立存储过程
-------------------------------------------------------------
-------------------------------------------------------------
--1添加院系Dept
--检查是否已存在同名的存储过程，若有，则删除。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '添加Dept' AND type = 'P')
DROP PROCEDURE 添加Dept
GO
--创建存储过程- 添加院系
CREATE PROCEDURE 添加Dept
@DeptNum char(10),
@DeptName varchar(20),
@DeptChairman varchar(10),
@DeptTel varchar(15)
AS
begin
insert into Dept
(DeptNum,DeptName,DeptChairman,DeptTel)
values (@DeptNum,@DeptName,@DeptChairman,@DeptTel)
end
go
select*from Dept
/*============================================================
============================================================*/
--2添加Major
--检查是否已存在同名的存储过程，若有，则删除。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '添加Major' AND type = 'P')
DROP PROCEDURE 添加Major
GO
--创建存储过程- 添加Major
CREATE PROCEDURE 添加Major(
@MajorNum char(10),
@DeptNum char(10),
@MajorName varchar(20),
@MajorAssistant varchar(10),
@MajorTel varchar(15)
)
AS
begin
insert into Major
(MajorNum,MajorName,DeptNum,MajorAssistant,MajorTel)
values (@MajorNum,@DeptNum,@MajorName,@MajorAssistant,@MajorTel)
end
go
select* from Major
/*=========================================================
============================================================*/
--3添加Student
--检查是否已存在同名的存储过程，若有，则删除。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '添加Student' AND type = 'p')
DROP PROCEDURE 添加Student
GO
--创建存储过程- 添加Student
CREATE PROCEDURE 添加Student(
@StudentNum char(10),
@DeptNum char(10),
@managerNum char(10),
@StudentName varchar(10),
@StudentSex varchar(2),
@StudentPassword varchar(20)
)
AS
begin
insert into Student
(StudentNum,DeptNum,managerNum,StudentName,StudentSex,StudentPassword)
values (@StudentNum,@DeptNum,@managerNum,@StudentName,@StudentSex,@StudentPassword)
end
go
select* from Student
/*===============================================================
============================================================*/
--4添加Teacher
--检查是否已存在同名的存储过程，若有，则删除。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '添加Teacher' AND type = 'p')
DROP PROCEDURE 添加Teacher
GO
--创建存储过程- 添加Teacher
CREATE PROCEDURE 添加Teacher(
@TeacherNum char(10),
@CourseNum char(10),
@ManagerNum char(10),
@TeacherName varchar(10),
@TeacherSex varchar(2),
@Title varchar(20),
@TeacherPassword varchar(20)
)
AS
begin
insert into Teacher
(TeacherNum,CourseNum,TeacherName,TeacherSex,Title,TeacherPassword)
values (@TeacherNum,@CourseNum,@TeacherName,@TeacherSex,@Title,@TeacherPassword)
end
go
select* from Teacher
update Teacher
set CourseNum='00001'
where TeacherNum='5432101';
/*===================================================
*==================================================*/
--5\添加Course
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '添加Course' AND type = 'p')
DROP PROCEDURE 添加Course
GO
--创建存储过程-添加Course
CREATE PROCEDURE 添加Course(
@CourseNum char(10),
@ManagerNum char(10),
@CourseName varchar(10),
@Time1 char(10),
@CourseTime2 char(20),
@CoursePlace char(10)
)
AS
begin
insert into Course
(CourseNum,CourseName,Time1,CourseTime2,CoursePlace)
values (@CourseNum,@CourseName,@Time1,@CourseTime2,@CoursePlace)
end
go
select*from Course
/*==================================================
=======================================================*/
--6添加Manager
--检查是否已存在同名的存储过程，若有，则删除。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '添加Manager' AND type = 'P')
DROP PROCEDURE 添加Manager
GO
--创建存储过程- 添加Manager
CREATE PROCEDURE 添加Manager(
@ManagerNum char(10),
@ManagerName varchar(20),
@ManagerSex char(2),
@ManagerPassword varchar(20)
)
AS
begin
insert into Manager
(ManagerNum,ManagerName,ManagerSex,ManagerPassword)
values (@ManagerNum,@ManagerName,@ManagerSex,@ManagerPassword)
end
go
select* from Manager
/*===================================================
===================================================*/
--6添加选修
--检查是否已存在同名的存储过程，若有，则删除。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '添加选修' AND type = 'P')
DROP PROCEDURE 添加选修
GO
--创建存储过程- 添加选修
CREATE PROCEDURE 添加选修(
@StudentNum char(10),
@CourseNum varchar(10)
)
AS
begin
insert into 选修
(StudentNum,CourseNum)
values (@StudentNum,@CourseNum)
end
go
exec 添加选修
'201258501111',
'数据库'
select* from 选修
/*============================================
3、建立视图
===============================================*/
--建立课程信息视图--------------------------------------------------------------------
create view Course
As
select CourseNum,CourseName,Time1,CourseTime2--课程编号,课程名称,学时,上课时间
From course
--建立院系视图--
create view Dept
As
Select DeptNum,DeptName,DeptChairman,DeptTel--院系代号,院系名,系主任,联系电话
From Dept
---建立专业视图--
create view Major
As
Select MajorNum,MajorName,majorAssistant,MajorTel--专业号,专业名称,辅导员，备注
From major
--建立学生与课程关系视图--
create view 选修
As
select StudentNum,CourseNum--学号,课程号
From 选修
--建立教师与课程关系视图--
create view TC
As
select CourseNum,TeacherNum--课程号,教师号
From TC
--建立教师信息视图--
create view Teacher
As
Select TeacherNum,deptNum,TeacherName,TeacherSex,Title--教师号,院系代号,姓名,性别,职称
From teacher
--建立管理员信息视图
create view manager
as
select ManagerNum,ManagerName,ManagerSex,ManagerPassword
/*===========================================================
============================================================*/
4、建立触发器
/*--建立学生添加院系触发器adddept,当该表中已存在所对应院系号码的院系时,
系统给予错误提示*/
CREATE TRIGGER adddept ON dept
FOR INSERT,UPDATE
AS
IF
(SELECT COUNT(*) FROM dept,inserted
WHERE dept.DeptNum=inserted.DeptNum)>0
BEGIN
PRINT '院系号码产生冲突，请核对后重试！'
ROLLBACK
END
-------------------------
/*--建立学生添加专业触发器major,当专业信息中的信息不对或者该表中已存在所对
应专业号码的专业时,系统给予错误提示*/
CREATE TRIGGER addmajor ON major
FOR INSERT,UPDATE
AS
IF
(SELECT COUNT(*) FROM dept,inserted
WHERE dept.DeptNum=inserted.DeptNum)=0
BEGIN
PRINT '未找到该专业的院系信息，请添加相应院系后重试！'
ROLLBACK
END
ELSE if
(SELECT COUNT(*) FROM major,inserted
WHERE major.MajorNum=inserted.MajorNum)>0
BEGIN
PRINT '院系号码产生冲突，请核对后重试！'
ROLLBACK
END
-------------------------------------------------------------
/*--建立添加学生触发器addstudent，当学生信息中的专业号不对或者系统中已经存在
所对应的学号的我学生时，系统会给予错误提示*/
CREATE TRIGGER addstudent ON student
FOR INSERT,UPDATE
AS
IF(SELECT COUNT(*) FROM major,inserted
where major.majorNum=inserted.MajorNum)=0
begin
print'未找到该学生的专业信息，请添加相应专业后重试！'
ROLLBACK
end
else if
(select count(*) from student,inserted
where student.studentNum=inserted.studentNum)>0
begin
print'学号产生冲突，请核对后重试'
ROLLBACK
END
四、测试（含最终结果分析）
1、添加
（1）添加院系
exec 添加Dept
'1235689',
'计控',
'黄小明',
'12345678900'
exec 添加Dept
'1234567',
'体育',
'李小鹏',
'12345668905'
exec 添加Dept
'1234568',
'艺院',
'马小乙',
'12345678907'
--12345678,'机械','王先生',12345678900;
（2）添加专业
exec 添加Major
'00000021',
'1235689',
'计科',
'欧老师',
'12345698901'
exec 添加Major
'00000002',
'1234567',
'体育',
'王小锤',
'12345678902'
exec 添Major
'00000003','考古','吴三省','12345678904'
（3）添加学生
exec 添加Student
'2012585011',
'李小花',
'女',
'1234501'
exec 添加Student
'2012585046',
'1235689',
'111110',
'马小花',
'女',
'1234502'
exec 添加Student
'2012585044',
'1234568',
'111111',
'由小花',
'女',
'1234503'
（4）添加教师
exec 添加Teacher
'5432101',
'00001',
'111110',
'王大锤',
'男',
'讲师',
'1111000001'
exec 添加Teacher
'5432102',
'00003',
'111110',
'武老师',
'男',
'教授',
'1111000002'
（5）添加课程
exec 添加Course
'00001',
'数据库',
'45',
'830930',
'1302'
exec 添加Course
'00003',
'111110',
'数据结构',
'50',
'8:00',
'1304'
（6）添加管理员
exec 添加Manager
'111110',
'张记得',
'男',
'87654'
exec 添加Manager
'111111',
'吴邪',
'男',
'34525'
exec 添加Manager
'111112',
'黎簇',
'男',
'23459'
五、结论（设计体会）
参考文献