書店會(huì)員管理系統(tǒng)
具體的效果圖看博客-----書店管理系統(tǒng)2（https://blog.csdn.net/qq_45981397/article/details/124062654?spm=1001.2014.3001.5502）
- 需求分析
(1).信息需求
書店的管理人員和員工可以為管理系統(tǒng)添加圖書的購買記錄和退書記錄.
會(huì)員可以購買圖書以及退書，工作人員對會(huì)員的姓名，性別，身份證號(hào)碼，電話號(hào)碼和會(huì)員編號(hào)進(jìn)行核證，以確定在購書時(shí)享受的折扣.
管理人員可以通過姓名，購買日期，身份證號(hào)，書本編號(hào)等信息來查詢會(huì)員的購書和退書記錄.
(補(bǔ))
(2) .功能需求
書店會(huì)員管理系統(tǒng)，大致要滿足的功能如下：
①. 圖書購買. 會(huì)員憑借會(huì)員卡進(jìn)行購買，記錄會(huì)員的性別，姓名，電話號(hào)碼，會(huì)員編號(hào)等……，同時(shí)工作人員協(xié)助完成購買操作，記錄下購買日期。
②. 圖書退款.當(dāng)會(huì)員進(jìn)行退款操作時(shí)，工作人員先記錄下會(huì)員的性別，姓名，電話號(hào)碼，會(huì)員編號(hào)等，同時(shí)檢查是否符合退書規(guī)則，并根據(jù)退書規(guī)則收取相應(yīng)的罰金。
③. 購買信息查詢.工作人員及管理人員可以對購買信息進(jìn)行查詢。應(yīng)支持精準(zhǔn)查詢：如通過，會(huì)員編號(hào)，會(huì)員電話號(hào)碼，圖書編號(hào)進(jìn)行查詢，也可以進(jìn)行模糊查詢：如通過購買日期，會(huì)員姓氏，圖書類型進(jìn)行查詢。
④. 退款信息查詢.工作人員及管理人員可以對退款信息進(jìn)行查詢。應(yīng)支持精準(zhǔn)查詢：如通過會(huì)員編號(hào)，會(huì)員電話號(hào)碼，圖書編號(hào)進(jìn)行查詢，也可以進(jìn)行模糊查詢：如通過退書日期，會(huì)員姓氏，圖書類型進(jìn)行查詢。
⑤. 員工查詢.管理人員可以對員工信息進(jìn)行查詢，可查詢員工的姓名，性別，聯(lián)系方式，工資等。
⑥. 更改圖書信息.管理人員可以對圖書信息進(jìn)行更改，如圖書類型，圖書價(jià)格,上架圖書，下架圖書等.
⑦. 更改員工信息.管理人員可以對員工信息進(jìn)行更改，如姓名，職位，工資，增加員工，刪除員工等.
⑧. 會(huì)員信息查詢.工作人員及管理人員可以對會(huì)員的信息進(jìn)行查詢，但所顯示的數(shù)據(jù)并不完全相同.
⑨. 更改會(huì)員信息.工作人員可以對會(huì)員的信息進(jìn)行添加，更改信息操作，但無法進(jìn)行刪除操作.
⑩. 圖書信息查詢.工作人員可以對圖書的信息進(jìn)行查詢.若某書已經(jīng)售出，則無法根據(jù)編號(hào)查到，若圖書已被退回，則可查到.
(3) .安全性與完整性要求
①．完整性的要求見下文的關(guān)系模式約束處。
②．為實(shí)現(xiàn)數(shù)據(jù)庫的安全性保證，不同的用戶擁有不同的權(quán)限。暫定如下：
1.管理人員：具有查看，修改工作人員信息的權(quán)限，具有查看，修改購書信息，退書信息的權(quán)限，且具有工作人員所具有的所有權(quán)限。
2.工作人員：具有查看，插入會(huì)員信息的權(quán)限，具有查看，修改圖書信息的權(quán)限。但工作人員只能查看會(huì)員的姓名，性別和會(huì)員類別，無法查看到會(huì)員的聯(lián)系方式（而管理人員具有此權(quán)限）。工作人員具有查看，插入，購書信息，查看，插入，修改退書信息的權(quán)限，工作人員僅可以修改退書信息中的罰款屬性，(原因見下文的退書信息表的補(bǔ)充說明)同理，無法查看表中會(huì)員的聯(lián)系方式。
3.會(huì)員：具有查看圖書信息的權(quán)限。
(注：此處的修改指的是對數(shù)據(jù)庫中某個(gè)關(guān)系的一系列操作，包括但不僅限于：INSERT,DELETE,MODIFY等等。
另：視圖權(quán)限在下文補(bǔ)充。)
(4) 數(shù)據(jù)字典
①．?dāng)?shù)據(jù)流圖
②．?dāng)?shù)據(jù)結(jié)構(gòu)
數(shù)據(jù)結(jié)構(gòu)：會(huì)員
含義說明：定義了一個(gè)會(huì)員的相關(guān)信息
組成結(jié)構(gòu)：會(huì)員編號(hào)，會(huì)員姓名，會(huì)員性別，會(huì)員類別，會(huì)員聯(lián)系方式（）
數(shù)據(jù)結(jié)構(gòu)：工作人員
含義說明：定義了一個(gè)工作人員的相關(guān)信息
組成結(jié)構(gòu)：員工編號(hào)，員工姓名，員工性別，員工身份證號(hào)，員工職位，員工工資
數(shù)據(jù)結(jié)構(gòu)：管理人員
含義說明：定義了一個(gè)管理人員的相關(guān)信息
組成結(jié)構(gòu)：管理員編號(hào)，管理員姓名，（管理員密碼）
數(shù)據(jù)結(jié)構(gòu)：圖書
含義說明：定義了圖書的相關(guān)信息
組成結(jié)構(gòu)：圖書編號(hào)，圖書名稱，圖書出版社，圖書價(jià)格，圖書作者，圖書類別， 圖書庫存數(shù)量
數(shù)據(jù)結(jié)構(gòu)：會(huì)員類別
含義說明：定義了一個(gè)會(huì)員類別分類的相關(guān)信息
組成結(jié)構(gòu)：類別編號(hào)，類別名稱，折扣
數(shù)據(jù)結(jié)構(gòu)：購買記錄
含義說明：定義了一個(gè)會(huì)員購買記錄的相關(guān)信息
組成結(jié)構(gòu)：購買記錄編號(hào)，購買時(shí)間，會(huì)員編號(hào)，會(huì)員聯(lián)系方式，會(huì)員姓名，會(huì)員類別，圖書編號(hào)，圖書名稱，辦理人
數(shù)據(jù)結(jié)構(gòu)：退書記錄
含義說明：定義了一個(gè)會(huì)員退書記錄的相關(guān)信息
組成結(jié)構(gòu)：退款記錄編號(hào)，退款時(shí)間，會(huì)員編號(hào)，會(huì)員聯(lián)系方式，會(huì)員姓名，圖書編號(hào)，圖書名稱，罰金，辦理人
③．?dāng)?shù)據(jù)項(xiàng)
數(shù)據(jù)項(xiàng)名 數(shù)據(jù)項(xiàng)含義 別名 類型 長度
會(huì)員編號(hào) 唯一標(biāo)識(shí)會(huì)員 編號(hào) 數(shù)值型 6
會(huì)員姓名 會(huì)員的姓名 姓名 字符型 1-3
會(huì)員性別 會(huì)員的性別 性別 字符型 1
會(huì)員類別 會(huì)員的類別（等級(jí)） 類別 字符型 4
會(huì)員聯(lián)系方式 會(huì)員的聯(lián)系方式（如電話號(hào)碼） 聯(lián)系方式 數(shù)值型 11
員工編號(hào) 唯一標(biāo)識(shí)員工 編號(hào) 數(shù)值型 6
員工姓名 員工的姓名 姓名 字符型 1-3
員工性別 員工的性別 性別 字符型 1
員工身份證號(hào) 員工的身份證號(hào)碼 身份證號(hào) 數(shù)值型 18
員工職位 員工所擔(dān)任的職務(wù) 職位 字符型 5
員工工資 員工的每個(gè)月工資（死工資（待）） 工資 數(shù)值型 4-5
管理員編號(hào) 唯一標(biāo)識(shí)管理員 編號(hào) 數(shù)值型 6
管理員姓名 管理員的姓名 姓名 字符型 1-3
圖書編號(hào) 唯一標(biāo)識(shí)圖書（同一本書具有不同的編號(hào)） 編號(hào) 數(shù)值型 6
圖書名稱 圖書的名稱 名稱 字符型 1-8
圖書出版社 圖書的出版社 出版社 字符型 5-12
圖書價(jià)格 圖書的價(jià)格 價(jià)格 數(shù)值型 2-3
圖書作者 圖書的作者 作者 字符型 3-10
圖書類別 圖書的類別（科幻，小說，散文等） 類別 字符型 2-5
圖書數(shù)量 該書店中此圖書的存量 數(shù)量 數(shù)值型 5
類別編號(hào) 會(huì)員的類別編號(hào) 編號(hào) 數(shù)值型 1-2
類別名稱 會(huì)員的類別名稱 名稱 字符型 4
折扣 會(huì)員所享有的折扣（依據(jù)不同類別而不同） 折扣 數(shù)值型 0-3
購買記錄編號(hào) 唯一標(biāo)識(shí)一條購買記錄 編號(hào) 數(shù)值型 6
購買時(shí)間 購買圖書的時(shí)間 購買時(shí)間 字符型 9-11
退款記錄編號(hào) 唯一標(biāo)識(shí)一條退款記錄 編號(hào) 數(shù)值型 6
退款時(shí)間 退掉圖書的時(shí)間 退款時(shí)間 字符型 9-11
罰金 某一次退款中所需繳納的罰金 罰金 數(shù)值型 2-3
- 概念設(shè)計(jì)
①．局部E-R圖
②．整體E-R圖
各實(shí)體的屬性如下：
會(huì)員(members)：會(huì)員編號(hào)，會(huì)員姓名，會(huì)員性別，會(huì)員聯(lián)系方式
圖書(book)：圖書編號(hào)，圖書類別，圖書作者，圖書價(jià)格，圖書出版社，圖書名稱，庫存數(shù)量
會(huì)員類別(member_category)：類別編號(hào)，類別名稱，折扣
工作人員(staff_member)：員工編號(hào)，員工姓名，員工性別，員工身份證號(hào)，員工職位，員工工資，管理員
管理人員(management)：管理員編號(hào)，管理員姓名
各聯(lián)系集的屬性如下：
購書記錄(purchase_information)：購買記錄編號(hào)，會(huì)員編號(hào)，購書時(shí)間，會(huì)員姓名，會(huì)員聯(lián)系方式，會(huì)員類別編號(hào)，圖書編號(hào)，圖書名稱，辦理人
退書記錄(refund_information)：退書記錄編號(hào)，會(huì)員編號(hào)，退書時(shí)間，會(huì)員姓名，會(huì)員聯(lián)系方式，圖書編號(hào)，圖書名稱，罰金，辦理人
- 邏輯結(jié)構(gòu)設(shè)計(jì)
①．E-R圖轉(zhuǎn)化為關(guān)系模式
由E-R圖轉(zhuǎn)化而來的關(guān)系模式如下：
[會(huì)員表] 會(huì)員信息表
列名 數(shù)據(jù)類型 大小 是否為空 其他約束
member_number(會(huì)員編號(hào)) unsigned mediumint — NOT NULL PRIMARY KEY（主鍵約束）
member_name(會(huì)員姓名) varchar 12 NOT NULL —
member_gender(會(huì)員性別) char 3 NOT NULL CHECK(male or female)
member_phone_number(會(huì)員聯(lián)系方式) numeric (12,0) NOT NULL UNIQUE(唯一約束)
member_category(會(huì)員類別) numeric (2,0) NOT NULL FOREIGN KEY(外鍵約束）
補(bǔ)充說明
1.會(huì)員編號(hào)的值限制在100000 - 999999之間。
[圖書表] 圖書信息表
列名 數(shù)據(jù)類型 大小 是否為空 其他約束
book_number(圖書編號(hào)) unsigned mediumint — NOT NULL PRIMARY KEY(主鍵約束)
book_category(圖書類別) varchar 15 NOT NULL -----
book_author(圖書作者) varchar 20 NOT NULL -----
book_price(圖書價(jià)格) numeric (5,2) NOT NULL -----
book_press(圖書出版社) varchar 20 NOT NULL -----
book_name(圖書名稱) varchar 15 NOT NULL -----
book_quantity(庫存數(shù)量) numeric (7,0) NOT NULL -----
補(bǔ)充說明
1.同一本書的圖書編號(hào)相同（此處的同一本書指的是作者出版社及圖書名稱相同的書，而不是一個(gè)個(gè)體）
2.圖書編號(hào)的值限定在100000 - 999999之間。
3.庫存數(shù)量限定其數(shù)值大于等于0.
[會(huì)員類別表] 會(huì)員類別信息表
列名 數(shù)據(jù)類型 大小 是否為空 其他約束
category_number(類別編號(hào)) numeric (2,0) NOT NULL PRIMARY KEY(主鍵約束)
category_name(類別名稱) varchar 15 NOT NULL -----
discount(折扣) numeric (1,1) NOT NULL CHECK( BETWEEN 0 AND 1)
補(bǔ)充說明
1.折扣應(yīng)限制在0-1之間（符合實(shí)際情況）.
[工作人員表] 工作人員信息表
列名 數(shù)據(jù)類型 大小 是否為空 其他約束
staff_number(員工編號(hào)) unsigned mediumint — NOT NULL PRIMARY KEY(主鍵約束)
staff_name(員工姓名) varchar 12 NOT NULL -----
staff_gender(員工性別) char 3 NOT NULL CHECK(male or female)
staff_id_number(員工身份證號(hào)) char 17 NOT NULL UNIQUE(唯一約束)
staff_post(員工職位) varchar 15 NOT NULL CHECK()
staff_wage(員工工資) numeric (5,2) NOT NULL -----
administractor(管理員) Unsigned mediumint — NOT NULL FOREIGN KEY(外鍵約束)
補(bǔ)充說明
1.此處的員工工資為每月工資（且隨職位而固定）
2.administractor和管理人員表相連，參照其屬性management_number.
3.員工編號(hào)和管理員號(hào)的值限制在100000 - 9999999之間
4.性別僅有兩個(gè)取值：男或女.
5.對員工的職位也應(yīng)有限制取值（已通過觸發(fā)器實(shí)現(xiàn)）
[管理人員表] 管理人員信息表
列名 數(shù)據(jù)類型 大小 是否為空 其他約束
management_number(管理員編號(hào)) unsigned mediumint — NOT NULL PRIMARY KEY(主鍵約束)
management_name(管理員姓名) varchar 15 NOT NULL -----
補(bǔ)充說明
1.管理人員的值應(yīng)限制在100000 - 999999之間。
[購買記錄表] 購買記錄信息表
列名 數(shù)據(jù)類型 大小 是否為空 其他約束
purchase_number(購買記錄編號(hào)) Unsigned
mediumint — NOT NULL PRIMARY KEY(主鍵約束)
member_number(會(huì)員編號(hào)) unsigned mediumint — NOTNULL FOREIGN KEY(外鍵約束)
purchase_time(購買時(shí)間) TIMESTAMP — NOT NULL CHECK()
member_name(會(huì)員姓名) varchar 12 NOT NULL -----
member_phone_number(會(huì)員聯(lián)系方式) numeric (12,0) NOT NULL -----
category_number(會(huì)員類別編號(hào)) numeric (2,0) NOT NULL FOREIGN KEY(外鍵約束)
book_number(圖書編號(hào)) unsigned mediumint — NOT NULL FOREIGN KEY(外鍵約束)
book_name(圖書名稱) varchar 15 NOT NULL -----
handlers(辦理人) unsigned mediumint — NOT NULL FOREIGN KEY(外鍵約束)
補(bǔ)充說明
1.添加購買記錄編號(hào)作為此表的主碼，應(yīng)限制購買時(shí)間<=今日.
2.member_number作為外碼與會(huì)員表相連，參照其主碼member_number.
3.category_number作為外碼與會(huì)員類別表相連，參照其主碼category_number(便于購書時(shí)的折扣查詢).
4.book_number作為外碼與圖書表相連，參照其主碼book_number.
5.handler作為外碼與工作人員表相連，參照其主碼staff_number.
6.購買記錄編號(hào)的值應(yīng)限制在100000 - 999999之間.
[退書記錄表] 退書記錄信息表
列名 數(shù)據(jù)類型 大小 是否為空 其他約束
refund_number(退書記錄編號(hào)) unsigned int — NOT NULL PRIMARY KEY(主鍵約束)
member_number(會(huì)員編號(hào)) unsigned int — NOT NULL FOREIGN KEY(外鍵約束)
refund_time(退書時(shí)間) TIMESTAMP — NOT NULL CHECK()
member_name(會(huì)員姓名) var char 12 NOT NULL -----
member_phone_number(會(huì)員聯(lián)系方式) numeric (12,0) NOT NULL -----
penalty(罰金) numeric (3,2) NOT NULL -----
book_number(圖書編號(hào)) unsigned int — NOT NULL FOREIGN KEY(外鍵約束)
book_name(圖書名稱) varchar 15 NOT NULL -----
handlers(辦理人) unsigned int — NOT NULL FOREIGN KEY(外鍵約束)
補(bǔ)充說明
1.添加退書記錄編號(hào)作為此表的主碼，應(yīng)限制退書時(shí)間<=今日.
2.member_number作為外碼與會(huì)員表相連，參照其主碼member_number.
3.book_number作為外碼與圖書表相連，參照其主碼book_number.
4.handler作為外碼與工作人員表相連，參照其主碼staff_number.
5.會(huì)員繳納了罰金后，工作人員可將該屬性修改為0(略不合理，待修改。)
6.handlers限制在100000至999999之間
7.退書記錄編號(hào)的值應(yīng)限制在100000 - 999999 之間.
②．關(guān)系模式的優(yōu)化
(1) 會(huì)員表
在會(huì)員表中，會(huì)員編號(hào)----->會(huì)員姓名，會(huì)員編號(hào)----->會(huì)員性別，會(huì)員編號(hào)----->會(huì)員聯(lián)系方式，其中會(huì)員編號(hào)為主碼，已滿足BCNF范式。
(2) 圖書
在圖書表中，圖書編號(hào)----->圖書類別， 圖書編號(hào)----->圖書作者，圖書編號(hào)----->圖書價(jià)格，圖書編號(hào)----->圖書出版社，圖書編號(hào)----->圖書名稱，圖書編號(hào)----->庫存數(shù)量，圖書作者、圖書名稱、圖書出版社----->圖書價(jià)格，但其中存在不包含碼的依賴，故滿足3NF范式。
分解為BCNF范式如下：
表1(圖書編號(hào)，圖書類別，庫存數(shù)量，圖書作者，圖書名稱，圖書出版社)
表2(圖書作者，圖書名稱，圖書出版社，圖書價(jià)格)
(3) 會(huì)員類別
在會(huì)員類別表中，類別編號(hào)----->類別名稱，類別編號(hào)----->折扣，類別名稱----->折扣，存在傳遞依賴，滿足2NF范式。
分解為BCNF范式如下：
表1(類別編號(hào)，類別名稱)
表2(類別名稱，折扣)
(4) 工作人員
在工作人員表中，員工編號(hào)----->員工姓名，員工編號(hào)----->員工性別，員工編號(hào)----->員工身份證號(hào)，員工編號(hào)----->員工職位，員工編號(hào)----->員工工資，員工身份證號(hào)----->員工姓名，員工身份證號(hào)----->員工性別，員工職位----->員工工資，存在傳遞依賴，滿足2NF范式。
分解為BCNF范式如下：
表1(員工編號(hào)，員工身份證號(hào)，員工職位)
表2(員工身份證號(hào)，員工性別，員工姓名)
表3(員工職位，員工工資)
(5) 管理人員
在管理人員表中，管理員編號(hào)----->管理員姓名，滿足BCNF范式
(6) 購買記錄
在購買記錄表中，購買記錄編號(hào)----->會(huì)員編號(hào)，購買記錄編號(hào)----->購買時(shí)間，購買記錄編號(hào)----->會(huì)員姓名，購買記錄編號(hào)----->會(huì)員聯(lián)系方式，購買記錄編號(hào)----->會(huì)員類別編號(hào)，購買記錄編號(hào)----->圖書編號(hào)，購買記錄編號(hào)----->圖書名稱，購買記錄編號(hào)----->辦理人，會(huì)員編號(hào)----->會(huì)員姓名，會(huì)員聯(lián)系方式，會(huì)員類別編號(hào)，圖書編號(hào)----->圖書名稱，存在傳遞依賴，滿足2NF范式。
分解為BCNF范式如下：
表1(購買記錄編號(hào)，購買時(shí)間，辦理人，會(huì)員編號(hào)，圖書編號(hào))
表2(會(huì)員編號(hào)，會(huì)員姓名，會(huì)員聯(lián)系方式，會(huì)員類別編號(hào))
表3(圖書編號(hào)，圖書名稱)
(7) 退書記錄
在退書記錄表中，退書記錄編號(hào)----->會(huì)員編號(hào)，退書時(shí)間，會(huì)員姓名，會(huì)員聯(lián)系方式，罰金，圖書編號(hào)，圖書名稱，辦理人，會(huì)員編號(hào)----->會(huì)員姓名，圖書編號(hào)----->圖書名稱，存在傳遞依賴，滿足2NF范式
分解為BCNF范式如下：
表1(退書記錄編號(hào)，退書時(shí)間，辦理人，罰金，會(huì)員編號(hào)，圖書編號(hào))
表2(會(huì)員編號(hào)，會(huì)員姓名，會(huì)員聯(lián)系方式)
表3(圖書編號(hào)，圖書名稱)
(注：若單純的追求更高級(jí)別的范式可能會(huì)對數(shù)據(jù)庫的管理造成極大的不便。）
③．關(guān)系模式的約束
(1).域約束
域約束是保證數(shù)據(jù)庫屬性取值的合理性。
屬性值應(yīng)該是域中的值，具體的定義可查看數(shù)據(jù)項(xiàng)，除此之外，一個(gè)屬性能否為NULL，也是域完整性約束的主要內(nèi)容。
包括但不僅限于檢查(CHECK)，默認(rèn)值(DEFAULT)，是否為空(NULL OR NOT NULL),外鍵(FOREIGN KEY)，主鍵(PRIMARY KEY)等等。
(2).唯一性約束
在本系統(tǒng)中，編號(hào)等，包括會(huì)員編號(hào)，圖書編號(hào)，工作人員編號(hào)，管理人員編號(hào)，購書記錄編號(hào)，退書記錄編號(hào)等必須不重復(fù)，應(yīng)創(chuàng)建唯一性索引，但由于均存在主碼約束，故可省略。
(3).聯(lián)系約束(參照約束)
購買記錄表參照會(huì)員表，以會(huì)員編號(hào)作為兩個(gè)關(guān)系進(jìn)行關(guān)聯(lián)的屬性。
購買記錄表參照會(huì)員類別表，以會(huì)員類別編號(hào)作為兩個(gè)關(guān)系進(jìn)行關(guān)聯(lián)的屬性。
購買記錄表參照圖書表，以圖書編號(hào)作為兩個(gè)關(guān)系進(jìn)行關(guān)聯(lián)的屬性。
購買記錄表參照工作人員表，以handler/stuff_number作為兩個(gè)關(guān)系進(jìn)行關(guān)聯(lián)的屬性。
退書記錄表參照會(huì)員表，以會(huì)員編號(hào)作為兩個(gè)關(guān)系進(jìn)行關(guān)聯(lián)的屬性。
退書記錄表參照圖書表，以圖書編號(hào)作為兩個(gè)關(guān)系進(jìn)行關(guān)聯(lián)的屬性。
退書記錄表參照工作人員表，以handler/stuff_number作為兩個(gè)關(guān)系進(jìn)行關(guān)聯(lián)的屬性。
工作人員表參照管理人員表，以administractor/management_number作為兩個(gè)關(guān)系進(jìn)行關(guān)聯(lián)的屬性。
(4) .業(yè)務(wù)約束(用戶定義完整性約束)
業(yè)務(wù)規(guī)則約束是針對指定應(yīng)用的邏輯和要求的，它們來源于使用數(shù)據(jù)庫組織中的應(yīng)用程序和策略。
在書店會(huì)員管理系統(tǒng)中，業(yè)務(wù)規(guī)則大致如下：
·編號(hào)均為6位(方便進(jìn)行數(shù)據(jù)庫的管理)
·購買時(shí)間/退書時(shí)間均不可大于今日
·性別(管理人員性別，工作人員性別，會(huì)員性別)的取值是’男’或‘女’
·會(huì)員享受的折扣的取值應(yīng)在(0,1)之間 (左開右開)
·圖書的庫存數(shù)量始終 >= 0,且當(dāng)庫存數(shù)量為0時(shí)，無法對該書(通過編號(hào)進(jìn)行區(qū)別)進(jìn)行購書操作。(通過約束完成.)
4.物理結(jié)構(gòu)設(shè)計(jì)（索引，視圖，觸發(fā)器，存儲(chǔ)過程，數(shù)據(jù)存儲(chǔ)和存取方法等）
(1).索引的創(chuàng)建
表名 列名 索引
會(huì)員表 會(huì)員編號(hào)（主鍵） 唯一索引
會(huì)員類別表 會(huì)員類別編號(hào)（主鍵） 唯一索引
圖書表 圖書編號(hào)（主鍵） 唯一索引
管理人員表 管理人員編號(hào)（主鍵） 唯一索引
工作人員表 工作人員編號(hào)（主鍵） 唯一索引
購買記錄表 購買記錄編號(hào)（主鍵） 唯一索引
退書記錄表 退書記錄編號(hào)（主鍵） 唯一索引
購買記錄表 購買時(shí)間 可重復(fù)索引
退書記錄表 退書時(shí)間 可重復(fù)索引
圖書表 圖書名稱 可重復(fù)索引
補(bǔ)充說明
1.由于可能存在多個(gè)客服同時(shí)買書/退書，因此在購買時(shí)間和退書時(shí)間上創(chuàng)建的是可重復(fù)索引.
2.根據(jù)圖書名稱的搜索量很大，且圖書會(huì)存在同名的情況，因此創(chuàng)建可重復(fù)索引.
(2) .視圖的創(chuàng)建
創(chuàng)建了4個(gè)視圖，便于工作人員和會(huì)員進(jìn)行信息的查詢。
– 視圖創(chuàng)建
– 會(huì)員基本信息視圖
CREATE VIEW members_view
(
會(huì)員編號(hào),
會(huì)員姓名,
會(huì)員性別,
會(huì)員聯(lián)系方式,
會(huì)員類別
)
AS
SELECT member_number,
member_name,
member_gender,
member_phone_number,
member_category
FROM
members;
– 圖書基本信息視圖
CREATE VIEW book_view
(
圖書編號(hào),
圖書名稱,
圖書作者,
圖書存量
)
AS
SELECT book_number,
book_name,
book_author,
book_quantity
FROM
book;
– 購買記錄基本信息視圖
CREATE VIEW purchase_view
(
購買編號(hào),
購買時(shí)間,
會(huì)員編號(hào),
圖書編號(hào),
圖書名稱
)
AS
SELECT purchase_number,
purchase_time,
member_number,
book_number,
book_name
FROM
purchase_information;
– 退書記錄基本信息視圖
CREATE VIEW refund_view
(
退書編號(hào),
退書時(shí)間,
會(huì)員編號(hào),
圖書編號(hào),
圖書名稱
)
AS
SELECT refund_number,
refund_time,
member_number,
book_number,
book_name
FROM
refund_information;
(3) .觸發(fā)器的創(chuàng)建
在圖書表上創(chuàng)建觸發(fā)器，其中一個(gè)在購買表進(jìn)行插入操作時(shí)觸發(fā)，使圖書表中該圖書（根據(jù)圖書編號(hào)確定）的數(shù)量-1（若成立）。另一個(gè)在退書表進(jìn)行插入操作時(shí)觸發(fā)，使圖書表中該圖書（根據(jù)圖書編號(hào)確定）的數(shù)量+1；
– 觸發(fā)器的創(chuàng)建
– 在購書信息表上上創(chuàng)建觸發(fā)器 買書時(shí)對庫存數(shù)量進(jìn)行修改
DELIMITER $$
CREATE TRIGGER purchase_trigger
AFTER INSERT
ON purchase_information
for each row
BEGIN
UPDATE book
SET book.book_quantity = book_quantity - 1
WHERE book.book_number = NEW.book_number;
END $$
DELIMITER ;
DROP TRIGGER purchase_trigger;
– 在退書信息表上創(chuàng)建觸發(fā)器，退書時(shí)對庫存數(shù)量進(jìn)行修改
DELIMITER $$
CREATE TRIGGER refund_trigger
AFTER INSERT
ON refund_information
for each row
BEGIN
UPDATE book
SET book.book_quantity = book_quantity + 1
WHERE book.book_number = new.book_number;
END $$
DELIMITER ;
DROP TRIGGER refund_trigger;
– 在購書信息表上創(chuàng)建 ， 需滿足職務(wù)為柜臺(tái) – DELIMITER $$ – CREATE TRIGGER purchase_information_trigger – AFTER INSERT – ON purchase_information – FOR EACH ROW – BEGIN – if – ( – (SELECT staff_member.staff_post – FROM staff_member – WHERE staff_member.staff_number = new.handlers – ) <> ‘柜臺(tái)’ #<>為不等于 – ) then – delete from purchase_information where purchase_information.purchase_number = new.purchase_number; – end if;
– END $$
– DELIMITER ;
– DROP TRIGGER purchase_information_trigger;
– 在購書信息表上創(chuàng)建觸發(fā)器， 約束辦理人的職務(wù)為：柜臺(tái)
DELIMITER $$
CREATE TRIGGER purchase_information_trigger
BEFORE INSERT
ON purchase_information
FOR EACH ROW
BEGIN
if
(
(SELECT staff_member.staff_post
FROM staff_member
WHERE staff_member.staff_number = new.handlers
) <> '柜臺(tái)' #<>為不等于
) then
signal sqlstate '45000' set message_text ='錯(cuò)誤！違背職務(wù)的規(guī)定' ;
end if;
END $$
DELIMITER ;
DROP TRIGGER purchase_information_trigger;
– 在退書信息表上創(chuàng)建觸發(fā)器， 約束辦理人的職務(wù)為：柜臺(tái)
DELIMITER $$
CREATE TRIGGER refund_information_trigger
BEFORE INSERT
ON refund_information
FOR EACH ROW
BEGIN
if
(
(SELECT staff_member.staff_post
FROM staff_member
WHERE staff_member.staff_number = new.handlers
) <> '柜臺(tái)' #<>為不等于
) then
signal sqlstate '45000' set message_text ='錯(cuò)誤！違背職務(wù)的規(guī)定' ;
end if;
END $$
DELIMITER ;
DROP TRIGGER refund_information_trigger;
– 在購書信息表上創(chuàng)建觸發(fā)器， 約束購書的時(shí)間 <= 現(xiàn)在電腦系統(tǒng)的時(shí)間
DELIMITER $$
CREATE TRIGGER purchase_time_trigger
BEFORE INSERT
ON purchase_information
FOR EACH ROW
BEGIN
if
(
to_days(new.purchase_time) - to_days(DATE(now())) > 0
)
THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Insert time cannot be later than now's time";
END IF;
END $$
DELIMITER ;
DROP TRIGGER purchase_time_trigger;
– 在退書信息表上創(chuàng)建觸發(fā)器， 約束退書的時(shí)間 <= 現(xiàn)在電腦系統(tǒng)的時(shí)間
DELIMITER $$
CREATE TRIGGER refund_time_trigger
BEFORE INSERT
ON refund_information
FOR EACH ROW
BEGIN
if
(
to_days(new.refund_time) - to_days(DATE(now())) > 0
)
THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Insert time cannot be later than now's time";
END IF;
END $$
DELIMITER ;
DROP TRIGGER refund_time_trigger；
(4) .存儲(chǔ)過程的創(chuàng)建
創(chuàng)建統(tǒng)計(jì)某個(gè)會(huì)員買的所有書籍的過程，以及統(tǒng)計(jì)某個(gè)會(huì)員退的所有書籍的過程（暫時(shí)）。
– 存儲(chǔ)過程的創(chuàng)建
– 統(tǒng)計(jì)某會(huì)員買的所有書籍
DELIMITER $$
CREATE PROCEDURE member_total_purchase(temp_number MEDIUMINT)
BEGIN
SELECT book_name,
member_number
FROM purchase_information
WHERE purchase_information.member_number = temp_number;
END $$
DELIMITER ;
DROP PROCEDURE member_total_purchase;
– 統(tǒng)計(jì)某會(huì)員退過的所有書籍
DELIMITER $$
CREATE PROCEDURE member_total_refund(temp_number MEDIUMINT)
BEGIN
SELECT book_name,
member_number
FROM refund_information
WHERE refund_information.member_number = temp_number;
END $$
DELIMITER ;
(5).數(shù)據(jù)存儲(chǔ)
①．買書信息表和退書信息表的存儲(chǔ)引擎均使用MYISAM,因?yàn)樵谫I書信息表和退書信息表中，會(huì)存在大量的選擇操作(查詢信息)和大量的插入操作。因此使用MYISAM引擎以便加快篩選數(shù)據(jù)的速度并且能允許同時(shí)選擇和插入數(shù)據(jù)。
②．其余表可均使用INNODB(且默認(rèn)為INNODB)。
具體實(shí)現(xiàn)見5.數(shù)據(jù)庫的創(chuàng)建。
(6).存取方法的創(chuàng)建
①．在各個(gè)表的主鍵上可建立b-tree索引。
②．在會(huì)員信息表中，會(huì)員類別的屬性值重復(fù)性可能較高，因此可在該屬性上建立聚簇存取方法。
具體實(shí)現(xiàn)見5.數(shù)據(jù)庫的創(chuàng)建。
(7).角色的創(chuàng)建
在數(shù)據(jù)庫中創(chuàng)建，方便于對各個(gè)用戶的權(quán)限管理。各個(gè)角色（用戶）的權(quán)限參照數(shù)據(jù)庫的安全性要求。
①．創(chuàng)建角色’administractor_role’，具有的權(quán)限見安全性要求，代碼如下：
– 角色：管理人員
CREATE ROLE ‘a(chǎn)dministractor_role’;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.staff_member
TO ‘a(chǎn)dministractor_role’;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.purchase_information
TO ‘a(chǎn)dministractor_role’;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.refund_information
TO ‘a(chǎn)dministractor_role’;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.book
TO ‘a(chǎn)dministractor_role’;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.member_category
TO ‘a(chǎn)dministractor_role’;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.members
TO ‘a(chǎn)dministractor_role’;
GRANT SHOW VIEW
ON bookStore.members_view
TO ‘a(chǎn)dministractor_role’;
GRANT SHOW VIEW
ON bookStore.book_view
TO ‘a(chǎn)dministractor_role’;
GRANT SHOW VIEW
ON bookStore.purchase_view
TO ‘a(chǎn)dministractor_role’;
GRANT SHOW VIEW
ON bookStore.refund_view
TO ‘a(chǎn)dministractor_role’;
– GRANT ALL PRIVILEGES
– ON .
– TO ‘a(chǎn)dministractor_role’;
②．創(chuàng)建角色’stuff_role’，具有的權(quán)限見安全性要求，代碼如下:
– 角色：工作人員
CREATE ROLE ‘stuff_role’;
– GRANT SHOW VIEW
– ON bookStore.members_view
– TO ‘stuff_role’; 可查詢聯(lián)系方式，不可取！
GRANT SHOW VIEW
ON bookStore.book_view
TO ‘stuff_role’;
GRANT SHOW VIEW
ON bookStore.purchase_view
TO ‘stuff_role’;
GRANT SHOW VIEW
ON bookStore.refund_view
TO ‘stuff_role’;
GRANT SELECT, UPDATE
ON bookStore.book
TO ‘stuff_role’;
GRANT SELECT, INSERT
ON bookStore.purchase_information
TO ‘stuff_role’;
GRANT SELECT, INSERT
ON bookStore.refund_information
TO ‘stuff_role’;
GRANT UPDATE(penalty)
ON bookStore.refund_information
TO ‘stuff_role’;
GRANT SELECT(member_numeber,member_name,member_gender,member_category)
on bookStore.members
TO ‘stuff_role’;
③．創(chuàng)建角色’member_role’，具有的權(quán)限見安全性要求，代碼如下：
– 角色：會(huì)員
CREATE ROLE ‘member_role’;
GRANT SHOW VIEW
ON bookStore.book_view
TO ‘member_role’;
GRANT SELECT
ON bookStore.book
TO ‘member_role’;
- 數(shù)據(jù)庫的創(chuàng)建(代碼)
(ps:database需自己創(chuàng)建！)
– 創(chuàng)建會(huì)員表
CREATE TABLE members
(
member_number MEDIUMINT UNSIGNED PRIMARY KEY CHECK(member_number BETWEEN 100000 AND 999999),
member_name VARCHAR(12) NOT NULL,
member_gender CHAR(3) CHECK( member_gender = ‘男’ OR member_gender = ‘女’),
member_phone_number INT UNIQUE CHECK(member_phone_number BETWEEN 10000000000 AND 19999999999),
member_category NUMERIC(2,0) NOT NULL,
FOREIGN KEY(member_category) references member_category(category_number),
UNIQUE INDEX index_member_number(member_number) using btree
)ENGINE = INNODB;
DROP TABLE members;
– 創(chuàng)建圖書表
CREATE TABLE book
(
book_number mediumint UNSIGNED PRIMARY KEY CHECK(book_number BETWEEN 100000 AND 999999),
book_category VARCHAR(15) NOT NULL,
book_author VARCHAR(20) NOT NULL,
book_price NUMERIC(5,2) NOT NULL,
book_press VARCHAR(20) NOT NULL,
book_name VARCHAR(15) NOT NULL,
book_quantity NUMERIC(7,0) NOT NULL,
UNIQUE INDEX index_book_number(book_number) using btree
)ENGINE = INNODB;
– 創(chuàng)建會(huì)員類別表
CREATE TABLE member_category
(
category_number NUMERIC(2,0) PRIMARY KEY,
category_name VARCHAR(15) NOT NULL,
discount NUMERIC(1,1) CHECK(discount between 0 and 1),
UNIQUE INDEX index_category_number(category_number) using btree
)ENGINE = INNODB;
– 創(chuàng)建工作人員表
CREATE TABLE staff_member
(
staff_number MEDIUMINT UNSIGNED PRIMARY KEY CHECK(staff_number BETWEEN 100000 AND 999999),
staff_name VARCHAR(12) NOT NULL,
staff_gender CHAR(3) CHECK(staff_gender = ‘男’ or staff_gender = ‘女’),
staff_id_number CHAR(18) NOT NULL unique,
staff_post VARCHAR(15) NOT NULL,
staff_wage NUMERIC(5,2) NOT NULL,
administractor MEDIUMINT UNSIGNED NOT NULL CHECK (administractor BETWEEN 100000 AND 999999),
FOREIGN KEY(administractor) REFERENCES management(management_number),
UNIQUE INDEX index_staff_number(staff_number) using btree
)ENGINE = InnoDB;
– 創(chuàng)建管理人員表
CREATE TABLE management
(
management_number MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY CHECK(management_number BETWEEN 100000 AND 999999), #char(6) like’[0-9][0-9][0-9][0-9][0-9][0-9]’
management_name VARCHAR(15) NOT NULL
)ENGINE = INNODB;
DROP TABLE management;
– 創(chuàng)建購書記錄表
CREATE TABLE purchase_information
(
purchase_number MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY CHECK(purchase_number BETWEEN 100000 AND 999999),
member_number MEDIUMINT UNSIGNED NOT NULL CHECK(member_number BETWEEN 100000 AND 999999),
purchase_time TIMESTAMP,
member_name VARCHAR(12) NOT NULL,
member_phone_number INT NOT NULL CHECK(member_phone_number BETWEEN 10000000000 AND 19999999999),
category_number NUMERIC(2,0) NOT NULL,
book_number MEDIUMINT UNSIGNED NOT NULL CHECK(book_number BETWEEN 100000 AND 999999),
book_name VARCHAR(15) NOT NULL,
handlers MEDIUMINT UNSIGNED NOT NULL CHECK(handlerS BETWEEN 100000 AND 999999),
FOREIGN KEY(member_number) REFERENCES members(member_number),
FOREIGN KEY(category_number) REFERENCES member_category(category_number),
FOREIGN KEY(book_number) REFERENCES book(book_number),
FOREIGN KEY(handlers) REFERENCES management(management_number),
UNIQUE INDEX index_purchase_number(purchase_number) using btree
)ENGINE = MYISAM;
DROP TABLE purchase_information;
– 創(chuàng)建退書記錄表
CREATE TABLE refund_information
(
refund_number MEDIUMINT UNSIGNED PRIMARY KEY CHECK(refund_number BETWEEN 100000 AND 999999),
member_number MEDIUMINT UNSIGNED NOT NULL CHECK(member_number BETWEEN 100000 AND 999999),
refund_time TIMESTAMP NOT NULL,
member_name VARCHAR(12) NOT NULL,
member_phone_number INT NOT NULL CHECK(member_phone_number BETWEEN 10000000000 AND 19999999999),
penalty NUMERIC(3,2) NOT NULL,
book_number MEDIUMINT UNSIGNED NOT NULL CHECK(book_number BETWEEN 100000 AND 999999),
book_name VARCHAR(15) NOT NULL,
handlers MEDIUMINT UNSIGNED NOT NULL CHECK(handlers BETWEEN 100000 AND 999999),
FOREIGN KEY(member_number) REFERENCES members(member_number),
FOREIGN KEY(book_number) REFERENCES book(book_number),
FOREIGN KEY(handlers) REFERENCES management(management_number),
UNIQUE INDEX index_refund_number(refund_number) using btree
)ENGINE = MYISAM;
#數(shù)據(jù)的植入：
INSERT INTO book VALUES (200001, ‘科幻小說’, ‘馬爾克斯’, 53.00, ‘出版社A’, ‘百年孤獨(dú)’, 100);
INSERT INTO book VALUES (200002, ‘愛情小說’, ‘川端康成’, 58.00, ‘出版社A’, ‘雪國’, 200);
INSERT INTO book VALUES (200003, ‘長篇小說’, ‘三島由紀(jì)夫’, 73.00, ‘出版社B’, ‘金閣寺’, 201);
INSERT INTO book VALUES (200005, ‘長篇小說’, ‘帕穆克’, 59.00, ‘出版社C’, ‘我的名字叫紅’, 300);
INSERT INTO book VALUES (200006, ‘愛情小說’, ‘沃勒’, 50.00, ‘出版社A’, ‘廊橋遺夢’, 350);
INSERT INTO member_category VALUES (1, ‘初級(jí)會(huì)員’, 0.9);
INSERT INTO member_category VALUES (2, ‘中級(jí)會(huì)員’, 0.7);
INSERT INTO member_category VALUES (3, ‘高級(jí)會(huì)員’, 0.5);
INSERT INTO management VALUES (400001, ‘管理人員A’);
INSERT INTO management VALUES (400002, ‘管理人員B’);
INSERT INTO management VALUES (400003, ‘管理人員C’);
INSERT INTO staff_member VALUES (300001, ‘工作人員A’, ‘男’, ‘12345678901234567’, ‘柜臺(tái)’, 5000.00, 400001);
INSERT INTO staff_member VALUES (300002, ‘工作人員B’, ‘女’, ‘12345678901234566’, ‘清潔’, 7000.00, 400001);
INSERT INTO staff_member VALUES (300003, ‘工作人員C’, ‘女’, ‘12345678901234565’, ‘雜務(wù)’, 6000.00, 400002);
INSERT INTO members VALUES (100001, ‘會(huì)員A’, ‘男’, 11111111111, 1);
INSERT INTO members VALUES (100002, ‘會(huì)員B’, ‘女’, 11111111112, 2);
INSERT INTO members VALUES (100003, ‘會(huì)員C’, ‘女’, 11111111113, 3);
INSERT INTO purchase_information VALUES (500001, 100001, ‘2021-6-1 00:00:00’, ‘會(huì)員A’, 11111111111, 1, 200001, ‘百年孤獨(dú)’, 300001);
INSERT INTO purchase_information VALUES (500002, 100002, ‘2021-6-2 00:00:00’, ‘會(huì)員B’, 11111111112, 2, 200006, ‘廊橋遺夢’, 300002);
INSERT INTO purchase_information VALUES (500003, 100003, ‘2021-6-3 00:00:00’, ‘會(huì)員C’, 11111111113, 3, 200005, ‘我的名字叫紅’, 300001);文章來源：http://www.zghlxwxcb.cn/news/detail-444907.html
INSERT INTO refund_information VALUES (600002, 100002, ‘2021-6-12 00:00:00’, ‘會(huì)員B’, 11111111112, 50.00, 200003, ‘金閣寺’, 300002);
INSERT INTO refund_information VALUES (600003, 100002, ‘2021-6-15 00:00:00’, ‘會(huì)員B’, 11111111112, 70.00, 200001, ‘百年孤獨(dú)’, 300001);
INSERT INTO refund_information VALUES (600001, 100001, ‘2021-6-11 00:00:00’, ‘會(huì)員A’, 11111111111, 30.00, 200002, ‘雪國’, 300001);文章來源地址http://www.zghlxwxcb.cn/news/detail-444907.html
到了這里，關(guān)于數(shù)據(jù)庫課程設(shè)計(jì)------書店管理系統(tǒng)的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
