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(所属行业,客户名称,产品名称,订购数量)
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
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
Original: https://blog.csdn.net/m0_63146092/article/details/122637879
Author: 心之叶
Title: 数据库课程设计(实训)
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/723635/
转载文章受原作者版权保护。转载请注明原作者出处!