實訓1E-R模型設計根據(jù)以下情況，為某一計算機經(jīng)銷商設計一個數(shù)據(jù)庫。實際情況描述如下。該經(jīng)銷商信息包括生產(chǎn)廠商、產(chǎn)品的信息。生產(chǎn)廠商的信息包括名稱、地址、電話等。產(chǎn)品的信息包括生產(chǎn)廠商、品牌、型號、配置、進價等。1．根據(jù)以上情況，對該數(shù)據(jù)庫進行第一層次的抽象設計。實體名稱屬性碼生產(chǎn)廠商名稱、地址、電話名稱產(chǎn)品生產(chǎn)廠商、品牌、型號、配置、進價型號2．一種產(chǎn)品可以由多個生產(chǎn)廠商提供，一個生產(chǎn)廠商也可以生產(chǎn)多種不同的產(chǎn)品，根據(jù)以上情況，為這兩個實體建立聯(lián)系。聯(lián)系名稱聯(lián)系的類型聯(lián)系集中的碼提供多對多產(chǎn)品型號、廠商名稱3．用E-R圖描述該數(shù)據(jù)庫。名稱生產(chǎn)廠商生產(chǎn)廠商產(chǎn)品地址電話品牌型號配置進價提供名稱生產(chǎn)廠商生產(chǎn)廠商產(chǎn)品地址電話品牌型號配置進價提供mn4．若向該數(shù)據(jù)庫中加入顧客信息，包括姓名、地址、身份證號等，一個顧客可以購買不同的產(chǎn)品，一種產(chǎn)品可以賣給不同的顧客，請修改該數(shù)據(jù)庫，并用E-R圖加以描述。名稱名稱生產(chǎn)廠商生產(chǎn)廠商產(chǎn)品地址電話品牌型號配置進價提供顧客身份證號姓名地址購買mnnm5．用E-R圖表示某倉庫管理的概念模型。各實體如下所示。倉庫：倉庫號、面積、電話號碼。零件：零件號、名稱、規(guī)格、單價、描述。供應商：供應商號、姓名、地址、電話號碼、賬號。職工：職工號、姓名、年齡、職稱。各實體間的聯(lián)系如下所示。一個倉庫可以存放多種零件，一種零件可以存放在多個倉庫中。一個倉庫有多個職工當倉庫員，一個職工只能在一個倉庫工作。職工之間具有領導與被領導關系，即倉庫主任領導若干個保管員。一個供應商可以供給多種零件，一種零件可以由多個供應商供給。要求：請繪制E-R圖，并注明屬性和聯(lián)系類型。實訓2關系模型設計1．在下面的E-R圖中，若顧客在購買了某種產(chǎn)品后，需要記錄下日期和數(shù)量等特征，請問怎么修改E-R圖？參考答案：2．將下面的E-R圖轉換為關系模式，并寫出每個關系模式的主碼、外碼和它們之間的參照關系。參考答案：1）關系模式：倉庫（倉庫號，面積，電話號碼），主碼：倉庫號職工（職工號，姓名，年齡，職稱，倉庫主任，倉庫號），主碼：職工號零件（零件號，名稱，規(guī)格，單價，描述），主碼：零件號供應商（供應商號，姓名，地址，電話號碼，賬號），主碼：供應商號供給（零件號，供應商號，供給數(shù)量），主碼：（零件號，供應商號）存放（倉庫號，零件號，存放數(shù)量），主碼：（倉庫號，零件號）2）外碼與參照關系：“供給”關系中“零件號”是外碼，它參照的是“零件”關系的主碼；“供給”關系中“零供應商號”是外碼，它參照的是“供應商”關系的主碼；“存放”關系中“零件號”是外碼，它參照的是“零件”關系的主碼；“存放”關系中“倉庫號”是外碼，它參照的是“倉庫”關系的主碼；“職工”關系中的“倉庫主任”是外碼，它參照的是“職工”關系的主碼；“職工”關系中的“倉庫號”是外碼，它參照的是“倉庫”關系的主碼。3．參考下面的E-R圖，將其轉換成相應的關系模式，并寫出每個關系模式的主碼、外碼和它們之間的參照關系。參考答案：1）關系模式和主碼：職工（職工號，姓名，工資，聘期，工廠編號），主碼：職工號工廠（工廠編號，廠名，地址）生產(chǎn)（工廠編號，產(chǎn)品編號，數(shù)量）產(chǎn)品（產(chǎn)品編號，規(guī)格，產(chǎn)品名）2）外碼和參照關系描述：“職工”關系中的“工廠編號”是外碼，它參照“工廠”關系中的“工廠編號”；“生產(chǎn)”關系中的“工廠編號”是外碼，它參照“工廠”關系中的“工廠編號”；“生產(chǎn)”關系中的“產(chǎn)品編號”是外碼，它參照“產(chǎn)品”關系中的“產(chǎn)品編號”。4．將下面的E-R圖轉換為關系模式，并寫出每個關系模式的主碼、外碼和它們之間的參照關系。參考答案：1）關系模式和主碼：讀者（讀者號，姓名，地址，性別，年齡，單位），主碼：讀者號書（書號，書名，作者，出版社），主碼：書號借閱（讀者號，書號，借出日期，應還日期），主碼：（讀者號，書號）2）外碼和參照關系：“借閱”關系中的“讀者號”與“書號”均為外碼，它們分別參照“讀者”關系中的“讀者號”，“書”關系中的“書號”。實訓3關系數(shù)據(jù)庫設計下表是一個描述學生情況的關系SLC，該關系的每個字段都是不可再分的，其關鍵字為(學號,課程號)，兩個屬性的組合可以確定元組在關系中的位置。其中，學號是不能重復的，姓名是可以重復的，同樣課程號也是不能重復的。請回答以下問題。學號姓名學院學院地址課程號課程名成績1031231張小燕智能制造學院4號樓1關系數(shù)據(jù)庫應用901031231張小燕智能制造學院4號樓2Python語言基礎601031231張小燕智能制造學院4號樓3數(shù)據(jù)采集與預處理501031233陳毓蘭數(shù)字經(jīng)貿(mào)學院5號樓1關系數(shù)據(jù)庫應用1001031233陳毓蘭數(shù)字經(jīng)貿(mào)學院5號樓2Python語言基礎892021501賈勝紅人工智能學院2號樓1關系數(shù)據(jù)庫應用952021501賈勝紅人工智能學院2號樓2Python語言基礎872021502金建娥人工智能學院2號樓1關系數(shù)據(jù)庫應用731.指出表中對應的關系名、屬性、元組、主碼，并寫出該表對應的關系模式。參考答案：關系名：SLC；屬性：學號、姓名、學院、學院地址、課程號、課程名、成績；元組：8個主碼：(學號,課程號)關系模式：SLC（學號，姓名，學院，學院地址，課程號，課程名，成績）2．分析表中是否存在下列問題。數(shù)據(jù)冗余。數(shù)據(jù)更新異常。數(shù)據(jù)插入異常。數(shù)據(jù)刪除異常。參考答案：數(shù)據(jù)冗余：一個學生只有一個姓名，但在上表中，若一個學生選修多門課程，則該學生的姓名就要重復多次。數(shù)據(jù)更新異常：假如學生轉專業(yè)到其他學院了，那么上表中對應的記錄的學院都需要修改，假如改得不一樣，或少改一處，則會造成數(shù)據(jù)不一致。數(shù)據(jù)插入異常：假如新成立了一個學院：建工學院，但還沒有招學生，所以不能在表中插入學院的記錄，也就不能在數(shù)據(jù)庫中保存建工學院的相關信息。同樣，如果新增一門課程，但還沒有學生選修，則也不能插入該課程的記錄中。數(shù)據(jù)刪除異常：如果人工智能學院的學生全部畢業(yè)了，則需要刪除該學院的學生記錄，但如果該學院的學生記錄全部被刪除了，則該學院的名稱和學院地址也從數(shù)據(jù)庫中刪除了。3．參照下圖，描述該關系模式中存在的函數(shù)依賴關系。參考答案：“學號”和“活動項目”決定“費用”，即“費用”函數(shù)以來于“學號”和“活動項目”。4．對上述關系進行第二范式分解。參考答案：原始關系模式：SLC（學號，姓名，學院，學院地址，課程號，課程名，成績）第二范式分解：SL（學號，姓名，學院，學院地址）C（課程號，課程名，成績）分解后的SL和C關系模式中，非主屬性都完全函數(shù)依賴于主碼，故符合第二范式。5．對上述關系進行第三范式分解。SL（學號，姓名，學院，學院地址）C（課程號，課程名，成績）第三范式分解：S（學號，姓名，學院）L（學院，學院地址）C（課程號，課程名，成績）分解后的S、L、C三個關系模式，沒有存在非主屬性傳遞函數(shù)依賴或者非主屬性之間函數(shù)依賴的關系，故符合第三范式。6．設有關系模式S1(學號,姓名,出生日期,所在系,宿舍樓)，其語義為一個學生只在一個系學習，一個系的學生只住在一個宿舍樓里。請指出該關系模式的主碼，判斷該關系模式是第幾范式，若不是第三范式，則將其規(guī)范化為第三范式，并指出分解后的每個關系模式的主碼和外碼。參考答案：關系模式的主碼：學號該關系模式數(shù)據(jù)第二范式，因為宿舍樓函數(shù)依賴于所在系，因為存在非主屬性之間函數(shù)依賴關系，或者說是非主屬性傳遞函數(shù)依賴于主碼，所以不屬于第三范式。分解如下：S(學號,姓名,出生日期,所在系)L(所在系，宿舍樓)分解后的S和L兩個關系模式，不存在非主屬性之間函數(shù)依賴關系，或者說不存在非主屬性傳遞函數(shù)依賴于主碼，故屬于第三范式。7．設有關系模式S2(學號,姓名,所在系,班級號,班主任,系主任)，其語義為一個學生只在一個系的一個班學習，一個系只有一個系主任，一個班只有一個班主任。請指出該關系模式的主碼，判斷該關系模式是第幾范式，若不是第三范式，則將其規(guī)范化為第三范式，并指出分解后的每個關系模式的主碼和外碼。參考答案：該關系模式的主碼是：學號該關系模式不屬于第三范式，因為存在非主屬性之間函數(shù)依賴關系，或者說是非主屬性傳遞函數(shù)依賴于主碼，所以不屬于第三范式。S2(學號,姓名,所在系,班級號,班主任,系主任)分解如下：S21(學號,姓名,所在系,班級號)S22(班級號,班主任)S23(所在系,系主任)分解后的S21 、S22和S23三個關系模式，不存在非主屬性之間函數(shù)依賴關系，或者說不存在非主屬性傳遞函數(shù)依賴于主碼，故屬于第三范式。實訓4熟悉SQLServer環(huán)境及物理創(chuàng)建數(shù)據(jù)庫與表1．掌握MicrosoftSQLServer2012軟件的安裝，詳見安裝視頻。2．熟悉MicrosoftSQLServer2012的環(huán)境及使用。第1步：在“開始”菜單中找到“SQLServerManagementStudio”，如下圖所示。第2步：在進入主界面之前進行數(shù)據(jù)庫連接，如下圖所示。第3步：單擊“連接”按鈕，進入主界面，如下圖所示。3．物理創(chuàng)建數(shù)據(jù)庫：在“對象資源管理器”中右擊“數(shù)據(jù)庫”，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”命令，創(chuàng)建“職工工資管理系統(tǒng)”數(shù)據(jù)庫。（注意：創(chuàng)建后根目錄下會有兩個文件，分別是職工工資管理系統(tǒng).mdf和職工工資管理系統(tǒng)_log.ldf）參考答案：根據(jù)上面步驟創(chuàng)建后，在根目錄下截圖以下兩個文件4．物理創(chuàng)建數(shù)據(jù)表：打開在第3題中創(chuàng)建的“職工工資管理系統(tǒng)”數(shù)據(jù)庫，右擊“表”節(jié)點，在彈出的快捷菜單中選擇“新建表”命令，創(chuàng)建職工表、部門表、工資表，三個數(shù)據(jù)表的結構如下所示。職工表列名數(shù)據(jù)類型長度是否為空是否為主鍵職工編號int4不允許主鍵姓名char8不允許性別char2允許出生日期datetime8允許部門編號int4不允許部門表列名數(shù)據(jù)類型長度是否為空是否為主鍵部門編號int4不允許主鍵部門名稱char6不允許地址char20允許工資表列名數(shù)據(jù)類型長度是否為空是否為主鍵職工編號int4不允許主鍵基本工資int4允許獎金int4允許思考：是否需要對表設置其他完整性約束？在完成物理創(chuàng)建之后，請截圖。參考答案：最后設置外鍵：實訓5數(shù)據(jù)定義語句第一題【數(shù)據(jù)庫的定義與修改】注意：請復制SQL語句，并截取查詢結果圖。具體要求如下。1．用SQL語句創(chuàng)建“工程零件”數(shù)據(jù)庫。該數(shù)據(jù)庫包含一個數(shù)據(jù)文件，邏輯文件名為engineering_data，磁盤文件名為D：\engineering_data.mdf，文件初始容量為5MB，最大容量為80MB，文件容量遞增值為1MB；事務日志邏輯文件名為engineering_log，磁盤文件名為D：\engineering_log.ldf，文件初始容量為1MB，最大容量為5MB，文件容量遞增值為10%。參考答案：CREATEDATABASE工程零件 /*數(shù)據(jù)庫名*/ONPRIMARY /*主數(shù)據(jù)文件*/(NAME=engineering_data, /*數(shù)據(jù)文件邏輯名*/FILENAME='D:\engineering_data.mdf', /*數(shù)據(jù)文件磁盤(物理)名*/SIZE=5MB, /*數(shù)據(jù)文件初始大小*/MAXSIZE=80MB, /*數(shù)據(jù)文件增長的上限*/FILEGROWTH=1MB /*文件增量*/)LOGON(NAME=engineering_log, /*事務日志文件邏輯名*/FILENAME='D:\engineering_log.ldf, /*事務日志文件磁盤(物理)名*/SIZE=1MB, /*事務日志文件初始大小*/MAXSIZE=5M, /*事務日志文件增長的上限*/FILEGROWTH=10% /*文件增量*/)參考答案：USEMASTERGOALTERDATABASEtest_newADDFILE/*新增輔助數(shù)據(jù)文件*/(NAME='',/*新增第一個輔助數(shù)據(jù)文件*/FILENAME='D:\engineering_data1.ndf',SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=1MB),(NAME='',/*新增第二個輔助數(shù)據(jù)文件*/FILENAME='D:\engineering_data2.ndf',SIZE=5MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)GO3．將“工程零件”數(shù)據(jù)庫的數(shù)據(jù)主文件engineering_data擴充到200MB。參考答案：USEMASTERGOALTERDATABASE工程零件MODIFYFILE/*修改用MODIFY*/(NAME='engineering_data',/*要修改的數(shù)據(jù)文件*/MAXSIZE=200MB)/*必須大于當前容量*/GO4．刪除輔助數(shù)據(jù)文件engineering_data2，從而縮小數(shù)據(jù)庫容量。參考答案：USEMASTERGOALTERDATABASE工程零件REMOVEFILEengineering_data2/*刪除用REMOVE*/GO第二題【數(shù)據(jù)表的定義、修改和刪除】在“工程零件”數(shù)據(jù)庫中有供應商、零件、工程和供應零件數(shù)據(jù)表。各表的結構如下。供應商表字段名字段類型是否為空是否主鍵供應商代號char(5)不允許主鍵姓名varchar(8)不允許所在城市varchar(20)不允許電話varchar(20)允許零件表字段名字段類型是否為空是否主鍵零件代號char(5)不允許主鍵零件名varchar(20)不允許規(guī)格varchar(40)不允許產(chǎn)地varchar(20)不允許顏色char(6)允許工程表字段名字段類型是否為空是否主鍵工程代號char(5)不允許主鍵工程名varchar(20)不允許負責人varchar(8)不允許預算money允許供應零件表字段名字段類型是否為空是否主鍵供應商代號char(5)不允許工程代號char(5)不允許零件代號char(5)不允許數(shù)量smallint不允許供貨日期smalldatetime允許具體要求如下。注意：請復制SQL語句，并截取查詢結果圖。1．用SQL語句在“工程零件”數(shù)據(jù)庫中創(chuàng)建以上4個表。（主鍵可以可視化設置）參考答案：createtable供應商(供應商代號char(5)notnull,姓名varchar(8)notnull,所在城市varchar(20)notnull,電話varchar(20))createtable零件(零件代號char(5)notnull,零件名varchar(20)notnull,規(guī)格varchar(40)notnull,產(chǎn)地varchar(20)notnull,顏色char(6))createtable工程(工程代號char(5)notnull,工程代號char(5)notnull,工程名varchar(20)notnull,負責人varchar(8)notnull,預算money)createtable供應零件(供應商代號char(5)notnull,工程代號char(5)notnull,零件代號char(5)notnull,數(shù)量smallintnotnull,供貨日期smalldatetime)2．為供應商表添加字段“住址”，類型為可變字符串，最大長度為30個字節(jié)。參考答案：altertable供應商add住址varchar(30)3．將供應零件表“數(shù)量”列的類型改為int。參考答案：altertable供應零件altercolumn數(shù)量int4．刪除零件表中的“顏色”字段。參考答案：altertable零件dropcolumn顏色5．刪除供應零件表。參考答案：droptable供應零件實訓6簡單查詢請附加數(shù)據(jù)庫（工程零件_data.mdf和工程零件_log.ldf），其操作步驟如下。（1）首先將兩個文件拷貝到數(shù)據(jù)庫的DATA目錄下。（2）右擊“數(shù)據(jù)庫”節(jié)點，在彈出的快捷菜單中選擇“附加”命令。（3）創(chuàng)建“工程零件”數(shù)據(jù)庫。該數(shù)據(jù)庫的表結構如下所示。供應商(供應商代號,姓名,所在城市,聯(lián)系電話)。零件(零件代號,零件名,規(guī)格,產(chǎn)地,顏色)。工程(工程代號,工程名,負責人,預算)。供應零件(供應商代號,工程代號,零件代號,數(shù)量,供貨日期)。注意：請復制查詢語句，并截取查詢結果圖。1．查詢供應商“王平”的基本信息。參考答案：select*from供應商where姓名='王平'2．查詢天津供應商的姓名和聯(lián)系電話。參考答案：select姓名,聯(lián)系電話from供應商where所在城市='天津'3．查詢所有姓“王”的供應商的姓名、聯(lián)系電話、所在城市。參考答案：select姓名,聯(lián)系電話,所在城市from供應商where姓名like'王%'4．查詢預算在50000～100000元的工程信息，并按預算降序排列。參考答案：select*from工程where預算between50000and100000orderby預算desc5．查詢供應商代號為S01、零件號為P03的工程的代號。參考答案：select零件代號,工程代號from供應零件where供應商代號='S01'and零件代號='P03'6．查詢S01號供應商在2016年以后的供貨情況，包括零件代號、數(shù)量、供貨日期。參考答案：select零件代號,數(shù)量,供貨日期from供應零件where供應商代號='S01'and供貨日期>2016-01-01實訓7聚集查詢注意：請復制查詢語句，并截取查詢結果圖。第一題【集函數(shù)】1．查詢工程的總數(shù)。參考答案：selectcount(工程代號)as總個數(shù)from工程或者selectcount(*)as總個數(shù)from工程2．查詢S01號供應商的供貨次數(shù)。參考答案：selectcount(供應商代號)as供貨次數(shù)from供應零件where供應商代號='S01'3．查詢J02工程使用的零件總數(shù)。參考答案：selectsum(數(shù)量)as零件總數(shù)from供應零件where工程代號='J02'【提高題】查詢供了貨的供應商人數(shù)。參考答案：selectcount(distinct供應商代號)as供應商人數(shù)from供應零件第二題【分組】1．查詢各產(chǎn)地的零件個數(shù)。參考答案：select產(chǎn)地,count(零件代號)as零件個數(shù)from零件groupby產(chǎn)地2．查詢每個工程使用的零件總數(shù)。參考答案：select工程代號,sum(數(shù)量)as零件總數(shù)from供應零件groupby工程代號3．查詢每個供應商的平均供應數(shù)量。參考答案：select供應商代號,avg(數(shù)量)as平均供應數(shù)量from供應零件groupby供應商代號【提高題】查詢工程代號為J02的工程使用的各種零件的零件代號及數(shù)量。參考答案：select零件代號,sum(數(shù)量)as總供應數(shù)量from供應零件where工程代號='J02'groupby零件代號第三題【分組后的篩選】1．查詢供貨次數(shù)大于2的供應商的代號。參考答案：select供應商代號from供應零件groupby供應商代號havingcount(供應商代號)>22．查詢所需零件總數(shù)大于50的工程的代號。參考答案：select工程代號from供應零件groupby工程代號havingsum(數(shù)量)>50【提高題】查詢?yōu)镴04工程供應過兩次及以上的供應商的代號，并按供應總數(shù)降序排列。參考答案：select供應商代號,sum(數(shù)量)as供應總數(shù)from供應零件where工程代號='J04'groupby供應商代號havingcount(數(shù)量)>=2orderbysum(數(shù)量)desc實訓8連接查詢注意：請復制查詢語句，并截取查詢結果圖。1．查詢使用了P03號零件的工程的代號、工程名。參考答案：selectdistinct供應零件.工程代號,工程名from供應零件,工程where供應零件.工程代號=工程.工程代號and零件代號='P03'2．查詢使用了上海供應商所供應零件的工程的代號。參考答案：selectdistinct工程代號from供應零件,供應商where供應零件.供應商代號=供應商.供應商代號and所在城市='上海'3．查詢使用了藍色零件的工程的工程代號。參考答案：selectdistinct供應零件.工程代號from供應零件,零件where供應零件.零件代號=零件.零件代號and顏色='藍色'4．查詢姓“王”的供應商的供貨信息，包括零件代號、零件名、數(shù)量、供貨日期，并將結果按數(shù)量升序排列。參考答案：selectdistinct零件.零件代號,零件名,數(shù)量,供貨日期from供應零件,零件,供應商where供應零件.零件代號=零件.零件代號and供應零件.供應商代號=供應商.供應商代號and姓名LIKE'王%'orderby數(shù)量5．查詢使用了S01號供應商所供應零件的工程的負責人。參考答案：select負責人from工程,供應零件where工程.工程代號=供應零件.工程代號and供應商代號='S01'6．查詢供貨次數(shù)大于2的供應商姓名。參考答案：selectdistinct姓名from供應零件,供應商where供應零件.供應商代號=供應商.供應商代號groupby姓名havingcount(供應零件.供應商代號)>27．查詢所需零件總數(shù)大于50的工程名。參考答案：selectdistinct工程名from供應零件,工程where供應零件.工程代號=工程.工程代號groupby工程名havingsum(數(shù)量)>50實訓9非相關子查詢注意：請復制查詢語句，并截取查詢結果圖。1．查詢J03號工程使用的零件的代號、零件名。參考答案：select零件代號,零件名from零件where零件代號in(select零件代號from供應零件where工程代號='J03')2．查詢使用了上海供應商所供應零件的工程的代號。參考答案：selectdistinct工程代號from供應零件where供應商代號in(select供應商代號from供應商where所在城市='上海')3．查詢使用了藍色零件的工程的代號。參考答案：selectdistinct工程代號from供應零件where零件代號in(select零件代號from零件where顏色='藍色')4．查詢沒有使用天津所產(chǎn)零件的工程的代號。參考答案：selectdistinct工程代號from供應零件where零件代號notin(select零件代號from零件where產(chǎn)地='天津')【提高題】查詢使用零件數(shù)量最多的工程的代號、工程名。參考答案：select工程代號,工程名from工程where工程代號in(select工程代號from供應零件 groupby工程代號havingsum(數(shù)量)>=all (selectsum(數(shù)量)from供應零件groupby數(shù)量))實訓10數(shù)據(jù)操縱創(chuàng)建“學生01”數(shù)據(jù)庫，該數(shù)據(jù)庫的表結構如下所示。學生表(學號,姓名,性別,班級,年齡,所在學院)課程表(課程號,課程名,教師,周課時數(shù),備注)選課表(學號,課程號,成績)要求如下。（1）學號和課程號的類型為char(10)，其他屬性的類型及長度自定義（選擇合適的數(shù)據(jù)類型和長度）。（2）姓名不能為空，性別的默認值為“男”，成績?yōu)?～100分。（3）定義每個表的主碼，并將選課表中的學號與學生表中的學號建立關聯(lián)，選課表中的課程號與課程表中的課程號建立關聯(lián)。在“學生01”數(shù)據(jù)庫中，創(chuàng)建3個表，用SQL語句完成。參考答案：創(chuàng)建學生表：Createtable學生表(學號char(10)primarykey,姓名char(10)notnull,性別char(2)default'男',班級char(10),年齡int,所在系char(20))創(chuàng)建課程表：Createtable課程表(課程號char(10)primarykey,課程名char(20),教師char(8),周課時數(shù)int,備注char(10))創(chuàng)建選課表：Createtable選課表(學號char(10),課程號char(10),成績floatcheck(成績>=0AND成績<=100)，primarykey(學號，課程號))實訓內容1．向學生表中插入以下數(shù)據(jù)。（'10001','王伶俐','女','大數(shù)據(jù)2101',19,'人工智能學院'）（'10002','張輝','男','大數(shù)據(jù)2101',20,'人工智能學院'）（'10003','李密','女','大數(shù)據(jù)2102',19,'人工智能學院'）（'10004','沈曉','男','大數(shù)據(jù)2102',20,'人工智能學院'）參考答案：insertinto學生表values('10001','王伶俐','女','大數(shù)據(jù)2101',19,'人工智能學院')insertinto學生表values('10002','張輝','男','大數(shù)據(jù)2101',20,'人工智能學院')insertinto學生表values('10003','李密','女','大數(shù)據(jù)2102',19,'人工智能學院')insertinto學生表values('10004','沈曉','男','大數(shù)據(jù)2102',20,'人工智能學院')2．向課程表中插入以下數(shù)據(jù)。（'C1','數(shù)據(jù)庫'）（'C2','英語'）（'C3','網(wǎng)頁設計'）參考答案：insertinto課程表values('C1','數(shù)據(jù)庫',null,null,null)insertinto課程表values('C2','英語',null,null,null)insertinto課程表values('C3','網(wǎng)頁設計',null,null,null)或者insertinto課程表（課程號，課程名）values('C1','數(shù)據(jù)庫')insertinto課程表（課程號，課程名）values('C2','英語')insertinto課程表（課程號，課程名）values('C3','網(wǎng)頁設計')3．向選課表中插入以下數(shù)據(jù)。（'10001','C1',89）（'10001','C2',67）（'10002','C2',60）（'10003','C3',90）insertinto選課表values('10001','C1',89)insertinto選課表values('10001','C2',67)insertinto選課表values('10002','C2',60)insertinto選課表values('10003','C3',90)4．學號為“10004”的學生，新選修了“C3”課程，成績未知。insertinto選課表values('10004','C3',null)5．將名字為“王伶俐”的學生的年齡修改為18歲。update學生表set年齡=18where姓名in(select姓名from學生表where姓名='王伶俐')6．刪除課程號為“C3”的所有選課信息。deletefrom選課表where課程號='C3'實訓11視圖1．建立視圖V1，查詢“上?！惫痰男畔ⅰ⒖即鸢福篶reateviewV1asselect*from供應商where所在城市=‘上?！痝o2．建立視圖V2，查詢預算在30000～50000元的工程信息，按預算降序排列，并顯示視圖V2的信息。參考答案：createviewV2asselect*from工程where預算between30000and50000orderby預算descgo3．建立視圖V3，查詢姓“王”的供應商的供貨信息，包括零件代號、零件名、數(shù)量、供貨日期，并按數(shù)量降序排列。參考答案：createviewV3asselectdistinct零件.零件代號,零件名,數(shù)量,供貨日期from供應零件,零件,供應商where供應零件.零件代號=零件.零件代號and供應零件.供應商代號=供應商.供應商代號and姓名like'王%'orderby數(shù)量go4．建立視圖V4，查詢使用零件數(shù)量最多的工程信息。參考答案：createviewV3asselect*from工程where工程代號in(select工程代號from供應零件groupby工程代號havingsum(數(shù)量)>=all(selectsum(數(shù)量)from供應零件groupby數(shù)量))go5．修改視圖V1中“溫州”供應商的信息。參考答案：alterviewV1asselect*from供應商where所在城市=‘溫州’go6．刪除視圖V2。dropviewV2實訓12約束實訓目的熟練掌握使用SQL語句實現(xiàn)各類約束。實訓重難點1．六大約束的定義。2．外部鍵約束及引用行為。實訓內容約束指的是對在一個基本表上進行的INSERT、UPDATE或DELETE操作的結果設置限制以幫助定義有效值的集合。主要有6類約束：NOTNULL約束、DEFAULT約束、PRIMARYKEY約束、UNIQUE約束、FOREIGNKEY約束、CHECK約束。（1）NOTNULL：非空約束，不接受該屬性為空的元組，可以在定義表時說明。（2）DEFAULT約束：指定了列的默認值，在定義表或修改已經(jīng)定義好的表時均可定義該約束。（3）PRIMARYKEY約束：定義主鍵，可以在定義表結構或修改表結構時指定該約束。（4）UNIQUE約束：使用UNIQUE約束確保在非主鍵列中不輸入重復值。需要注意UNIQUE約束與PRIMARYKEY約束的不同。（5）FOREIGNKEY約束：實現(xiàn)了實體間的引用完整性。FOREIGNKEY約束的主要目的是控制存儲在外鍵表中的數(shù)據(jù)，還可以控制對主鍵表中數(shù)據(jù)的修改。如果試圖刪除主鍵表中的行或更改主鍵值，而該主鍵值與另一個表的FOREIGNKEY約束值相關，則該操作有兩種不同的引用行為（在SQLServer中）：NOACTION或CASCADE。（6）CHECK約束：用于將列的取值限制在指定的范圍內，從而使數(shù)據(jù)庫中存放的數(shù)據(jù)都是有意義的值。要求：按照下面的關系模式要求，寫出各約束定義。1．創(chuàng)建如下兩張表。圖書信息表字段名字段類型約束圖書號char(10)主鍵圖書名varchar(30)非空出版社varchar(30)唯一價格float默認值：20借書信息表字段名字段類型約束讀者號char(10)主鍵圖書號char(10)數(shù)量int默認值：1借書日期datetime唯一應還日期datetime唯一參考答案：CREATETABLE圖書信息表(圖書號char(10)NOTNULLPRIMARYKEY,圖書名varchar(30)NOTNULL,出版社varchar(30)NOTNULL,CONSTRAINTUK_出版社UNIQUE(出版社),價格floatdefault20)CREATETABLE借書信息表(讀者號char(10)NOTNULL,圖書號char(10)NOTNULL,數(shù)量intdefault1,借書日期datetime,CONSTRAINTUK_借書日期UNIQUE(借書日期),應還日期datetime,CONSTRAINTUK_應還日期UNIQUE(應還日期),PRIMARYKEY(讀者號,圖書號))2．假設圖書信息表中已經(jīng)有一行記錄（'100001','關系數(shù)據(jù)庫設計與應用','bbb',21），請分析下面語句的出錯原因。①INSERTINTO圖書信息表(圖書號,圖書名,出版社)；VALUES('100001',NULL,'aaa')；②INSERTINTO圖書信息表VALUES('100002','關系數(shù)據(jù)庫設計與應用','bbb',18)；參考答案：①出錯的原因是：不能將值NULL插入列'圖書名'，表'圖書館數(shù)據(jù)庫.dbo.圖書信息表'；列不允許有Null值。INSERT失敗。②出錯的原因是：唯一約束，因為出版社是唯一值，不可以重復插入。違反了UNIQUEKEY約束“UK_出版社”。不能在對象“dbo.圖書信息表”中插入重復鍵。重復鍵值為(bbb)。3．給借書信息表定義外鍵。參考答案：ALTERTABLE借書信息表ADDCONSTRAINTFK_圖書號FOREIGNKEY(圖書號)REFERENCES圖書信息表(圖書號)4．請分析下面語句的出錯原因。INSERTINTO借書信息表VALUES('001','100002',2,'2021-11-3','2021-11-18')參考答案：出錯的原因在于外鍵沖突了，INSERT語句與FOREIGNKEY約束"FK_圖書號"沖突。該沖突發(fā)生于數(shù)據(jù)庫"圖書館數(shù)據(jù)庫"，表"dbo.圖書信息表",column'圖書號'。5．要求圖書信息表中的“價格”字段的值介于0至100元之間。參考答案：ALTERTABLE圖書信息表ADDCONSTRAINTCHK_價格CHECK(價格>=0AND成績<=100)實訓13存儲過程實訓目的1．掌握常見系統(tǒng)存儲過程的應用；2．掌握用戶自定義存儲過程的創(chuàng)建與調用。實訓重難點1．常見的系統(tǒng)存儲過程；2．用戶自定義存儲過程的應用（難點）。實訓準備請附加數(shù)據(jù)庫，相關操作詳見實訓6中的“實訓準備”。實訓內容第一題【系統(tǒng)存儲過程的使用】1．將“工程零件”數(shù)據(jù)庫改名為“工程零件系統(tǒng)”數(shù)據(jù)庫。參考答案：execsp_renamedb'工程零件','工程零件系統(tǒng)'2．查詢實訓11中視圖V3的定義內容。參考答案：execsp_helptext'V3'3．查詢工程表的信息。參考答案：execsp_help'工程'第二題【用戶自定義存儲過程】1．沒有參數(shù)的存儲過程：創(chuàng)建一個無參數(shù)的存儲過程p1并調用該存儲過程，要求查詢以下信息：供應商代號、姓名、工程名、零件名、數(shù)量和供貨日期。創(chuàng)建p1。參考答案：createprocp1asbeginselect供應零件.供應商代號,姓名,工程名,零件名,數(shù)量,供貨日期 from供應零件,供應商,零件,工程 where供應零件.工程代號=工程.工程代號 and供應零件.供應商代號=供應商.供應商代號 and供應零件.零件代號=零件.零件代號end調用p1。參考答案：execp12．有輸入?yún)?shù)的存儲過程：創(chuàng)建一個帶有參數(shù)的存儲過程p2，并調用該存儲過程，要求該存儲過程根據(jù)傳入的“供應商代號”在供應商表中查詢該供應商信息。創(chuàng)建p2。參考答案：createprocp2@idchar(5)asbeginselect*from供應商where供應商代號=@idend調用p2。參考答案：execp2@id='S01'3．有參數(shù)默認值的存儲過程：創(chuàng)建一個帶有參數(shù)默認值的存儲過程p3，并調用該存儲過程，要求該存儲過程根據(jù)查詢指定“預算”范圍的工程信息。默認值：最低預算為30000元，最高預算為100000元。創(chuàng)建p3。參考答案：createprocp3@預算1money=30000,@預算2money=100000asbeginselect*from工程where預算between@預算1and@預算2end用多種形式調用p3。參考答案：execp3execp350000execp350000,80000execp3@預算2=600004．有輸出參數(shù)的存儲過程：創(chuàng)建一個帶有輸出參數(shù)的存儲過程p4，該存儲過程根據(jù)傳入的供應商姓名，查詢該供應商的代號，并調用該存儲過程，根據(jù)供應商姓名，查詢該供應商編號。創(chuàng)建p4。參考答案：createprocp4@namechar(8),@id_outchar(5)outputasbeginselect@id_out=供應商代號from供應商where姓名=@namereturnend調用p4。參考答案：declare@id_savechar(5)execp4@name='王平',@id_out=@id_saveoutput--print@id_saveselect*from供應商where供應商代號=@id_save實訓14觸發(fā)器實訓目的1．掌握觸發(fā)器的創(chuàng)建、修改和刪除操作；2．掌握觸發(fā)器的觸發(fā)執(zhí)行；3．理解觸發(fā)器與約束的不同。實訓重難點1．觸發(fā)器的創(chuàng)建；2．觸發(fā)器的執(zhí)行原理（難點）。實訓準備請附加數(shù)據(jù)庫，相關操作詳見實訓6中的“實訓準備”。除了使用約束來完成數(shù)據(jù)的用戶自定義完整性，還可以使用觸發(fā)器來實現(xiàn)用戶自定義完整性。本書介紹了DML觸發(fā)器，它有三種類型：INSERT觸發(fā)器、UPDATE觸發(fā)器和DELETE觸發(fā)器。實訓內容第一題【存儲過程與觸發(fā)器的比較】1．設計一個存儲過程proc_update，當向零件表中修改某指定零件名稱時，如果該零件在供應零件表中已有記錄，則提示“該零件名稱不能進行修改”，并拒絕修改，否則可以修改。參考答案：createprocedureproc_update@零件代號char(5),@零件名稱varchar(50)asbeginifnotexists(select*from零件where零件代號=@零件代號)update零件set