一、实验一：图书销售管理系统数据库SQL应用编程
二、实验时间：2023.4.21
三、实验目的
结合图书销售管理系统数据库开发项目案例，开展数据库SQL应用编程实践，培养数据库SQL操作访问、存储过程与触发器处理的数据库编程能力。
四、实验原理
在PostgreSQL数据库中，存储过程、触发器和游标都可使用PL／pgSQL编程实现的数据处理。按照PL／pgSQL提供的语句和程序结构，实现数据库后端功能编程处理。可以使用数据库开发工具pgAdmin或psql命令行工具执行PL／pgSQL程序，实现数据库后端功能处理。
五、实验内容
针对图书销售管理系统基本需求，开发实现图书销售管理系统数据库，具体实验内容如下:
1.基于图书销售管理系统基本数据需求，给出图书销售管理系统数据库设计方案。
2.在数据库服务器中，执行SQL创建图书销售管理系统数据库BookSale。
3.在数据库BookSale中，执行SQL创建数据库表、视图、索引等对象。
4.在数据库BookSale中，执行SQL进行数据增、删、查、改访问操作。
5.在数据库BookSale中，采用PL/pgSQL语言编写存储过程函数Pro_CurrentSale，实现当日图书销售量及销售金额汇总统计。
6.在数据库BookSale中，采用PL/pgSQL语言编写过程语句块，实现对存储过程函数Pro_CurrentSale的调用，并输出统计结果。
7.在数据库BookSale中，采用PL/pgSQL语言编写编写图书销售表Insert触发器Tri_InsertSale，实现图书库存数据同步修改处理。
8.在数据库BookSale中，对图书销售表Insert触发器Tri_InsertSale程序进行功能验证。
在实验计算机上，利用pgAdmin4数据库管理工具及SQL、PL/pgSQL语言，完成图书销售管理系统数据库应用编程操作，同时记录实验过程的步骤、操作、运行结果界面等数据，为撰写实验报告提供素材。
六、实验设备及环境
“数据库原理及应用”实验所涉及的机房硬件设备为pc计算机、服务器以及网络环境，pc计算机与服务器在同一局域网络。
操作系统： Windows10 / Windows 11
管理工具： pgAdmin4
DBMS系统： PostgreSQL 15.1
七、实验步骤
（1）基于图书销售管理系统基本数据需求，抽取实体，确定实体属性及标识符，确定实体间的联系，之后创建概念数据模型，之后将概念数据模型转换成逻辑数据模型，并进行相应的规范化完善，之后将实体转换成关系表，并将实体间的联系进行转换生成物理数据模型。
（2）在数据库服务器中，执行SQL创建图书销售管理系统数据库BookSale。
（3）在数据库BookSale中，执行SQL创建三个表：书籍表（Book），该表存储图书信息，包含书名、作者、ISBN号、定价、图书库存这几个字段；销售表（Sale），该表存储销售信息，包含销售流水号、销售时间、销售数量、销售金额、身份证号、ISBN号这几个字段；顾客表（Customer），该表存储客户信息，包含身份证号、姓名、电话、地址这几个字段。之后根据需求创建相应的视图、索引等对象。
（4）在数据库BookSale中，执行SQL进行数据增、删、查、改访问操作。
（5）在数据库BookSale中，采用PL/pgSQL语言编写存储过程函数Pro_CurrentSale，实现当日图书销售量及销售金额汇总统计。之后实现对存储过程函数Pro_CurrentSale的调用，并输出统计结果。
（6）在数据库BookSale中，采用PL/pgSQL语言编写编写图书销售表Insert触发器Tri_InsertSale，实现图书库存数据同步修改处理。之后先插入数据到sale表中，再对图书销售表Insert触发器Tri_InsertSale程序进行功能验证。
八、实验数据及结果分析
（1）基于图书销售管理系统基本数据需求，给出图书销售管理系统数据库的概念数据模型、逻辑数据模型、物理数据模型。
图书销售管理系统概念数据模型如下图1-1所示。
图1-1 图书销售管理系统概念数据模型
图书销售管理系统逻辑数据模型如下图1-2所示。
图1-2 图书销售管理系统逻辑数据模型
图书销售管理系统物理数据模型如下图1-3所示。
图1-3 图书销售管理系统物理数据模型
- 在数据库服务器中，执行SQL创建图书销售管理系统数据库BookSale，代码如下所示。
代码1 创建图书销售管理数据库代码
| CREATE DATABASE BookSale; |
代码运行结果如图1-4所示。
图1-4 创建图书销售管理数据库
- 在数据库BookSale中，执行SQL创建三个表：书籍表（Book），该表存储图书信息，包含书名、作者、ISBN号、定价、图书库存这几个字段；销售表（Sale），该表存储销售信息，包含销售流水号、销售时间、销售数量、销售金额、身份证号、ISBN号这几个字段；顾客表（Customer），该表存储客户信息，包含身份证号、姓名、电话、地址这几个字段。之后根据需求创建相应的视图、索引等对象。代码如下所示。
代码2 创建三个关系表以及相应索引代码
| /*==============================================================*/ /* Table: Book */ /*==============================================================*/ create table Book ( isbn CHAR(17) not null, bookname VARCHAR(50) not null, bookauthor VARCHAR(50) not null, fixprice MONEY not null, inventory INT4 not null, constraint PK_BOOK primary key (isbn) ); /*==============================================================*/ /* Index: Book_PK */ /*==============================================================*/ create unique index Book_PK on Book ( isbn ); /*==============================================================*/ /* Table: Customer */ /*==============================================================*/ create table Customer ( IDnumber CHAR(18) not null, name VARCHAR(50) not null, telephone CHAR(11) not null, address VARCHAR(50) not null, constraint PK_CUSTOMER primary key (IDnumber) ); /*==============================================================*/ /* Index: Customer_PK */ /*==============================================================*/ create unique index Customer_PK on Customer ( IDnumber ); /*==============================================================*/ /* Table: Sale */ /*==============================================================*/ create table Sale ( serialnumber SERIAL not null, isbn CHAR(17) not null, IDnumber CHAR(18) not null, saletime DATE not null, salenumber INT4 not null, salevalue MONEY not null, constraint PK_SALE primary key (serialnumber) ); /*==============================================================*/ /* Index: Sale_PK */ /*==============================================================*/ create unique index Sale_PK on Sale ( serialnumber ); /*==============================================================*/ /* Index: saled_FK */ /*==============================================================*/ create index saled_FK on Sale ( isbn ); /*==============================================================*/ /* Index: buy_FK */ /*==============================================================*/ create index buy_FK on Sale ( IDnumber ); alter table Sale add constraint FK_SALE_BUY_CUSTOMER foreign key (IDnumber) references Customer (IDnumber) on delete restrict on update restrict; alter table Sale add constraint FK_SALE_SALED_BOOK foreign key (isbn) references Book (isbn) on delete restrict on update restrict; |
创建图书销售管理系统数据库表对象，如图1-5所示。
图1-5 创建图书销售管理系统数据库表对象
在表sale建立IDnumber和isbn两个外键，如图1-6所示。
图1-6 设置外键
为图书销售管理系统数据库的三个表分别创建相应的索引对象，如图1-7所示。
图1-7 创建索引
- 在数据库BookSale中，执行SQL进行数据增、删、查、改访问操作。
向数据库的三个关系表执行插入数据的SQL语句，代码如下所示。
代码3 插入数据SQL代码
| --书籍表插入数据 INSERT INTO Book (bookname,bookauthor,isbn,fixprice,inventory) VALUES('鲁滨逊漂流记','丹尼尔·笛福','978-7-532-74836-9',39,20), ('西游记','吴承恩','978-7-532-74834-5',48,15), ('红楼梦','曹雪芹','978-7-532-74835-2',59,10), ('水浒传','施耐庵','978-7-532-74837-6',42,25); --用户表插入数据 INSERT INTO Customer (idnumber,name,telephone,address) VALUES('310101199001010001','孔德阳','13812345678','上海市浦东新区张江镇'), ('310101199001010002','郭子尧','13912345678','上海市徐汇区漕河泾'), ('310101199001010003','王婷','13612345678','上海市长宁区虹桥'), ('310101199001010004','赵树','13712345678','上海市闵行区七宝'); --销售表插入数据 INSERT INTO Sale(Serialnumber,saletime,salevalue,salenumber,idnumber,isbn) VALUES('1','2023-04-20 09:30:00',2,78,'310101199001010001','978-7-532-74836-9'), ('2','2023-01-20 10:15:00',1,48,'310101199001010002','978-7-532-74834-5'), ('3','2023-05-20 11:20:00',3,177,'310101199001010003','978-7-532-74835-2'), ('5','2023-04-10 14:30:00',4,168,'310101199001010004','978-7-532-74837-6'); |
插入结果如下图1-8所示。
图1-8 插入数据
执行查询book表数据的SQL语句，代码如下所示。
代码4 查询书籍表信息SQL代码
| --查询书籍表信息 SELECT * FROM Book; |
执行结果如下图1-9所示。
图1-9 查询book表数据信息
删除sale表中顾客名为“孔德阳”的购买记录数据，代码如下所示。
代码5 删除顾客孔德阳购买书籍信息代码
| --删除孔德阳(身份证号为310101199001010001)的购买书籍信息 DELETE FROM sale WHERE idnumber = ‘310101199001010001’; |
删除结果如下图1-10所示。
图1-10 删除数据
删除数据后，查询sale来验证删除是否成功，代码如下所示。
代码6 查询销售表信息代码
| --删除孔德阳(身份证号为310101199001010001)的购买书籍信息后，查询销售表 --检查是否删除成功 SELECT * FROM sale; |
查询如下图1-11所示。
图1-11 查询sale表
执行修改数据的SQL语句，代码如下所示。
代码7 修改顾客郭子尧地址信息代码
| --修改郭子尧(身份证号为310101199001010002)的地址信息为河北省石家庄市 UPDATE customer SET address = ‘河北省石家庄市’ WHERE idnumber = ‘310101199001010002’; |
如下图1-12所示。
图1-12 修改customer表数据
修改后查询customer表，代码如下所示。
代码8 查询顾客郭子尧地址信息代码
| --修改郭子尧(身份证号为310101199001010002)的地址信息为河北省石家庄市后 --查询郭子尧的住址信息，检查是否更新成功 SELECT address FROM customer WHERE idnumber = ‘310101199001010002’; |
查询如下图1-13所示。
图1-13 查询customer表数据
- 在数据库BookSale中，采用PL/pgSQL语言编写存储过程函数Pro_CurrentSale，实现当日图书销售量及销售金额汇总统计。之后实现对存储过程函数Pro_CurrentSale的调用，并输出统计结果。
创建存储过程函数Pro_CurrentSale，代码如下所示。
代码9 创建存储过程函数Pro_CurrentSale代码
| --采用PL/pgSQL语言编写存储过程函数Pro_CurrentSale，实现当日图书销售量及销售金额汇总统计 CREATE OR REPLACE FUNCTION Pro_CurrentSale (OUT allmount Integer, OUT allmoney money) AS $$ BEGIN SELECT SUM(salenumber) INTO allmount FROM sale WHERE saletime = CURRENT_DATE; SELECT SUM(salevalue) INTO allmoney FROM sale WHERE saletime = CURRENT_DATE; END; $$ LANGUAGE plpgsql;; |
创建结果如下图1-14所示。
图1-14 创建存储过程函数Pro_CurrentSale
调用该存储过程函数，代码如下所示。
代码10 调用存储过程函数代码
| --采用PL/pgSQL语言编写过程语句块，实现对存储过程函数Pro_CurrentSale的调用，并输出统计结果 SELECT * FROM Pro_CurrentSale(); |
调用结果如图1-15所示。
图1-15 调用存储过程函数
- 在数据库BookSale中，采用PL/pgSQL语言编写编写图书销售表Insert触发器Tri_InsertSale，实现图书库存数据同步修改处理。之后先插入数据到sale表中，再对图书销售表Insert触发器Tri_InsertSale程序进行功能验证。
编写触发器函数Tri_func，代码如下所示。
代码11 创建触发器函数Tri_func代码
| --采用PL/pgSQL语言编写编写图书销售表Insert触发器函数Tri_func CREATE OR REPLACE FUNCTION Tri_func RETURNS TRIGGER AS $$ BEGIN UPDATE book SET inventory = inventory - new.salenumber WHERE book.isbn = new.isbn; RETURN NEW; END; $$ LANGUAGE plpgsql; |
如下图1-16所示。
图1-16 编写触发器函数Tri_func
创建触发器Tri_InsertSale，代码如下所示。
代码12 创建触发器代码
| --采用PL/pgSQL语言编写编写图书销售表Insert触发器Tri_InsertSale，实现图书库存数据同步修改处理。 CREATE TRIGGER Tri_InsertSale AFTER INSERT ON sale FOR EACH ROW EXECUTE PROCEDURE Tri_func(); |
创建结果如图1-17所示。
图1-17 创建触发器
插入数据前，查询book表查看书籍库存，代码如下所示。
代码13 查询各书籍库存代码
| --对读书销售表Insert触发器Tri_InsertSale程序进行功能验证 --插入数据前查询book表，查看各书籍库存 SELECT bookname,inventory FROM book; |
如图1-18所示。
图1-18 插入数据前查询各书籍库存
向sale表中插入数据，代码如下所示。
代码14 向sale表中插入数据代码
| --对读书销售表Insert触发器Tri_InsertSale程序进行功能验证 --插入数据到sale表 INSERT INTO sale VALUES('7','978-7-532-74836-9','310101199001010003','2023-04-21',1,39), ('8','978-7-532-74834-5','310101199001010004','2023-04-21',2,96), ('9','978-7-532-74837-6','310101199001010001','2023-04-23',3,126); |
如图1-19所示。
图1-19 向sale表中插入数据
插入数据后，再次查询book表中各书籍的库存来验证触发器功能，代码如下所示。
代码15 再次查询各书籍库存代码
| --对读书销售表Insert触发器Tri_InsertSale程序进行功能验证 --插入数据后查询book表各书籍的库存 SELECT bookname,inventory FROM book; |
如图1-20所示。
图1-20 查询book表验证触发器功能
从book表前后变化以及插入相应的数据来看，触发器可以实现图书库存数据同步修改处理。
九、总结及心得体会
通过本次实验，进一步提升了我的数据库SQL操作访问、存储过程与触发器处理的数据库编程能力。加深了我对数据库存储过程和触发器功能的认识。同时也提高了我的数据库设计能力，熟悉了如何使用PowerDesigner进行数据库的概念数据模型、逻辑数据模型、物理数据模型的设计与转换。并且也进一步加深了我对PL／pgSQL语言的理解以及如何使用PL／pgSQL来实现存储过程、触发器的创建
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
