2023年7月27日发(作者：)

《数据库原理与应用》

2011 / 2012 学年 第 1 学期

实验八 数据库设计

姓 名：____###______

学 号：__ ##########_

班 级：__ ####### _ _

指导教师：__######## __

计算机科学与工程学院 一、系统需求分析

1.1 系统要求

(1)系统用户由三类组成：教师、学生和管理员；

(2)管理员负责的主要功能：

 用户管理（教师、学生及管理员的增、删、改）；

 课程管理（添加，删除和修改）

 选课管理（实现选课功能开放和禁止、教师成绩输入开放和禁止）

(3)学生通过登录，可以查询课程的基本信息、实现选课、退课和成绩查询；

(4)教师通过登录，可以查看选课学生的基本信息，可以输入成绩；

1.2 需求分析（主要完成功能分析，建立功能模块图）

 本系统是为了更好管理学生选课排课信息而设计的。由于学校的学生和课程繁多，包含的信息量大且复杂，有必要建立一个学生选课排课系统，使选课排课管理工作规范化，系统化，程序化，提高信息处理的速度和准确性，能够及时、准确、有效的查询和修改选课排课相关信息。

二、概念结构设计

成绩

授课教室 授课时间

N

学生

选课

M

课程

P

授课

1

教师

人数

集成后选课管理系统E-R图模型

性别

学号 姓名

年龄

系别

学生

N

人数

选课

成绩

课程名

M

学分

课程

课程号

P

授课时间

授课

授课教室

1

教师工号

办公地点

教师

教师姓名

教师性别

选课管理系统E-R图

三、逻辑结构设计

3.1 E-R图转换为关系模型

学生（学号，姓名，性别，系别，年龄）

课程（课程号，课程名，学分）

教师（教师工号，教师姓名，教师性别，办公地点）

选课（学号，课程号，成绩，人数）

授课（课程号，教师工号，授课时间，授课地点）

3.2 数据库表的基本结构（用表格方式表示，参照实验2中给出的表的基本结构说明）

（1）学生表Student：

列名

SNO

SNAME

SSEX

SAGE

SDEPT

说明

学号

姓名

性别

年龄

系别

数据类型

CHAR(7)

CHAR(10)

CHAR(2)

SMALLINT

VARCHAR(20)

约束

主码

NOT NULL

取“男”或“女”

取值15-30

默认“计算机系”

（2）课程表Course:

列名

CNO

CNAME

CCREDIT

列名

TNO

TNAME

TADDRESS

TSEX

列名

SNO

(4)选课表Xcourse：

说明

学号

数据类型

CHAR(7)

约束

主码，引用Student说明

课程号

课程名

学分

说明

教师工号

教师姓名

办公地点

教师性别

数据类型

CHAR(7)

VACHAR(20)

SMALLINT

数据类型

CHAR(7)

CHAR(10)

CHAR(8)

CHAR(2)

约束

主码

NOT NULL

大于0

约束

主码

NOT NULL

NOT NULL

取“男”或“女”

（3）教师表Teacher: 的外码

CNO

XGRADE

XNO

(5)授课表Scourse:

列名

CNO

TNO

STIME

SADDRESS

说明

课程号

教师工号

授课时间

授课地点

数据类型

CHAR(7)

CHAR(7)

Datetime

CHAR(8)

约束

主码，引用Course的外码

NOT NULL，引用Teacher的外码

NOT NULL

NOT NULL

课程号

成绩

人数

CHAR(7)

SMALLINT

SMALLINT

主码，引用Course的外码

大于0，小于100

小于等于120

四、SQL编程（主要包括创建表和创建必要的视图、触发器、存储过程等）

4.1 创建表

(1)创建Student表：

create TABLE Student (

SNO char(7) PRIMARY KEY,

SNAME char(10) not null,

SSEX char(2) check(SSEX=’男’ or ‘女’),

SAGE smallint check(SAGE>=15 and SAGE<=30),

SDEPT char(20) default '计算机系'

)

(2)创建Course表：

create TABLE Course (

CNO char(7) primary key,

CNAME char(20) not null,

CCREDIT smallint check(CCREDIT>0)

)

(3)创建Teacher表：

create TABLE Teacher (

TNO char(7) primary key,

TNAME char(20) not null,

TADDRESS char(8) not null,

TSEX char(2) check(TSEX=’男’ or ’女’)

)

(4)创建Xcourse表：

create TABLE Xcourse (

SNO char(7) foreign key (SNO) references Student (SNO),

CNO char(7) foreign key (CNO) references Course (CNO), XGRADE SMALLINT check(XGRADE>0 and XGRADE<100),

XNO SMALLINT check(XNO<=120),

Primary key (SNO,CNO)

)

(5)创建 Scourse 表：

create TABLE Scourse (

CNO char(7) primary key,

TNO char(7) not null,

STIME Datetime not null,

SADDRESS char(8) not null,

foreign key(CNO) references Course (CNO),

foreign key(TNO) references Teacher(TNO)

)

4.2数据库授权

(1)管理员授权：

grant all privileges on table Student,Course,Teacher,Xcourse,Scourse

To admin

(2)教师授权

grant select update (XGRADE) on table Student, ,Xcourse

To teacher

(3)学生授权

grant select on table Student,Course,Teacher,Xcourse,Scourse

To student

4.3建立视图

(1)建立学生学号姓名总成绩视图StudentGrade

create view StudentGrade (学号，姓名，总成绩)

as

select , SNAME,SUM(GRADE)

from Student,Xcourse where =

group by ,SNAME

(2)建立视图UnpastStudent,视图中包含所有成绩不及格的学生的学号，姓名，课程名，成绩

create view UnpastStudent(学号，姓名，课程名，成绩)

as

select ,SNAME,CNAME,XGRADE FROM Student,Xcourse where XGRADE<60

and =

4.4定义触发器

（1）定义一个触发器，其基本功能是在表中增加或修改一个选课记录时，检查该课程的选课人数是否超过限定（可自行定义一个限定值，根据表中数据的情况而定）。若超过限定值，则拒绝操作。

create TRIGGER trig1 on Xcourse

for INSERT,UPDATE

as

if(select COUNT(*) from Xcourse where CNO=(select CNO from inserted)>120 begin

print '选课人数超过上限'

rollback

end

五、设计小结（本次设计小结，存在的问题，收获等）

通过本次设计实验，将以前学过的数据库理论知识得以具体实践，掌握了数据库设计的基本步骤，以及每一步应该具体做的工作，而且进一步巩固了SQL语言基础，为以后的诸多工作打下了良好的基石。