數據庫系統(tǒng)原理及應用實驗報告.doc

數據庫系統(tǒng)原理及應用實驗指導書 數據庫系統(tǒng)原理及應用實驗指導書學 號： 姓 名： 專業(yè)班級： 指導教師： 15一“教學管理系統(tǒng)” 實驗數據庫模式描述2關系模式描述定義Student (sno,sname,sbirthday,ssex,sclass,sremark,address,zipcode,phone,email) Primary key (sno)Course (cno, cname, cpno, ctime, credit) Primary key (cno) Foreign key (cpno)Score (sno, cno, score) Primary key (sno, cno) Foreign key (sno) Foreign key (cno)Teacher (tno, tname, tsex, tbirthday, position, department, tamount, experience) Primary key (tno)Teaching (tno, cno, tdate, classroom, sclass) Primary key (tno, cno) Foreign key (tno) Foreign key (cno) 二上機實驗（一）實驗目標通過“數據庫系統(tǒng)概論”書本知識學習和“數據庫原理與應用”課程教學所授知識對“教學管理系統(tǒng)”實驗內容進行分析，建立相應的數據庫、數據表、數據視圖以及相應的界面程序，利用SQL結構化查詢語言提供的數據操縱功能，編程設計實現(xiàn)對數據的定義、修改、刪除、查詢、更新以及控制等操作，并按照實驗指導書的各項要求完成實驗。 （三）實驗內容實驗一：設計數據庫、數據表和數據視圖并編程實現(xiàn)1. 目的要求（1）這是一個驗證性實驗。（2）利用所學E-R數據模型圖概念，描述“學生管理” 系統(tǒng)中數據表之間以及各數據項之間的聯(lián)系。并按照課程教學大綱要求，轉化相關E-R圖為關系模型中對應的二維表，指出每個數據表中的關鍵字、索引項、外碼以及表中需要滿足完整性約束條件；（3）對照關系模型中(1:1)、(1:n)、(m:n)實體聯(lián)系，指出各數據表實體屬于哪一類聯(lián)系。（4）熟悉微軟公司SQL Server的企業(yè)管理器（后臺實驗平臺）和查詢分析器（前臺實驗平臺）內嵌的SQL編程環(huán)境，正確登錄、退出和使用實驗平臺環(huán)境；（5）熟練掌握SQL環(huán)境下的程序編輯和運行命令；（6）建立一個教學管理數據庫，即建立五個相關數據表。練習并掌握定義、刪除和修改表的數據結構操作。建立數據庫和數據表、數據視圖并完成對各個數據表輸入一定查詢要求的模擬數據（模擬數據記錄盡量多輸一些且數據要能滿足查詢要求）的內容。2. 實驗內容（1）根據實驗建議所示，建立“學生管理”數據庫，這里是以SQL Server 2000為例來（2）建立數據表的方法，建立五個相關的數據表。完成數據庫的建立并定義各表的完整性約束條件。分別使用 企業(yè)管理器 和SQL 語言 來實現(xiàn)。（3）熟練掌握并應用SQL中的數據表定義、刪除和修改等操作命令；（4）熟練掌握為各表常用字段建立索引，以提高數據檢索效率。建表的代碼如下：分別為student,course,score,teacher,teaching表use student_managment;create table student (sno char(9),sname char(10),sbirthday Datetime,ssex char(2) constraint c2 check(ssex in(男,女),sclass char(20),sremark char(100),address char(40),zipcode char (6),phone char(15),email char(40),constraint pk_student primary key (sno);create table course if not exists(cno char(6),cname char(20),cpno char(6),ctime numeric(2),credit numeric(2),score numeric(3),constraint pk_course primary key (cno),);create table score (sno char(9),cno char(6),score numeric(3),primary key (sno,cno),foreign key (sno) references student(sno),foreign key (cno) references course(cno);create table teacher (tno char(7),tname char(10),tsex char(2),tbirthday Datetime,position char(12),department char(16),tamount numeric(7,2),experience char(200),primary key (tno),);create table teaching (tno char(7) ,cno char(6) ,tdate datetime,classroom char(10),sclass char(20),primary key (tno,cno),foreign key (tno) references teacher (tno),foreign key (cno) references course (cno);實驗二：設計數據插入、修改、刪除、更新、查詢和視圖查詢等操作并編程實現(xiàn)1. 目的要求（1）熟練掌握數據更新語句，靈活地操作插入數據、修改數據和刪除數據；（2）熟練掌握關系數據庫中的完整性概念的應用；（3）了解數據錄入的方法和過程。（4）掌握單表查詢的基本方法；（5）掌握連接查詢的基本方法；（6）熟練掌握以下練習，并進行以下各類查詢： 選擇表中的若干列、查詢全部列、查詢經過計算的值； 選擇表中的若干元組，即消除取值重復的行與查詢滿足指定條件的元組（包括：比較大小、確定范圍、確定集合、字符匹配、涉及空值和多種條件查詢）； 對查詢結果排序； 使用集函數； 對查詢結果分組（7）熟練掌握以下練習，并進行下列各類連接查詢： 等值與非等值連接查詢； 自身連接； 外連接； 復合條件連接；（8）掌握嵌套查詢的基本方法；（9）掌握集合查詢的基本方法；（10）熟練掌握查詢視圖操作（11）熟練掌握更新視圖操作（12）熟練關系的完整性概念，領會視圖的用途（13）練習以下各類查詢： 帶有IN謂詞的子查詢； 帶有比較運算符的子查詢； 帶有ANY或ALL謂詞的子查詢； 帶有EXISTS謂詞的子查詢； 視圖查詢與更新操作；2. 實驗內容（1）根據以下給定的數據表信息分別對student, course, score, teacher, teching 表進行數據插入和修改，以完成給定的數據錄入，同時要求輸入班級同學名錄模擬客觀真實情況，給student、score表添加30條以上記錄，以便以后查詢應用方便。Student表：SnosnameSbirthdaySclass011110101章海潮1982.02.07信管系0101011111103王天力1982.03.04電商系0101011111204董丞悟1982.06.06電商系0102011112110馬麗鵑1982.04.03工商系0101011113104楊乾坤1982.03.23會計系0101011113221劉抗日1983.01.16會計系0102011114111丘海棠1983.02.08旅游系0101011115208齊振國1982.07.22旅游系0102001011101王曉悅1982.01.12材料系0001001011212程伊蓮1982.03.21材料系0002代碼如下：插入數據(30條)：use student_managment;insert into student (sno,sname,sbirthday,sclass) values(011110101,章海潮,19820207,信管系),(011111103,王天力,19820304,電商系),(011111204,董丞悟,19820606,電商系),(011112110,馬麗鵑,19820403,工商系),(011113104,楊乾坤,19820323,會計系),(011113221,劉抗日,19830116,會計系),(011114111,丘海棠,19830208,旅游系),(011115208,齊振國,19820722,旅游系),(001011101,王曉悅,19820112,材料系),(011111212,程伊蓮,19820321,材料系),(011115210,娜迦,19820622,電商系0102),(011115211,大娜迦,19830512,電商系0101),(011115212,美杜莎,19830203,天災系0102),(011115213,影魔sf,19820906,天災系0102),(011115214,沙王,19850312,電商系0102),(011115215,劍圣,19830112,天災系0102),(011115216,食人魔,19820521,天災系0102),(011115217,惡魔巫師,19820813,近衛(wèi)系0102),(011115218,潮汐獵人,19861012,近衛(wèi)系0102),(011115219,賞金獵人,19861121,近衛(wèi)系0102),(011115220,圣堂刺客,19840325,近衛(wèi)系0102),(011115221,隱形刺客, 19820623,近衛(wèi)系0102),(011115222,幻影刺客,19820721,物流系0102),(011115223,沉默術士,19870213,物流系0102),(011115224,遺忘法師,19820326,物流系0102),(011115225,狼人,19820506,敏捷系0102),(011115226,山嶺巨人,19821013,力量系0102),(011115228,大魔導師,19850623,智力系0102),(011115229,秀逗導師,19820703,敏捷系0102),(011115227,暗影惡魔,19850722,敏捷系0102);course表：CnoCnameCpnoCreditCtimeC001數據庫原理C005464C002高等數學472C003管理信息系統(tǒng)C001454C004操作系統(tǒng)C006332C005數據結構C007432C006計算機文化基礎232C007C語言程序設計C006332代碼如下：use student_managment;insert into course (cno,cname,credit,ctime) values(C002,高等數學,4,72),(C006,計算機文化基礎,2,32);insert into course (cno,cname,cpno,credit,ctime) values(C003,管理信息系統(tǒng),C001,4,54);insert into course (cno,cname,cpno,credit,ctime) values(C004,操作系統(tǒng),C006,3,32),(C001,數據庫原理,C005,4,64),(C007,C語言程序設計,C006,3,32),(C005,數據結構,C0074,32);score表：SnoCnoScore011110101C00190011111103C00185011111204C00173011112110C00198011113104C00196011113221C00197011114111C00183011115208C00185011110101C00792011111103C00797代碼如下：use student_managment;insert into score (sno,cno,score) values(011111103,C003,85),(011111103,C002,97),(011111103,C004,85),(011111103,C005,97),(011111103,C006,85),(011111103,C008,97),(011111103,C009,85),(011111103,C010,97),(001011212,C001,65),(001011212,C008,92),(001011212,C003,80),(001011212,C002,85),(001011212,C004,89),(001011212,C005,77),(001011212,C006,66),(001011212,C009,73);teacher表：TnoTnameTsexDepartmentT001江承基男信息管理系T002梁其征男信息管理系T003徐茉莉女信息管理系T004吳雅云女信息管理系T005楊運鐸男電子商務系T006張昌盛男電子商務系T007程力衡男電子商務系代碼如下：use student_managment;insert into teacher (tno,tname,tsex,department) values(T001,江承基,男,信息管理系),(T002,梁其征,男,信息管理系),(T003,徐茉莉,女,信息管理系),(T004,吳雅云,女,信息管理系),(T005,楊運鐸,男,電子商務系),(T006,張昌盛,男,電子商務系),(T007,程力衡,男,電子商務系);Teaching表TnocnotdateclassroomsclassT001C0051988-01-09西二405信管系0101T002C0071990-02-01西二406電商系0101T003C0011985-09-10西二504電商系0102T004C0061984-07-15主401工商系0101T005C0041989-11-12主402會計系0101T006C0021991-05-21西二506會計系0102代碼如下：insert into teaching (tno,cno,tdate,classroom,sclass) values(T001,C005,19880109,西二405,信管系0101),(T002,C007,19900201,西二406,電商系0101),(T003,C001,19850910,西二504,電商系0102),(T004,C006,19840715,主401,工商系0101),(T005,C004,19891112,主402,會計系0101),(T006,C002,19910521,西二506,會計系0102);（2）將兩個同學（數據自己臨時設置，用后即刪除）的兩門課程的成績以運行SQL語句插入的形式加入score表中。該題用以驗證、理解和掌握關系模型的完整性規(guī)則；insert into score (sno,cno,score) values(011111993,C003,85),(011111993,C002,97);（3）求每門課的平均成績，并把結果存入average表；insert into average(cno,av_score) select cno,AVG(score) from scoregroup by cno;（4）將學生“馬麗鵑”的出生日期改為“1982.8.20”；USE appleUPDATE StudentSET sbirthday =1982-08-20WHERE sno =011112110;（5）將所有學生的zipcode屬性列值填補上；INSERT INTO Student（zipcode） values（230009);（6）將average表中的所有課程的平均成績置零； UPDATE average SET Avg_score=0;（7）刪除average表中的課程號為c007的平均成績記錄； DELETE FROM average WHERE cno=c007;（8）刪除所有average表中平均成績記錄； DELETE FROM average;（9）建立一個臨時學生信息表（tstudent），刪除該表中的學號前六位為001011的所有學生記錄。 CREATE TABLE tstudent( sno Char(9),snameChar(10) constraint c1 not null,sbirthdayDatetime,ssexChar(2) constraint c2 check(ssex in (男,女),sclassChar(20),sremarkChar(100),addressChar(40),zipcodeChar(6),phoneChar(15),emailChar(40), constraint pk_student primary key(sno) );INSERT INTO tstudentSELECT * FROM Student;DELETE FROM tstudent（10）查詢全體學生的學號與姓名；USE appleSELECT sno,snameFROM student1（11）查詢全體學生的學號、姓名、所屬班級；USE appleSELECT sno,sname,sclassFROM student1（12）查詢全體學生的詳細記錄；USE appleSELECT *FROM student1（13）查詢全體學生的姓名及其年齡；（14）查詢全體學生的姓名、出生年份；USE appleSELETE sname,sbirthdayFROM student1（15）查詢所有修過課的學生的學號；USE appleSELECT sno FROM score（16）查詢“信管系0101”班全體學生名單；USE appleSELETE snameFROM student1WHERE sclass= 信管系0101（17）查詢查詢所有年齡在27歲以下的學生姓名及其年齡；select sname,2011-YEAR(sbirthday) from student where YEAR(sbirthday) 1984;-年齡小于27歲就是出生年份大于2011減去27即1984年就行了-（18）查詢考試成績有不及格的學生的學號；select sno from score where score 1984;-注釋年齡小于歲就是出生年份大于減去即年就行了-（32）查找選修了“C001”號課程的學生學號及其成績，查詢結果按分數降序排序；select sno,scorefrom score where cno = C001 order by score desc;（33）查詢全體學生情況，查詢結果按所在班級升序排列，對同一班級中的學生按年齡降序排列；select *from studentorder by sclass,sbirthday desc;（34）查詢學生總人數；select COUNT(*)from student;（35）查詢選修了課程的學生人數；select COUNT(distinct sno) from score;（36）在所有課程中查詢最高分的學生學號和成績；select sno,max(score)from score group by sno;（37）查詢學習“C001”課程的學生最高分數；select max(score)from score where cno = C001;（38）計算各個課程號與相應的選課人數；select cno,count(sno)from score group by cno（39）查詢“電商系0102”班選修了兩門課程以上的學生學號、姓名；select sname,sno from studentwhere sno in(select score.sno from student,scorewhere sclass = 電商系group by score.snohaving COUNT(*)2);（40）自然連接student和score表；Select student.sno, sname, sbirthday, sclass,ssex, sremark, phone, zipcode, email,cno,score from student,score where student.sno = score.sno;（41）使用自身連接查詢每一門課程的間接先行課（即先行課的先行課）select o,second.cpno from course first,course secondwhere first.cpno = o;（42）使用復合條件連接查詢選修“c001”號課程且成績在90分以上的所有同學；select student.sno,sname from student,scorewhere cno = C001 and score 90（43）使用復合條件連接查詢每個學生選修的課程名及其成績；select cname,score.scorefrom course,score where o = o（44）查詢選修了全部課程的學生；select sname,sno from student where not exists(select * from course where not exists(select * from score where sno = student.sno and cno =o);（45）查詢至少選修全部學分數為4個學分的課程的學生的學號、姓名；select sno,sname from student where sno in (select sno from score,coursewhere o = ogroup by score.snohaving SUM(credit) = 4);（46）查詢所有選修了C001號課程的學生學號、姓名；select sname,student.sno from student,scorewhere cno = C001 and student.sno = score.sno;（47）查詢選修了課程C001或c007的學生學號、姓名；select sname,student.sno from student,scorewhere student.sno = score.sno and cno = C001 or cno = C007 ;（48）查詢“會計系0102”班的學生及年齡不大于27歲（現(xiàn)有年齡）的學生；select sname,sno from studentwhere sclass = 會計系 and YEAR(sbirthday) = 1984;-年齡不大于歲就是出生年份大于減去即年就行了-（49）查詢既選修了課程C001又選修了課程c007的所有學生學號、姓名；select sno from score where cno = C001 intersectselect sno from score where cno = C007-或者select sno from score where cno = C001 and sno in (select snofrom scorewhere cno = C007);（50）查詢選修了課程名為“數據庫原理”的學生的學號、姓名、性別、年齡；select student.sno,sname,ssex,2011-YEAR(sbirthday)from student,score,course where o = o and score.sno = student.sno and cname = 數據庫原理;（51）查詢其他班中比“信管系0101”班所有學生年齡都小的學生名單；select sno,sname,ssex,2011-YEAR(sbirthday) from studentwhere sclass = (select sclass from student where sname = 張振國)and sname 張振國;（52）查詢與“齊振國”在同一個班學習的學生學號、姓名、性別、年齡；select sno,sname,2011-YEAR(sbirthday) from studentwhere year(sbirthday) (select YEAR(sbirthday) from student where sclass = 信管系)and sclass 信管系;-將年齡轉化為出生日期的年份-（53）建立“信管系0101”班學生的視圖，定義視圖名為“info_student1”；create view info_student1 as select * from student where sclass = 近衛(wèi)系;（54）建立“信管系0101”班學生的視圖，定義視圖名為“info_student2”，并要求進行修改與插入時，仍須保證該視圖只有“信管系0101”班學生；create view info_student2 as select * from studentwhere sclass = 近衛(wèi)系with check option;（55）建立“信管系0101”班選修了“C001”課程的學生的視圖，定義視圖名為“info_C001_student1”；create view info_C001_student1 as select student.sno,sname,sclass,sbirthday from student,scorewhere sclass = 近衛(wèi)系 and cno = C001（56）建立“信管系0101”班選修了“C001”課程且成績在90分以上的學生的視圖，定義視圖名為“info_c001_student2”；create view info_C001_student2 as select student.sno,sname,sclass,sbirthday from student,scorewhere sclass = 近衛(wèi)系 and score 90;（57）定義一個反映學生年齡的視圖，定義視圖名為“vbirthday_student”；create view vbirthday_studentas select sno,sbirthday from student;（58）將學生表中所有女生記錄定義為一個視圖，視圖名為“vfemale_student”；create view vfemale_student as select sno,sbirthday,sname,ssex from studentwhere ssex = 女;（59）將學生的學號及其平均成績定義為一個視圖，視圖名為“vaverage_student”；create view vaverage_student (sno,average) as select sno,AVG(score) from scoregroup by sno;（60）刪除視圖“info_student1”，刪除后即重建；drop view info_student1;（61）在“信管系0101”班學生視圖中找出年齡小于27歲（現(xiàn)在的年齡）的學生；select sno,2011-YEAR(sbirthday) from info_student1where YEAR(sbirthday) 1984;-年齡小于歲就是出生年份大于減去即年就行了-（62）利用視圖查詢“信管系0101”班選修了“C001”課程的學生；select distinct sno,sname from info_C001_student1;（63）通過“信管系0101”班info_student2視圖中學號“011111103”的學生姓名改為“潘長江”；update info_student2 set sname = 潘長江 where sno = 011115219;（64）向“信管系0101”班info_student1視圖中插入一個新學生記錄，其中：學號：011111136，姓名：張藝謀，性別：男，出生日期：1987.11.9；Insert into info_student1 (sno,sname,ssex,sbirthday) Values (011111136,張藝謀,男,19871109);（65）通過視圖info_student1刪除信管系0101班學號為“011111135”、姓名為“黃健中”的學生記錄；Delete from info_student1 where sno = 011111135 and sname = 黃健中;三、實驗心得有關于數據庫實驗的心得體會，總的來說，受益匪淺。在這短短的一天試驗中，我們學到了很多東西，包括建表，導入數據，查詢，插入。最重要的是我們有機會用電腦自己進行實踐，沒接觸的時候總是覺得它比較深奧或是不可接近的新型語言，盡管自己對C語言非常感興趣，但還是有些心理上的陌生感。學習數據庫就和我們平時的其它科目學習一樣感覺它有永無止境的知識，數據庫是我在高中時候聽過，到了大學漸漸了解了些，但就其原理性的內容還不知道，也就是根本就不清楚什么是數據庫，只是知道一個所謂的中國字典里的名詞。我認識它是從我接觸實驗運作開始的，剛開始就是建立數據庫，兩種驗證模式，沒什么東西但還覺得不錯。進而就是操作語言了，緊接著就是觸發(fā)器的使用，進而對數據庫高級的使用，等等。 開始知道數據庫的時候想學，不知道從何而起，不懂的話怎么問，從什么地方學起。后來到大三開學后有數據庫原理必修課，非常高興。當時感覺SQL Sever數據庫管理既然是單獨一門課程一定會講的比較細，也能學到真正實用的內容。學了這門課以后發(fā)現(xiàn)和我想的基本是一樣的，老師對學生也比較和藹可親