数据库培训中心信息管理系统课程设计

数据库培训中心信息管理系统课程设计
-CAL-FENGHAI-(2020YEAR-YICAI)_JINGBIAN
软件学院
课程设计报告书
课程名称数据库原理及应用课程设计
设计题目培训中心信息管理系统
专业班级
学号
姓名
指导教师
目录
1.设计时间...................................................... . (3)
2.设计目的...................................................... . (3)
3.设计任务...................................................... . (3)
4.设计内容...................................................... . (3)
需求分析.............................................................. .. (3)
系统设计.............................................................. .. (3)
4.2.1概念结构设计.............................................................. . (4)
4.2.2逻辑结构设计.............................................................. . (6)
4.2.3物理结构设计.............................................................. . (6)
系统实施.............................................................. (8)
4.3.1数据库实现.............................................................. . (8)
4.3.2数据载入.............................................................. . (12)
运行与测试.......................................................... (14)
4.4.1运行与测试.......................................................... .. (14)
4.4.2分析.......................................................... . (16)
5.总结与展望.............................................................. .. (17)
6.参考文献.............................................................. (18)
7.成绩评定.............................................................. (18)
图3-1
建表：课程文件
CREATE TABLE COURSE
(
CNO INT PRIMARY KEY,
CNAME CHAR(10),
PERIOD INT,
TUITION MONEY,
PLACE CHAR(10),
TNAME CHAR(8),
NUP INT,
ANUP INT
FOREIGN KEY(TNAME) REFERENCES TEACHER(TNAME) ON DELETE CASCADE
ON UPDATE CASCADE
);
图3-2
建表：教师文件
CREATE TABLE TEACHER
(
TNAME CHAR(8) PRIMARY KEY,
SEX CHAR(2) CHECK(SEX IN(‘男’，’女’))
);
图3-3
建表：学员文件
CREATE TABLE STUDENT
(
SNO INT PRIMARY KEY,
SNAME CHAR(8),
TOPTEACHER CHAR(8) ,
CM INT,
ATIME DATETIME,
SUMMONEY MONEY
FOREIGN KEY(TOPTEACHER) REFERENCES TEACHER(TNAME) ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(CM) REFERENCES CLASS(CM)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
图3-4建表：账目文件
CREATE TABLE ACCOUNTS
(
SNO INT NOT NULL,
CNO INT NOT NULL,
CM INTNOT NULL,
SUMMONEY MONEY,
PAYTIME DATETIME,
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES STUDENT(SNO) ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY(CNO) REFERENCES COURSE(CNO) ON DELETE NO ACTION
ON UPDATE NO ACTION ,
FOREIGN KEY(CM) REFERENCES CLASS(CM)
ON DELETE NO ACTION
ON UPDATE NO ACTION ,
);
图3-5
建表：选课文件
CREATE TABLE SC
(
SNO INT,
CNO INT,
GRADE INT CHECK(GRADE>=0 AND GRADE<=100 ) PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES STUDENT(SNO) ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(CNO) REFERENCES COURSE(CNO) ON DELETE NO ACTION
ON UPDATE NO ACTION
);
图3-6
4.3.2 数据载入
（1）教师表
表2-1（2）班级表
表2-2
（3）学员表
表2-3（4）课程表
表2-4（5）账目表
表2-5
（6）选课表
表2-6
运行与测试
4.4.1 运行与测试
（1）检验课程是否额满
SELECT*
FROM COURSE
WHERE NUP>ANUP;
图4-1
(2)报名登记
INSERT INTO STUDENT(SNO,SNAME,TOPTEACHER,CM,ATIME,SUMMONEY)
VALUES(30,’田雨橙’,’姜延吉’,’2’,’2012-9-1’,4800); UPDATE COURSE
SET ANUP=ANUP+1
图4-2（3）支付学费
INSERT INTO ACCOUNTS(SNO,CNO,CM,SUMMONEY,PAYTIME) VALUES (30,4,2,4800,’2012-9-3’);
图4-3
（4）查询
[1]学生选课
SELECT CNAME
FROM STUDENT,COURSE,SC
WHERE SNAME=’张琪’ AND = AND =
图4-4
[2]某位教师所授课程
SELECT CNAME
FROM STUDENT,COURSE,SC
WHERE SNAME=’刘金光’ AND = AND =
图4-5
（5）注销
DELETE
FROM SC
WHERE ‘任丽君’=(SELECT SNAME FROM STUDENT WHERE AND
‘C语言’=(SELECT CNAME FROM COURSE WHERE =;
图4-6
UPDATE
COURSE
SET ANUP=ANUP-1
WHERE CNAME=’C语言’;
图4-7
4.4.2 分析
系统还有很多不足之处，需求分析不够全面，查询方法不够简捷，多处反复插入同一数据，造成冗余。

5总结与展望。