PAGE10/NUMPAGES10數據庫實驗報告總結數據庫實驗報告

劉皓冰

實驗(一):熟練掌握SQL語言

實驗目的：熟悉上機環境，創建數據庫，在數據庫上建立關系模式，插入數據，

進行相應的查詢操作。

實驗內容：具體包括如下三部分。

一、熟悉上機環境。

客戶/服務器結構，數據庫服務器在一臺NT服務器上，同學們通過客戶機(操作系統為Windows2000)上安裝的SQLServer客戶端程序,使用SQLServer數據庫服務器。具體包括：

1.了解SQLServer環境。鼠標點擊開始，進入“MicrosoftSQLServer→企業

管理器”，點擊SQLServer組下的數據庫服務器（服務器名稱為NEUC-201S(WindowsNT)）,可以看到服務器上的圓形標志變為綠色，說明客戶端程序已與服務器連接成功。點擊服務器（NEUC-201S(WindowsNT)）下的數據庫，可以看到服務器上已建立的數據庫，你可訪問你有權訪問的數據庫，并進行相應的操作功能。因為，數據庫服務器上建有許多數據庫,每個數據庫都有一些合法的用戶。

2.鼠標點擊開始，進入“MicrosoftSQLServer→查詢分析器”，輸入用戶名和

口令，進入SQL查詢分析器。如：你登錄的客戶機為27號，那么請以用戶名user27，口令為user27登錄，登錄后缺省連到數據庫user27上，user27/user27是數據庫user27的創建者，因此用戶user27/user27具有在數據庫user27上創建表等一切權力。

3.在SQL查詢分析器環境下，你就可進行SQL命令語句操作了。

二、在數據庫useri上創建學生選課有關關系模式，并插入相應的數據，實現有

關查詢。

1.描述學生、課程情況的E-R圖如下，請將其轉化為用關系數據模型描述的關系模式

學生課程選修學號姓名年齡所在系

成績課號

課程名學時

學分先行課號

droptableStudents;

droptableCourses;

droptableSC;

createtableStudents(

Snonchar(10),

Snamenchar(20),

Sageint,

Sdeptnchar(20),

primarykey(Sno),

);

createtableCourses(

Cnonchar(4),

Cnamenchar(50),

Ctimeint,

Ccreditint,

Cpnonchar(4)

primarykey(Cno),

foreignkey(Cpno)referencesCourses,

);

createtableSC(

Snonchar(10),

Cnonchar(4),

Gradeint,

primarykey(Sno,Cno),

foreignkey(Sno)referencesStudents,

foreignkey(Cno)referencesCourses,

);

2.在數據庫中定義這些關系模式，并向基本表中插入如下數據：

insertintoStudentsvalues('20134017','劉皓冰',19,'計算機學院');insertintoStudentsvalues('20134028','袁堃皓',20,'計算機學院');insertintoStudents(Sno,Sname,Sage,Sdept)values('20130000','范冰冰',22,'表演學院');

insertintoStudentsvalues('20130011','黃曉明',23,'表演學院');insertintoStudentsvalues('20130012','王一',23,'表演學院');insertintoStudentsvalues('20130013','李天一',23,'表演學院');insertintoStudentsvalues('20130014','李二麻',23,'表演學院');deletefromStudentswhereSno='20130015';

insertintoStudentsvalues('20130015','李白',23,'計算機學院')insertintoStudentsvalues('20134018','王妮娜',21,'計算機學院')insertintoStudentsvalues('20134019','劉十',20,'計算機學院')

insertintoCoursesvalues('1001','C++語言',30,3,null)

insertintoCoursesvalues('1002','編譯原理',30,3,'1008')

insertintoCoursesvalues('1003','算法設計',40,4,'1001')

insertintoCoursesvalues('1004','離散數學',30,3,null)

insertintoCoursesvalues('1005','線性代數',20,2,null)

insertintoCoursesvalues('1006','Java語言',30,3,'1001')

insertintoCoursesvalues('1007','Python語言',20,2,'1009')

insertintoCoursesvalues('1008','計算機組成原理',40,4,null)

insertintoCoursesvalues('1009','操作系統',20,2,'1008')

insertintoCourses(Cno,Cname,Ctime,Ccredit,Cpno)values('1000','數據庫',30,3,'1009')

insertintoSC(Sno,Cno,Grade)values('20134017','1000',100)

insertintoSCvalues('20134017','1001',97)

insertintoSCvalues('20134017','1002',95)

insertintoSCvalues('20134028','1000',100)

insertintoSCvalues('20134028','1004',95)

insertintoSCvalues('20130000','1005',91)

insertintoSCvalues('20130011','1001',87)

insertintoSCvalues('20130011','1002',90)

insertintoSCvalues('20130000','1003',97)

insertintoSCvalues('20134019','1007',99)

3.插入相應的數據，試著插入重復的元組，結果如何？

由于在定義關系模式時，已經添加主鍵約束（primarykey(Sno),primarykey(Cno),primarykey(Sno,Cno)），所以不可以再插入重復的元組

4.在已建立的關系模式之上（已插入一些數據）建立主鍵約束，參照約束和用

戶定義的約束（要求學生年齡不小于14歲，不大于35歲），如果約束不能建立，請分析原因，修改后建立上述約束。

由于在定義關系模式時，已經添加主鍵約束（primarykey(Sno),primarykey(Cno),primarykey(Sno,Cno)）和參照約束(foreignkey(Sno)referencesStudents,foreignkey(Cno)referencesCourses,foreignkey(Cpno)referencesCourses)，

現添加用戶定義的約束（要求學生年齡不小于14歲，不大于35歲）:altertableStudentsaddcheck(Sage>=14andSage18andSage80

11.求計算機系每個學生的姓名，選修的課程名和成績。

selectSname,Cname,GradefromStudents,SC,Courses

whereStudents.Sno=SC.Snoandhttp://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmlo=http://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmloandSdept='計算機學院'

12.求每個學生的學號及平均成績。

selectStudents.Sname,avg(Grade)平均成績fromStudents,SC

whereStudents.Sno=SC.Sno

groupbyStudents.Sname

13.求男學生每一年齡組中超過1人的年齡組及人數。

altertableStudentsaddSsexnchar(8)

updateStudentssetSsex='男'whereStudents.Sno='20130013'

selectSage年齡組,count(Sno)人數fromStudentsgroupbySagehavingcount(*)>1

14.求每一門課程的間接先行課號。

selecthttp://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmlo課程號,courses2.Cpno間接先行課號fromCoursescourses1,Coursescourses2

wherecourses1.Cpno=http://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmlo

15.求選修了全部課程的學生的學號。

selectStudents.Sno學號

fromStudents

wherenotexists(select*

fromCourses

wherenotexists(select*

fromSC

whereSno=Students.Snoand

Cno=http://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmlo))

實驗(三):數據庫的安全性和完整性

實驗目的：了解和使用安全子系統；進行完整性定義和檢查。

實驗內容：具體包括如下三部分。

一熟練掌握SQL(續)

1.修改表Students,在其中加入一個字段性別sexchar(1)

altertableStudentsaddSsexnchar(8)

2.創建索引：在Students表的Sno上創建唯一聚簇索引,在SC表的（Sno,Cno）

上創建唯一索引.查詢Students表的內容,記錄的順序有變化嗎?查詢SC表的內容,記錄的順序有變化嗎?

不可以建因為Sno是主鍵

3.創建視圖

?創建計算機系學生視圖CS_Students，其中包括學號、年齡和性別。

帶withcheckopition子句

不帶withcheckopition子句

時各插入一計算機系學生記錄，通過視圖查詢插入的記錄，結果如何？通過表Students查詢插入的記錄，查看所在系字段上的值？

createviewCS_Students

as

selectsno,sage,ssex,sdept

fromstudents

wheresdept='計算機學院'

withcheckoption

insertintoCS_Studentsvalues('20114055','23','女','計算機學院');

dropviewCS_Students

createviewCS_Students

as

selectsno,sage,ssex,sdept

fromstudents

wheresdept='計算機學院'

insertintoCS_Studentsvalues('20114056','23','女','藝術學院');select*fromCS_Students

沒有withcheckoption時不對where子句進行檢查，可以插入

?創建一視圖，表示學生的平均成績，其中包括Sno,avgGrade。通過該視圖插入一學生姓名和平均成績，結果如何？通過該視圖查找平均成績在90分以上的學生的學號和平均成績，結果又如何？

createviewAvg_Stu(sno,avg_grade)

as

selectsno,avg(grade)fromSCgroupbysno

insertintoAvg_Stuvalues('20114057',91)

select*fromAvg_Stuwhereavg_grade>90

二了解和使用安全子系統

1、將sqlserver的一合法帳戶(user60)加入到你的數據庫當中來，并給其授權，使之：

具有創建表的權限

具有對Students表查詢的權限

具有對Studetns表的年齡字段修改的權限

SP_grantdbaccessuser51

grantcreatetabletouser51

grantselectonStudentstouser51

grantupdate(Sage)onStudentstouser51

2、用user60/user60進入系統，完成權限驗證：

在Students表上，實現select*fromStudents

在course表上，實現select*fromcourse

updateStudetnssetSage=Sage+1

updateStudentssetSdept=’CS’

select*fromhttp://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmler51.Student

grantselectonCoursestouser51#為了讓user51能夠在我的Courses表上查找select*fromhttp://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmler51.Course

updatehttp://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmler51.StudentsetSage=Sage+1

select*fromhttp://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmler51.Student

grantupdate(Sdept)onStudentstouser51

updatehttp://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmler51.StudentsetSdept='CS'

select*fromhttp://./doc/51ba331711a6f524ccbff121dd36a32d7375c70e.htmler51.Student

三完整性定義和檢查

1、創建表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’）createtableStudents_1(

Snochar(4)notnull,

Snamechar(10),

Sdeptchar(10)default'CS',

Sageintcheck(Sage>=16andSage<=25),

Ssexchar(1)

)

insertintoStudents_1values('9921','cccc','MA',23,'f')

insertintoStudents_1values('9922','ddd','MA',27,'m')

insertintoStudents_1(Sno,Sname,Sage,Ssex)values('9923','eeeee',21,'m')insertintoStudents_1values('9922','ddd','MA',23,'m')

2、創建表course，要求課號作為主鍵

在course表上插入一課程記錄（’c9’,40,’cname1’,1）

在course表上插入一課程記錄（’c9’,40,’cname2’,1）

在course表上插入一課程記錄（null,25,’canme3’,2）

createtableCourses_1(

Cnochar(4),

Ctimeint,

Cnamechar(50),

Ccreditint,

primarykey(Cno),

)

insertintoCourses_1values('c9',40,'cname1',1)

insertintoCourses_1values('c9',40,'cname2',1)

insertintoCourses_1values(null,25,'cname3',2)

3、修改表Studetns，指定學號為主鍵，然后

插入一記錄（’9908’,’shang’,’CI’,20）

插入一記錄（’9908’,’shang’,’CI’,20）

插入一記錄（’’,’liang’,’CS’,18）

插入一記錄（’’,’an’,’CS’,19）

altertableStudents_1addprimarykey(Sno)

insertintoStudents_1(Sno,Sname,Sdept,Sage)values('9908','shang','CI',20)

insertintoStudents_1(Sno,Sname,Sdept,Sage)values('9908','shang','CI',20)

insertintoStudents_1(Sno,Sname,Sdept,Sage)values('','liang','CS',18)

insertintoStudents_1(Sno,Sname,Sdept,Sage)values('','an','CS',19)

4、創建sc表，要求表sc中的學號參照表Studetns中的學號，sc中的課號參照course中的課號。參照約束創建完成后，向這三個表插入數據。

先在sc表插入一個選課記錄，看看有什么情況發生？

createtableSC_1(

Snochar(4),

Cnochar(4),

Gradeint,

primarykey(Sno,Cno),

foreignkey(Sno)referencesStudents_1,

foreignkey(Cno)referencesCourses_1,

);

insertintoSC_1values('9921','c9',100)

先刪除sc中的某個記錄，看看有什么情況發生？

deletefromSC_1whereSno='9921'

將Studetns表中的學號9906改為9907，看看有什么情況發生？updateStudents_1setSno='8888'whereSno='9908'

實驗(四):事務的并發控制

實驗目的：通過實驗了解實際系統中三級封鎖協議的實現方法和使用方法，體會加鎖操作、死鎖檢測與解除。

實驗內容：具體分如下五部分。

說明：

（1）Settransactionisolationlevelreaduncommitted（讀取未提交內容）

readcommitted（讀取提交內容）

repeatableread（可重讀）

serializable（可串行化）

分別對應隔離級0,1,2,3。

（2）要求兩人一組。

1、隔離級1(缺省)方式下體會兩事務的并發執行結果。具體步驟如下(以user1和

user2為例):

●user1首先將user2接納為自己數據庫的用戶,并授權user2可以查詢學生

表students

●user1輸入并執行下列語句

begintransaction

updatestudentssetage=20+1wheresno=’95001’1寫

●user2輸入并執行下列語句

begintransaction

select*fromstudentswheresno=’95001’2讀

1)觀察發生的現象,user2的語句可以執行嗎(是否可以讀到別人沒有提交的數

據)?

不可以執行

2)user1輸入并執行下列語句

commit

觀察發生的現象,user2的語句執行了嗎?

可以執行

3)加鎖和解鎖操作的完成者是數據庫用戶嗎?

●user2輸入并執行下列語句

commit

不是，是數據庫系統

2、隔離級1(缺省)方式下體會兩事務的并發執行結果。具體步驟如下(以user1和

user2為例):

●user1首先將user2接納為自己數據庫的用戶,并授權user2可以查詢學生

表students

●user2輸入并執行下列語句

begintransaction

selectagefromstudentswheresno=’95001’2讀

●user1輸入并執行下列語句

begintransaction

updatetablestudentssetage=20+1wheresno=’95001’1寫

1)觀察發生的現象,user1的語句可以執行嗎(是否可以修改別人正在讀的數據)?可以執行

●user2輸入并執行下列語句

selectagefromstudentswheresno=’95001’2再讀

2)觀察發生的現象,user2的語句執行了嗎?結果如何(是否可重復讀)?

user2輸入并執行下列語句

commit

不可以執行

●user1輸入并執行下列語句

commit

user2語句這時執行了，可以讀到修改后的結果

3、在隔離級0方式下體會兩事務的并發執行結果。具體步驟如下(以user1和

user2為例):

●user1首先將user2接納為自己數據庫的用戶,并授權user2可以查詢學生

表students

●user1輸入并執行下列語句

begintransaction

updatetablestudentssetage=20+1wheresno=’95001’1寫

●user2輸入并執行下列語句

begintransaction

settransactionisolationlevelReadUncommitted

select*fromstudentswheresno=’95001’2讀

1)觀察發生的現象,user2的語句可以執行嗎(是否可以讀到別人沒有提交的數

據)?/