廣工工業(yè)大學(xué)數(shù)據(jù)庫實驗指導(dǎo)及報告

編號： 2012版數(shù)據(jù)庫原理及應(yīng)用實驗報告實驗時間： 2011-2012春季 實驗班級： 自動化電氣09級 班 實驗報告總份（片）數(shù)： 份（片）實驗教師： 鮑芳 實驗中心 508 實驗室廣東工業(yè)大學(xué)實驗報告（范本，非設(shè)計性實驗）要求采用學(xué)校的實驗報告書（16開）。如打印也應(yīng)采用實驗報告書的版頭（A4紙），不接受電子版!每份實驗報告必須包含以下5項：一、 實驗?zāi)康呐c要求（按實驗指導(dǎo)書“實驗?zāi)康摹碧顚懀┒?實驗方案（按實驗指導(dǎo)書“實驗步驟”及本文檔的實驗內(nèi)容要求填寫）三、 實驗結(jié)果和數(shù)據(jù)處理（按實驗操作結(jié)果記錄填寫）四、 結(jié)論（可根據(jù)實驗內(nèi)容給出所對應(yīng)數(shù)據(jù)庫理論要點）五、 問題與討論（可回答思考題或?qū)嶒炛杏龅降膯栴}及解決方法）若在報告中描述你在實驗中遇到的問題及解決方案，將有機會獲得額外加分！廣東工業(yè)大學(xué)實驗報告_自動化_學(xué)院_11電氣_專業(yè)_2_班 成績評定_學(xué)號_姓名_張錦淇_(合作者_(dá)號_ 教師簽名_預(yù)習(xí)情況操作情況考勤情況數(shù)據(jù)處理情況實驗 一 題目 數(shù)據(jù)庫系統(tǒng)設(shè)計 第_周星期_第_節(jié)一、 實驗?zāi)康呐c要求掌握數(shù)據(jù)庫的基本設(shè)計技術(shù)，熟悉數(shù)據(jù)庫概念模型設(shè)計的每個任務(wù)步驟，并加深對關(guān)系數(shù)據(jù)庫概念和特點的理解。要求學(xué)生根據(jù)周圍的實際情況自選一個小型的數(shù)據(jù)庫應(yīng)用項目并深入到應(yīng)用項目的現(xiàn)實世界中進行系統(tǒng)分析和數(shù)據(jù)庫設(shè)計，例如選擇學(xué)生成績管理系統(tǒng)、圖書管理系統(tǒng)或倉庫管理系統(tǒng)等。學(xué)生成績管理數(shù)據(jù)庫的基本要求：1. 能夠記錄學(xué)生的基本信息，包括學(xué)號、姓名、性別、出生日期及所屬學(xué)院；2. 能夠記錄課程的基本信息，包括課程號，課程名，先行課號；3. 能夠記錄學(xué)生選課情況及選修課程的成績；（學(xué)生成績管理系統(tǒng)及圖書管理系統(tǒng)數(shù)據(jù)庫應(yīng)用項目介紹可參見蔡延光數(shù)據(jù)庫原理及應(yīng)用第11章或苗雪蘭數(shù)據(jù)庫原理及應(yīng)用第5章）。給出各表的關(guān)系結(jié)構(gòu)和表級約束、給出各屬性域的描述（包括物理描述和語義描述）；二、 實驗方案利用數(shù)據(jù)庫設(shè)計的5個步驟（識別實體、確定關(guān)系、列出屬性、確定鍵及屬性的域?qū)傩缘挠颍?，對學(xué)生成績管理系統(tǒng)（必選）、圖書管理系統(tǒng)（可選）使用鴨腳模型對學(xué)生選課系統(tǒng)（必選）及圖書管理系統(tǒng)進行相關(guān)數(shù)據(jù)庫的數(shù)據(jù)建模及設(shè)計。l 使用ChenERD或鴨腳模型表示所設(shè)計的概念模型；l 給出各表的關(guān)系結(jié)構(gòu)和表級約束；l 給出各屬性域的描述，包括物理描述和語義描述；三、 數(shù)據(jù)庫應(yīng)用項目進行概念模型設(shè)計實驗結(jié)果和數(shù)據(jù)處理1. 識別實體，確定關(guān)系：學(xué)生（學(xué)號、姓名、性別、出生日期、學(xué)院）課程（課程號、課程名、現(xiàn)行課號）選課（學(xué)號、課程號、成績）說明：作為主鍵，學(xué)號必須存在學(xué)生表中、課程號必須存在課程表中。2. 列出屬性及屬性的域1) 學(xué)生表屬性類型約束學(xué)號Char(20)主鍵姓名Char(20)NULL性別Char(20)男 或 女出生日期smalldatetimeNULL學(xué)院VarcharNOT NULL2) 課程表屬性類型約束課程號Char(20)主鍵課程名Char(20)NOT NULL先行課號Char(20)NULL3) 選課表學(xué)號Char(20)外鍵課程號Char(20)外鍵成績int01003. 鴨腳模型 課程課程號（PK）課程名先行課程號 學(xué)生 學(xué)號（PK）姓名性別出生日期學(xué)院 選課學(xué)號（FK）課程號（FK）成績 4. 關(guān)系結(jié)構(gòu)和表級約束最大基數(shù)：學(xué)生與選課為M：N課程與選課為1：N最小基數(shù)：學(xué)生與選課為1-0關(guān)系課程與選課為1-0關(guān)系表級約束：1）性別只可以取男或女2）選課成績依賴于課程號和學(xué)生號四、 結(jié)論答：數(shù)據(jù)庫不僅僅是保存數(shù)據(jù)，而且保存了數(shù)據(jù)之間的關(guān)系。多表之間存在約束條件，由業(yè)務(wù)的規(guī)則設(shè)定，多表保證了信息的完整性，每個表都有自己的主鍵，表中的外鍵要參照完整性約束。五、 問題與討論1. 關(guān)系數(shù)據(jù)庫中如何保存實體之間的關(guān)系？2. 學(xué)生選課系統(tǒng)中屬性學(xué)號、課程號及成績采用哪種數(shù)據(jù)類型更好，數(shù)值型還是字符型？為什么？3. 在所設(shè)計的數(shù)據(jù)模型中體現(xiàn)的業(yè)務(wù)規(guī)則是什么？1.答：通過表和表中放置外鍵來保存關(guān)聯(lián)表之間的聯(lián)系。2.答：學(xué)號，課程號采用數(shù)值型的數(shù)據(jù)更好，可以防止學(xué)生在輸入學(xué)號的時候出錯。3.答：體現(xiàn)出來的業(yè)務(wù)規(guī)則是：首先要有學(xué)生號和課程號，才能在選課表中生成一個記錄（參照完整性約束）。其次，一個學(xué)生可以選擇多門課，也可以不選課；一門課可以被多個學(xué)生選擇，也可以沒有學(xué)生選擇。廣東工業(yè)大學(xué)實驗報告_自動化_學(xué)院 _11電氣_專業(yè)_2_班 成績評定_學(xué)號_ 姓名_張錦淇 (合作者_(dá)號_)教師簽名_預(yù)習(xí)情況操作情況考勤情況數(shù)據(jù)處理情況實驗 二 題目 數(shù)據(jù)庫的定義實驗 第_周星期_第_節(jié)一、 實驗?zāi)康呐c要求要求熟練掌握和使用SQL、SQL Server企業(yè)管理器創(chuàng)建數(shù)據(jù)庫、表、索引和修改表結(jié)構(gòu)，并學(xué)會使用SQL Server查詢分析器接受SQL語句和進行結(jié)果分析。二、 實驗方案1、 在企業(yè)管理器中創(chuàng)建及打開數(shù)據(jù)庫，在“新建數(shù)據(jù)庫”時指明數(shù)據(jù)文件及事務(wù)日志的位置；2、 在SQL Server查詢分析器中用SQL語句實現(xiàn)以下各表：學(xué)生（學(xué)號，姓名，性別，出生日期，學(xué)院）；課程（課程號，課程名，先行課號）；選課（學(xué)號，課程號，成績）；3、建立庫、表和表間的聯(lián)系，選擇合適的數(shù)據(jù)類型，定義必要的列級約束（包括性別約束和成績約束）和表級約束。創(chuàng)建數(shù)據(jù)庫中的表后，在查詢分析器的對象瀏覽器中可見該表及其所擁有的約束：學(xué)生：2課程：1選課：4三、 實驗結(jié)果和數(shù)據(jù)處理create table 學(xué)生( 學(xué)號 char(20) primary key , 姓名 varchar(20) , 性別 char(20) check (性別 in (男 ,女) ), 出生日期 smalldatetime, 學(xué)院 varchar(20)create table 課程(課程號 char (20) primary key , 課程名 char (20), 先行課號 char(20) )create table 選課( 學(xué)號 char (20) foreign key (學(xué)號) references 學(xué)生 (學(xué)號) , 課程號 char(20) foreign key (課程號) references 課程 (課程號), 成績 int check (成績=0 and 成績 (select 成績from 選課where 課程號=C1 and 學(xué)號in(select 學(xué)號from 學(xué)生where 姓名=張三)3. 求選修C2課程的學(xué)生姓名select 姓名from 學(xué)生where 學(xué)號in (select 學(xué)號from 選課where 課程號=C2)4. 求沒有選修C2課程的學(xué)生姓名select 姓名from 學(xué)生where 學(xué)號not in (select 學(xué)號from 選課where 課程號=C2)5. 求選修C1課程而沒有選修C2課程的學(xué)生姓名select 姓名from 學(xué)生where 學(xué)號in(select 學(xué)號from 選課where 課程號=C1)and 學(xué)號not in(select 學(xué)號from 選課where 課程號=C2)四、 結(jié)論 對索引優(yōu)化，建索引的選擇必須結(jié)合SQL查詢、修改、刪除語句的需要，一般的說法是在WHERE里經(jīng)常出現(xiàn)的字段建索引。如果在WHERE經(jīng)常是幾個字段一起出現(xiàn)而且是用AND連接的，那就應(yīng)該建這幾個字段一起的聯(lián)合索引，而且次序也需要考慮，一般是最常出現(xiàn)的放前面。五、 問題與討論1、 嵌套查詢和連接查詢有何區(qū)別？在本實驗的查詢?nèi)蝿?wù)中，哪些是可以由嵌套查詢和連接查詢實現(xiàn)？當(dāng)查詢同時涉及兩個以上的表時，稱為連接查詢。連接查詢包括等值連接查詢、自然連接查詢、非等值連接查詢、自身連接查詢、外連接查詢和復(fù)合條件查詢。在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另外一個查詢塊的WHERE字句或HAVING短語的條件中的查詢稱為嵌套查詢。連接查詢：求選修了高等數(shù)學(xué)的學(xué)生學(xué)號和姓名，求選修C2課程的學(xué)生姓名。嵌套查詢：求C1課程的成績高于張三的C1課程成績的學(xué)生學(xué)號和成績，求選修C1課程而沒有選修C2課程的學(xué)生姓名2、 描述實驗中遇到的問題及解決方法在寫SQL語句使因為空格，半角標(biāo)點等問題系統(tǒng)經(jīng)常出錯，經(jīng)過排查后，知道以后寫SQL時格式要注意的內(nèi)容。廣東工業(yè)大學(xué)實驗報告_自動化_學(xué)院 _11電氣_專業(yè)_2_班 成績評定_學(xué)號_姓名_張錦淇_ (合作者_(dá)號_) 教師簽名_預(yù)習(xí)情況操作情況考勤情況數(shù)據(jù)處理情況實驗 六 數(shù)據(jù)庫統(tǒng)計查詢實驗 第_周星期_第_節(jié)一、 實驗?zāi)康呐c要求熟悉掌握SQL Server查詢分析器的使用方法，加深對SQL語言查詢語句的理解，熟悉掌握數(shù)據(jù)查詢中的分組和統(tǒng)計的操作方法。二、 實驗方案通過SQL Server查詢分析器實現(xiàn)以下對數(shù)據(jù)庫的統(tǒng)計查詢；1. 用SQL語句實現(xiàn)學(xué)生人數(shù)的統(tǒng)計；2. 用SQL語句實現(xiàn)計算機系學(xué)生人數(shù)的統(tǒng)計；3. 用SQL語句實現(xiàn)各系學(xué)生人數(shù)的統(tǒng)計；4. 用SQL語句統(tǒng)計選修某課程學(xué)生的人數(shù)；5. 用SQL語句統(tǒng)計選修了課程的學(xué)生的人數(shù)；6. 用SQL語句統(tǒng)計被選課程及選修該課程的人數(shù)；7. 用SQL語句統(tǒng)計每個學(xué)生全部課程的平均成績；8. 用SQL語句統(tǒng)計選修課超過3門課的學(xué)生學(xué)號；（可選）三、實驗結(jié)果和數(shù)據(jù)處理1. 用SQL語句實現(xiàn)學(xué)生人數(shù)的統(tǒng)計select count(*) as 學(xué)生人數(shù)from 學(xué)生2. 用SQL語句實現(xiàn)計算機系學(xué)生人數(shù)的統(tǒng)計select count(*) as 計算機系人數(shù)from 學(xué)生where 學(xué)院=計算機系3. 用SQL語句實現(xiàn)各系學(xué)生人數(shù)的統(tǒng)計select 學(xué)院, count(*) as 學(xué)生人數(shù)from 學(xué)生group by 學(xué)院4. 用SQL語句統(tǒng)計選修某課程學(xué)生的人數(shù)select count(*) as 微機原理課程人數(shù)from 學(xué)生where 學(xué)號in (select 學(xué)號from 選課where 課程號in (select 課程號from 課程where 課程名=微機原理)5. 用SQL語句統(tǒng)計選修了課程的學(xué)生的人數(shù)select count(*) as 已選修課程人數(shù)from 學(xué)生where 學(xué)號in (select 學(xué)號from 選課where 課程號is not null)6. 用SQL語句統(tǒng)計被選課程及選修該課程的人數(shù)select 課程號,count(學(xué)號) as 學(xué)生人數(shù)from 選課group by 課程號7. 用SQL語句統(tǒng)計每個學(xué)生全部課程的平均成績select 學(xué)號, avg(成績) as 平均成績from 選課group by 學(xué)號8. 用SQL語句統(tǒng)計選修課超過3門課的學(xué)生學(xué)號select 學(xué)號from 選課group by 學(xué)號 having count(*)3四、結(jié)論SQL Server包括很多內(nèi)置的函數(shù)。它們中的一些被稱為聚集函數(shù)，聚集函數(shù)在結(jié)果列表中提供匯總值。分組查詢使用GROUP BY字句。HAVING字句用于篩選分組的最終結(jié)果，只有滿足HAVING字句指定條件的組才輸出。五、問題與討論1、試用GROUP BY（分組條件）字句后，語句中的統(tǒng)計函數(shù)的運行結(jié)果有什么不同？運行結(jié)果會根據(jù)GROUP BY字句中的條件進行一定順序的排列。2、描述實驗中遇到的問題及解決方法對group by 語句還不太了解如何使用，經(jīng)過本實驗，知道此語句是使結(jié)果按照指定的順序排列。廣東工業(yè)大學(xué)實驗報告_自動化_學(xué)院 _11電氣_專業(yè)_2_班 成績評定_學(xué)號_張錦淇_姓名_張錦淇_(合作者_(dá)號_) 教師簽名_預(yù)習(xí)情況操作情況考勤情況數(shù)據(jù)處理情況實驗七題目 數(shù)據(jù)庫的視圖和圖標(biāo)的定義及使用實驗 第_周星期_第_節(jié)一、 實驗?zāi)康呐c要求掌握SQL Server中視圖的創(chuàng)建和使用，加深對視圖和SQL Server圖表作用的理解。二、 實驗方案通過SQL Server實現(xiàn)數(shù)據(jù)庫的視圖的創(chuàng)建及使用，完成以下任務(wù)：1、建立計算機系學(xué)生的視圖；2、由學(xué)生、課程和選課三個表，定義一個計算機系的學(xué)生成績視圖，其屬性包括學(xué)號、姓名、課程名和成績；3、將學(xué)生的學(xué)號、總成績、平均成績定義成一個視圖；4、統(tǒng)計學(xué)生的學(xué)號、姓名、總成績及平均成績（可利用3的視圖或group by 學(xué)生.學(xué)號,學(xué)生.姓名）；三、實驗結(jié)果和數(shù)據(jù)處理1、建立計算機系學(xué)生的視圖create view 計算機學(xué)生信息as select 學(xué)號,姓名,性別,出生日期,學(xué)院from 學(xué)生where 學(xué)院=計算機系2、由學(xué)生、課程和選課三個表，定義一個計算機系的學(xué)生成績視圖，其屬性包括學(xué)號、姓名、課程名和成績create view 計算機系學(xué)生成績as select 學(xué)生.學(xué)號,姓名,課程名,成績from 學(xué)生,課程,選課where 學(xué)生.學(xué)號=選課.學(xué)號and 選課.課程號=課程.課程號and 學(xué)生.學(xué)院=計算機系3、將學(xué)生的學(xué)號、總成績、平均成績定義成一個視圖create view 學(xué)生個人成績as select 學(xué)號, sum (成績) as 總成績,avg(成績) as 平均成績from 選課group by 學(xué)號4、統(tǒng)計學(xué)生的學(xué)號、姓名、總成績及平均成績（可利用3的視圖或group by 學(xué)生.學(xué)號,學(xué)生.姓名）create view 學(xué)生個人成績 as select 學(xué)生.學(xué)號,學(xué)生.姓名,sum (成績) as 總成績,avg(成績) as 平均成績from 選課,學(xué)生where 學(xué)生.學(xué)號=選課.學(xué)號group by 學(xué)生.學(xué)號,學(xué)生.姓名四、結(jié)論視圖是關(guān)系數(shù)據(jù)庫系統(tǒng)提供給用戶以多角度觀察數(shù)據(jù)庫中數(shù)據(jù)的一種重要方法。視圖就是從一個或幾個基本表（或視圖）導(dǎo)出的虛表。視圖在概念上與基本表等同，視圖一經(jīng)定義，則可以和基本表一樣進行查詢、刪除、更新等操作，而且用戶可以在視圖上再定義視圖。五、問題與討論1、為什么要建立視圖？視圖和基本表有什么不同？答：視圖能夠簡化用戶的操作，視圖能夠以多角度觀察數(shù)據(jù)庫中的數(shù)據(jù)，視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性，視圖能夠?qū)C密數(shù)據(jù)提供安全保護，適當(dāng)?shù)睦靡晥D可以更加清晰的表達(dá)查詢。能夠讓用戶對感興趣的項目一目了然。2、group by語句在使用時需要注意什么事項？GROUP BY 語句中的表達(dá)式必須與選擇列表表達(dá)式完全匹配。3、描述實驗中遇到的問題及解決方法在進行第一個任務(wù)時，是要求查詢 “計算機”學(xué)生的信息，但是我的數(shù)據(jù)庫的屬性是“計算機系”，一開始where語句后面我寫的是學(xué)院=計算機，出來的視圖全為NULL，后來改為學(xué)院=計算機系，就能將計算機的學(xué)生的信息視圖出來了。在進行第4個任務(wù)時，select語句中的表達(dá)式?jīng)]有和group by 中的表達(dá)式完全一致，系統(tǒng)報錯，將它們改為一致后，命令就能夠成功執(zhí)行。2011數(shù)據(jù)庫原理及應(yīng)用實驗安排（實驗一至實驗七）一、實驗注意事項：1. 本實驗使用MS SQL SERVER 2000的企業(yè)管理器及查詢分析器；2. 在企業(yè)管理器中創(chuàng)建及打開數(shù)據(jù)庫，在“新建數(shù)據(jù)庫”時要指明數(shù)據(jù)文件及事務(wù)日志的位置（可指向U盤），不要使用缺省設(shè)置；3. 在查詢分析器中輸入SQ