數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告課程學(xué)生選課系統(tǒng)的數(shù)據(jù)庫SQLSERVER包含ER圖與實(shí)現(xiàn)

1、.數(shù)據(jù)庫系統(tǒng)概論實(shí)驗(yàn)報(bào)告學(xué) 號(hào)：姓 名：提交日期：2010-12-15成 績：.;【實(shí)驗(yàn)名稱】學(xué)生選課系統(tǒng)的數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)【實(shí)驗(yàn)內(nèi)容】1、熟悉數(shù)據(jù)庫設(shè)計(jì)方法；2、熟悉E-R圖到關(guān)系模式的轉(zhuǎn)換；3、掌握SQL Server 2005數(shù)據(jù)庫的創(chuàng)建SQL語句,理解數(shù)據(jù)庫文件組、文件邏輯名和物理名稱的具體含義；4、掌握SQL Server 2005 中用SQL語句建立數(shù)據(jù)庫表，設(shè)置數(shù)據(jù)庫完整性約束；5、掌握用SQL Server 2005 建立表的關(guān)系圖，清楚的表示各表之間的聯(lián)系?！緦?shí)驗(yàn)步驟】一、 數(shù)據(jù)庫的邏輯設(shè)計(jì)圖（一）班級(jí)、學(xué)生、課程實(shí)體類型及聯(lián)系型的ER圖圖（二）班級(jí)實(shí)體的分ER圖圖（三）課程

2、實(shí)體的分ER圖圖（四）學(xué)生實(shí)體的ER圖將上述的E-R圖轉(zhuǎn)換為關(guān)系模型，關(guān)系的主鍵用下橫線標(biāo)出。學(xué)生（學(xué)號(hào)，姓名，性別，年齡，班級(jí)號(hào)）課程（課程號(hào)，課程名，課時(shí) ，學(xué)分）班級(jí)（班級(jí)號(hào)，班級(jí)名，人數(shù)，專業(yè)）選課（課程號(hào)學(xué)號(hào)，成績）二、 數(shù)據(jù)庫的物理設(shè)計(jì)1 建表語句：本數(shù)據(jù)庫共創(chuàng)建了四張表，其建表語句如下 1班級(jí)表create table class(classNo int not null primary key,className char(20),sdept char(20),number int)2學(xué)生表create table student(studentNo int not null

3、primary key,studentName char(20),sex char(20),age int,classNo int)3.課程表create table course(courseNo int not null primary key,courseName char(20),grade int)4選課表create table choice(studentNo int,courseNo int。result int,primary key(studentNo,courseNo)2說明外碼語句alter table choiceadd foreign key (studentNo)

4、 references student(studentNo), foreign key (courseNo) references course(courseNo)alter table studentadd foreign key (classNo) references class(classNo)3記錄插入語句：（1）class表：insert class values (40801,yyy,os,35)insert class values (40802,xx,ad,45)insert class values (40803,rr,cs,36)insert class values (

5、40804,yy,os,35)insert class values (40805,ee,ry,78)insert class values (40806,yyy,os,35)insert class values (40807,xg,ad,45)insert class values (40808,ry,cs,36)insert class values (40809,yd,os,65)insert class values (408010,eg,ry,48)（2）student表：insert student values(10001,aa,f,23,40801)insert studen

6、t values(10002,df,f,23,40802)insert student values(10003,adf,f,22,40802)insert student values(10004,gh,m,23,40803)insert student values(10005,dc,f,24,40804)insert student values(10006,sd,m,23,40802)insert student values(10007,dfc,f,23,40803)insert student values(10008,xc,m,25,40805)insert student va

7、lues(10009,sd,f,23,40806)insert student values(100010,cv,f,27,40806)（3）course表：insert course values(1,operating,48,8)insert course values(2,computer,24,4)insert course values(3,music,56,12)insert course values(4,compile,48,8)insert course values(5,oracle,32,6)insert course values(6,sql,48,8)（4）choic

8、e表：insert choice values(10001,1,80)insert choice values(10001,2,85)insert choice values(10002,3,86)insert choice values(10002,5,45)insert choice values(10003,1,80)insert choice values(10004,2,87)insert choice values(10004,6,89)insert choice values(10005,1,90)insert choice values(10005,4,100)insert c

9、hoice values(10006,1,60)insert choice values(10006,3,49)insert choice values(10007,5,70)insert choice values(10008,1,70)insert choice values(10008,6,78)insert choice values(10009,2,80)insert choice values(100010,3,78)insert choice values(100010,1,80)insert choice values(100010,2,78)insert choice val

10、ues(100010,4,89)insert choice values(100010,5,45)4、查詢記錄：1.選擇輸出學(xué)號(hào)、姓名滿足性別為女，年齡小于25select studentNo,studentNamefrom studentwhere sex=fand age50的班級(jí)輸出其班級(jí)號(hào)和人數(shù)create view Numasselect classNo,numberfrom classwhere number50查詢語句select * from Num6.插入數(shù)據(jù)插入數(shù)據(jù)：插入student表中一個(gè)學(xué)生，學(xué)生名為newstudent，性別為女，年齡34，班級(jí)號(hào)為40806inse

11、rt student values(100011,newstudent,f,24,40806)三、 利用SQL Server 2005的Management Studio來創(chuàng)建表的關(guān)系圖,創(chuàng)建好的關(guān)系圖如下所示:四 實(shí)驗(yàn)體會(huì)通過這次實(shí)驗(yàn)，讓我對(duì)sql server2005的使用更加熟悉了，同時(shí)也加深了我對(duì)課本上數(shù)據(jù)庫知識(shí)的理解，這次實(shí)驗(yàn)可以說是我第一次完成的一個(gè)整體的數(shù)據(jù)庫實(shí)驗(yàn)。此次實(shí)驗(yàn)中，我完成了從概念設(shè)計(jì)到邏輯設(shè)計(jì)，也就是完成了ER圖的設(shè)計(jì)并完成了向關(guān)系模型的轉(zhuǎn)換，完成了數(shù)據(jù)庫的建立、表的建立、視圖的創(chuàng)建，最后還完成了表之間的關(guān)系圖。一個(gè)數(shù)據(jù)庫的創(chuàng)建首先要對(duì)客戶的需求進(jìn)行充分的分析，找出創(chuàng)

12、建數(shù)據(jù)庫所需的實(shí)體和實(shí)體間的關(guān)系畫出E-R 圖，確定要?jiǎng)?chuàng)建的表的數(shù)量和每張表的屬性、主鍵和外鍵。在動(dòng)手實(shí)驗(yàn)的過程中我遇到很多困難，比如如何刪除表中的數(shù)據(jù)但不保留到日志文件中，數(shù)據(jù)的不普遍性所造成的查詢等操作的空表等等，但是正是這些問題才讓我學(xué)到了更多，同時(shí)也享受到了解決問題后的喜悅。更重要的是，我對(duì)數(shù)據(jù)庫產(chǎn)生了濃厚的興趣，相信以后會(huì)把學(xué)到的東西應(yīng)用到實(shí)踐中去的。五 附錄（SQL語句）-班級(jí)表create table class(classNo int not null primary key,className char(20),sdept char(20),number int)-學(xué)生表cr

13、eate table student(studentNo int not null primary key,studentName char(20),sex char(20),age int,classNo int)create table course(courseNo int not null primary key,courseName char(20),courseTime int,grade int)create table choice(studentNo int,courseNo int,result int,primary key(studentNo,courseNo)alte

14、r table choiceadd foreign key (studentNo) references student(studentNo), foreign key (courseNo) references course(courseNo)alter table studentadd foreign key (classNo) references class(classNo)insert class values (40801,yyy,os,35)insert class values (40802,xx,ad,45)insert class values (40803,rr,cs,3

15、6)insert class values (40804,yy,os,35)insert class values (40805,ee,ry,78)insert class values (40806,yyy,os,35)insert class values (40807,xg,ad,45)insert class values (40808,ry,cs,36)insert class values (40809,yd,os,65)insert class values (408010,eg,ry,48)select * from studentinsert student values(1

16、0001,aa,f,23,40801)insert student values(10002,df,f,23,40802)insert student values(10003,adf,f,22,40802)insert student values(10004,gh,m,23,40803)insert student values(10005,dc,f,24,40804)insert student values(10006,sd,m,23,40802)insert student values(10007,dfc,f,23,40803)insert student values(10008

17、,xc,m,25,40805)insert student values(10009,sd,f,23,40806)insert student values(100010,cv,f,27,40806)insert course values(1,operating,48,8)insert course values(2,computer,24,4)insert course values(3,music,56,12)insert course values(4,compile,48,8)insert course values(5,oracle,32,6)insert course value

18、s(6,sql,48,8)insert choice values(10001,1,80)insert choice values(10001,2,85)insert choice values(10002,3,86)insert choice values(10002,5,45)insert choice values(10003,1,80)insert choice values(10004,2,87)insert choice values(10004,6,89)insert choice values(10005,1,90)insert choice values(10005,4,100)insert choic