实验3《视图与完整性约束》

一、实验目的

学习灵活熟练的进行视图的操作&＃xff0c;认识视图的作用&＃xff1b;掌握完整性控制的方法。
1.熟悉和掌握对数据表中视图的定义操作和SQL命令的使用&＃xff1b;
2.熟悉和掌握对数据表中视图的查询操作和SQL命令的使用&＃xff1b;
3.熟悉和掌握对数据表中视图的更新操作和SQL命令的使用&＃xff0c;并注意视图更新与基本表更新的区别与联系&＃xff1b;
4&＃xff0e;熟悉SQL语言对数据库进行完整性控制的方法。

二、实验内容

&＃xff08;一&＃xff09;以S , C , SC表为基础完成以下视图定义及使用
1&＃xff0e;定义“SSCH”院学生基本情况视图V_SSCH&＃xff1b;
2&＃xff0e;将S&＃xff0c;C&＃xff0c;SC表中学生的学号&＃xff0c;姓名&＃xff0c;课程号&＃xff0c;课程名&＃xff0c;成绩定义为视图V_S_C_G;
3&＃xff0e;将各院学生人数&＃xff0c;平均年龄定义为视图V_NUM_AVG;
4&＃xff0e;将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;
5&＃xff0e;查询平均成绩为90分以上的学生学号、姓名和成绩&＃xff1b;
6&＃xff0e;通过视图V_SSCH&＃xff0c;新增加一个学生记录 (‘S12’,‘YAN XI’,19, ‘SSCH’)&＃xff0c;并查询结果&＃xff1b;
7&＃xff0e;通过视图V_SSCH&＃xff0c;删除学号为“S12”学生信息&＃xff0c;并查询结果&＃xff1b;
8&＃xff0e;将视图V_SSCH中学号为“S12”的学生改名“中南人”。
&＃xff08;二&＃xff09;使用SQL进行数据完整性控制&＃xff1a;包括三类完整性、check短语、constrain子句。

1. 在创建下列关系表时完成如下约束&＃xff1a;定义实体完整性;参照完整性&＃xff08;外码、在删除S中的元组时级联删除SC中相应元组、当更新S中的Sno时同时更新SC中的Sno&＃xff09;;用户定义完整性&＃xff1a;学生年龄<30。
2. 修改S中的约束条件&＃xff0c;学号在100&＃xff0d;1000之间。
   学生关系表S &＃xff1a;

创建课程关系表C &＃xff1a;

创建学生-课程表SC &＃xff1a;

3&＃xff0e;用实验验证当操作违反了完整性约束时&＃xff0c;系统如何处理&＃xff1f;
问题&＃xff1a;外键与参照主键是否一定要相同&＃xff1f;

三、实验方法与实验步骤

&＃xff08;1&＃xff09;实验方法&＃xff1a;
本次实验利用SQL Server 2019和Microsoft SQL Server Management Studio 17实验工具、SQL语言进行对数据库表和视图的基本操作&＃xff0c;实验方案为参考书本知识及网络资料完成实验内容要求。

&＃xff08;2&＃xff09;实验步骤&＃xff1a;

1. 连接到学生-课程数据库
2. 复习有关视图操作的SQL语言命令&＃xff1b;复习有关完整性约束操作的SQL语言命令
3. 定义视图、对视图进行查询、修改&＃xff1b;
4. 进行完整性约束定义、修改&＃xff1b;
5. 进行约束违例验证。

四、实验结果

&＃xff08;一&＃xff09;以S , C , SC表为基础完成以下视图定义及使用
1&＃xff0e;定义“SSCH”院学生基本情况视图V_SSCH&＃xff1b;

create view V_SSCH as
(select * from S where sdept&＃61;&＃39;SSCH&＃39;);select * from V_SSCH;


2&＃xff0e;将S&＃xff0c;C&＃xff0c;SC表中学生的学号&＃xff0c;姓名&＃xff0c;课程号&＃xff0c;课程名&＃xff0c;成绩定义为视图V_S_C_G;

create view V_S_C_G as
(select S.Sno,S.Sname,SC.Cno,SC.grade from S,SC where S.Sno&＃61;SC.Sno);select * from V_S_C_G;


3&＃xff0e;将各院学生人数&＃xff0c;平均年龄定义为视图V_NUM_AVG;

create view V_NUM_AVG as
(select count(*) 各院人数,avg(Sage) 平均年龄 from S group by sdept);select * from V_NUM_AVG;


4&＃xff0e;将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;

create view V_AVG_S_G as
(select count(*) 每个学生选修课程门数,avg(grade) 平均成绩 from SC group by Sno);select * from V_AVG_S_G;


5&＃xff0e;查询平均成绩为90分以上的学生学号、姓名和成绩&＃xff1b;

select S.Sno,S.Sname,SC.grade from S,SC where S.Sno&＃61;SC.Sno and SC.Sno in (select SC.Sno from SC group by Sno having avg(SC.grade)>90);


6&＃xff0e;通过视图V_SSCH&＃xff0c;新增加一个学生记录 (‘S12’,‘YAN XI’,19, ‘SSCH’)&＃xff0c;并查询结果&＃xff1b;

insert into V_SSCH(Sno,Sname,Sage,sdept)
values(&＃39;S12&＃39;,&＃39;YAN XI&＃39;,19, &＃39;SSCH&＃39;);select * from V_SSCH;


7&＃xff0e;通过视图V_SSCH&＃xff0c;删除学号为“S12”学生信息&＃xff0c;并查询结果&＃xff1b;

delete from V_SSCH
where Sno&＃61;&＃39;S12&＃39;;select * from V_SSCH;


8&＃xff0e;将视图V_SSCH中学号为“S12”的学生改名“中南人”。

update V_SSCH
set Sname&＃61;&＃39;中南人&＃39;
where Sno&＃61;&＃39;S12&＃39;;


&＃xff08;二&＃xff09;使用SQL进行数据完整性控制&＃xff1a;包括三类完整性、check短语、constrain子句。

1. 在创建下列关系表时完成如下约束&＃xff1a;定义实体完整性;参照完整性&＃xff08;外码、在删除S中的元组时级联删除SC中相应元组、当更新S中的Sno时同时更新SC中的Sno&＃xff09;;用户定义完整性&＃xff1a;学生年龄<30。

CREATE TABLE S(Sno VARCHAR(20) NOT NULL,Sname VARCHAR(20),Ssex VARCHAR(4),Sage int CHECK (Sage<30),sdept VARCHAR(50),PRIMARY KEY(Sno)
);CREATE TABLE C(Cno VARCHAR(50),Cname VARCHAR(50),Cpno VARCHAR(50),ccredit INT,PRIMARY KEY(Cno)
);create table SC(Sno varchar(20),Cno varchar(50),grade int,primary key(Sno,Cno),foreign key(Sno) references S,foreign key(Cno) references C
);


2. 修改S中的约束条件&＃xff0c;学号在100&＃xff0d;1000之间。

alter table S add constraint CK_S_Sno check(Sno>100 and Sno<10000);


3&＃xff0e;用实验验证当操作违反了完整性约束时&＃xff0c;系统如何处理&＃xff1f;
例如&＃xff1a;当S、SC、C表数据如下时&＃xff1a;



①在S表中插入一条数据&＃xff1a;

insert into S(Sno,Sname,Ssex,Sage,sdept)values(820690305,&＃39;yzy&＃39;,&＃39;男&＃39;,20,&＃39;软件工程系&＃39;);


结果如上图所示&＃xff0c;Sno的值在插入过程违背了用户定义完整性&＃xff0c;插入失败&＃xff1b;

②在SC表中插入一条数据&＃xff1a;

insert into SCvalues(0310,&＃39;2-5&＃39;,100);


结果入上图所示&＃xff0c;0310的Sno在表S中并不存在&＃xff0c;而Sno又是SC参照S的外键&＃xff0c;违背了参照完整性。

问题&＃xff1a;外键与参照主键是否一定要相同&＃xff1f;
答&＃xff1a;外键与参照主键不一定要相同。

五、实验小结

通过本次实验主要是对视图的操作更加熟练了&＃xff0c;能够对完整性进行控制。
  遇到的问题主要有&＃xff1a;
  ①对于各类完整性的约束定义不太清楚&＃xff1b;
  解决方式&＃xff1a;查阅网络资料&＃xff0c;复习老师上课所讲内容。
  ②如何设置实验验证完整性定义是否成功。
  解决方式&＃xff1a;通过完整性本身定义&＃xff0c;对表进行违背完整性的操作。
  有待改进&＃xff1a;部分操作可以有多种方式&＃xff0c;可以进行比较然后选择择优操作。