数据库课程设计（实训）

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;

1. 创建数据库的所有数据表；

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(所属行业,客户名称,产品名称,订购数量)

AS

SELECT trades,cname,pname,amount

FROM orderS,clientS,product

WHERE clientS.cno=orderS.cno AND product.pno=orderS.pno;



SHOW CREATE VIEW c;

 

●  编写SQL语句插入一条客户订购产品记录，内容自定。

●  编写SQL语句修改一条厂家供应产品记录，内容自定。

UPDATE supply

SET sdate='2023-12-3'

WHERE fno='f5';

 

●  编写查询程序，并将程序存为脚本文件。

① 查询各客户订购的某同一种产品的总数量和平均单价。

要求首先按客户分组，然后每一组内再按产品分组。

SELECT cname AS 客户名称, pname AS 产品名称,SUM(amount) AS 总数量,AVG(unitprice) AS 平均单价

FROM clients,orders,product

WHERE clients.cno=orders.cno AND product.pno=orders.pno

GROUP BY clients.cno,pname;

 

② 查询各种产品的所有厂家的名称和单价，并按单价从低到高的顺序排列。

SELECT pname AS 产品名称,fname AS 厂家名称, unitprice AS 单价

FROM factory,product,supply

WHERE factory.fno=supply.fno AND product.pno=supply.pno

ORDER BY unitprice ASC;

 

●创建一个存储过程，实现对指定客户订购某种产品总数量的统计（其中，指定客户和特定产品以存储过程的输入参数设定），并自行检查执行该存储过程的正确性。

DELIMITER//

CREATE PROCEDURE t_amount(IN cno1 CHAR(10),IN pno1 CHAR(10))

BEGIN

SELECT  cno,product.pname,SUM(amount) AS 总数量

FROM `orders`JOIN `product` ON orders.pno=product.pno

 WHERE product.pname=pno1  AND  orders.cno=cno1;

END;

//

DELIMITER;

CALL t_amount('c01','电脑');

 

●创建一个存放厂家供应产品数量的统计表（包括厂家编码、厂家名称、产品编码、产品名称和供应数量等列信息）；然后创建一个触发器，使得厂家修改产品供应数量时，与统计表的数据保持一致。

CREATE TABLE 统计表

AS

SELECT factory.fno,fname ,product.pno,pname,samount

FROM supply,factory,product

WHERE factory.fno=supply.fno AND product.pno=supply.pno;

触发器

DELIMITER//

create trigger 统计表_update AFTER update on supply

for each row

begin

update 统计表 set samount=new.samount

where 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