2014广工数据库实验报告

数据库原理实验报告专业计算机科学与技术实验三题目创建和使用视图、索引、存储过程实验一数据库及基本表的建立一、实验目的student_course表(学生选课成绩表)、teacher_course表(教师上课课表)等。题目1、创建数据库：数据库用于学生管理，命名为SC要求：数据文件和日志文件分别存储在E盘自己的目录下。根据实际的数据量确定数据文件的初始大小为30MB,日志文件的初始大小为3MB。根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。实现代码及截图：创建数据库SQL语言实验结果截图显示题目2、创建基本表创建各表的实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示1.创建student表s_nochar(8)primarykey,snamechar(8)notnull,sbirthdaysmalldatetime,dnochar(6),spnochar(8),class_nochar(4),constraintfk_student_dnoforeignkey(dno)referencesdepartment(dno),constraintfk_student_spnoforeignkey(spno)referencesspeciality(spno))2.创建course表CREATETABLEcourse(cnochar(10)primarykey,ctnotinyint,credittinyint,constraintfk_course_spnoreferencesspeciality(spno)实验结果截图显示列名sbirthday数据类型char(8)char(8)char(2)smalldatetimechar(6)char(8)char(4)77允许空允许空数据类型允许空数据类型char(20)char(8)tinyinttinyinttinyinttinyinttinyintWexperimentWsemestercredit3.创建student_course表CREATETABLEstudent_course(S_nochar(8),tcidsmallint,scoretinyint,constraintpk_student_courseprimarykey(s_no,tcid),constraintfkstudentcoursesnoreferencesstudent(sno)ondeletecascade)4.创建teacher表CREATETABLEteacher(t_nochar(8)primarykey,t_sexchar(2),t_birthdaysmalldatetime,dnochar(6),techtitlechar(10).constraintfk_teacher_dnoreferencesdepartment(dno))5.创建department表CREATETABLEdepartment(dnochar(6)primarykey,dept_namechar(20)notnull,headerchar(8))6.创建speciality表CREATETABLEspeciality(spnochar(8)primarykey,dnochar(6)notnull,constraintfk_speciality_dnoreferencesdepartment(dno)列名数据类型char(8)smallinttinyint允许空7列名?t_not_name数瑞类型char(8)char(8)char(2)允许空t_birthdaysmalldatetimetech_titlechar(10)列名dept_nameheader数据类型char(6)char(20)char(8)允许空着着列名数据类型充许空7.创建teachercourseCREATETABLEteachercourse(tcidsmallintprimarykey,constraintfk_teacher_courset_noforeignkey(t_no)referencesteacher(t_no),constraintfk_teacher_course_spnoreferencesspeciality(spno),constraintfk_teacher_course_cnoreferencescourse(cno))8.创建class表CREATETABLEclass(class_nochar(4),constraintpk_classprimarykey(spno,class_no),referencesspeciality(spno)列名dlass_nosemestershcoolyear数据类型smallintchar(8)char(8)char(4)char(10)char(6)char(10)通通允许空eachpteu?spnodass_noheader数据类型char(8)char(4)char(8)日日允许空题目3、查看各数据表之间的关系，生成数据库关系图。shcoolyear题目4、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示1.插入department表数据department(dno,dept_name,header)values('xy01','计算机学院',张小辉)department(dno,dept_name,header)values('xy02',机电学院!;刘小华)department(dno,dept_name,header)department(dno,dept_name,header)values('xy04',外国语学院',彭小彦)department(dno,dept_name,header)2.插入speciality表数据INSERTINTOspeciality(spno,dno,spname)values(zy01001','xy01,网络工程)INSERTINTOspeciality(spno,dno,spname)INSERTINTOspeciality(spno,dno,spname)values(zy01003',xy01',计算机科学与技术)INSERTINTOspeciality(spno,dno,spname)values('zy03001',xy03',通信工程)INSERTINTOspeciality(spno,dno,spname)3.插入class表数据INSERTINTOclass(spno,class_no,header)INSERTINTOclass(spno,class_no,header)INSERTINTOclass(spno,class_no,header)INSERTINTOclass(spno,class_no,header)INSERTINTOclass(spno,class_no,header)实验结果截图显示(插入数据的表格)表-dbo,departmentWIN-DPC47M5LOC8.mednodeptnameheader米张小辉刘小华张小辉刘小华梁小伟彭小彦吴小祖NULLxy02xy03xy04xy05NULL机电学院信息工程学院外国语学院应用数学学院NULL表-dbo.departmentWIN-DP▶Zy010orxy01网络工程zy01002xy01软件工程zy01003xy01计算机科学与技术zy03001Xy03通信工程zy04001xy04国际商务米NULLNULLNULL表-dbo.class表-dbo.speciality表-dbi▶米zy01001zy01002zy03001Zy04001NULLNULL黄小勃周小伦王小宏陈小迅萧小腾NULL4.插入student表数据素-dbo.atudent#-dho.clas#,dbo.Peciality素，dhodistudent(s_no,sname,sex,sbirthday,dno,spno,class_典典典典成小龙称小离学外满盘小擅女更女INSERTINTORLNEAM&INRLstudent(s_no,sname,sex,sbirthday,dno,spno,class_student(s_no,sname,sexsbirthday,dno,spno,class_student(s_no,sname,sexsbirthday,dno,spno,classpartmentWIN-DPC47M5LOC8.master-ScNstudent(s_no,sname,sex,sbirthday,dno,spno,class_小t5.插入course表数据course(cno,cname,spno,ctno,lecture,experiment,se内mester,credit)values('kc001',数据结构course(cno,cname,spno,ctno,lecture,experiment,secourse(cno,cname,spno,ctno,lecture,experiment,semestercredit)values(kc003’,'计算机组成原理表-dbo.course表-dbo.student表-dbo.classnonameSDNO▶米ka002数据结构数据库原理Zy01001kd003计算机组成原理zy01001ka009信号与系统zy03001kc015商务翻译zy04001NULLNULLNULLospeoythe0和tmae4惊毒黑212course(cno,cname,spno,ctno,lecture,experiment,seNLMMWW1献course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values('kc015',商务翻译values(3002001',101,80)INSERTINTOstudent_course(s_no,tcidscore)values(3002002',102,90)INSERTINTOstudent_course(s_no,tcidscore)INSERTINTOstudent_course(s_no,tcidscore)values(3002004',102,75)INSERTINTOstudent_course(s_no,tcidscore)7.插入teacher表数据INSERTINTOteacher(t_no,t_name,t_sext_birthday,dno,tech_INSERTINTOteacher(t_no,t_name,t_sext_birthday.dno,tech_INSERTINTOteacher(t_no,t_name,t_sext_birthday,dno,tech_e)values(2002003''柯小腾!,男INSERTINTOteacher(t_no,t_name,t_sext_birthday,dno,tech_INSERTINTOteacher(t_no,t_name,t_sext_birthday,dno,tech_8.插入teacher_course表数据INSERTINTOter,shcoolyear)INSERTINTO▼▶米teacheer-coe-Roc?0281010解hemO1n1排士新的择性2092000200200*2002004辆子紫手20020012/010011001kct015MA4Z&MRter,shcoolyear)ter,shcoolyear)ter,shcoolyear)共8组(共8个表)实现代码：查询分析器执行情况：SQL语句及执行结果显示SETsex='男'student表更改前后的内容截图显示素-dbo.atudent·dhocims男女重女cims#,dbo.peciality事.dhodi1994/0140:00.001994/11/10:00:001995/W160-00:001995/12/120:00:001993/6/100:00:00WRLJ002005NUA主子犀成小龙秒小熟学小需盘小得ATR毒·dbo,student基子都用3002003称小康女LLARLWIHDPC47MSLOCB.SC-5QLQueryLiqPWIN1994/11/8.0-V01m0100110021991212.0po3001100119231610.0。*4040041011001MLLMN/MRARL查询分析器执行情况：',1994-08-04','xy01'student表更改前后的内容截图显示表-dbo.studentWiNOR4?M5LCX#Que2N*王子器界19943002002抵小意册199/14/80.n01h01011002300200料十惠★1995W160.no1ho100?10013002004李小奥1995/12/12-603y0300110012002005盘小常闻190/V10.0.60440400110013002008王小器男1994/8/40.no1Y01001100W·000.ataoentn王德中重WNmW奖类肃)0AK0001402.建这8个表的先后顺序问题。由于主外键的约束，建表的顺序不能随便调乱，要先理清了答：表(table)为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名作为数据库中的数据存储单位，存储在数据文件中。答：应注意每一个数据的类型，长度。按照建表时每个数据的类型来插入数据时，当实验中有多个表且表间存在外键约束时，应该注意先对被依赖的表进行数据插入。注意表中各个属性的约束，例如主键、非空、唯一等约束。答：为了数据表中的属性添加列级或表级约束条件，例如主键、非空、唯一等。如果发现违WIN-DPC47M5LOC8.SC-SQLQuery3.sql*WIN-DPC47M5LOC8.master-SC.scADDCONSTRAINTU2|UNIQUE(spname)INSERTINTOspeciality(spno,dno,spname)values('zy11100','xy01','网络工程')I消息违反了UNIquEREY约束`u2'。不能在对象'dba.speciality’语句已终止。中插入重复键。7、如何对student_course表的score列设置检查约束?如何通过插入新的记录来检验检查约束?WIN-DPC47M5LOC8.SC-SQLQuery3.sql*WIN-DPC47M5LOC8.master-SC.sgl表-dbo.student表-dboADDCONSTRAINTUCHECK(ScoreBETWEEN0AND100)INSERTINTOstudentcourse(sno,tcid,score)values('3002008',101,180) H消息消息547,级别16,状态0,第4行INSERT语句与CHECK约束"U1"冲突。该冲突发生于数据库"Sc",表"dbo.student_course",coiumn'score'。8、怎样来实现“允许空值”?设置“允许空值”时应注意哪些问题?实验二、查询数据库实验项目性质：基础型实验计划学时：2一、实验目的包括简单查询、嵌套查询、连接查询等基本实现方法，掌握存储查询结果的方法，体会各种三、实验主要仪器设备和材料四、实验方法、步骤及结果测试1、对实验一建立的数据库表进行查询查询分析器执行情况：SQL语句及执行结果显示SELECTs_no学号，sname姓名.birthday出身年月查询结果截图显示围结果消息学号姓名出身年月13002001王子昂1994-08-040023002002成小龙1994-11-0800:00:0033002003朴小惠1995-08-1600:00:0043002004李小镐1995-12-1200:00:0053002005金小贤1993-06-1000:00:00(2)、查询全体学生的学号、姓名·年龄查询分析器执行情况：SQL语句及执行结果显示SELECTs_no学号，sname姓名。查询结果截图显示画结果画结果消息学号姓名年龄王子昂20成小龙20朴小惠19李小镐19金小贤21(3)、查询院系编号为'xy01'(计算机学院)的全部学生的学号、姓名。性别和出身年月实现代码及查询结果截图：SQL语句及执行结果显示SELECTsno学号sname姓名。。ex性别，sbirthday出身年月WHEREdno='xy01'查询结果截图显示结果消息学号姓名十了13002001王子昂男1994-08-040023002002成小龙男1994-11-0800:00:0033002003朴小惠女1995-08-1600:00:00(4)、查询“网络工程”专业(专业代码为‘zyO1001’)并且班级代码为‘1001’的实现代码及查询结果截图：SQL语句及执行结果显示年月查询结果截图显示结果结果消息 学号姓名出身年月王子昂成小龙12(5)、查询在‘1988-05-09’以后出生的计算机学院(院系编号为‘100001’)学生的学号、查询分析器执行情况：SQL语句及执行结果显示别，sbirthday出身年月WHEREdno='xy01'ANDsbirthday>'1988-05-09'查询结果截图显示123学号姓名性别出身年月 300200单击市器择警1994-08-0400:00:0030020023002002xJ3002003朴小惠女1995-08-1600:00:00(6)、查询全部学生的学号、姓名。性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：别，sbirthday出身年月查询结果截图显示雨结果消息 学号姓名性别出身年月 13002005金小贤男1993-06-1000:00:0043002003朴小惠女1995-08-1600:00:连接查询：(1)、查询全部学生的学号、姓名。性别、所在院系名称和专业名称实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECTs_no学号，sname姓名·ex性别，department.dept_name院校，speciality.spname专业FROMstudent,department,specialityWHEREstudent.dno=department.dnoANDstudentspno=speciality.spno;查询结果截图显示王子昂男计算机学院网络工程1234512345朴小惠女计算机学院计算机学院信息工程学院外国语学院网络工程软件工程通信工程国际商务(2)、查询选修了课程101(上课编号)的学生的学号、姓名。专业名称和这门课的成绩查询分析器执行情况：SQL语句及执行结果显示SELECTstudentsno学号，sname姓FROMstudentstudent_course,specialityWHEREstudent.s_no=studentcourse.snoANDstudentspno=speciality.spnoANDstudent_couse.tcid=101;查询结果截图显示123学号姓名王子昂朴小惠金小贤专业网络工程软件工程国际商务成绩实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECTstudents_no学号，student.sname姓FROMstudentstudent_course,course,teacher_courseWHEREstudent.s_no=student_course.s_noANDteacher_course.tcid=student_course.tcidANDo=teacheroANDscore<60;查询结果截图显示学号姓名不及格课程没有不及格课程·现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECTs_no学号，sname姓名WHEREsnonotir)查询结果截图显示123学号300200130020033002005姓名王子昂朴小惠金小贤(2)、查询每门课都是80分以上的学生的学号与姓名。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECTsno学号：sname姓名)查询结果截图显示 学号成小龙朴小惠金小贤300200230020033002005姓名122、选用Northwind数据库进行查询(1)、对NothWind.Products表进行简单查询；在查询分析器在窗口下实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECT*FROMProducts1?11角mAngtdfweieCnatAoin'iCmunBedChetAmesDmeeMGesnims'boswtemhessUbusCenebudPaNeCiyMalyfnteuGiiaaiCtesCueenMwchegoaFntwPteA=M=TangnsCnadmaw5BslnepsheminsesnekoseOitahkseyTum=DumsntfesnbetMAuCe)NiucatUneGamiGummeimrenBafogaTcnutdoRAaSauitssuinmynrPoummrtOtMnsrangdurginaulafaeswexgnefsneaG#tusAHimeepetinsa4oaoo1协亮用见属才高责容一位具者是高sat0mt4-2waT-te4用发侧0406097.00器刚间mtt=热甜i强晋道17相强晋道#校别朝加t4Bere言A20y管地管地m查询单价(UnitPrice)小于20的Products;实现代码及查询结果截图：查询分析器执行情SQL语句及执行结果显示SELECT*FROMProducts查询结果截图显示(有所省略)2数寒2*辩*偏钟24钟测辅非解0钟料算例4建加有有容的而但的形得①名风用的作居制有自自的的而自有有有相属成为容作用百者言死三。有有容的而但的形得①名风用的作居制有自自的的而自有有有相属成为容作用百者言死三。的a为而0的而颌00而常a00的而的6的e用的0而的的的的的Y的ws45Mhmnmfmnc*5Aie#GF查询Products中最高单价(UnitPrice)是多少；实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECTMAX(UnitPrice)最高单价FROMProducts;查询结果截图显示查询结果截图显示FROMProduct结果消息最高单价(2)、在查询分析器在窗口下用SELECT语句完成连接(嵌套)查询：查询分析器执行情况：SQL语句及执行结果显示SELECTProducts.ProductID,Products.ProductNameFROMProductsWHEREProducts.ProductIDIN(SELECT[OrderDetails].ProductID查询结果截图显示(有所省略)rost125满thoducsChgOogAnimeSepDaAren'sCaimSeongCetAid*Oie0oMGmndhonenbrySetUhbeyOrpeeDrndA**00CmreeSuMhhKotskuAusQaieCabOhiesoManesogelaFasesKenhOuvuefheuysAMmonCmrmvonTyFeannsOhociatsfiisoySeoeOusahlosXashthtetOiiarifsidstonmNNngCemCmbCmmeeShog?ShokcieRSentThuingeRenatsrstdoryoneeTuleMsscarpomfaieiSs*pakhShnelveAih查询所有被CustomerID为AROUT客和订购过的ProductsID和ProductName;实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示SELECTDISTINCTP.ProduONP.ProductID=OD.ProductWHEREO.CustomerlD='AROUT'查询结果截图显示日础第衣着器比的进者意白言第常苦或容军身色见良在日2衣着器比的进者意白言第常苦或容军身色见良在日5RcdhmOars*FaxH和#ronnc#eeeCmeeedALhugegt*gnAegetee#g=查询所有被CustomerID为AROUT客和订购过，且单价在20以上的ProductsID实现代码及查询结果截图：查询分析器执行情况：SELECTDISTINCTP.ProductID,P.ProductNameWHEREPProductID=OD.ProductIDANDOD.OrderlD=0.Ord国结果消息 查询分析器执行情况：SQL语句及执行结果显示SELECT*FROMProductsWHEREUnitPrice=(SELECTMAX(UnitPrice)ProductsProduaProductNinmeSuppbeCatQuanntyPertJnitUntPniUnitsinStnLinrtsOnO0ReorderteDigcontG与其自身进行连接，要为表取两个不同的别名。外连接：在连接中把舍弃的元组也保存在答：注意所使用的连接查询是否符合要求。连接双方的属性类型要一样。进行多表连接时，特别注意不要忘记查询条件，特别是连接多个表且记录数目较多时。因为如果不指明连接条(一)、视图题目1:建立“计算机学院”的学生基本情况视图ies_student_view,该视图包括计算机学院所有学生的学号、姓名。性别、出生年月、专业名称。执行ies_student_view视图并观SELECTDISTINCTS.s_no学号，S.sname姓名。sex性别，S.sbirthday出生年月，SP.s专业名称WHEREdept_name='计算机学院')ANDS.spno=SP.spnc查询视图的SQL语句及执行结果截图学号姓者性别出生年月要业名称3002001王子器男1004-00-04.00.0000健江程3002002城小市男1904-11-08.00.00.00替工程K*K2A71WD*T人Wi*-DAC4NmoatusentA55,a*ca11tyAH,rntDmeapirtmat01.in.jha命个已就还先A。题目2:建立课程101(上课编号)的学生名册的视图，该名册包括学生的学号、姓名。专业名称和这门课的成绩；并查询结果。CREATEVIEWiec_student_coursel_viewAS别，P.spname专业名称，SC.score成绩FROMstudentS,specialityP,departmentD,student_courseSCWHERES.dno=D.dnoandP.dno=D.dnoandS.s_no=SC.s_noandSC.tcid=101andS.spno=PspnoWoi-0FC47N0.OCK-NQ其aatvi*i*tntW***中H1141)F4结果消息学号姓名性别专业名称成绩题目3:建立统计不及格情况的视图，列出不及格学生的学号、姓名··及格的课程代码课程名称，SC.score成绩，SC.tcid课程号WHERES.dno=D.dnoandP.dno=D.dnoand说细梦号共数名课样名称查询视图的SQL语句及执行结果截图SELECT*FROMiec_student_fail_view 结果消息学号姓名课程名称成绩课程号13002003朴小惠软件工程4810123002004李小镐通信工程56102题目4:修改视图ies_student_view,使该视图包括所有学生的学号、姓名。性别、出生年月、专业名称、院系名称。AS别，S.sbirthday出生年月，D.deptname学院名称，P.spnamefromstudentasS,departmentasD,specialityPwhereS.dno=D.dnoLtDViler_atssentwiesCT1.30学号，5姓名.3*性则，1.sblrthday出生年月.b.deptsame学院名称，Fpm#3Ana-D,dts暑已成功光成1.查询视图的SQL语句2.修改前后的视图执行结果截图显示smacF*rkoMlesatudent_view日基册请学号姓者性别出注年月专业者称13002001王子器男1094-00-04000000同工样23002002德小意男104-11-0800.00.00网替工程33002005朴小患女1000-08-1600-00.00软中工理学群能者性剂出去单月家探名释n*13002001主子罪男1004-01-040000-00计X机3工程3002002主男1994-11-000000.00计机学国工程33000003教十患女1000-08-10.-000000计靠机学院转序工理43002004零寸磷典171200.00.00工程字转通信工理53002000小班期T009-06-1000co9o外图学围环南毒题目5:删除视图ies_student_view。并查询结果。运行后可看到没有该视图显示命令已成功完成。图不存在的信息) 消息208,级别16,状态1,第1行对象名'ies_student_view'无效。(二)、索引题目1:在student_course表(学生选课表)的学生学号(sno)列上创建索引sc_sno_indexVVIN-UPL4/IVIDLUU8.master-SL.SqVV(消息命令已成功完成。重生果StN9ndedescigtane创建索引的SQL语句及执行结果显示 消息SQL语句及运行结果截图4结rendutanndnntndOnPPIAARY(三)、存储过程题目1:创建一个名称为“Stulnfo”的存储过程，要求完成以下功能：在student表中查询xxx班学生的学号、姓名。性别、出生日期四个字段的内容。查询分析器执行情况：if(object_id('StuInfo',p')isnotnull)ASBEGINprint'请输入班级编号ELSEWHEREclass_no=@class执行结果截图显示结果消息学号姓名性别出生年月123002003330020044题目2:创建一个存储过程StuScoreInfo,完成的功能是在表student、表course和表student_course中查询以下字段：班级、学号、姓名。性别、课程名称、学生成绩。查询分析器执行情况：IF(object_id('StuScorelnfo',p')isIF(@departmentisnull)print'请输入学院编号'END执行结果截图显示execStuScoreInfoxy04田结果消息姓名性别课程名称学生成绩班级学号H班级学号姓名EXECStuScoreInfoxy01用结果消息班级学号姓名性别课程名称学生成绩王子昂男数据结构成小龙男数据结构朴小惠女数据库原理题目3:创建存储过程list_famous_teachers,该存储过程列出SC库中所有年龄在35岁以下的教师的教师号、姓名。所在学院和年龄。查询分析器执行情况：DROPPROClist_famous_teachersCREATEPROClist_famous_teachersASBEGINFROMteacherT,departmentDEND执行结果截图显示WIN-DPC47M5LOC8.SC-SQLQueWIN-DPC47M5LOC8.SC-SQLQue教师编号姓名学院名称年龄信息工程学院24题目4:创建存储过程list_student_department,,该存储过程接收学院代码作为输入参数，列出数据库sc中某个院系学生的全部信息。查询分析器执行情况：SQL语句IF(object_id('list_student_department','P')isnotnull)ASBEGINELSEFROMstudentS,departmentD,specialityPWHERES.dno=D.dnoandS.spno=PspnoandD.dept_name-@dept_name;END执行结果截图显示EXEClist_student_department计算机学院消息结果面消息结果面成小龙学号男男女123成小龙学号男男女123结果消息1学号姓名性别出生年月专业名称班级编号1EXEClist_student_department信息热行星学院 学号姓名性别出生年月专业名称班级编号四、实验中出现的问题及解决方案在定义存储过程中，为了避免重复定义，应该在定义新的存储过程之前，首先删除之前的答：不对。RDBMS执行CREATEVIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查3.视图是一个虚表，数据库中只存放视图的定义，而不存放视图包含的数据，这些数据仍存放在原来基本表中。这句话对吗?4.基本表中的数据如果发生变化，视图中查询出数据也随之变化。这句话对吗?答：视图是一个虚表，数据库中只存放视图的定义，而不存放视图包含的数据，这些数据仍1)视图能够简化用户的操作2)视图使用户能以多钟角度看待同一数据4)视图能够对机密数据提供安全保护5)适当的利用视图可以更清晰的表达查询6.可更新视图的限制。可更新的。在上述实验中iesstudentview视图是可以更新的，其视图更新既是对student实验四小型数据库规划设计一、实验目的学习数据库原理及应用目的就是能创建和管理一个真实的数据库，通过规划设计一个小型数据库系统。让学生掌握数据库规划设计的基本技术，熟悉数据库的设计的基本方法和步骤，明白数据库设计各阶段的任务，加深对数据库系统概念和特点的理解。,根据你所熟悉的实际情况规划一个小型的数据库应用项目，学生学籍管理系统，进行系统分析和数据库设计。1、规划设计一个小型系统的数据库，按照给定要求，对各设计阶段进行描述，要求给出数据库设计的E-R图，并将E-R图转换成相应的模型，编制SQL命令脚本。四、实验方法、步骤及结构测试本系统设计一个学生学籍管理系统，统计学生信息，对学生的在校情况进行记录。满足学生查询成绩，进行选课登记。满足教师对学生的查询，成绩修改和评价。本系统主要是对学生的在校情况进行记录存储。学生学籍管理系统涉及数据信息有：院系信息、班级信息、任课教师信息、学生信息、学籍变更信息、学生奖惩信息、课程信息、选课信息和成绩等。3、建立系统的E-R图处罚方式级别时间级别名称奖金处罚信息属于惩罚获得奖励属于奖励信息时间电话学籍变动学籍变动性别专业变动信息专业课程名学分编号课时入学时间时姓名生日班级编号负责人生日性别系员责人编号人Y教师姓名邮箱电话职称snamesexnativebirthdaydnosDnodassnoentimehometels12001王子昂男河南省洛阳…1994/8/40:.…xy01zy0101bj132012/9/10.…河南省洛阳市涧s12002吴彦祖男香港九龙区…1993/12/80.…xy01zy0101bj132012/9/10.…香港九龙区民乐s12003赵本山男xy01Zy0101bj132012/9/10…辽宁省沈阳市赵江省杭州市汤s12004汤唯女浙江省杭州…1994/12/40.…xy01zy0101bj132012/9/10.…xs12005陈冠希男香港九龙区…1993/11/24…bj212012/9/10.…香港九龙区旺角s12006张柏芝女香港九龙区.…1994/12/14…xy01Zy0102香港九龙区旺角s12007谢霆锋男香港九龙区…1993/1/140…bj212012/9/10…香港九龙区旺角s12008余文乐男…香港九龙区1991/10/24.……xy01zy0102bj212012/9/10.…香港九龙区旺角s12009杨幂女北京市朝阳…1994/7/140…xy02zy0201bj322012/9/10…北京市朝阳区迎…12456880199xs12010杨颖女上海市浦东…1992/10/40..xy02zy0201bj322012/9/10…上海市浦东区杨s12011彭于晏男台湾省澎湖…1995/7/80:…xy03zy0301bj422012/9/10.…台网省澎湖彭家铺16852664751xs12012邓超男江西省南昌…1992/3/300.…xy04zy0401bj522012/9/10…江西省南昌市邓…12577841265xs12013王宝强男河北省邢台…1994/2/220.…xy05zy0501bj622012/9/10.…河北省邢台市宝…12452889652xS12014郑恺男上海市浦东…1995/1/200.…xy06zy0601bj722012/9/10…上海市浦东区郑ULLNULLNUILNULLNULLNULLNULLNULLNULLNULLNULL01计算机学院杜琪峰大学城校区工学一号馆020-845978..大学城校区工学二号馆020-847658…xy02外国语学院…冯小刚陈凯歌大学城校区工学三号馆020-878597..张艺谋大学城校区工学四号馆020-875466…吴宇森大学城校区工学二号馆020-812589..xy06自动化学院…李安大学城校区工学一号馆020-857884..NULLNULL__SDnOzy0102zy0103zy0201zy0202zy0301zy0302zy0401zy0501zy0601NULLxy01xy01Xy01xy02xy02xy03xy03xy04xy05xy06NULLsDname英语系日语系车辆工程NULL表01转出xs12001xS12002Xs12003Xs12004xS12005xs12006xs12007xs12008xs12009xs12010XS12011XS12012xS12013xS12014NULLNULLNULLNULLzy0301NULL汪涵孟非黄磊郭德纲柳岩小沈阳于谦刘烨郭冬临NULLPunishment表punishmentidsnoplevelsprectimeenable1xs12002012013/10/24…T2xS12005022014/1/50:…T3xs12010012014/5/60:…T4xs12014022013/7/40:…FNULLNULLNULLNULLNULLReward表rewardid1XS12001232014/5/300.…4XS120072014/1/80:…52014/6/190…6XS1201272014/4/70:…NULLNULLNULLNULL2专业必修课3非专业选修课dnoDNOhomezipcodeT001迈克尔贝男1978/6/20:…xy0101美国洛杉矶565520785-8956248558989@g.T002卡梅隆男xy01加拿大安大…415320975-844624855555@g…T0O3斯蒂芬男1947/8/80:…xy06美国俄亥俄州774420565-67560488003445@…T004胡玫女xy02北京100000010-65882314577258@g…T005刘伟强男1975/4/150…xy02香港560000080-44571145532665@g…T006宁浩男xy03山西420110120-712565dfbaga@gm…T007黄渤男1980/1/250..山东青岛130520034-7455146s⁵dfy19@g…T008徐峥男xy05上海420000045-4753458bf1dz53@g…NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLdassnoCnoschoolyeardasstimedassroomT001ZyO101bj13ka001上大二周三第五六节102T002bj21ka006下大一周二第三四节115T004bj32kd009上大二周一第五六节102T006bj42ka012上大三周五第一二节114T007bj52ka013下大二周三第五六节108T008bj62kc015上大一周四第一二节109T003bj72kc016上大一周三第五六节111NULLNULLNULLNULLNULLNULLNULLNULLNULL违反校纪记过一次严重违反校纪NULLNULLNULLChange表changeidsnochangenorectime2014/1/140… 2013/11/16…3XS120112014/8/80:…4XS12013 2013/12/26…NULLNULL 2014/9/180…NULL42214221444223242?mALDNOproname教接副教授讲师博士NULLNULL02校级优秀学生80004省级优秀学生200005国家级优秀学生5000NULLNULLNULL0kc00Bw010u012kc014u.01车NL 2y0102 型GiarpeGiarpe编译原理2yvo领计原理1人工智能1中国然游她理3计露机绝成源理数据连原理2英语翻译2外文名薯喜析韩卧喜3专业登论2车模制造通值技术商业基本法3整苹论快联网基进AZAC/IAZ00000000昃导411111111111111ALELplewrsunkm=mmsmhePwed4**mprafessg6、创建视图，并查询结果。(截图显示相关代码及实验结果)--建立计算机学院学生的视图ASFROMdepartmentWHEREdname='计算机学院)学号姓名性别年龄专业名称班级xs12001王子昂男计算机科学与技术bj13xS12002吴彦祖男计算机科学与技术bj13xs12003赵本山男计算机科学与技术bj13xS12004汤唯女计算机科学与技术bj13xs12005陈冠希男软件工程bj21xs12006张柏芝女软件工程bj21xs12007谢霆锋男软件工程bj21xs12008余文乐男软件工程bj21NULLNULLNULLNULLNULLNULL--建立奖励学生视图CREATEVIEWreward_VIEWASFROMstudentS,rlevelsRL,rewardRWHERES.sno=R.snoANDR.r_levels=RL.r_levelsxs12001王子昂国家级优秀学生xs12003赵本山院级优秀学生2014/10/240:…Xs12006张柏芝市级优秀学生校级优秀学生XS12009杨幂省级优秀学生XS12012邓超校级优秀学生8002014/11/200:…Xs12013王宝强校级优秀学生2014/4/70:00…NULLNULLNULLNULL--建立惩罚学生视图ASWHERES.sno=P.snoANDPp_levels=PLp_levels处分学号姓名时间讳纪情况处分学号姓名时间吴彦祖违反校纪记过一次2013/10/24…陈冠希严重违反校纪记过一次，开除学籍2014/1/50:…杨颖违反校纪记过一次2014/5/60:…郑恺严重违反校纪记过一次，开除学籍2013/7/40:…7、创建索引并查看索引(截图显示相关代码及实验结果)在student表的Sno属性上建立索引视图-dbo.punishment_VIEWWIN-DPC47M5LOC8.S…-SQLQuery1.sql*摘要结果消息! 1PKstudent_014935CBclustered,u在teacher表的tno属性上建立索引视图-dbo.punishment_VIEWWIN-DPC47M5LOC8.S…-SQLQuery1.sql*摘要execsp_helpIndexteacherH国结果消息index_nameindex_descriptionindexke1PKteacher15502E782teacher_t_no_indexnonclusteredlocatedonPRIMARYt_no8、创建存储过程，执行相应的存储过程并观察结果。(截图显示相关代码及实验结果)创建存储过程list_student_department该存储过程接收学院代码作为输入参数，列出某个院系的学生的所有信息DROPPROClist_student_departmentCREATEPROClist_stuBEGINPRINT'请输入院系'P.spname专业名称，S.classno班级编号FROMstudentS,departmentD,sWHERES.dno=D.dnoandS.spno=P.spnoandD.dname=@dname 学号姓名性别出生年月专业名称班级编号计算机科学与技术bj132xs12002吴彦祖男1993-12-0800.00:00计算机科学与技术bj13计算机科学与技术bj134xs12004汤唯女1994-12-0400:00:00计算机科学与技术bj138xs12008余文乐更1991-10-2400:00:00软件工程execlist_student_department外国语学院结果消息学号姓名性别出生年月专业名称杨幂女1994-07-1400:00:00英语系班级编号2xs12010杨颖女1992-10-0400:00:00英语系bj32创建存储过程list_famousteachers,该存储过程列出SC库中所有年龄在35岁以下的教师的教师号、姓名。所在学院和年龄。IF(object_id(list_famous_teachers',p')isnotnull)DROPPROClist_famous_teachersCREATEPROClist_famousteachersASBEGIN年龄FROMteacherT,departmentDWHERETdno=D.dnoandyear(getdate))-year(tbirthday)<35面结果消息_famous_teachers123教师编号姓名学院名称年龄T002T007T008卡梅隆黄渤徐峥计算机学院33信息工程学院34应用数学学院33(dnochar(6)notnullprimheaderchar(8),dtelvarchar(40),)(key,constraintfk_speciareferencesdepartment(dno))(snochar(8)notnullprimaryknativechar(20),homevarchar(40),telvarchar(40),referencesdepartment(dno),referencesspeciality(sp)CREATETABLEchange_code(char(8)char(8)notchar(8)notnull,)(rrname_r_money_r_money)(p_levelsnullp_actionchar(20)notnull,)(ctnotinyintnotnullprimary)(pnochar(6)notnullprimarypronamechar(10)notnull,)CREATETABLEcourse(cnochar(10)notnull,spnochar(8),cnamechar(20)notnull,ctnotinyint,semestertinyint,constraintfk_course_spnoforeignkey(spno)referencesspeciality(spno),constraintfk_course_ctnoforeignkey(ctno)referencescoursetype(ctno))(snochar(8)notnull,tcidsmallintnotnull,scoretinyint,constraintpkstudentcourseprimarykey(sno,tcid),constraintfk_student_course_snoforeignkey(sno)referencesstudent(sno)ondeletecascade)(t_nochar(8)nottnamechar(8)notnull,t_sexchar(2),t_b