數據庫設計作業——《網上書店系統》數據庫設計

一、功能需求

• 普通用戶：可以進行最基礎的登陸操作，可瀏覽圖書、按類別查詢圖書、查看 圖書的詳細信息，還可以注冊成為會員。
• 會員：需要填寫詳細信息（真實姓名、性別、手機號、地址、郵箱等），可瀏覽圖書、按類別查詢圖書、查看圖書的詳細信息，在此基礎上，還可以訂購圖書、生成訂單、查看訂單。
• 管理員：可以瀏覽圖書、按類別查詢圖書、查看圖書的詳細信息，需要查看訂單、處理訂單進行發貨處理，同時可以查看、修改個人信息，更新圖書信息，如增加、刪除圖書、更新庫存量、修改商品名稱、增加圖書的屬性列等。
• 圖書：需要管理員進行編排，按圖書編號、書名、圖書類型、作者、出版社、出版日期、定價、折扣、庫存數量等信息分類。

二、數據庫概念結構設計

1. 概念結構設計（E-R圖）

2. 邏輯結構設計（數據庫表、關系模式）

• 普通用戶（賬號 ，密碼）
• 會員（賬號，密碼，會員名，注冊時間，手機號，郵箱，地址，性別，真實姓名）
• 注冊（賬號） 外鍵：賬號
• 管理員（管理員編號，管理員密碼，管理員名稱，郵箱，手機號）
• 訂單（訂單編號，會員賬號，下單日期，訂購數量，送貨方式，支付方式，賣出 總價，收貨地址，收貨人聯系方式，收貨人姓名，發貨日期，負責該訂單的 管理員（編號））
• 訂購（賬號，訂單編號） 外鍵：賬號，訂單編號
• 處理（管理員編號，訂單編號） 外鍵：管理員編號，訂單編號
• 圖書（圖書編號，書名，圖書類型，作者，出版社，出版日期，定價，折扣，庫 存數量，會員賬號） 外鍵：會員賬號
• 訂單詳情（訂單編號，圖書編號，訂購數量，訂購價格） 外鍵：訂單編號，圖書編號

規范化分析：

• 普通用戶關系中賬號為主鍵，其中函數依賴有：賬號—>密碼
  因為在普通用戶關系中不存在非主屬性對碼的部分函數依賴和傳遞函數依賴，所以普通用戶關系是屬于3NF。
• 會員關系中賬號為主鍵，其中函數依賴有：賬號—>密碼，賬號—>會員名，賬號—>注冊時間，賬號—>手機號，賬號—>郵箱，賬號—>地址，賬號—>性別，賬號—>真實姓名
  因為在會員關系中不存在非主屬性對碼的部分函數依賴和傳遞函數依賴，所以會員關系是屬于3NF。
• 管理員關系中管理員編號為主鍵，其中函數依賴有：管理員編號—>管理員密碼，管理員編號—>管理員名稱，管理員編號—>郵箱，管理員編號—>手機號
  因為在管理員關系中不存在非主屬性對碼的部分函數依賴和傳遞函數依賴，所以管理員關系是屬于3NF。
• 訂單關系中訂單編號為主鍵，其中函數依賴有：訂單編號—>會員賬號，訂單編號—>下單時間，訂單編號—>訂購總數，訂單編號—>送貨方式，訂單編號—>支付方式，訂單編號—>賣出總價，訂單編號—>收貨地址，訂單編號—>收貨人聯系方式，訂單編號—>收貨人姓名，訂單編號—>發貨日期，訂單編號—>負責該訂單的管理員（編號）
  因為在訂單關系中不存在非主屬性對碼的部分函數依賴和傳遞函數依賴，所以訂單關系是屬于3NF。
• 圖書關系中圖書編號為主鍵，其中函數依賴有：圖書編號—>書名，圖書編號—>圖書類型，圖書編號—>作者，圖書編號—>出版社，圖書編號—>出版日期，圖書編號—>定價，圖書編號—>折扣，圖書編號—>庫存數量
  因為在圖書關系中不存在非主屬性對碼的部分函數依賴和傳遞函數依賴，所以圖書關系是屬于3NF。

3. 建立和管理基本表

表1 會員表

屬性	字段名	數據類型	是否為空/約束條件
賬號	ACCOUNT_NUMBER	CHAR(20)	主鍵
密碼	PASSWORD	VARCHAR(50)	非空
地址	ADDRESS	VARCHAR(50)	可以空
會員名	MEMBER_ID	VARCHAR(50)	可以空
注冊時間	REGTIME	VARCHAR(50)	可以空
手機號	PHONE_NUMBER	VARCHAR(50)	可以空
郵箱	POSTBOX	VARCHAR(50)	可以空
性別	SEX	VARCHAR(8)	可以空
真實姓名	NAME	VARCHAR(50)	可以空

表2 管理員表

屬性	字段名	數據類型	是否為空/約束條件
管理員編號	ADMINISTRATOR_ID	CHAR(20)	主鍵
管理員密碼	AD_CODE	CHAR(30)	非空
管理員名稱	AD_NAME	VARCHAR(20)	非空
郵箱	E_MAIL	VARCHAR(50)	可以空
手機號	TELEPHONE	VARCHAR(20)	可以空

表3 訂單表

屬性	字段名	數據類型	是否為空/約束條件
訂單編號	ORDER_ID	CHAR(20)	主鍵
會員賬號	NUMBER	CHAR(30)	非空
下單日期	DATE	VARCHAR(20)	可以空
訂購總數	SUM	INT	非空
送貨方式	WAY	VARCHAR(20)	非空
支付方式	PAYMENT_WAY	VARCHAR(20)	非空
賣出總價	TOTAL_PRICE	FLOAT	可以空
收貨地址	SHIPPING_ADD	VARCHAR(50)	非空
收貨人聯系方式	CONTACT	VARCHAR(20)	非空
收貨人姓名	CONSIGNEE	VARCHAR(50)	非空
發貨日期	ISSUANCE_DATE	VARCHAR(20)	可以空
負責該訂單的管理員	ADMINISTRATOR_ID	VARCHAR(20)	非空

表4 圖書表

屬性	字段名	數據類型	是否為空/約束條件
圖書編號	BOOK_ID	CHAR(20)	主鍵
書名	BOOK_NAME	CHAR(30)	非空
圖書類型	TYPE	VARCHAR(20)	可以空
作者	AUTHOR	VARCHAR(20)	非空
出版社	PRESS	VARCHAR(50)	非空
出版日期	PRESS_DATE	VARCHAR(20)	非空
定價	PRICE	FLOAT	非空
折扣	DISCOUNT	FLOAT	非空
庫存數量	STOCK_NUM	INT	可以空
會員賬號	MEM_NUM	CHAR(20)	非空

4. 使用SQL語句建立基本表

CREATE TABLE MEMBER
(ACCOUNT_NUMBER CHAR(20) NOT NULL,PASSWORD VARCHAR(50) NOT NULL,ADDRESS	VARCHAR(50),MEMBER_ID VARCHAR(50),REGTIME	VARCHAR(50),PHONE_NUMBER VARCHAR(50),POSTBOX	VARCHAR(50),SEX	VARCHAR(8),NAME VARCHAR(50)
)CREATE TABLE ADMINISTRATOR
(ADMINISTRATOR_ID CHAR(20) NOT NULL,AD_CODE	CHAR(30) NOT NULL,AD_NAME	VARCHAR(20) NOT NULL,E_MAIL VARCHAR(50),TELEPHONE VARCHAR(20)
)CREATE TABLE ORDER_SHOW
(ORDER_ID CHAR(20) NOT NULL,NUMBER CHAR(30) NOT NULL,DATE VARCHAR(20),SUM	INT NOT NULL,WAY	VARCHAR(20) NOT NULL,PAYMENT_WAY	VARCHAR(20) NOT NULL,TOTAL_PRICE	VARCHAR(20),SHIPPING_ADD VARCHAR(50) NOT NULL,CONTACT	VARCHAR(20) NOT NULL,CONSIGNEE VARCHAR(50) NOT NULL,ISSUANCE_DATE VARCHAR(20),ADMINISTRATOR_ID VARCHAR(20) NOT NULL
)CREATE TABLE BOOK
(BOOK_ID	CHAR(20) NOT NULL,BOOK_NAME CHAR(30) NOT NULL,TYPE VARCHAR(20),AUTHOR VARCHAR(20) NOT NULL,PRESS VARCHAR(50) NOT NULL,PRESS_DATE VARCHAR(20) NOT NULL,PRICE FLOAT NOT NULL,DISCOUNT FLOAT NOT NULL,STOCK_NUM INT,MEM_NUM CHAR(20) NOT NULL
)

5. 管理基本表

--(1) 向圖書表增加“頁數”列，其數據類型是整數型
ALTER TABLE BOOK ADD B_PAGE INT--(2) 將“圖書表”中的“頁數”數據類型改成短整型
ALTER TABLE BOOK ALTER COLUMN B_PAGE SMALLINT--(3) 增加“圖書表”中“頁數”取值必須大于0的約束條件
ALTER TABLE BOOK ADD CONSTRAINT CK_PAGE CHECK(B_PAGE > 0)--(4) 刪除“圖書表”中的頁數列
ALTER TABLE BOOK DROP CONSTRAINT CK_PAGE--(5) 刪除“圖書表”
DROP TABLE BOOK

6. 建立和管理視圖

--建立一個“圖書類別”為“計算機”的視圖
GO
CREATE VIEW COMPUTER AS SELECT * FROM BOOK WHERE TYPE = '計算機'
GO

7. 建立和管理索引

--在會員表上為姓名建立索引
CREATE UNIQUE INDEX MEMBER_NAME ON MEMBER(NAME)

8. 數據查詢

--（1）查詢“張玲”的會員信息
SELECT * FROM MEMBER WHERE NAME = '張玲'--（2）查詢圖書“中國通史”的價格
SELECT PRICE FROM BOOK WHERE BOOK_NAME = '中國通史'--（3）查詢網上書店系統中每種數的庫存數量，并按照庫存數量由多到少排列
SELECT STOCK_NUM FROM BOOK ORDER BY STOCK_NUM DESC--（4）查詢“陳東光”訂購圖書的訂單情況
SELECT * FROM ORDER_SHOW WHERE CONSIGNEE = '陳東光'--（5）列出賬號為“jtxia”的會員所訂購的所有圖書信息
SELECT * FROM BOOK WHERE MEM_NUM = 'jtxia' --（6）列出“英語初級聽力（學生用書）”圖書的訂購情況，按照訂購數量由大到小降序排列
SELECT * FROM ORDER_SHOW,BOOK WHERE ORDER_SHOW.NUMBER = MEM_NUM AND BOOK_NAME = '英語初級聽力（學生用書）' ORDER BY SUM DESC--（7）查詢訂單數位列前三的會員信息
SELECT * FROM MEMBER WHERE NAME IN (SELECT TOP 3 NAME FROM ORDER_SHOW,MEMBER WHERE ACCOUNT_NUMBER = NUMBER GROUP BY NAME ORDER BY SUM(ORDER_ID))--（8）查詢訂購過“英語初級聽力”，或者“高等數學”的會員賬號和姓名
SELECT ACCOUNT_NUMBER,NAME FROM MEMBER,ORDER_SHOW,BOOK WHERE MEMBER.ACCOUNT_NUMBER = ORDER_SHOW.NUMBER AND ORDER_SHOW.NUMBER = BOOK.MEM_NUM AND BOOK_NAME = '英語初級聽力（學生用書）' OR BOOK_NAME = '高等數學'--（9）統計會員所有訂單的訂購總價
SELECT SUM(TOTAL_PRICE) FROM ORDER_SHOW--（10）查詢訂購總價大于200的會員的賬號
SELECT NUMBER FROM ORDER_SHOW,MEMBER WHERE ACCOUNT_NUMBER = NUMBER GROUP BY NUMBER HAVING SUM(TOTAL_PRICE) > 200

9. 數據更新

INSERT INTO MEMBER VALUES ('jtxia','123456','河北省廊坊市','@~@','2020-2-1','12345678912','123@qq.com','女','張玲')--（1）書店新進一種圖書，即向圖書表中增加一種新的圖書，ISBN號為9787302164784，書名為軟件工程導論（第5版），作者為張海潘，出版社為清華大學出版社，定價為35，折扣為8，圖書類別為計算機，圖書概況為“本書可作為高等院校軟件工程課程的教材或教學參考書”，庫存量為50
INSERT INTO BOOK VALUES ('9787302164784','軟件工程導論（第5版）','計算機','張海潘','清華大學出版社','2014-3-8',35.0,0.80,50,'jskdb')--（2）書店價格調整，將“軟件工程導論（第5版）”圖書的“折扣”更新為7.5折
UPDATE BOOK SET DISCOUNT = 0.75 WHERE BOOK_NAME = '軟件工程導論（第5版）'--（3）會員地址變更，將會員賬號為“jtxia”的地址更新為“天津大學”
UPDATE MEMBER SET ADDRESS = '天津大學' WHERE ACCOUNT_NUMBER = 'jtxia'--（4）書店進貨，將所有圖書的庫存量增加10
UPDATE BOOK SET STOCK_NUM = STOCK_NUM + 10--（5）活動促銷，將“計算機”類圖書的折扣全部減1（多打一折）
UPDATE BOOK SET DISCOUNT = DISCOUNT - 0.1 WHERE TYPE = '計算機'--（6）由于圖書下架，刪除圖書“軟件工程導論（第5版）”
DELETE FROM BOOK WHERE BOOK_NAME = '軟件工程導論（第5版）'

10. 數據庫維護

數據庫備份

SQL Server 2008提供了4種不同的備份方式，分別是：①完整備份，備份整個數據庫的所有內容，包括事務日志。②差異備份，它是完整備份的補充，差異備份只備份上次完整備份后更改的數據。③事務日志備份，事務日志備份只備份事務日志里的內容。④數據庫文件和文件組備份，如果在創建數據庫時，為數據庫創建了多個數據庫文件或文件組，可以使用該備份方法。
常見的數據庫備份you兩種方法：一種是利用Management Studio進行數據庫備份；另一種時利用SQL語句進行備份。

• 利用Management Studio進行數據庫備份 在備份數據庫之前，首先需要新建設備以用于存儲備份的數據庫。
  新建備份設備的步驟為：打開“對象資源管理器”，點擊“服務器對象”，右擊“備份設備”，選擇“新建備份設備”，在打開的“備份設備”窗口中，輸入備份設備的名稱“網上書店系統_bak”，在“文件”路徑處輸入所屬路徑，單擊確定，即可在左側的對象資源管理器中看到新建的備份文件“網上書店系統_bal”。
  備份數據庫：打開“服務器對象”，右擊“網上書店系統_bak”這個新建立的備份設備，單擊“備份數據庫”，在打開的備份數據庫窗口中，選擇“網上書店系統”數據庫、備份類型為“完整”、備份集名稱默認為“網上書店系統-完整
  數據庫 備份”，單擊“確定”按鈕即可看到備份數據庫成功的提示對話框。
• 使用SQL語句備份數據庫
  1）在新建查詢窗口中，輸入下面的語句創建備份設備。 SP_ADDUMPDEVICE
  ‘disk’,’網上書店系統_bak’,’文件路徑’
  2）在新建查詢窗口中，輸入下面的語句備份數據庫。 BACKUP DATABASS
  網上書店系統 TO DISK = ‘網上書店系統_bak’
  3）單擊工具欄上的“！執行(X)”按鈕，可以看到消息窗口提示備份成功的消息。