東北大學數(shù)據(jù)庫實驗報告2014

數(shù)據(jù)庫實驗報告班級：計算機1105班學號：20113263姓名：張少杰時間：2014年6月19日實驗(一):熟練掌握SQL語言實驗目的：熟悉上機環(huán)境，創(chuàng)建數(shù)據(jù)庫，在數(shù)據(jù)庫上建立關(guān)系模式，插入數(shù)據(jù)，進行相應的查詢操作。實驗內(nèi)容：具體包括如下三部分。熟悉上機環(huán)境。客戶/服務器結(jié)構(gòu)，數(shù)據(jù)庫服務器在一臺NT服務器上，同學們通過客戶機(操作系統(tǒng)為Windows2000)上安裝的SQLServer客戶端程序,使用SQLServer數(shù)據(jù)庫服務器。具體包括：了解SQLServer環(huán)境。鼠標點擊開始，進入“MicrosoftSQLServer企業(yè)管理器”，點擊SQLServer組下的數(shù)據(jù)庫服務器（服務器名稱為NEUC-201S(WindowsNT)）,可以看到服務器上的圓形標志變?yōu)榫G色，說明客戶端程序已與服務器連接成功。點擊服務器（NEUC-201S(WindowsNT)）下的數(shù)據(jù)庫，可以看到服務器上已建立的數(shù)據(jù)庫，你可訪問你有權(quán)訪問的數(shù)據(jù)庫，并進行相應的操作功能。因為，數(shù)據(jù)庫服務器上建有許多數(shù)據(jù)庫,每個數(shù)據(jù)庫都有一些合法的用戶。鼠標點擊開始，進入“MicrosoftSQLServer查詢分析器”，輸入用戶名和口令，進入SQL查詢分析器。如：你登錄的客戶機為27號，那么請以用戶名user27，口令為user27登錄，登錄后缺省連到數(shù)據(jù)庫user27上，user27/user27是數(shù)據(jù)庫user27的創(chuàng)建者，因此用戶user27/user27具有在數(shù)據(jù)庫user27上創(chuàng)建表等一切權(quán)力。在SQL查詢分析器環(huán)境下，你就可進行SQL命令語句操作了。在數(shù)據(jù)庫useri上創(chuàng)建學生選課有關(guān)關(guān)系模式，并插入相應的數(shù)據(jù)，實現(xiàn)有關(guān)查詢。描述學生、課程情況的E-R圖如下，請將其轉(zhuǎn)化為用關(guān)系數(shù)據(jù)模型描述的關(guān)系模式學生學生課程選修學號姓名年齡所在系成績課號課程名學時學分先行課號根據(jù)E-R圖按如下代碼建立三個表：CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,SnameCHAR(20)UNIQUE,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20));CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CreditSMALLINT,FOREIGNKEY(Cpno)REFERENCESCourse(Cno));CREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno));在Course表中插入學時字段altertableCourseaddCtimesmallintNULL運行后建表成功。在數(shù)據(jù)庫中定義這些關(guān)系模式，并向基本表中插入如下數(shù)據(jù)：INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100001','張一','男','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100002','張二','女','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100003','張三','男','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100004','張四','女','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100005','張五','男','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100006','張六','女','IS',18);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('1','數(shù)據(jù)庫原理',NULL,2);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('2','數(shù)據(jù)結(jié)構(gòu)',NULL,2);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('3','C語言',NULL,2);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('4','算法',NULL,2);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('5','高等數(shù)學',NULL,2);INSERTINTOSC(Sno,Cno)VALUES('201100001','1');INSERTINTOSC(Sno,Cno)VALUES('201100002','2');INSERTINTOSC(Sno,Cno)VALUES('201100003','3');INSERTINTOSC(Sno,Cno)VALUES('201100004','4');INSERTINTOSC(Sno,Cno)VALUES('201100005','5');插入相應的數(shù)據(jù)，試著插入重復的元組，結(jié)果如何？INSERTINTOSC(Sno,Cno)VALUES('201100005','5');在已建立的關(guān)系模式之上（已插入一些數(shù)據(jù)）建立主鍵約束，參照約束和用戶定義的約束（要求學生年齡不小于14歲，不大于35歲），如果約束不能建立，請分析原因，修改后建立上述約束。altertablestudentaddcheck(Sagebetween14and35)在建立主鍵約束前如果存在數(shù)據(jù)不滿足將要建立的主鍵約束條件，則不能建立主鍵約束。先將相關(guān)數(shù)據(jù)修改為滿足主鍵約束條件再建立主鍵約束則操作成功。如果約束建立后，請試著插入重復元組，結(jié)果如何？試著插入重復元組，無法插入：INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('201100005','張五','男',18,'IS');

實驗(二):熟練掌握SQL語言用SQL完成下列查詢求選修了課程的學生的學號，要求：(1)不使用distinct語句，寫出其SQL語句SelectSnofromSC(2)使用distinct語句，消除重復元組，寫出其SQL語句selectdistinctSnofromSC(3)如果該查詢寫成：selectStudent.SnofromStudent,SCwhereStudent.Sno=SC.Sno請問該查詢結(jié)果和上面哪個結(jié)果是相同的？(3)和（1）的結(jié)果相同求開設(shè)的課程號、課程名、學時和學分，要求對查詢結(jié)果的列重新命名。selectCnoMyCno,CnameMyCname,CtimeMyCtime,CreditMyCredictfromCourse求計算機系和數(shù)學系的學生學號、姓名和年齡。selectSno,Sname,SagefromStudentwhereSdept='CS'orStudent='Math'求不是數(shù)學系、計算機系的學生的情況，要求select后寫*代替列出所有的列名。select*fromStudentwhereStudent.Sdept!='CS'andStudent.Sdept!='Math'求全體學生的信息，要求按年齡升序排列。select*fromStudentorderbySageasc求計算機系年齡在18~20歲之間的學生姓名和年齡。selectSname,SagefromStudentwhereSage>=18andSage<=20andSdept='CS'求姓名是以“XX”開頭的計算機系學生。select*fromStudentwhereSnamelike'張李%'andSdept='CS'實驗(三):熟練掌握SQL語言求選修了2課程的學生的學號、姓名、成績。selectStudent.Sno,Student.Sname,SC.GradefromStudent,SCwhereSC.Sno=Student.SnoandSC.Cno=2求學號為95開頭的學生的學號和所選修的課程的學分。selectStudent.Sno,Course.CreditfromStudent,SC,CoursewhereStudent.Snolike'2012%'andStudent.Sno=SC.SnoandSC.Cno=Course.Cno3求選修2課程且成績?yōu)?0分以上的學生的學號、姓名和成績。selectStudent.Sno,Student.Sname,SC.GradefromStudent,SCwhereStudent.Sno=SC.SnoandSC.Cno=2andSC.Grade>804求計算機系每個學生的姓名，選修的課程名和成績。selectStudent.Sname,SC.Cno,SC.GradefromStudent,SCwhereStudent.Sdept='CS'andStudent.Sno=SC.Sno5求每個學生的學號及平均成績。selectSno,AVG(Grade)fromSCgroupbySno6求男學生每一年齡組中超過1人的年齡組及人數(shù)。selectSage,Count(Sno)fromStudentgroupbySagehavingCount(Sno)>17求每一門課程的間接先行課號。selectC1.Cno,C2.CpnofromCourseC1,CourseC2whereC1.Cpno=C2.Cno8.求選修了全部課程的學生的學號。selectSnofromStudentwherenotexists(select*fromCoursewherenotexists(select*fromSCwhereSno=Student.SnoandCno=Course.Cno));

實驗(四):數(shù)據(jù)庫的安全性實驗目的：了解和使用安全子系統(tǒng)和檢查。實驗內(nèi)容：具體包括如下二部分。一熟練掌握SQL(續(xù))修改表Students,在其中加入一個字段性別sexchar(1)CREATETABLEStudent(SnoCHAR(9),SnameCHAR(20),SageSMALLINT,SdeptCHAR(20));CREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100003','張一','IS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100002','張二','CS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100001','張三','CS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100004','張四','CS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100005','張五','CS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100006','張六','IS',18);INSERTINTOSC(Sno,Cno,Grade)VALUES('201100002','2',88);INSERTINTOSC(Sno,Cno，Grade)VALUES('201100002','1',98);INSERTINTOSC(Sno,Cno,Grade)VALUES('201100001','2',77);INSERTINTOSC(Sno,Cno,Grade)VALUES('201100001','1',80);INSERTINTOSC(Sno,Cno,Grade)VALUES('201100005','2',92);altertableStudentaddsexchar(1);創(chuàng)建索引：在Students表的Sno上創(chuàng)建唯一聚簇索引,在SC表的（Sno,Cno）上創(chuàng)建唯一索引.查詢Students表的內(nèi)容,記錄的順序有變化嗎?查詢SC表的內(nèi)容,記錄的順序有變化嗎?CreateuniqueclusteredindexStudent_snoonStudent(Sno);createuniqueindexSno_cnoonSC(Sno,Cno);創(chuàng)建視圖創(chuàng)建計算機系學生視圖CS_Students，其中包括學號、年齡和性別。 帶withcheckopition子句 不帶withcheckopition子句時各插入一計算機系學生記錄，通過視圖查詢插入的記錄，結(jié)果如何？通過表Students查詢插入的記錄，查看所在系字段上的值？createviewCS_students1asselectSno,Sage,sexfromStudentwhereSdept='CS'withcheckoptionINSERTINTOCS_students1VALUES('201100005',18,’m’);createviewCS_students2asselectSno,Sage,sexfromStudentwhereSdept='CS'INSERTINTOCS_students2VALUES('201100007',18,’m’);創(chuàng)建一視圖，表示學生的平均成績，其中包括Sno,avgGrade。通過該視圖插入一學生姓名和平均成績，結(jié)果如何？通過該視圖查找平均成績在90分以上的學生的學號和平均成績，結(jié)果又如何？createviewStuavggrade(sno,avggrade)asselectSno,avg(Grade)fromSCgroupbySC.snoInsertintoStuavggradevalues('201100003',87)selectsno,avggradefromStuavggradewhereavggrade>=90二了解和使用安全子系統(tǒng)1、將sqlserver的一合法帳戶(user62)加入到你的數(shù)據(jù)庫當中來，并給其授權(quán)，使之：SP_grantdbaccessuser62;具有創(chuàng)建表的權(quán)限grantcreatetabletouser62;具有對Students表查詢的權(quán)限grantselectonStudenttouser62;具有對Studetns表的年齡字段修改的權(quán)限grantupdate(Sage)onStudenttouser622、用user62/user62進入系統(tǒng)，完成權(quán)限驗證：在Students表上，實現(xiàn)select*fromStudentsselect*fromdb63.user63.Student在course表上，實現(xiàn)select*fromcourseselect*fromdb63.user63.courseupdateStudetnssetSage=Sage+1updatedb63.user63.StudentsetSage=Sage+1select*fromdb63.user63.StudentupdateStudentssetSdept=’CS’updatedb63.user63.StudentsetSdept='CS'select*fromdb63.user63.Student3、你是數(shù)據(jù)庫的dbo，你來查看dept表，結(jié)果如何？使用setuser命令使自己成為user60后，查看dept表，結(jié)果如何？實驗(五):數(shù)據(jù)庫的完整性實驗目的：了解和使用完整性定義和檢查1、創(chuàng)建表Studetns，要求學生的年齡在16和25歲之間，并且所在系的缺省取值為‘CS’在Studetns表上插入一學生記錄（‘9921’，‘cccc’,’MA’,23,’f’）在Studetns表上插入一學生記錄（‘9922’，‘ddd’,’MA’,27,’m’）在Studetns表上插入一學生記錄（‘9923’，‘eeeee’,21,’m’）在Studetns表上插入一學生記錄（‘9922’，‘ddd’,’MA’,23,’m’）CREATETABLEStudent(SnoCHAR(9)notnull,SnameCHAR(20),Sdeptchar(20)default'CS',SageSMALLINTcheck(Sage>=16andSage<=25),Ssexchar(2));insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9921','cccc’,’MA’,23,’f’);insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9922','ddd’,’MA’,27,’m’);insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9923','eeeee’,21,’m’);insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9922','ddd’,’MA’,23,’m’);2、創(chuàng)建表course，要求課號作為主鍵 在course表上插入一課程記錄（’c9’,40,’cname1’,1） 在course表上插入一課程記錄（’c9’,40,’cname2’,1） 在course表上插入一課程記錄（null,25,’canme3’,2）createtablecourse(cnochar(10)primarykey,ctimechar(10),cnamechar(20),cscorechar(10));insertintocourse(cno,ctime,cname,cscore)values(’c9’,40,’cname1’,1);insertintocourse(cno,ctime,cname,cscore)values(’c9’,40,’cname2’,1);insertintocourse(cno,ctime,cname,cscore)values(null,25,’canme3’,2);3、修改表Studetns，指定學號為主鍵，然后 插入一記錄（’9908’,’shang’,’CI’,20） 插入一記錄（’9908’,’shang’,’CI’,20） 插入一記錄（’’,’liang’,’CS’,18） 插入一記錄（’’,’an’,’CS’,19）ALTERTABLEStudentADDCONSTRAINTpk_SnoPRIMARYKEY(Sno)insertintoStudent(Sno,Sname,Sdept,Sage)values('9908','shang','CI',20);insertintoStudent(Sno,Sname,Sdept,Sage)values('9908','shang','CI',20);insertintoStudent(Sno,Sname,Sdept,Sage)values(‘’,’liang’,’CS’,18);insertintoStudent(Sno,Sname,Sdept,Sage)values(‘’,’an’,’CS’,19);4、創(chuàng)建sc表，要求表sc中的學號參照表Studetns中的學號，sc中的課號參照course中的課號。參照約束創(chuàng)建完成后，向這三個表插入數(shù)據(jù)。先在sc表插入一個選課記錄，看看有什么情況發(fā)生？ 先刪除sc中的某個記錄，看看有什么情況發(fā)生？ 將Studetns表中的學號9906改為9907，看看有什么情況發(fā)生？insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9906','ddd’,’MA’,23,’m’);createtablesc(Snochar(9),Cnochar(10),Gradeint,foreignkey(Sno)referencesStudent(Sno),foreignkey(Cno)referencescourse(cno);insertintoSC(Sno,Cno,Grade)values('9922','c9',85);deletefromscwhereSno='9922';updateStudentssetSno=‘9907’whereSno=‘9906’;如果SC引用了9906insertintoSC(Sno,Cno,Grade)values('9906','c9',75);updateStudentssetSno=‘9906’whereSno=‘9907’;

實驗(六):事務的并發(fā)控制實驗目的：通過實驗了解實際系統(tǒng)中,三級封鎖協(xié)議的實現(xiàn)方法和使用方法，體會加鎖操作、死鎖檢測與解除。實驗內(nèi)容：具體分如下五部分。說明：（1）Settransactionisolationlevelreaduncommittedreadcommittedrepeatablereadserializable分別對應隔離級0,1,2,3。（2）要求兩人一組。CREATETABLEstudents(snoCHAR(9)primarykey,snameCHAR(20),sdeptchar(20),sageSMALLINT,ssexchar(2));insertintostudents(sno,sname,sdept,sage,ssex)values('95001','張三','CS',23,'m');insertintostudents(sno,sname,sdept,sage,ssex)values('95101','張麗','CS',25,'f');insertintostudents(sno,sname,sdept,sage,ssex)values('95201','張偉','CS',20,'m');createtablecourse(cnochar(10)primarykey,ctimechar(10),cnamechar(20),cscorechar(10));insertintocourse(cno,ctime,cname,cscore)values('c9',40,'math',1);createtablesc(snochar(9),cnochar(10),gradeint,foreignkey(sno)referencesstudents(sno),foreignkey(cno)referencescourse(cno));insertintosc(sno,cno,grade)values('950001','c9',85);隔離級1(缺省)方式下體會兩事務的并發(fā)執(zhí)行結(jié)果。具體步驟如下(以user1和user2為例):user1首先將user2接納為自己數(shù)據(jù)庫的用戶,并授權(quán)user2可以查詢學生表studentssp_revokedbaccessuser62;SP_grantdbaccessuser62;grantselectonstudentstouser62;user1輸入并執(zhí)行下列語句begintransactionupdatestudentssetage=20+1wheresno=’95001’user2輸入并執(zhí)行下列語句begintransactionselect*fromstudentswheresno=’95001’觀察發(fā)生的現(xiàn)象,user2的語句可以執(zhí)行嗎(是否可以讀到別人沒有提交的數(shù)據(jù))?不可以。user1輸入并執(zhí)行下列語句commit觀察發(fā)生的現(xiàn)象,user2的語句執(zhí)行了嗎?查詢成功。執(zhí)行了。3)加鎖和解鎖操作的完成者是數(shù)據(jù)庫用戶嗎?user2輸入并執(zhí)行下列語句commit不是。是數(shù)據(jù)庫系統(tǒng)。隔離級1(缺省)方式下體會兩事務的并發(fā)執(zhí)行結(jié)果。具體步驟如下(以user1和user2為例):user1首先將user2接納為自己數(shù)據(jù)庫的用戶,并授權(quán)user2可以查詢學生表studentsSP_grantdbaccessuser62;grantselectonstudentstouser62;user2輸入并執(zhí)行下列語句begintransactionselectagefromstudentswheresno=’95001’user1輸入并執(zhí)行下列語句begintransactionupdatetablestudentssetage=20+1wheresno=’95001’觀察發(fā)生的現(xiàn)象,user1的語句可以執(zhí)行嗎(是否可以修改別人正在讀的數(shù)據(jù))?User1的語句可以執(zhí)行。user2輸入并執(zhí)行下列語句selectagefromstudentswheresno=’95001’觀察發(fā)生的現(xiàn)象,user2的語句執(zhí)行了嗎?結(jié)果如何(是否可重復讀)?不可以。user2輸入并執(zhí)行下列語句commituser1輸入并執(zhí)行下列語句commit在隔離級0方式下體會兩事務的并發(fā)執(zhí)行結(jié)果。具體步驟如下(以user1和use