数据库课程设计（实训）

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语句：

1. CREATE TABLE clientS
3. (
5. cno CHAR(10) primary key,
7. cname CHAR(10),
9. trades CHAR(10),
11. caddress  CHAR(10),
13. clinkman  CHAR(10),
15. ctelephone  CHAR(15)
17. );
19. CREATE TABLE product
21. (
23. pno CHAR(10) primary key,
25. pname VARCHAR(20),
27. spec VARCHAR(20),
29. unit VARCHAR(20),
31. unitprice int(10)
33. );
35. CREATE TABLE factory
37. (
39. fno CHAR(10)primary key,
41. fname VARCHAR(20),
43. faddress VARCHAR(20),
45. salesmanager VARCHAR(20),
47. ftelephone CHAR(20)
49. );
51. CREATE TABLE orderS
53. (
55. cno CHAR(10),
57. pno CHAR(20) ,
59. amount INT,
61. dateS DATETIME,
63. primary key(cno,pno,DATES),
65. foreign key (cno) references clientS(cno),
67. foreign key (pno) references product(pno)
69. );
71. CREATE TABLE supply
73. (
75. fno CHAR(10),
77. pno CHAR(20) ,
79. sdate DATETIME,
81. samount  INT,
83. primary key(pno,fno,sdate),
85. Foreign key (pno) references product(pno),
87. Foreign key (fno) references factory(fno)
89. );

复制代码

（3）编辑数据：给每个表输入至少10个记录（均为模拟数据）；

   为调试后续编程，应要求每个客户订购多种产品，每种产品有多个厂家供应。客户、产品有5个记录即可，但订购、供应和厂家要有更多个记录。另外，要求同一种产品不同厂家的单价不相同。

SQL语句：(每个表插入数据)客户数据

1. insert into clients values('c01','陈东','家电公司','东莞','谢总',12345659112);
3. insert into clients values('c02','陈成','电子厂','深圳','陈总',12895659112);
5. insert into clients values('c03','马白云','人工智能','上海','马总',12785645212);
7. insert into clients values('c04','王小强','化工厂','广州','王总',12325059112);
9. insert into clients values('c05','沈夏','外卖行业','湛江','沈总',12565625012);
11. insert into clients values('c06','吴东','电脑公司','上海','吴总',12345659112);

复制代码

SQL语句：(每个表插入数据)产品数据

1. insert into product values('p01','电脑','台装','台',800);
3. insert into product values('p02','冰箱','台装','台',2300);
5. insert into product values('p03','字典','大盒装','本',300);
7. insert into product values('p04','电动车','辆装','辆',3000);
9. insert into product values('p05','感冒药','100大盒装','盒',3000);
11. INSERT INTO product VALUES('p06','电脑','台装','台',900);

复制代码

 

SQL语句：(每个表插入数据)厂家数据

1. insert into factory values('f1','电子厂','广东','谢小梅','13654250563');
3. insert into factory values('f2','手机厂','湖北','刘黑仔','15425023653');
5. insert into factory values('f3','东厂','广西','马仔','15648972505');
7. insert into factory values('f4','北厂','海南','马海','15642232508');
9. insert into factory values('f5','西北厂','吉林','李菲','15642325045');
11. insert into factory values('f6','东北厂','湖南','李四','10232504231');
13. insert into factory values('f7','东南厂','河北','王六','13564250641');
15. insert into factory values('f8','西北厂','江西','张三','10232509874');
17. insert into factory values('f9','华南厂','新疆','小明','12342505200');
19. insert into factory values('f10','台北厂','山西','小张','12325056456');
21. insert into factory VALUES('f11','小厂','安徽','姚小桃',13654987563);

复制代码

 

SQL语句：(每个表插入数据)订购数据

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

复制代码

 

SQL语句：(每个表插入数据)订购数据

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

复制代码

 

（4）设计并实现如下对象或应用

●  创建数据库表之间的关系图；

●  在客户表上创建关于“所属行业”列的一个升序索引；

1. CREATE INDEX ik_trades ON CLIENTs(trades ASC);

复制代码

●  创建一个包含“所属行业”、“客户名称”、订购的“产品名称”和“订购数量”等列信息的视图；

1. CREATE VIEW c(所属行业,客户名称,产品名称,订购数量)
3. AS
5. SELECT trades,cname,pname,amount
7. FROM orderS,clientS,product
9. WHERE clientS.cno=orderS.cno AND product.pno=orderS.pno;
13. SHOW CREATE VIEW c;

复制代码

 

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

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

1. UPDATE supply
3. SET sdate='2023-12-3'
5. WHERE fno='f5';

复制代码

 

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

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

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

1. SELECT cname AS 客户名称, pname AS 产品名称,SUM(amount) AS 总数量,AVG(unitprice) AS 平均单价
3. FROM clients,orders,product
5. WHERE clients.cno=orders.cno AND product.pno=orders.pno
7. GROUP BY clients.cno,pname;

复制代码

 

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

1. SELECT pname AS 产品名称,fname AS 厂家名称, unitprice AS 单价
3. FROM factory,product,supply
5. WHERE factory.fno=supply.fno AND product.pno=supply.pno
7. ORDER BY unitprice ASC;

复制代码

 

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

1. DELIMITER//
3. CREATE PROCEDURE t_amount(IN cno1 CHAR(10),IN pno1 CHAR(10))
5. BEGIN
7. SELECT  cno,product.pname,SUM(amount) AS 总数量
9. FROM `orders`JOIN `product` ON orders.pno=product.pno
11.  WHERE product.pname=pno1  AND  orders.cno=cno1;
13. END;
15. //
17. DELIMITER;
19. CALL t_amount('c01','电脑');

复制代码

 

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

1. CREATE TABLE 统计表
3. AS
5. SELECT factory.fno,fname ,product.pno,pname,samount
7. FROM supply,factory,product
9. WHERE factory.fno=supply.fno AND product.pno=supply.pno;
11. 触发器
13. DELIMITER//
15. create trigger 统计表_update AFTER update on supply
17. for each row
19. begin
21. update 统计表 set samount=new.samount
23. where fno=OLD.fno AND PNO=OLD.PNO;
25. END;//
27. delimiter;

复制代码

供应表更新之前

统计表 

更新之后UPDATE supply

1. SET samount =45 WHERE fno='f2';
3. Supply

复制代码

 

统计表

 

4. 数据库管理与维护

●通过SQL语句添加数据库用户user01，并授予该用户对订购数据表的insert、select、update和delete权限；

1. CREATE USER 'user01'@'localhost' IDENTIFIED BY '123456';
3. GRANT INSERT ON `order` TO 'user01'@'localhost';
5. GRANT SELECT ON `order` TO 'user01'@'localhost';
7. GRANT UPDATE ON `order` TO 'user01'@'localhost';
9. GRANT DELETE ON `order` TO 'user01'@'localhost';

复制代码

●通过SQL语句实现对数据库的完全备份和还原。

备份：

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

复制代码

  

还原：

 

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

复制代码