内蒙古大学 2017-2018《数据库原理与应用A》实验指导书 作业一
（本文中蓝色字体为个人理解，非标准答案，仅供参考）
=================================================================================
作业一 数据库模式设计及建立
一、实验内容及说明
1.实验目的：
理解和掌握数据库 DDL 语言，能够熟练地使用 SQL DDL 语句创建、修改和删除数据库、模型和基本表，对表中数据进行更新操作。
2.实验内容和要求：
理解和掌握 SQL DDL 语句和更新操作语句的语法，特别是各种参数的具体含义和使用方法；使用 SQL 语句创建、修改和删除数据库、模式和基本表。
3.实验重点和难点：
实验重点：创建数据库、基本表。
实验难点：创建基本表时，为不同的列选择合适的数据类型，正确创建表级和列级完整性约束，如列值是否允许为空、主码和外码等。注意：数据完整性约束，可以在创建基本表时定义，也可以先创建表然后定义完整性约束；由于完整性约束的限制，被引用的表要先创建。
二、实验步骤
（一）熟悉上机环境和 sqlplus 中的各种操作命令
（二）数据库模式建立
1、建立学生关系模式
2、建立课程关系模式
3、建立选课关系模式
4、建立教师关系模式
5、建立系别关系模式
（三）SQL 对数据的 DDL 操作
1. 熟悉上机环境和 sqlplus 中的各种操作命令。
2. 用 SQL 语言对上述 1,2,3,4,5 五个表进行建立（注意，建表顺序不一定是1,2,3,4,5）。
要建立这5张表，必须首先分析出建表的先后顺序。
由于外码的存在，如果不按顺序建表，就会违反参照完整性，在SQL中的错误就是"未找到父项关键字"。
————————————————————————————————————————————————
1、对 学生表(Student) 分析
表中最后一行属性 所在系(sdno) 是外码，含义是这名学生的所在系。
那么哪张表还有学生的所在系信息呢？
纵观5张表，只有 系别表(Dept)的 系编号(dno) 名称与 所在系(sdno)接近
而且数据类型都是char，长度都是10，
系编号(dno)是 系别表(Dept)的主码， 所在系(sdno)是 学生表(Student)的外码。
所以外码—— 所在系(sdno)的被参照表是 系别表(Dept)，参照表是 学生表(Student)
结论1：应该先建立系别表(Dept)，后建立学生表(Student)，含义是先建立系，才能招收学生
————————————————————————————————————————————————
2、对 课程表(Course) 分析
表中第三行属性 教师(ctno) 是外码，含义是这门课程的授课教师。
那么哪张表还有课程的授课教师信息呢？
纵观5张表，只有 教师表(Dept)的 教师号(tno) 名称与 教师(ctno)接近，
而且数据类型都是char，长度都是10，
教师号(tno)是 教师表(Teacher)的 主码， 教师(ctno)是 课程表(Course)的外码。
所以外码—— 教师(ctno)的被参照表是 教师表(Teacher)，参照表是 课程表(Course)
结论2：应该先建立教师表(Teacher)，后建立课程表(Course)，含义是先有教师，才能授课
—————————————————————————————————————————————
3、对 选课表(SC) 分析
因为表中有两个外码，所以先分析学号(sno)，后分析课程号(cno)
I：先分析学号(sno)
表中第一行属性 学号(sno) 是外码，含义是选了课的这名学生的学号。
那么哪张表还有学生的学号信息呢？
纵观5张表，只有 学生表(Student)的 学号(sno) 名称与 学号(sno)相同，
而且数据类型都是char，长度都是6，
学号(sno)是 学生表(Student)的 主码， 学号(sno)是 选课表(SC)的外码。
所以外码—— 学号(sno)的被参照表是 学生表(Student)，参照表是 选课表(SC)
结论3-1：应该先建立学生表(Student)，后建立选课表(SC)，含义是先有学生，才能选课
-----------------------------------------------------------------------------------------------------------------
Ⅱ：后分析课程号(cno)
表中第二行属性 课程号(cno) 是外码，含义是这名学生选了哪门课。
那么哪张表还有课程信息呢？
纵观5张表，只有 课程表(Course)的 课程号(sno) 名称与 课程号(sno)相同，
而且数据类型都是char，长度都是4，
课程号(sno)是 课程表(Course)的 主码， 课程号(sno)是 选课表(SC)的外码。
所以外码—— 课程号(sno)的被参照表是 课程表(Course)，参照表是 选课表(SC)
结论3-2：应该先建立课程表(Course)，后建立选课表(SC)，含义是先有课程，才能选课
综合结论3-1 与 结论3-2
结论3-1：应该先建立学生表(Student)，后建立选课表(SC)，含义是先有学生，才能选课
结论3-2：应该先建立课程表(Course)，后建立选课表(SC)，含义是先有课程，才能选课
得出结论3
结论3：应该先建立课程表(Course)和学生表(Student)，后建立选课表(SC)，含义是先有课程和学生，才能选课
———————————————————————————————————————————————
4、对 教师表(Teacher) 分析
表中最后一行属性 所在系(sdno) 是外码，含义是这名教师的所在系。
那么哪张表还有教师的所在系信息呢？
纵观5张表，只有 系别表(Dept)的 系编号(dno) 名称与 所在系(sdno)接近
而且数据类型都是char，长度都是10，
系编号(dno)是 系别表(Dept)的主码， 所在系(sdno)是 教师表(Teacher)的外码。
所以外码—— 所在系(sdno)的被参照表是 系别表(Dept)，参照表是 教师表(Student)
结论4：应该先建立系别表(Dept)，后建立教师表(Student)，含义是先建立系，才能招聘教师
—————————————————————————————————————————————————
5、对 系别表(Dept) 分析
因为系别表(Dept)没有外码，所以不会违反参照完整性。
结论5：应该最先建立系别表(Dept)，以便被其他表参照
—————————————————————————————————————————————
综合结论1到结论5：
结论1：应该先建立系别表(Dept)，后建立学生表(Student)，含义是先建立系，才能招收学生
结论2：应该先建立教师表(Teacher)，后建立课程表(Course)，含义是先有教师，才能授课
结论3：应该先建立课程表(Course)和学生表(Student)，后建立选课表(SC)，含义是先有课程和学生，才能选课
结论4：应该先建立系别表(Dept)，后建立教师表(Student)，含义是先建立系，才能招聘教师
结论5：应该最先建立系别表(Dept)，以便被其他表参照
得出建表顺序：
①系别表(Dept)->②教师表(Teacher)->③课程表(Course)->④学生表(Student)->⑤选课表(SC)
SQL语言建表： （Oracle 10g测试通过）
create table dept /*创建系别表*/
(
dno char(10),
dname char(15) constraint dept_C_dname not null, /*not null只能是列级约束，非空属性dname的列级完整性约束名是dept_C_dname*/
constraint dept_P_dno primary key(dno) /*最后一行没有逗号，主码dno的表级实体完整性约束名是dept_P_dno*/
);create table teacher /*创建教师表*/
(
tno char(10),
tname char(8) constraint teacher_C_tname not null, /*not null只能是列级约束，非空属性tname的列级完整性约束名是teacher_C_tname*/
tsex char(2),
tage smallint,
prof char(10),
tdno char(10),
constraint teacher_P_tno primary key(tno), /*主码dno的表级实体完整性约束名是teacher_P_tno*/
constraint teacher_R_tdno foreign key(tdno) references dept(dno) /*最后一行没有逗号，外码tdno的表级参照完整性约束名是teacher_R_tdno*/
);create table course /*创建课程表*/
(
cno char(4),
cname char(20),
ctno char(10),
ccredit smallint,
constraint course_P_cno primary key(cno), /*主码cno的表级实体完整性约束名是course_P_cno*/
constraint course_R_ctno foreign key(ctno) references teacher(tno) /*最后一行没有逗号，外码ctno的表级参照完整性约束名是course_R_ctno*/
);create table student /*创建学生表*/
(
sno char(6),
sname char(8),
ssex char(2),
sage smallint,
sdno char(10),
constraint student_P_sno primary key(sno), /*主码sno的表级实体完整性约束名是student_P_sno*/
constraint student_R_sdno foreign key(sdno) references dept(dno) /*最后一行没有逗号，外码sdno的表级参照完整性约束名是student_R_sdno*/
);create table sc /*创建选课表*/
(
sno char(6),
cno char(4),
grade smallint,
constraint sc_P_sno_cno primary key(sno,cno), /*主码(sno,cno)的表级实体完整性约束名是sc_P_sno_cno*/
constraint sc_R_sno foreign key(sno) references student(sno), /*外码sno的表级参照完整性约束名是sc_R_sno*/
constraint sc_R_cno foreign key(cno) references course(cno) /*最后一行没有逗号，外码cno的表级参照完整性约束名是sc_R_cno*/
);3. 用“desc <表名>;” 命令检查所构建表的表结构是否正确。
desc dept;
desc teacher;
desc course;
desc student;
desc sc;
4. 用“select * from user_tables;” 命令检查当前用户所建表情况是否正确。
select * from user_tables;5. 用“select * from user_constraints;”命令检查所构建表的约束是否正确。
熟悉各约束定义，R: foreign key，P: primary key， C: Not Null 或 Check，U: Unique
select * from user_constraints;6. 给 Student 表增加一个地址(address，长度为 10 的字符串)属性。
alter table student add( address char(10) );执行后，需要用desc，查看表结构是否增加上address属性
desc student;
7. 将 Student 表地址(address)数据类型改为长度为 13 的字符串。
alter table student modify( address char(13) );执行后，需要用desc，查看表结构是否将address属性的数据类型改为char(13)
desc student;
8. 给 Course 表增加一个开课学期（term 数据类型 smallint）属性。
alter table course add( term smallint );执行后，需要用desc，查看表结构是否增加上term属性
desc course;
（四）SQL 对数据的更新操作
1. 在 Dept 表中输入以下5条合法记录。
insert into dept values('001','cs');
insert into dept values('002','is');
insert into dept values('003','ma');
insert into dept values('004','ea');
insert into dept values('005','sw');2. 在 Teacher 表中输入以下7条合法记录。
insert into teacher values('70001','李勇','男',46,'教授','001');
insert into teacher values('74005','任白','男',42,'副教授','003');
insert into teacher values('80003','李明','女',36,'讲师','003');
insert into teacher values('72004','王华平','女',50,'教授','005');
insert into teacher values('74036','陈刚','男',42,'教授','001');
insert into teacher values('79058','张雪','女',36,'副教授','004');
insert into teacher values('74025','苏红','女',36,'副教授','002');
3. 在 course 表中输入以下6条合法记录。
因为course表增加了term属性，所以插入值时最后一个属性term赋空值null
insert into course values('1001','数据库','70001',4,null);
insert into course values('2002','数学','80003',2,null);
insert into course values('1004','操作系统','74036',3,null);
insert into course values('1005','数据结构','70001',4,null);
insert into course values('3001','英语','79058',3,null);
insert into course values('5012','生物信息学','72004',5,null);
4. 在 student 表中输入以下8条合法记录。
因为student表增加了address属性，所以插入值时最后一个属性address赋空值null
insert into student values('209001','李勇','男',20,'001',null);
insert into student values('209002','刘晨','女',19,'002',null);
insert into student values('209003','王敏','女',18,'004',null);
insert into student values('209004','张立','男',19,'003',null);
insert into student values('209005','赵强','男',21,'002',null);
insert into student values('209006','陈伟','男',19,'001',null);
insert into student values('209007','王珊','女',21,'005',null);
insert into student values('209008','张华','男',19,'001',null);
5. 在 SC 表中输入以下10 条合法记录。(注意，有些课程可有多人选，有些课程可能没人选)
insert into sc values('209001','1001',92);
insert into sc values('209001','2002',85);
insert into sc values('209001','1005',88);
insert into sc values('209002','2002',90);
insert into sc values('209002','1005',80);
insert into sc values('209003','2002',67);
insert into sc values('209003','3001',56);
insert into sc values('209004','1001',87);
insert into sc values('209004','1005',69);
insert into sc values('209005','1001',82);
insert into sc values('209006','3001',87);
insert into sc values('209007','1004',65);
insert into sc values('209008','1001',84);
6. 用“SELECT * FROM <表名>;”命令检查表中数据的正确性。
select * from dept;select * from teacher;select * from course;select * from student;select * from sc;7. 给 student 表建立索引 index_name，按 sname 升序。
create index index_name on student(sname);用select * from user_indexes查询创建的索引
8. 建立视图 view1，列出学生选课情况，要求有 sname,cname,grade 三个字段。
create view view1 as
select sname,cname,grade
from student,course,sc
where student.sno=sc.sno
and course.cno=sc.cno;用select * from user_views;查询创建的视图
用select * from view1;查询视图view1
9. 建立视图 view2，列出学生基本信息，要求有 sno,ssex,sage,birthday 四个字段。
create view view2 as
select sno,ssex,sage,to_char(sysdate,'yyyy')-sage birthday /*也可以直接用年份-sage求出生年份，如2018-sage birthday*/
from student; 用select * from user_views;查询创建的视图
用select * from view2;查询视图view2
10. 建立“cs”系学生成绩视图 JSGV（sno,cno,grade）。
create view JSGV as
select sc.sno,cno,grade
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs';用select * from user_views;查询创建的视图
用select * from JSGV;查询视图JSGV
（五）简单的检查和调试语句
1. 用“select * from user_indexes;”检查当前用户建立索引情况是否正确。
select * from user_indexes;2. 用“select * from user_views;” 检查当前用户建立视图情况是否正确。
select * from user_views;3. 用“select * from user_tables;” 检查当前用户建立表情况是否正确。
select * from user_tables;4. 用“select * from user_constraints;” 检查当前用户建立表情况是否正确。
select * from user_constraints;5. 用“desc”检查当前表的表结构。
desc dept;
desc teacher;
desc course;
desc student;
desc sc;
6. 用“show user”显示当前用户。
show user;