數(shù)據(jù)庫原理與應(yīng)用大作業(yè)

1、數(shù)據(jù)庫原理與應(yīng)用綜合設(shè)計(jì)任務(wù)書 前言 數(shù)據(jù)庫原理與應(yīng)用課程的重點(diǎn)知識模塊包括：1）數(shù)據(jù)庫設(shè)訃、2）用SQL實(shí)現(xiàn)建庫、建表、 查詢、更新、和創(chuàng)建視圖、3）存儲過程和觸發(fā)器設(shè)計(jì)。針對這三個應(yīng)用能力，用一個案例作為背景， 布豊三次大作業(yè)。 在校大學(xué)生都能理解“圖書管理系統(tǒng)”的應(yīng)用場合和業(yè)務(wù)流程。因此，以圖書管理系統(tǒng)作為案例 來布巻作業(yè)，可以降低業(yè)務(wù)分析難度，讓學(xué)生將主要精力放在知識消化與技術(shù)應(yīng)用上。 本文檔包括四個部分。第一部分描述系統(tǒng)的需求，第二部分提出E-R模型設(shè)計(jì)和關(guān)系模型設(shè)計(jì)的 任務(wù)：第三部分提出在SQL Server中，用SQL語句來建庫、建表、查詢、更新數(shù)據(jù)、創(chuàng)建視圖的任 務(wù)：第四部分，

2、根據(jù)應(yīng)用需求、安全需求和數(shù)據(jù)完整性要求，提岀設(shè)計(jì)存儲過程和觸發(fā)器的任務(wù)。 每個任務(wù)之前，都給岀了完成任務(wù)所需要掌握的關(guān)鍵知識點(diǎn)，學(xué)生可以在對這些知識點(diǎn)進(jìn)行復(fù)習(xí) 的基礎(chǔ)上完成任務(wù)，每個任務(wù)是一次大作業(yè)。 第一部分案例的需求描述 本部分描述“圖書管理系統(tǒng)”的需求，學(xué)生通過閱讀本部分內(nèi)容，了解系統(tǒng)的功能要求、運(yùn)行環(huán) 境，對系統(tǒng)所需的數(shù)據(jù)有總體認(rèn)識，作為三次作業(yè)的基礎(chǔ)。 1需求故事 潔是圖書閱覽室的管理員，她找到程序開發(fā)小組“楓”，請他們幫助開發(fā)一套管理程序。他們國 坐在一起，開始以下對話： 潔：你們進(jìn)來都看到了，那兒排大拒子，全都放滿了書，很多毅師來借書，管理起來越來越難，希望 幫我開發(fā)一個管理軟件

3、。 楓：你希望軟件提供哪些功能。 潔：詩先是能登記和查看圖書信息，借書和還書的吋候要方便操作。對于我來說，看圖書的狀態(tài)很重 要，就爰說妥知道哪些圖書已經(jīng)借出去了，哪些書已經(jīng)過了借閱期，此外還矣做一些統(tǒng)計(jì)表 大概就是這些吧。 楓：你怎么登記借書呢？規(guī)定的借閱期有多長？過了借閱期怎么辦？ 潔：每個老師和有一個工號，我就計(jì)工號和圖書的ISBN號，一個人最多能借2本書。不同類型的書 借閱期限是不一樣的，比如文學(xué)作島的借閱期規(guī)定是15天，教學(xué)參考書的借閱期限是半年。有的 人不自覺，過期好久都不還，我現(xiàn)在是翻記錄本一個個地找，然后打電話到他的部門，并且發(fā)催 還郵件。 R：同一 ISBN只有一本書嗎？ 潔：

4、是的，現(xiàn)在把書分成這樣幾類：教學(xué)參考書、文學(xué)作島、學(xué)術(shù)期刊、學(xué)術(shù)專著。 楓：你想統(tǒng)計(jì)一些什么數(shù)據(jù) 潔：主矣是分類統(tǒng)計(jì)圖書冊數(shù)和價值、圖書借閱次數(shù)。我們想知道哪些圖書股受歡迎。 楓：閱覽室是你一個人在管么？ 潔：閱覽室有兩個人。我們倆都有一臺計(jì)算機(jī)，最好兩臺計(jì)算機(jī)都町以用上這個軟件。 1. 2需求分析 1）功能需求 教師信息管理：用于教師基本資料的增刪改查。 圖書信息管理：用于圖書基本信息的增刪改查，分類統(tǒng)計(jì)圖書冊數(shù)和價值。 借書登記：記錄借書時間、所借圖書、借書人、辦理人。 還書登記：記錄還書時間、所還圖書、還書人、辦理人。 催還：查詢借閱逾期的借書信息，給借書人發(fā)電子郵件，給借書人的部門打電

5、話。 2）運(yùn)行環(huán)境要求 )BMSPCFC （lZ0 圖1-2：運(yùn)行環(huán)境拓?fù)鋱D 系統(tǒng)采用C/S模式，有兩臺PC和一臺服務(wù)器，聯(lián)成一個局域網(wǎng)。PC安裝圖書管理軟件的客戶 端，服務(wù)器上安裝DBMS,服務(wù)器也可由兩臺PC中的一臺來代替。 第二部分作業(yè)1ER模型與關(guān)系模型設(shè)計(jì) （滿分8分） 本部分的任務(wù)是：在需求分析的基礎(chǔ)上，進(jìn)行ER圖設(shè)計(jì)，然后將ER模型轉(zhuǎn)換為關(guān)系模型。 任務(wù): 1) 根據(jù)需求描述，繪制E-R圖。 2) 將E-R圖轉(zhuǎn)換成關(guān)系模型，寫出所有的關(guān)系模式，并寫岀每一個關(guān)系是按照什么規(guī)則轉(zhuǎn)換 而成的0 3) 在已經(jīng)形成的關(guān)系模型下，舉例說明連接運(yùn)算、投影運(yùn)算、選擇運(yùn)算。 參考答案：(屬性寫在了

6、實(shí)體和聯(lián)系圖形內(nèi)) 任務(wù)： 1)根據(jù)需求描述，分別為圖書管理系統(tǒng)”的不同功能模塊繪制局部E-R圖。 教師信息管理： 圖書基本信息管理: 借還書登記: 請瀏覽后下載，資料供參考，期待您的好評與關(guān)注！ 催還書登記: 2）整體ER圖 3）關(guān)系模式 類型表（類型拿,借閱期限），依據(jù)：實(shí)體轉(zhuǎn)換為關(guān)系。 圖書表（ISBN,書名，作者，岀版社，出版時間，單價，類型名，狀態(tài)），依據(jù)： 實(shí)體轉(zhuǎn)換為關(guān)系，1： N的聯(lián)系合并到N,在N方增加一個外鍵：類型名。注：狀 態(tài)屬性為（庫存，借出） 管理員表（管理員名,性別，口令），依據(jù)：實(shí)體轉(zhuǎn)換為關(guān)系。 教師表（教師工號,姓鋼,性別，Email,部門名），依據(jù)：實(shí)體轉(zhuǎn)換為關(guān)

7、系，1： N的聯(lián)系合并 到N,在N方增加一個外鍵：部門名。 部門表（部門名,電話），依據(jù)：實(shí)體轉(zhuǎn)換為關(guān)系。 借還表（借還業(yè)務(wù)編號,ISBN,教師工號，借辦理人，借出日期，還辦理人，歸還日期），依 據(jù)，M:N聯(lián)系轉(zhuǎn)換為一個關(guān)系，取聯(lián)系的實(shí)體的鍵加聯(lián)系屬性作為苴屬性，可以增加一個列 作為主鍵。 催還表（催還業(yè)務(wù)編號，ISBN,教師工號，辦理人，催還日期），依據(jù)，M:N聯(lián)系轉(zhuǎn)換為一個 關(guān)系，取聯(lián)系的實(shí)體的鍵加聯(lián)系屬性作為其屬性，可以增加一個列作為主鍵。 4）在已經(jīng)形成的關(guān)系模型下，舉例說明連接運(yùn)算、投影運(yùn)算、選擇運(yùn)算。 連接運(yùn)算：査看類型的圖書已經(jīng)超過了有效借岀期限。（需要圖書，類型，借還關(guān)系） 投

8、影運(yùn)算：査看圖書的書名、作者。 選擇運(yùn)算：査看單價高于50元的圖書。 第三部分作業(yè)2用SQL語句建庫、査詢、更新數(shù)據(jù)、創(chuàng)建視圖 （滿分9分） 本部分的任務(wù)是：在關(guān)系模型的基礎(chǔ)上，設(shè)計(jì)岀數(shù)據(jù)庫的邏輯結(jié)構(gòu)，然后在SQL Server中用SQL 語句創(chuàng)建數(shù)據(jù)庫、數(shù)據(jù)表、并輸入一些原始數(shù)據(jù)，寫SQL語句，進(jìn)行數(shù)據(jù)增刪改，創(chuàng)建視圖。 任務(wù)： 1）在第一次大作業(yè)的參考答案基礎(chǔ)上，將每個關(guān)系轉(zhuǎn)換為SQL Server支持的表結(jié)構(gòu)，寫岀表結(jié) 構(gòu)。 2）使用SQL語句，創(chuàng)建數(shù)據(jù)庫，數(shù)據(jù)庫名為Bookstore 3）使用SQL語句，在數(shù)據(jù)庫中創(chuàng)建表，設(shè)泄表的主鍵約束、外鍵約束、檢查約朿、非空約束。 4 ）寫以下增

9、刪改查操作的SQL語句 1、增加三條類型表數(shù)據(jù) 2、增加三條教師表數(shù)據(jù) 3、增加五條圖書表數(shù)據(jù) 4、某教師今天借了某書，涉及兩個操作，一是圖書表的狀態(tài)改為借出”，二是增加一 個借還表記錄 5、將某書的單價改為32元。 5 ）寫岀創(chuàng)建以下視圖的SQL語句 1. 查詢某教師的借還書記錄 2. 查詢某本書的流轉(zhuǎn)記錄 3. 借出但未歸還的圖書視圖 4. 借閱逾期視圖 5. 圖書分類統(tǒng)汁冊數(shù)和總價值的視圖 參照完成： 1）表結(jié)構(gòu) 管理員表 列需 類型 約束 用戶名 Char(lO) Pk 性別 Char(2) NOT NULL且默認(rèn)為男 口令 Nchar(2O) NOT NULL 類型表 列名 類型 約

10、束 類型名 NChar(lO) pk 借閱期限 tinyint NOT NULL 圖書表 列名 類型 約束 ISBN Char(20) pk 書名 Nchar(2O) NOT NULL 作者 Nchar(2O) NOT NULL 出版社 Nchar(2O) NOT NULL 出版時間 DateTinie NOT NULL 單價 money NOT NULL 類型名 Nchar(lO) FK references類型表.圖書類型 狀態(tài) Nchar(2) 狀態(tài)in （庫存，借出） 部門表 列名 類型 約朿 部門名 NChar(IO) Pk 電話 char(ll) NOT NULL 教師表 列名 類

11、型 約束 工號 Char(4) Pk 姓名 Nchar(4) NOT NULL 性別 Char(2) NOT NULL且默認(rèn)為男 姓名 Nchar(4) NOT NULL Email Char(3O) NOT NULL 部門名 Nchar(lO) Fk references部門表部門名 借還記錄表 列需 類型 約束 業(yè)務(wù)編號 int Pk自動標(biāo)示 ISBN char(2O) Fk references 圖書表isbn 工號 Char(4) Fk references教師表工號 借辦理人 Char(lO) NOT NULL 借岀日期 datetime NOT NULL 還辦理人 Char(lO)

12、 歸還日期 datetime 催還記錄表 列需 類型 約束 業(yè)務(wù)編號 int Pk自動標(biāo)示 ISBN char(ll) Fk references 圖書表isbn 工號 Char(4) Fk references教師表工號 辦理人 Char(10) NOT NULL 催還日期 datetime NOT NULL 1) 使用SQL語句，創(chuàng)建數(shù)據(jù)庫，數(shù)據(jù)庫名為Bookstore 語句：Create Databse Books tore 2) 使用SQL語句，在數(shù)據(jù)庫中創(chuàng)建表，設(shè)定表的主鍵約束、外鍵約束、 檢查約束、非空約束。 語句： CREATE TABLE 管理員表( 管理員nchar (10)

13、 NOT NULL, 性別Char (2) not null DEFAULT 男, 口令nchar(20) NOT NULL, CONSTRAINT PK_管理員 PRIMARY KEY CLUSTERED (管理員名 ASC) )ON PRIMARY GO CREATE TABLE 類型表( 類型名nchar (10) NOT NULL, 借閱期限tinyint NOT NULL, CONSTRAINT PK_類型PRIMARY KEY CLUSTERED (類型名 ASC) )ON PRIMARY GO CREATE TABLE 圖書表( ISBN char (20) NOT NULL,

14、書名 nchar (20) NOT NULL, 作者 nchar (20) NOT NULL, 出版社 nchar (20) NOT NULL , 出版時間 datetime NOT NULL , 單價 money NOT NULL, 類型名 nchar (10) NOT NULL, 狀態(tài) nchar (2) NOT NULL CHECK (狀態(tài) in庫存 J1 借出)f CONSTRAINT PK_圖書 PRIMARY KEY CLUSTERED (ISBN ASC), CONSTRAINT FK_圖書類型FOREIGN KEY (類型名)REFERENCES類型表(類型名) ON UPDA

15、TE CASCADE ON DELETE CASCADE )ON PRIMARY GO CREATE TABLE 部門表( 部門名 nchar (10) NOT NULL, 電話 char (11), CONSTRAINT PK_部門表 PRIMARY KEY CLUSTERED (部門名 ASC) )ON PRIMARY GO CREATE TABLE 教師表( 工號 char (4) NOT NULL, 姓名 nchar (4) NOT NULL, 性別 Char (2) not null DEFAULT ，男, Email char (30) NOT NULL, 部門名 nchar (1

16、0) NOT NULL, CONSTRAINT PK_教師表PRIMARY KEY CLUSTERED (工號ASC), CONSTRAINT FK_部門表_教師表FOREIGN KEY (部門名)REFERENCES 部門表(部門名) ON UPDATE CASCADE ON DELETE CASCADE, )ON PRIMARY GO CREATE TA3LE借還記錄表( 業(yè)務(wù)編號 int identity (0,1) NOT NULL , ISBN char (20) NOT NULL, 工號 char (4) NOT NULL, 代辦理人char (10) NOT NULL, 借出日期

17、 date time NOT NULL, 還辦理人char (10)NULL, 歸還 日 Mljdate time NULL, CONSTRAINT PK_借還記錄表PRIMARY KEY CLUSTERED (業(yè)務(wù)編號ASC), CONSTRAINT FK_借還記錄表教師表FOREIGN KEY (工號REFERENCES 教師表(工號) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT FK_ 借還記錄表借還記錄表 FOREIGN KEY (ISBN) REFERENCES 圖書表(ISBN) ON UPDATE CASCADE ON DELE

18、TE CASCADE )ON PRIMARY GO CREATE TABLE催還記錄表( 業(yè)務(wù)編號int identity (0,1) NOT NULL, ISBN char (20) NOT NULL, 工號 char (4) NOT NULL, 辦理人 char (10) NOT NULL, 催還日期datetime , CONSTRAINT PK_催還記錄表PRIMARY KEY CLUSTERED (業(yè)務(wù)編號 ASC), CONSTRAINT FK_催還記錄表教師表FOREIGN KEY (工號)REFERENCES 教師表(工號) ON UPDATE CASCADE ON DELET

19、E CASCADE, CONSTRAINT FK_催還記錄表圖書FOREIGN KEY (ISBN) REFERENCES 圖書表(ISBN) ON UPDATE CASCADE ON DELETE CASCADE )ON PRIMARY GO 3)寫以下增刪改查操作的SQL語句 1. 壤加三條類型表數(shù)據(jù) 語句： INSERT INTO類型表（類型名，借閱期限）VALUES （ 1 it算機(jī)150） INSERT INTO類型表（類型名，借閱期限）VALUES （文學(xué)1,180） INSERT INTO類型表（類型名，借閱期限）VALUES （，經(jīng)濟(jì)-30） delete類型表 -select

20、 * from 類型表 2. 増加三條部門表數(shù)據(jù) 語句： INSERT INTO 部門表（部門名，電話）VALUES （1 網(wǎng)絡(luò)系 11 ） INSERT INTO 部門表（部門名，電話）VALUES （1 外語系11 ） INSERT INTO 部門表（部門名，電話）VALUES管理系 1 , 1） -select * from 部門表 3. 增加三條教師表數(shù)據(jù) 語句： INSERT INTO教師表（工號姓名，性別,Email 部門名）VALUES （1 J001 1 , 1張三JI女 ，網(wǎng)絡(luò)系） INSERT

21、INTO教師表（工號，姓劃，性別,Email，部門名）VALUESJ002，J李四，男 1 jLS J，網(wǎng)絡(luò)系） INSERT INTO教師表（工號/姓名，性別，Email 部門名）VALUESJ003，J王五男 , 1 外語系*） select * from 教師表 4. 增加五條圖書表數(shù)據(jù) 語句： INSERT INTO圖書表（ISBN,書需，作者，出版社，岀版時間，單價，類型劃，狀態(tài)） VALUES （1 97893022186611, C#,，江紅 D靑華大學(xué)出版社2012.2.10 , 45,計(jì)算機(jī)- 庫存J INSERT INTO圖書表（ISBN,書名，作者，出版社，出版時間，單價

22、，類型名，狀態(tài)） VALUES （9787802229761,，人生博弈密碼，呂叔春，中國華僑岀版社 2009.2.1,36.8,，文學(xué)，J 庫存 T INSERT INTO圖書表（ISBN,書名，作者，出版社，岀版時間，單價，類型劃，狀態(tài)） VALUES （ 9787301150894 , 1經(jīng)濟(jì)學(xué)原理，梁小民J，北京大學(xué)出版社-2009.4.11,54, 經(jīng)濟(jì)，借出J -select * from 圖書表 5. 增加三條管理員表數(shù)據(jù) 語句： INSERT INTO理員表（管理員名/性別，口令）VALUES （ 11 z 1 男JwgOOlT INSERT INTO理員表（管理員名性別/口令

23、）VALUES （ 11 , 1 女 INSERT INTO管理員表（管理員名性別/口令）VALUES （ 1徐管J 男 jXg003f） -select * from 管理員表 -select * from 教師表 select * from 部門表 -select * from 類型表 -select * from 圖書表 -select * from 管理員表 6. 某教師今天借了某書，涉及兩個操作，一是圖書表的狀態(tài)改為“借出”，二是增加一個借還表記 錄 語句： insert借還記錄表(ISBN，工號，代辦理人，借出日期，還辦理人，歸還日期) values(l9787802229761,

24、JOOl1,wgOOlJ,2012.3.20,Lg002,11) -select * from借還記錄表 -delete借還記錄表 update圖書表 set狀態(tài)=，借出1 where ISBN=197878022297611 select * from 圖書表 7. 將某書的單價改為32元。 語句： update圖書表 set單價=32 where ISBN=197878022297611 select * from 圖書表 8. 査詢某教師的借還書記錄 語句： select * from 教師表 -select * from 部門表 -select * from 類型表 圖書表 -sele

25、ct * from 管理員表 -select * from 借還記錄表 select a工號丿姓名，部門名，書名 from教師表a join借還記錄表c on a工號工號 join圖書表 b on c工SBN=b ISBN where a.工號=J0011 go 9. 査詢某本書的流轉(zhuǎn)記錄 語句： -select * from借還記錄表 select a工號,姓名，部門名，書名，借出日期，歸還日期 from教師表a,圖書表b,借還記錄表c xjhere a匸號=c. J1 號 and b . ISBN=c . ISBN and b ISBN=, 97 87 8022297 61 4)寫出創(chuàng)建以

26、下視圖的SQL語句 1. 借出圖書視圖 語句： select * from借還記錄表 -select * from 圖書表 Create view借出圖書視圖 As select * from圖書表where狀態(tài)=借出， go 2. 借閱逾期視圖 語句： -select * from 圖書表 -select * from借還記錄表 select * from 類型表 Create View借閱逾期視圖 SELECT a. ISBN, a.書名/ b工號，b借出日期 FROM 圖書表 a JOIN 借還記錄表 b ON a. ISBN=b . ISBN JOIN 類型表C ON a.類型劃=c.

27、類型名 WHERE a.狀態(tài)=1 in ill 1 and datedif f (day, b借出日期,getaate () ) c .借閱期限 select * from借閱邂期視圖 3圖書分類統(tǒng)計(jì)冊數(shù)視圖 語句： -select * from 圖書表 Create View圖書統(tǒng)訃視圖 As SELECT類型, count （*）各類圖書冊數(shù)，sum （單價）各類圖書總價 FROM圖書表 GROUP By類型名 -select * from圖書統(tǒng)計(jì)視圖 第四部分 作業(yè)3一存儲過程與觸發(fā)器設(shè)計(jì)（滿分8分） 任務(wù)： 1）為Bookstore數(shù)據(jù)庫增加一個用戶operL該用戶可以進(jìn)行系統(tǒng)所需的應(yīng)

28、用操作，權(quán)限盡可能精簡。 諳描述該用戶的權(quán)限。 2）設(shè)計(jì)存儲過程 a）數(shù)據(jù)初始化過程spjnito包含的操作有：所有的用于測試圖書信息、教師信息、歷史記錄淸 空，輸入圖書分類、輸入管理員信息、輸入借書規(guī)定。 b）某教師在某時間借了某本書，由某管理員辦理。 c）某教師在某時間還了某本書，由某管理員辦理 d）查看某人的全部借還書記錄 e）查看某本書的歷史流轉(zhuǎn)記錄 f）某管理員在某時間給全部借閱過期者發(fā)催還郵件。 g）對書名進(jìn)行模糊搜索（搜索條件可以是多個模糊列值的組合，例如：搜索書名包含網(wǎng)絡(luò)” 兩個字，并且是近3年岀版的圖書） 3）設(shè)計(jì)觸發(fā)器 a）拒絕超級用戶sa對所有的數(shù)據(jù)的修改，凡是SA進(jìn)行的修改全部回退。 b