内蒙古大学 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;

 

=======================================

作业一         作业二          作业三           作业四           作业五

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

AN_drew

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值