数据库复习资料

实验二概念模型及其转化（答案）【实验目的】1.使用E-R图，完成数据库概念模型的设计2.掌握将E-R图转化为关系模式3.数据库的具体实现【课内知识回顾】1.E-R图分别用矩形框、菱形框、椭圆形框来表示实体、关系和属性2.E-R图向关系模型的转换规则：1）每一个实体集转换为一个关系模式2）实体集间的联系转换可以有以下转换方法m:n联系：将一个联系转换为一个独立的关系模式1:1联系:可以与任意一端对应的关系模式合并，只需要在关系中加入另一端的码和联系本身的属性1:n联系：可以与n端对应的关系模式合并，只需要加入1端的码和联系本身的属性3.关系模式如：student（学号，姓名，性别，出生日期）4.两表建立关联的字段，必须要有相同的数据类型【实验内容】一.数据库的设计及实现1.根据以下描述，对数据库进行概念设计，并用E-R图来表示。要求设计一个小型的“电影公司管理系统”的数据库，该系统的情况大致如下：电影数据库涉及如下数据：有多个电影公司，每个电影公司可以制作多部电影，能拥有多名自己的电影演员；每部电影只能由一家电影公司负责拍摄；每部电影都可能有多个演员出演，而每个演员可以出演多部电影。2.根据上题的E-R图，写出该数据库的所有“关系模式”电影公司（公司编号，公司名称，公司负责人，公司电话，公司地址）影片（影片编号，影片名，开拍时间，结束时间，发片时间，公司编号）演员（演员编号，姓名，性别，出生日期，演员类型，公司编号）出演表（影片编号，演员编号，拍摄时间，拍摄地点，备注）影片电影公司演员拍摄拥有出演N11NNM3.创建名为“电影公司管理系统”的数据库，并将上题的“关系模式”以“表”的形式加以实现4.创建各表的主键约束，并文字说明每个表的主键5.为数据库中的各表创建必要的外键约束，并用文字说明表之间的关联情况6.为该数据库创建一张关系图，以自己的姓名命名实验三单表查询1.以T-SQL命令方式完成下面各题：在student表中，查询所有女同学的记录select*fromstudentwhere性别=0将所有“计算机”专业的学生的奖学金增加100元updatestudentset奖学金=奖学金+100where专业=’计算机’在student表中，增加一条记录（09011，李小明，true，01-01-1991，计算机，200,厦门，15159512345）insertintostudentvalues(‘09011’，’李小明’，true，’01-01-1991’，’计算机’，200,’厦门’，’15159512345’)在student表中，增加一个字段，字段名为“入学成绩”，smallint，并为“外语”专业的同学设置入学成绩为400，为“计算机”专业的同学设置入学成绩为450altertablestudentadd入学成绩smallintupdatestudentset入学成绩=400where专业=’外语’updatestudentset入学成绩=450where专业=’计算机’在grade表中，查询出所有成绩不及格的同学的学号select学号fromgradewhere成绩60在grade表中，求出‘C01’这门课程的平均成绩、最高成绩、最低成绩，并为它们设置别名selectavg(成绩)as平均成绩，max(成绩)as最高成绩,min（成绩）as最低成绩fromgradewhere课程号=’C01’在student表中，查询所有“计算机”专业的女同学的奖学金大于200小于1000的学生的姓名，专业，奖学金select姓名，专业，奖学金fromstudentwhere专业=‘计算机’and性别=0and奖学金200and奖学金1000在student表中，学号字段中前两位表示学生的入学时间，要求查询出所有09年入学的所有的学生的学号，姓名，年龄select学号，姓名，year(getdate())-year(出生日期)as年龄fromstudentwhereleft(学号，2)=‘09’在student表中，查询所有姓名只有两个字的学生的学号，姓名select学号，姓名fromstudentwhre姓名like‘__’在student表中，查询出所有姓名中有“平”字的学生的学号，姓名select学号，姓名fromstudentwhere姓名like‘%平%’查询所有修了‘C01’这门课程的学号，姓名selects.学号，姓名fromstudents,gradegwheres.学号=g.学号and课程号=‘C01查询所有1992年出生的学生的信息select*fromstudentwhereyear(出生日期)=’1992’实验四表结构的修改及表数据的管理一、实验目的：1.分别掌握在SSMS及T-SQL语言修改表结构2.掌握使用T-SQL语言为表添加约束3.掌握在SSMS及T-SQL语言实现对表数据的增、删、改二、实验内容：2.从ftp上下载数据库“高校信息管理系统”，并附加改数据库3.在SSMS中，完成以下约束的设置student字段名数据类型约束学号Char(5)姓名Char(8)非空性别Bit出生日期Smalldatetime专业Varchar(40)默认为“计算机系”奖学金Smallint介于60与80之间（包括边界）grade字段名数据类型约束学号Char(5)主键外键，参考student表课程号Char(3)外键，参考course表成绩Decimal(4,1)course字段名数据类型约束课程号Char(3)主键课程号必须以“C”字母打头课程名Varchar(40)课程名必须唯一学时smallint默认为90学时学分smallint4.在SSMS中完成下面各题：为student表增加一条记录学号姓名性别出生日期专业奖学金家庭住址联系电话09001张三true1991-1-1计算机500福州null为表“class”增加一个字段“班长”（char(6)非空）将表“class”中的“班长”字段的数据类型由char(6)改为char(8)5.以T-SQL方式完成以下各题为student表增加一条记录学号姓名性别出生日期专业奖学金家庭住址联系电话09002李四false1991-2-3外语600泉州13600234235insertintostudentvalues('09002','李四',0,'1991-2-3','外语',600,'泉州','13600234235')将表“grade1”中的所有记录添加到“grade”表中insertintogradeselect*fromgrade1由于课程代码输错，要求将“grade”表中的所有“C01”课程改为“C13”updategradeset课程号='C13'where课程号='C03'删除“grade”表中所有成绩在60分-80分（包括边界）的记录deletegradewhere成绩=80and成绩=606.根据下图，完成数据库中各表的外键约束，并为该数据库创建一个关系图7.分析题（请说明原因）在SSMS或使用T-SQL语句，为“grade”表增加记录学号课程号成绩09013C0240问：该操作能否正确执行，为什么？答:因为grade表与student之间建立了外键关系,grade表中的学号要参考student表的学号,而09013这个学号在student表中没有将第2大题的第4小题中的“班长”字段的字符长度仍然改为char(6)，该操作能否正常执行，为什么？答:不行,因为字符长度变少,可能会造成数据的丢失8.以T-SQL命令方式完成下面查询：查询出所有成绩在60分-80分之间的所有成绩信息select*fromgradewhere成绩=80and成绩=60在student表中，查询所有女同学的记录select*fromstudentwhere性别=0在grade表中，求出‘C01’这门课程的平均成绩、最高成绩、最低成绩，并为它们设置别名selectavg(成绩)as平均分,max(成绩)as最高分,最低分=min(成绩)fromgradewhere课程号='C01'在student表中，查询所有“计算机”专业的女同学的奖学金大于200小于1000的学生的姓名，专业，奖学金select姓名,专业,奖学金fromstudentwhere专业='计算机'and性别=0and奖学金=200and奖学金=1000实验五查询综合练习1.在student表中，查询出所有在1月1日出生的学生的姓名、出生日期select姓名,出生日期fromstudentwheremonth(出生日期)='1'andday(出生日期)='1'2.在student表中，查询出各个专业的同学的奖学金的平均值，要求输出专业与平均值select专业,avg(奖学金)as平均奖学金fromstudentgroupby专业3.在grade表中，查询出各门课程中所有及格的成绩的平均值，要求，输出课程号及平均值select课程号,avg(成绩)as平均成绩fromgradewhere成绩=60groupby课程号,成绩4.在grade表中，求出各个同学所修的所有课程的平均成绩，输出平均成绩及格的同学的学号及平均成绩。select学号,avg(成绩)as平均成绩fromgradegroupby学号havingavg(成绩)=605.在student表中，查询出所有奖学金大于200的同学的学号、姓名、奖学金，要求按照学号降序排列。select学号,姓名,奖学金fromstudentwhere奖学金200orderby学号desc6.在student表中，查询出所有“计算机”专业的18岁同学的学号、姓名、出生日期，要求按照出生日期升序排列。select学号,姓名,出生日期fromstudentwhere专业='计算机'andyear(getdate())-year(出生日期)=18orderby出生日期7.在grade表中，查询出修了‘C01’课程且成绩及格的同学的记录，要求先以成绩降序排列，成绩相同的同学再以学号降序排列。select*fromgradewhere课程号='C01'and成绩=60orderby成绩desc,学号desc8.在student表中，求出奖学金最多的两位同学的记录。selecttop2*fromstudentorderby奖学金desc9.在student表和grade表中，查询所有“计算机”专业的女同学“C01”的最高成绩。selecttop1*fromstudentass,gradeasgwheres.专业='计算机'ands.性别='0'andg.课程号='C01'orderby成绩desc10.查询出所有修了“C01”课程且成绩大于50，小于90的同学的学号、姓名及成绩。selects.学号,s.姓名,g.成绩fromstudentass,gradeasgwheres.学号=g.学号andg.课程号='C01'andg.成绩50andg.成绩9011.查询出所有在校同学的成绩情况，要求输出同学的学号，姓名，课程号，成绩（左连接）。selects.学号,姓名,g.课程号,成绩fromstudentassleftjoingradeasgons.学号=g.学号12.查询出各个专业各门课程的成绩的平均值和最高值，并按照专业降序排列。select专业,课程号,avg(成绩)as平均成绩,max(成绩)as最高成绩fromstudent,gradewherestudent.学号=grade.学号groupby专业,课程号orderby专业desc13.查询出修了同一门课程的的同学的人数，要求显示课程号和人数。select课程号,人数=count(学号)fromgradegroupby课程号14.查询出修了“计算机英语”这门课程的同学的学号，姓名。selects.学号,姓名fromstudenta