13信管实验报告
《数据库原理与技术》实验报告 专 业 信息管理与信息系统 班 级 信管 132 班 学 号 ***1 学生姓名 阿布都拉·吐鲁甫 指导老师 米晓红 河南科技大学管理学院 2015 年 06 月 20 日
-1-实验一：数据定义及更新语句练习一、实验目的 熟练掌握用 SQL 语句实现数据库和基本表的创建、数据的更新。
二、实验内容（1）用 SQL 语句建立如下 mySPJ 数据库，包括 S，P，J，和 SPJ 四个基本表(教材第二章习题 5 中的四个表)，要求实现关系的三类完整性。
S(SNO,SNAME,STATUS,CITY);P(PNO,PNAME,COLOR,WEIGHT);J(JNO,JNAME,CITY);SPJ(SNO,PNO,JNO,QTY)；（2）分别使用插入、删除、修改的方式更新基本表中的数据。
a.利用 Insert 语句将习题中给出的示例记录插入各表。
b.利用 Update 更新表中的记录：
①将 p 表中的所有红色零件的重量增加 5。
②将 spj 表中所有天津供应商的 QTY 属性值减少 10。用子查询。
c.利用 Delete 语句删除 p 表中的所有红色零件的记录。
SQL 语句执行后返回基本表查看更新后的结果，如果与期望不符，分析原因并记录在实验报告中。
三、完成情况 CREATE TABLE S(SNO CHAR(9)PRIMARY KEY, SNAME CHAR(20)UNIQUE, STATUS SMALLINT, CITY CHAR(20));CREATE TABLE P(PNO CHAR(9)PRIMARY KEY, PNAME CHAR(20), COLOR CHAR(20), WEIGHT SMALLINT);CREATE TABLE J(JNO CHAR(9)PRIMARY KEY, JNAME CHAR(20)UNIQUE, CITY CHAR(20));CREATE TABLE SPJ
-2-(SNO CHAR(9), PNO CHAR(9), JNO CHAR(9), QTY SMALLINT, PRIMARY KEY(SNO,PNO,JNO), FOREIGN KEY(SNO)REFERENCES S(SNO), FOREIGN KEY(PNO)REFERENCES P(PNO), FOREIGN KEY(JNO)REFERENCES J(JNO));INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES("S1"," 精益 ",20," 天津 ");INSERT INTO S VALUES("S2"," 盛锡 ",10," 北京 ");INSERT INTO S VALUES("S3"," 东方红 ",30," 北京 ");INSERT INTO S VALUES("S4"," 丰泰盛 ",20," 天津 ");INSERT INTO S VALUES("S5"," 为民 ",30," 上海 ");INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES("P1"," 螺母 "," 红 ",12);INSERT INTO P VALUES("P2"," 螺栓 "," 绿 ",17);INSERT INTO P VALUES("P3"," 螺丝刀 "," 蓝 ",14);INSERT INTO P VALUES("P4"," 螺丝刀 "," 红 ",14);INSERT INTO P VALUES("P5"," 凸轮 "," 蓝 ",40);INSERT INTO P VALUES("P6"," 车轮 "," 红 ",30);INSERT INTO J(JNO,JNAME,CITY)
-3-VALUES("J1"," 三建 "," 北京 ");INSERT INTO J VALUES("J2"," 一汽 "," 长春 ");INSERT INTO J VALUES("J3"," 弹簧厂 "," 天津 ");INSERT INTO J VALUES("J4"," 造船厂 "," 天津 ");INSERT INTO J VALUES("J5"," 机车厂 "," 唐山 ");INSERT INTO J VALUES("J6"," 无线电厂 "," 常州 ");INSERT INTO J VALUES("J7"," 半导体厂 "," 南京 ");INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES("S1","P1","J1",200);INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES("S1","P1","J3",100);INSERT INTO SPJ VALUES("S1","P1","J4",700);INSERT INTO SPJ VALUES("S1","P2","J2",100);INSERT INTO SPJ VALUES("S2","P3","J1",400);INSERT INTO SPJ VALUES("S2","P3","J2",200);INSERT INTO SPJ VALUES("S2","P3","J4",500);INSERT INTO SPJ VALUES("S2","P3","J5",400);INSERT
-4-INTO SPJ VALUES("S2","P5","J1",400);INSERT INTO SPJ VALUES("S2","P5","J2",100);INSERT INTO SPJ VALUES("S3","P1","J1",200);INSERT INTO SPJ VALUES("S3","P3","J1",200);INSERT INTO SPJ VALUES("S4","P5","J1",100);INSERT INTO SPJ VALUES("S4","P6","J3",300);INSERT INTO SPJ VALUES("S4","P6","J4",200);INSERT INTO SPJ VALUES("S5","P2","J4",100);INSERT INTO SPJ VALUES("S5","P3","J1",200);INSERT INTO SPJ VALUES("S5","P6","J2",200);INSERT INTO SPJ VALUES("S5","P6","J4",500);UPDATE P SET WEIGHT=WEIGHT+5 WHERE COLOR=" 红 ";UPDATE SPJ SET QTY=QTY-10 WHERE SNO IN(SELECT SNO FROM S WHERE CITY=" 天津 ");DELETE FROM SPJ WHERE PNO IN
-5-(SELECT PNO FROM P WHERE COLOR=" 红 ");DELETE FROM P WHERE COLOR=" 红 ";四、实验结果 Exe1-1
-6-Exe1-2
-7-Exe1-3 Exe1-4
-8-五、问题与解决（小四号字，宋体）1 ．实验中遇到的问题及解决过程 2 ．实验中产生的错误及原因分析 首先写出执行语句不成功的时候系统报告的错误信息。然后分析错误原因，并给出解决办法。
六、思考题 回答思考题提出的问题。（小四号字，宋体）七、实验总结 实验体会和收获。
（小四号字，宋体）
-9-实验二：
简单查询和连接查询 一、实验目的 熟练掌握用 SQL 语句实现的简单查询和多个数据表连接查询。
二、实验内容（一）完成下面的简单查询：
①查询所有“天津”的供应商明细； ②查询所有“红色”的 14 公斤以上的零件。
③查询工程名称中含有“厂”字的工程明细。
（二）完成下面的连接查询：
①等值连接：求 s 表和 j 表的相同城市的等值连接。
②自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。
③笛卡尔积：求 s 和 p 表的笛卡尔积。
④左连接：求 j 表和 spj 表的左连接。
⑤右连接：求 spj 表和 j 表的右连接。
三、完成情况 CREATE TABLE S(SNO CHAR(9)PRIMARY KEY, SNAME CHAR(20)UNIQUE, STATUS SMALLINT, CITY CHAR(20));CREATE TABLE P(PNO CHAR(9)PRIMARY KEY, PNAME CHAR(20), COLOR CHAR(20), WEIGHT SMALLINT);CREATE TABLE J(JNO CHAR(9)PRIMARY KEY, JNAME CHAR(20)UNIQUE, CITY CHAR(20));CREATE TABLE SPJ(SNO CHAR(9), PNO CHAR(9), JNO CHAR(9)，-10-QTY SMALLINT, PRIMARY KEY(SNO,PNO,JNO), FOREIGN KEY(SNO)REFERENCES S(SNO), FOREIGN KEY(PNO)REFERENCES P(PNO), FOREIGN KEY(JNO)REFERENCES J(JNO));INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES("S1"," 精益 ",20," 天津 ");INSERT INTO S VALUES("S2"," 盛锡 ",10," 北京 ");INSERT INTO S VALUES("S3"," 东方红 ",30," 北京 ");INSERT INTO S VALUES("S4"," 丰泰盛 ",20," 天津 ");INSERT INTO S VALUES("S5"," 为民 ",30," 上海 ");INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES("P1"," 螺母 "," 红 ",12);INSERT INTO P VALUES("P2"," 螺栓 "," 绿 ",17);INSERT INTO P VALUES("P3"," 螺丝刀 "," 蓝 ",14);INSERT INTO P VALUES("P4"," 螺丝刀 "," 红 ",14);INSERT INTO P VALUES("P5"," 凸轮 "," 蓝 ",40);INSERT INTO P VALUES("P6"," 车轮 "," 红 ",30);INSERT INTO J(JNO,JNAME,CITY)VALUES("J1"," 三建 "," 北京 ");INSERT INTO J
-11-VALUES("J2"," 一汽 "," 长春 ");INSERT INTO J VALUES("J3"," 弹簧厂 "," 天津 ");INSERT INTO J VALUES("J4"," 造船厂 "," 天津 ");INSERT INTO J VALUES("J5"," 机车厂 "," 唐山 ");INSERT INTO J VALUES("J6"," 无线电厂 "," 常州 ");INSERT INTO J VALUES("J7"," 半导体厂 "," 南京 ");INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES("S1","P1","J1",200);INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES("S1","P1","J3",100);INSERT INTO SPJ VALUES("S1","P1","J4",700);INSERT INTO SPJ VALUES("S1","P2","J2",100);INSERT INTO SPJ VALUES("S2","P3","J1",400);INSERT INTO SPJ VALUES("S2","P3","J2",200);INSERT INTO SPJ VALUES("S2","P3","J4",500);INSERT INTO SPJ VALUES("S2","P3","J5",400);INSERT INTO SPJ VALUES("S2","P5","J1",400);INSERT
-12-INTO SPJ VALUES("S2","P5","J2",100);INSERT INTO SPJ VALUES("S3","P1","J1",200);INSERT INTO SPJ VALUES("S3","P3","J1",200);INSERT INTO SPJ VALUES("S4","P5","J1",100);INSERT INTO SPJ VALUES("S4","P6","J3",300);INSERT INTO SPJ VALUES("S4","P6","J4",200);INSERT INTO SPJ VALUES("S5","P2","J4",100);INSERT INTO SPJ VALUES("S5","P3","J1",200);INSERT INTO SPJ VALUES("S5","P6","J2",200);INSERT INTO SPJ VALUES("S5","P6","J4",500);SELECT* FROM S WHERE CITY=" 天津 ";SELECT PNAME FROM P WHERE COLOR=" 红 " AND WEIGHT>14;SELECT* FROM J WHERE JNAME LIKE "% 厂 ";SELECT* FROM S,J WHERE S.CITY=J.CITY;SELECT S.SNAME,J.JNAME,P.PNAME FROM S,J,P,SPJ WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND P.PNO=SPJ.PNO;
-13-SELECT* FROM P,S;SELECT* FROM J LEFT OUTER JOIN SPJ ON(J.JNO=SPJ.JNO);SELECT* FROM SPJ RIGHT OUTER JOIN J ON(SPJ.JNO=J.JNO);四、实验结果 Exe2-1 Exe2-2 Exe2-3
-14-Exe2-4 Exe2-5
-15-Exe2-6 Exe2-7
-16-Exe2-8 五、问题与解决（小四号字，宋体）1 ．实验中遇到的问题及解决过程 2 ．实验中产生的错误及原因分析 首先写出执行语句不成功的时候系统报告的错误信息。然后分析错误原因，并给出解决办法。
六、思考题 回答思考题提出的问题。（小四号字，宋体）
-17-七、实验总结 实验体会和收获。
（小四号字，宋体）
-18-实验三：
分组查询和嵌套查询 一、实验目的 熟练掌握用 SQL 语句实现多个数据表的分组查询和嵌套查询。
二、实验内容（一）分组查询：
1、求各种颜色零件的平均重量。
2、求北京供应商和天津供应商的总个数。
3、求各供应商供应的零件总数。
4、求各供应商供应给各工程的零件总数。
5、求使用了 100 个以上 P1 零件的工程名称。
6、求各工程使用的各城市供应的零件总数。
（二）嵌套查询：
1、in 连接谓词查询：
① 查询没有使用天津供应商供应的红色零件的工程名称。
② 查询供应了 1000 个以上零件的供应商名称。(having)2、比较运算符：求重量大于所有零件平均重量的零件名称。
3、Exists 连接谓词：
① 查询供应 J1 的所有的零件都是红色的供应商名称。
② 至少用了供应商 S1 所供应的全部零件的工程号 JNO。
三、完成情况 CREATE TABLE S(SNO CHAR(9)PRIMARY KEY, SNAME CHAR(20)UNIQUE, STATUS SMALLINT, CITY CHAR(20));CREATE TABLE P(PNO CHAR(9)PRIMARY KEY, PNAME CHAR(20), COLOR CHAR(20), WEIGHT SMALLINT);CREATE TABLE J(JNO CHAR(9)PRIMARY KEY, JNAME CHAR(20)UNIQUE, CITY CHAR(20));
-19-CREATE TABLE SPJ(SNO CHAR(9), PNO CHAR(9), JNO CHAR(9), QTY SMALLINT, PRIMARY KEY(SNO,PNO,JNO), FOREIGN KEY(SNO)REFERENCES S(SNO), FOREIGN KEY(PNO)REFERENCES P(PNO), FOREIGN KEY(JNO)REFERENCES J(JNO));INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES("S1"," 精益 ",20," 天津 ");INSERT INTO S VALUES("S2"," 盛锡 ",10," 北京 ");INSERT INTO S VALUES("S3"," 东方红 ",30," 北京 ");INSERT INTO S VALUES("S4"," 丰泰盛 ",20," 天津 ");INSERT INTO S VALUES("S5"," 为民 ",30," 上海 ");INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES("P1"," 螺母 "," 红 ",12);INSERT INTO P VALUES("P2"," 螺栓 "," 绿 ",17);INSERT INTO P VALUES("P3"," 螺丝刀 "," 蓝 ",14);INSERT INTO P VALUES("P4"," 螺丝刀 "," 红 ",14);INSERT INTO P VALUES("P5"," 凸轮 "," 蓝 ",40);INSERT INTO P VALUES("P6"," 车轮 "," 红 ",30);INSERT
-20-INTO J(JNO,JNAME,CITY)VALUES("J1"," 三建 "," 北京 ");INSERT INTO J VALUES("J2"," 一汽 "," 长春 ");INSERT INTO J VALUES("J3"," 弹簧厂 "," 天津 ");INSERT INTO J VALUES("J4"," 造船厂 "," 天津 ");INSERT INTO J VALUES("J5"," 机车厂 "," 唐山 ");INSERT INTO J VALUES("J6"," 无线电厂 "," 常州 ");INSERT INTO J VALUES("J7"," 半导体厂 "," 南京 ");INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES("S1","P1","J1",200);INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES("S1","P1","J3",100);INSERT INTO SPJ VALUES("S1","P1","J4",700);INSERT INTO SPJ VALUES("S1","P2","J2",100);INSERT INTO SPJ VALUES("S2","P3","J1",400);INSERT INTO SPJ VALUES("S2","P3","J2",200);INSERT INTO SPJ VALUES("S2","P3","J4",500);INSERT INTO SPJ VALUES("S2","P3","J5",400);
-21-INSERT INTO SPJ VALUES("S2","P5","J1",400);INSERT INTO SPJ VALUES("S2","P5","J2",100);INSERT INTO SPJ VALUES("S3","P1","J1",200);INSERT INTO SPJ VALUES("S3","P3","J1",200);INSERT INTO SPJ VALUES("S4","P5","J1",100);INSERT INTO SPJ VALUES("S4","P6","J3",300);INSERT INTO SPJ VALUES("S4","P6","J4",200);INSERT INTO SPJ VALUES("S5","P2","J4",100);INSERT INTO SPJ VALUES("S5","P3","J1",200);INSERT INTO SPJ VALUES("S5","P6","J2",200);INSERT INTO SPJ VALUES("S5","P6","J4",500);SELECT COLOR,AVG(WEIGHT)FROM P GROUP BY P.COLOR;SELECT CITY,COUNT(*)FROM S WHERE CITY=" 北京 " OR CITY=" 天津 " GROUP BY CITY;SELECT SUM(QTY)FROM SPJ;SELECT SNO,SUM(QTY)FROM SPJ GROUP BY SPJ.SNO;SELECT J.JNAME
-22-FROM J WHERE J.JNO IN(SELECT SPJ.JNO FROM SPJ WHERE SPJ.PNO="P1" AND SPJ.QTY>100);SELECT S.CITY,SUM(SPJ.QTY)FROM SPJ,S WHERE S.SNO=SPJ.SNO GROUP BY S.CITY;SELECT J.JNAME FROM J WHERE JNO IN(SELECT JNO FROM SPJ,P WHERE J.CITY!=" 天津 " AND P.COLOR=" 红 ");SELECT J.JNAME FROM J WHERE J.JNO IN(SELECT SPJ.JNO FROM SPJ GROUP BY SPJ.JNO HAVING SUM(SPJ.QTY)>1000);SELECT P.PNAME FROM P WHERE P.WEIGHT>=ANY(SELECT AVG(WEIGHT)FROM P);SELECT SNAME FROM S WHERE NOT EXISTS(SELECT* FROM SPJ WHERE JNO="J1" AND SNO=S.SNO AND NOT EXISTS(SELECT PNO FROM P WHERE PNO=SPJ.PNO AND COLOR=" 红 "));SELECT DISTINCT JNO FROM SPJ SPJX WHERE NOT EXISTS(SELECT* FROM SPJ SPJY WHERE SPJY.SNO="S1" AND NOT EXISTS(SELECT* FROM SPJ SPJZ WHERE SPJZ.SNO=SPJX.SNO AND SPJZ.SNO=SPJY.SNO));四、实验结果 Exe3-1
-23-Exe3-2 Exe3-3
-24-Exe3-4 Exe3-5
-25-Exe3-6 Exe3-7
-26-Exe3-8 Exe3-9
-27-Exe3-10 Exe3-11
-28-五、问题与解决（小四号字，宋体）1 ．实验中遇到的问题及解决过程 2 ．实验中产生的错误及原因分析 首先写出执行语句不成功的时候系统报告的错误信息。然后分析错误原因，并给出解决办法。
六、思考题 回答思考题提出的问题。（小四号字，宋体）七、实验总结 实验体会和收获。
（小四号字，宋体）
-29-实验四：
视图操作和数据控制 一、实验目的 熟练掌握用 SQL 语句实现视图操作和数据控制。
二、实验内容（一）定义如下视图：
① 查询北京的供应商的编号、名称和城市。
② 查询 S1 供应商的所有供应明细。
③ 查询各工程名称使用的各种颜色零件的个数。
查询上面定义的视图。
（二）数据控制：
① 使用 GRANT 把对 S 表查询的权利授予 WangLi。
② 使用 GRANT 把对 P 表查询、插入、修改、删除的权利授予 LiMing。
③ 使用 REVOKE 把 LiMing 对 P 表插入、删除的权利回收。
三、完成情况 CREATE TABLE S(SNO CHAR(9)PRIMARY KEY, SNAME CHAR(20)UNIQUE, STATUS SMALLINT, CITY CHAR(20));CREATE TABLE P(PNO CHAR(9)PRIMARY KEY, PNAME CHAR(20), COLOR CHAR(20), WEIGHT SMALLINT);CREATE TABLE J(JNO CHAR(9)PRIMARY KEY, JNAME CHAR(20)UNIQUE, CITY CHAR(20));CREATE TABLE SPJ(SNO CHAR(9), PNO CHAR(9), JNO CHAR(9), QTY SMALLINT，-30-PRIMARY KEY(SNO,PNO,JNO), FOREIGN KEY(SNO)REFERENCES S(SNO), FOREIGN KEY(PNO)REFERENCES P(PNO), FOREIGN KEY(JNO)REFERENCES J(JNO));INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES("S1"," 精益 ",20," 天津 ");INSERT INTO S VALUES("S2"," 盛锡 ",10," 北京 ");INSERT INTO S VALUES("S3"," 东方红 ",30," 北京 ");INSERT INTO S VALUES("S4"," 丰泰盛 ",20," 天津 ");INSERT INTO S VALUES("S5"," 为民 ",30," 上海 ");INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES("P1"," 螺母 "," 红 ",12);INSERT INTO P VALUES("P2"," 螺栓 "," 绿 ",17);INSERT INTO P VALUES("P3"," 螺丝刀 "," 蓝 ",14);INSERT INTO P VALUES("P4"," 螺丝刀 "," 红 ",14);INSERT INTO P VALUES("P5"," 凸轮 "," 蓝 ",40);INSERT INTO P VALUES("P6"," 车轮 "," 红 ",30);INSERT INTO J(JNO,JNAME,CITY)VALUES("J1"," 三建 "," 北京 ");INSERT INTO J VALUES("J2"," 一汽 "," 长春 ");
-31-INSERT INTO J VALUES("J3"," 弹簧厂 "," 天津 ");INSERT INTO J VALUES("J4"," 造船厂 "," 天津 ");INSERT INTO J VALUES("J5"," 机车厂 "," 唐山 ");INSERT INTO J VALUES("J6"," 无线电厂 "," 常州 ");INSERT INTO J VALUES("J7"," 半导体厂 "," 南京 ");INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES("S1","P1","J1",200);INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES("S1","P1","J3",100);INSERT INTO SPJ VALUES("S1","P1","J4",700);INSERT INTO SPJ VALUES("S1","P2","J2",100);INSERT INTO SPJ VALUES("S2","P3","J1",400);INSERT INTO SPJ VALUES("S2","P3","J2",200);INSERT INTO SPJ VALUES("S2","P3","J4",500);INSERT INTO SPJ VALUES("S2","P3","J5",400);INSERT INTO SPJ VALUES("S2","P5","J1",400);INSERT INTO SPJ
-32-VALUES("S2","P5","J2",100);INSERT INTO SPJ VALUES("S3","P1","J1",200);INSERT INTO SPJ VALUES("S3","P3","J1",200);INSERT INTO SPJ VALUES("S4","P5","J1",100);INSERT INTO SPJ VALUES("S4","P6","J3",300);INSERT INTO SPJ VALUES("S4","P6","J4",200);INSERT INTO SPJ VALUES("S5","P2","J4",100);INSERT INTO SPJ VALUES("S5","P3","J1",200);INSERT INTO SPJ VALUES("S5","P6","J2",200);INSERT INTO SPJ VALUES("S5","P6","J4",500);CREATE VIEW IS_BEIJING AS SELECT SNO,SNAME,CITY FROM S WHERE CITY=" 北京 ";CREATE VIEW IS_SI AS SELECT* FROM S WHERE SNO="S1";CREATE VIEW IS_JNAME_COLOR_QTYT AS SELECT JNAME,COLOR,QTY FROM J,P,SPJ WHERE J.JNO=SPJ.JNO AND P.PNO=SPJ.PNO;GRANT SELECT
-33-ON S TO WANGLI;GRANT SELECT,INSERT,UPDATE,DELETE ON P TO LIMING;REVOKE INSERT,DELETE ON P FROM LIMING;四、实验结果 Exe4-1 Exe4-2
-34-Exe4-3 Exe4-4 Exe4-5
-35-Exe4-6 五、问题与解决（小四号字，宋体）1 ．实验中遇到的问题及解决过程 2 ．实验中产生的错误及原因分析 首先写出执行语句不成功的时候系统报告的错误信息。然后分析错误原因，并给出解决办法。
七、实验总结 实验体会和收获。
（小四号字，宋体）
-36-综合实验：
选课 小型数据库系统的设计与实现 一、需求分析 学生选课的原因每个大学需要这种系统。。
二、概念结构设计 学生 课程 选修 系别 教师 属于 拥有 教授 年龄 学号 性别 专业 姓名 成绩 课时 课程名称 编号 学分 院系 专业 姓名 工号 性别 电话 职称
-37-三、逻辑结构设计（1）建立教师信息表（jiaoshi）列名 数据类型 主外键 是否空值 tno char(8)主键 not null tname Varchar(10)null tsex char(2)null zhicheng char(20)null dept char(20)null tel char(11)null（2）建立选修课程信息表（xuanke）列名 数据类型 主外键 键 是否空值 cno char(8)主键 not null cname char(60)Not null cscore int null ctime int null（3）建立教师授课表（shouke）列名 数据类型 主外键 是否空值 tno char(8)主键 not null cno char(8)null（4）建立院系表（department）列名 数据类型 主外键 是否空值 dno char(8)null professional Varchar(10)主键 not null（5）建立学生信息表（xuesheng）列名 数据类型 主外键 是否空值 sno char(12)主键 not null sname Varchar(10)null sex char(2)null age char(2)null professional char(40)外键 not null（6）建立查询分数表（score）列名 数据类型 主外键 是否空值 sno char(12)主键 not null cno Varchar(10)null
-38-score char(2)主键 not null（7）学生与选课关系表（xkhexuan）列名 数据类型 主外键 是否空值 sno char(12)主键 not null Cno char(4)null 四、物理结构设计 create table jiaoshi(tno char(8)primary key not null, tname varchar(10), tsex char(2)default"女", zhicheng char(20), dept char(20), tel char(11))create table xuanke(cno char(8)primary key not null, cname char(60)not null, cscore int, ctime int)create table shouke(tno char(8)primary key not null, cno char(8))create table department(dno char(30)not null, professional char(40)not null primary key ,)create table xuesheng(sno char(12)primary key not null, sname varchar(10), sex char(2)default"男", age char(2), professional char(40)not null foreign key references department(professional)
-39-)create table score(sno char(12)not null foreign key references xuesheng(sno), cno char(8)not null foreign key references xuanke(cno), score int, primary key(sno,cno))create table xhexuan(sno char(12)primary key not null, Cno char(4))五、数据库的实施和维护 建立 Student 视图（学号，姓名，课程，分数）create view Student as select score.sno,xuesheng.sname,score.cno,xuanke.cname,score.score from xuesheng,score,xuanke where xuesheng.sno=score.sno and xuanke.cno=score.cno group by score.sno,score.cno,xuesheng.sname,score.score,xuanke.cname（1）、选课查询 select xuesheng.sno,sname,professional,xuanke.cno,cname from xuesheng,xhexuan,xuanke where xuesheng.sno=xhexuan.sno and xuanke.cno=xhexuan.cno and xuesheng.sno=201107034109 Groupby xuesheng.sno,sname,xuanke.cno,cname,professional（2）、分数查询 select score.sno,xuesheng.sname,score.cno,xuanke.cname,score.score from xuesheng,score,xuanke where xuesheng.sno=score.sno and xuanke.cno=score.cno group by score.sno,score.cno,xuesheng.sname,score.score,xuanke.cname
-40-（3）、教师信息查询 select jiaoshi.tno,tname,tsex,zhicheng,dept,tel,shouke.cno,cname from jiaoshi,shouke,xuanke where jiaoshi.tno = shouke.tno and xuanke.cno=shouke.cno（4）、删除 delete form xhexuan where sno=201107034101（5）、查询 select xuesheng.sno,xuanke from xuesheng,xhexuan where xuesheng.sno=xhexuan.sno group by xuesheng.sno ,xuanke 六、实验总结 实验体会和收获。
（小四号字，宋体）
-41-实验成绩 评价项目 评分等级 实验一 一 实验二 二 实验三 三 实验四 四 综合实验 验 综合评价 价 独立完成完整的实验内容，结果完全正确，报告内容完整，排版整洁美观，能真实体现实际操作过程及遇到的问题。
A（优秀）完成实验，实验内容较为完整，结果正确，报告内容较为完整，排版较为整洁美观，能体现实际操作过程及遇到的问题。
B（良好）基本完成实验，结果正确，报告内容欠缺，排版较为整洁美观，能体现实际操作过程及遇到的问题。
C（中等）不能独立完成完整的实验内容，结果不真实，报告内容欠缺，排版欠整洁美观，不能体现实际操作过程及遇到的问题。
D（及格）不能按时参加实验或未提交实验报告。
E（不及格）
