1..數據庫設計

（1）項目背景 ??

? ? 已知產品供應與訂購的業務關系如下圖：

? ? ?其中，客戶（client）的屬性有：客戶編碼（cno）、客戶名稱（cname）、所屬行業（trades）、客戶地址（caddress）、客戶聯系人（clinkman）、客戶電話（ctelephone） ；產品（product）的屬性有：產品編號（pno）、產品名稱（pname）、規格（spec）、單位（unit）、單價（unitprice）；廠家（factory）的屬性有：廠家編碼（fno）、廠家名稱（fname）、廠家地址（faddress）、銷售經理（salesmanager）、廠家電話（ftelephone）。

? ? ? 在客戶訂購(order)產品中，一個客戶可以訂購多種產品，一種產品也可以被多個客戶訂購，客戶訂購產品時須標明訂購的訂購數量（amount）和訂購日期（date）；在廠家供應（supply）產品中，一種產品可以由多個廠家供應，一個廠家也可供應多種產品，廠家供應產品時須標明供應日期（sdate ）和供應數量（samount）。

（2）數據庫結構設計

概念設計：根據項目背景分析實體和實體之間的關系，畫出實體關系圖。

邏輯設計：

●?將實體關系轉化為實體關系模型并要求滿足3NF。?

●?由關系模型確定數據庫的表結構，包括必須的完整性約束及其表之間的聯系。

客戶表

屬性	類型	是否為主鍵	備注
cno	CHAR(10)	是	客戶編碼?PRIMARY ?KEY
cname	CHAR（10）	否	客戶名稱
trades	CHAR（10）	否	所屬行業
caddress	CHAR（10）	否	客戶地址
clinkman	CHAR（10）	否	客戶聯系人
ctelephone	CHAR（15）	否	客戶電話

產品表

屬性	類型	是否為主鍵	備注
pno	CHAR(10)	是	產品編號 PRIMARY??KEY
pname	VARCHAR(20)	否	產品名稱
spec	VARCHAR(20)	否	規格
unit	VARCHAR(20)	否	單位
unitprice	INT(10)	否	單價

廠家表

屬性	類型	是否為主鍵	備注
fno	CHAR(10)	是	廠家編碼 PRIMARY??KEY
fname	VARCHAR(20)	否	廠家名稱
faddress	VARCHAR(20)	否	廠家地址
salesmanager	VARCHAR(20)	否	銷售經理
ftelephone	CHAR(20)	否	廠家電話

訂購表

屬性	類型		是否為主鍵	備注
cno	CHAR(20)		是	客戶編碼
pno	CHAR(20)		是	產品編號
amount	INT		否	訂購數量
dates	DATATIME		否	訂購日期
(cno，pno，dates) ?		PRIMARY??KEY
FOREIGN ?KEY(cno)		REFERENCES ?clients(cno)
FOREIGN ?KEY(pno)		REFERENCES ?product(pno)

供應表

屬性	類型		是否為主鍵	備注
pno	INT(20)		是	產品編號
fno	INT(20)		是	廠家編碼
sdate	CHAR		否	供應日期
samount	INT		否	供應數量
(pno,fno，sdate)		PRIMARY??KEY
FOREIGN ?KEY(pno)		REFERENCES ?product(Pno)
FOREIGN ?KEY(fno)		REFERENCES ?Factory(Fno)

3.數據庫實現與應用

（1）創建名為“TEST_學號”的數據庫；

mysql>CREATE DATABASE test_2020111250;

創建數據庫的所有數據表；

SQL語句：

CREATE TABLE clientS(cno CHAR(10) primary key,cname CHAR(10),trades CHAR(10),caddress ?CHAR(10),clinkman ?CHAR(10),ctelephone ?CHAR(15));CREATE TABLE product(pno CHAR(10) primary key,pname VARCHAR(20),spec VARCHAR(20),unit VARCHAR(20),unitprice int(10));CREATE TABLE factory(fno CHAR(10)primary key,fname VARCHAR(20),faddress VARCHAR(20),salesmanager VARCHAR(20),ftelephone CHAR(20));CREATE TABLE orderS(cno CHAR(10),pno CHAR(20) ,amount INT,dateS DATETIME,primary key(cno,pno,DATES),foreign key (cno) references clientS(cno),foreign key (pno) references product(pno));CREATE TABLE supply(fno CHAR(10),pno CHAR(20) ,sdate DATETIME,samount ?INT,primary key(pno,fno,sdate),Foreign key (pno) references product(pno),Foreign key (fno) references factory(fno));

（3）編輯數據：給每個表輸入至少10個記錄（均為模擬數據）；

???為調試后續編程，應要求每個客戶訂購多種產品，每種產品有多個廠家供應。客戶、產品有5個記錄即可，但訂購、供應和廠家要有更多個記錄。另外，要求同一種產品不同廠家的單價不相同。

SQL語句：(每個表插入數據)客戶數據

insert?into clients?values('c01','陳東','家電公司','東莞','謝總',12345659112);insert into clients?values('c02','陳成','電子廠','深圳','陳總',12895659112);insert into clients?values('c03','馬白云','人工智能','上海','馬總',12785645212);insert into clients?values('c04','王小強','化工廠','廣州','王總',12325059112);insert into clients?values('c05','沈夏','外賣行業','湛江','沈總',12565625012);insert into clients?values('c06','吳東','電腦公司','上海','吳總',12345659112);

SQL語句：(每個表插入數據)產品數據

insert into product values('p01','電腦','臺裝','臺',800);insert into product values('p02','冰箱','臺裝','臺',2300);insert into product values('p03','字典','大盒裝','本',300);insert into product values('p04','電動車','輛裝','輛',3000);insert into product values('p05','感冒藥','100大盒裝','盒',3000);INSERT INTO product VALUES('p06','電腦','臺裝','臺',900);

?

SQL語句：(每個表插入數據)廠家數據

insert into factory values('f1','電子廠','廣東','謝小梅','13654250563');insert into factory values('f2','手機廠','湖北','劉黑仔','15425023653');insert into factory values('f3','東廠','廣西','馬仔','15648972505');insert into factory values('f4','北廠','海南','馬海','15642232508');insert into factory values('f5','西北廠','吉林','李菲','15642325045');insert into factory values('f6','東北廠','湖南','李四','10232504231');insert into factory values('f7','東南廠','河北','王六','13564250641');insert into factory values('f8','西北廠','江西','張三','10232509874');insert into factory values('f9','華南廠','新疆','小明','12342505200');insert into factory values('f10','臺北廠','山西','小張','12325056456');insert into factory VALUES('f11','小廠','安徽','姚小桃',13654987563);

?

SQL語句：(每個表插入數據)訂購數據

insert into orders values('c01','p01',11,'2022-1-2');insert into orders values('c02','p02',22,'2022-2-3');insert into orders values('c03','p03',33,'2022-3-4');insert into orders values('c04','p04',44,'2022-4-5');insert into orders values('c05','p05',55,'2022-5-7');insert into orders values('c05','p05',66,'2022-5-9');insert into orders values('c06','p06',99,'2022-5-10');

?

SQL語句：(每個表插入數據)訂購數據

insert into supply values('f1','p01','2022-1-1','14');insert into supply values('f2','p02','2022-2-2','25');insert into supply values('f4','p03','2022-3-4','47');insert into supply values('f4','p04','2022-4-4','47');insert into supply values('f5','p05','2022-5-5','58');insert into supply values('f6','p03','2022-1-5','69');insert into supply values('f7','p04','2022-2-4','71');insert into supply values('f8','p05','2022-2-8','82');insert into supply values('f11','p06','2022-2-9','90');insert into supply values('f9','p01','2022-4-1','93');insert into supply values('f10','p02','2022-5-2','91');

?

（4）設計并實現如下對象或應用

● ?創建數據庫表之間的關系圖；

● ?在客戶表上創建關于“所屬行業”列的一個升序索引；

CREATE INDEX ik_trades ON CLIENTs(trades ASC);

● ?創建一個包含“所屬行業”、“客戶名稱”、訂購的“產品名稱”和“訂購數量”等列信息的視圖；

CREATE VIEW c(所屬行業,客戶名稱,產品名稱,訂購數量)ASSELECT trades,cname,pname,amountFROM orderS,clientS,productWHERE clientS.cno=orderS.cno AND product.pno=orderS.pno;SHOW CREATE VIEW c;

?

● ?編寫SQL語句插入一條客戶訂購產品記錄，內容自定。

● ?編寫SQL語句修改一條廠家供應產品記錄，內容自定。

UPDATE supplySET sdate='2023-12-3'WHERE fno='f5';

?

● ?編寫查詢程序，并將程序存為腳本文件。

① 查詢各客戶訂購的某同一種產品的總數量和平均單價。

要求首先按客戶分組，然后每一組內再按產品分組。

SELECT cname AS 客戶名稱, pname AS 產品名稱,SUM(amount) AS 總數量,AVG(unitprice) AS 平均單價FROM clients,orders,productWHERE clients.cno=orders.cno AND product.pno=orders.pnoGROUP BY clients.cno,pname;

?

② 查詢各種產品的所有廠家的名稱和單價，并按單價從低到高的順序排列。

SELECT pname AS 產品名稱,fname AS 廠家名稱, unitprice AS 單價FROM factory,product,supplyWHERE factory.fno=supply.fno AND product.pno=supply.pnoORDER BY unitprice ASC;

?

●創建一個存儲過程，實現對指定客戶訂購某種產品總數量的統計（其中，指定客戶和特定產品以存儲過程的輸入參數設定），并自行檢查執行該存儲過程的正確性。

DELIMITER//CREATE PROCEDURE t_amount(IN cno1 CHAR(10),IN pno1 CHAR(10))BEGINSELECT ?cno,product.pname,SUM(amount) AS 總數量FROM `orders`JOIN `product` ON orders.pno=product.pnoWHERE product.pname=pno1 ?AND ?orders.cno=cno1;END;//DELIMITER;CALL t_amount('c01','電腦');

?

●創建一個存放廠家供應產品數量的統計表（包括廠家編碼、廠家名稱、產品編碼、產品名稱和供應數量等列信息）；然后創建一個觸發器，使得廠家修改產品供應數量時，與統計表的數據保持一致。

CREATE TABLE 統計表ASSELECT factory.fno,fname ,product.pno,pname,samountFROM supply,factory,productWHERE factory.fno=supply.fno AND product.pno=supply.pno;觸發器DELIMITER//create trigger 統計表_update AFTER update on supplyfor each rowbeginupdate 統計表 set samount=new.samountwhere fno=OLD.fno AND PNO=OLD.PNO;END;//delimiter;

供應表更新之前

統計表?

更新之后UPDATE supply

SET samount =45 WHERE fno='f2';Supply

?

統計表

?

4. 數據庫管理與維護

●通過SQL語句添加數據庫用戶user01，并授予該用戶對訂購數據表的insert、select、update和delete權限；

CREATE USER 'user01'@'localhost' IDENTIFIED BY '123456';GRANT INSERT ON `order` TO 'user01'@'localhost';GRANT SELECT ON `order` TO 'user01'@'localhost';GRANT UPDATE ON `order` TO 'user01'@'localhost';GRANT DELETE ON `order` TO 'user01'@'localhost';

●通過SQL語句實現對數據庫的完全備份和還原。

備份：

mysqldump -u root -p ?test_2020111250 >F:\xie\2020111250.sql

??

還原：

?

mysql -u root -p test_2020111250 <F:\xwq\xw.sql

總結

以上是生活随笔為你收集整理的数据库课程设计（实训）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。