结合图书销售管理系统数据库开发项目案例,开展数据库SQL应用编程实践,培养数据库SQL操作访问、存储过程与触发器处理的数据库编程能力。
首先对SEO靠我图书销售管理系统进行数据需求分析,定义组成系统数据结构的实体、实体属性以及实体之间的关系。
采用实体关系图(E-R模型图)方法来展示图书销售管理系统的概念数据模型与逻辑数据模型。
利用PowerDesigSEO靠我ner数据库软件系统进行系统物理数据模型设计,对设计的图书销售管理系统数据库模型进行检验与完善,并对系统进行数据库设计,给出设计方案。
基于数据库设计方案,通过SQL编程执行来完成对数据库的创建与数据访SEO靠我问操作以及相应的后端编程操作。
在本实验中,使用SQL语句完成对数据库、关系表、索引、视图、触发器、存储过程的创建,
并编写SQL语句对数据库表进行数据的增删查改操作,以及利用视图、存储过程、触发器实现业SEO靠我务数据处理。
视图是一种从基础数据库表中获取数据所组成的虚拟表,在数据库中只需存储视图结构定义,而不存储视图所包含的数据,
使用视图可以简化复杂的SQL查询,提SEO靠我高数据访问安全性,提供一定的数据逻辑独立性,可以集中展示用户所感兴趣的特定数据。
创建视图:Create view <视图名>[(列名 1), (列名 2), …] as <SELECT 查询>; SEO靠我 查看视图:Select * from <视图名>; 删除视图:Drop view <视图名>;索引是一种针对表中指定列的值进行排序的数据结构,使用它可以加快表中数据的查SEO靠我询。
在数据表中建立类似于图书目录的索引结构,并将索引列的值及索引指针操作保存在索引结构中。
此后在对数据表进行数据查询时,首先在索引结构中查找符合条件的索引指针值, 然后再根据索引指针快速找到对应的数据SEO靠我记录,这样可实现快速检索元组数据的目的。
创建索引:Create unique index <索引名> on <表名><(列名[,…])>; 修改索引:Alter index <索引名>SEO靠我 rename to <新索引名>; 删除索引:Drop index <索引名>;存储过程是由一组能完成特定功能的SQL语句构成,它把经常会反复使用的SQL语句逻辑块封装起来,SEO靠我经编译后,存储在数据库服务端,
当再次调用时,不需再次编译,用户连接到数据库服务端时,用户通过指定存储过程的名称并给出参数,数据库就可以找到相应的存储过程并予以调用。
创建存储过程: CrSEO靠我eate [or replace] function name ( [ [ argmode ] [ argname ] argtype [{ default | =} default_SEO靠我expr ] [,…] ] ) [returns retype | returns table ( column_name column_type [,…] ) ] As $$ DecSEO靠我lare --声明段 Begin --函数体语句End; $$ language lang_name; 调用存储过程: SEO靠我 Select function_name;触发器与存储过程不同,存储过程通过其他程序启动运行或直接启动运行,而触发器由一个事件触发启动运行,也就是触发器在某个事件发生时自动隐式运SEO靠我行。
Postgresql触发器在系统执行某种特定类型的操作时,数据库将自动执行指定的特殊函数。
触发器常用于定义逻辑计较复杂的完整性约束,或某种业务规则的约束。
创建触发器: CreateSEO靠我 [constraint] Trigger name { before | after |instead of}{event[or…]} On table_name SEO靠我 [ from referenced_table_name] [ for [each ]{ row | statement } ] [when (condition ) ] SEO靠我 Execute procedure function_name ( arguments )针对图书销售管理系统基本需求,开发实现图书销售管理系统数据库,具体实验内容如下:
1.基于图书销售管理SEO靠我系统基本数据需求,给出图书销售管理系统数据库设计方案。
2.在数据库服务器中,执行SQL创建图书销售管理系统数据库BookSale。
3.在数据库BookSale中,执行SQL创建数据库表、视图、索引等对SEO靠我象。
4.在数据库BookSale中,执行SQL进行数据增、删、查、改访问操作。
5.在数据库BookSale中,采用PL/pgSQL语言编写存储过程函数Pro_CurrentSale,实现当日图书销售量SEO靠我及销售金额汇总统计。
6.在数据库BookSale中,采用PL/pgSQL语言编写过程语句块,实现对存储过程函数Pro_CurrentSale的调用,并输出统计结果。
7.在数据库BookSale中,采用SEO靠我PL/pgSQL语言编写图书销售表Insert触发器Tri_InsertSale,实现图书库存数据同步修改处理。
8.在数据库BookSale中,对图书销售表Insert触发器Tri_InsertSalSEO靠我e程序进行功能验证。
在实验计算机上,利用pgAdmin4数据库管理工具及SQL、PL/pgSQL语言,完成图书销售管理系统数据库应用编程操作,
同时记录实验过程的步骤、操作、运行结果界面等数据,为撰写实SEO靠我验报告提供素材。
“数据库原理及应用”实验所涉及的机房硬件设备为pc计算机、服务器以及网络环境,pc计算机与服务器在同一局域网络。
操作系统: Windows10 / Windows 11
管SEO靠我理工具: pgAdmin4
DBMS系统: PostgreSQL 14
(1)图书销售管理系统数据库BookSale创建操作。
采用SQL语句执行方式,创建图书销售管理系统数据库BookSale。
(SEO靠我2)在图书销售管理系统数据库BookSale中创建数据库表、视图、索引等对象。
采用SQL语句执行方式,创建图书表Book、作者表Author、出版社表Publisher、库存流水表Bookstock、SEO靠我客户表Customer、销售表Sale,以及各表主键和外键的创建,并为各表创建索引。
(3)对图书销售管理系统数据库表进行数据增、删、查、改SQL操作。
为 Book、Author、Publisher、CSEO靠我ustomer表准备样本数据,采用SQL语句执行方式,将样本数据插入到表中。
对各表进行数据修改、删除、查询、统计等访问操作。
(4)编写存储过程Pro_CurrentSale,实现当日图书销售量及销售金SEO靠我额汇总统计。
(5)编写过程语句块,实现对存储过程Pro_CurrentSale的调用,并输出统计结果。
(6)编写图书销售表Insert触发器Tri_InsertSale,实现图书库存数据同步修改处理。SEO靠我
编写图书销售表Insert触发器Tri_InsertSale,实现在Sale表数据插入时,级联操作 Bookstock表,将图书的库存流水进行记录,同时级联更新Book表中对应图书的库存数据。
(7)对SEO靠我图书销售表Insert触发器Tri_InsertSale程序进行功能验证。
为 Sale表准备样本数据,将样本数据插入到表中之后查看Bookstock表是否有对应的更新,并对比插入数据前后BookstocSEO靠我k表中对应数据的修改情况。
数据库BookSale创建操作SQL程序代码:
create database BookSale;运行操SEO靠我作界面:
在目录处刷新,查看语句运行结果:
结果说明:
成功创建了BookSale数据库。创建数据库表SQL程序代码:
creaSEO靠我te table Author (Author_ID char(18) not null,Author_Name varchar(20) not null,Author_Gender SEO靠我char(2) not null,constraint Author_PK primary key(Author_ID) ); create table PublishSEO靠我er (Publisher_ID char(11) not null,Publisher_Name varchar(20) not null,Publisher_phone varchSEO靠我ar(15) not null,constraint Publisher_PK primary key(Publisher_ID) ); create table BoSEO靠我ok (Book_ISBN char(13) not null,Book_Name varchar(50) not null,Book_Pubdate date not null,BoSEO靠我ok_Price money not null,Book_Stock int4 not null,Author_ID char(18) null,Publisher_ID char(11) null,SEO靠我constraint Book_PK primary key(Book_ISBN) ); create table Customer (CustomerSEO靠我_ID char(18) not null,Customer_Name varchar(20) not null,Customer_phone varchar(15) not null,constraSEO靠我int Customer_PK primary key(Customer_ID) ); create table Sale (Sale_ID varchSEO靠我ar(11) not null,Sale_Date date not null,Sale_Number int4 not null,Sale_Amount money not null,Book_ISSEO靠我BN char(13) null,Customer_ID char(18) null,constraint Sale_PK primary key(Sale_ID) ); SEO靠我 create table Bookstock (Stock_ID varchar(10) not null,Stock_Date date not null,Stock_OperaSEO靠我tion char(4) not null,Stock_Change int4 not null,Book_ISBN char(13) null,constraint Stock_PK primarySEO靠我 key(Stock_ID) ); alter table Book add constraint Book_Author_FK foreign key(Author_SEO靠我ID) references Author(Author_ID) on delete restrict on update restrict; alter table Book addSEO靠我 constraint Book_Publisher_FK foreign key(Publisher_ID) references Publisher(Publisher_ID) on deleteSEO靠我 restrict on update restrict; alter table Sale add constraint Sale_Book_FK foreign key(Book_SEO靠我ISBN) references Book(Book_ISBN) on delete restrict on update restrict; alter table Sale addSEO靠我 constraint Sale_Customer_FK foreign key(Customer_ID) references Customer(Customer_ID) on delete resSEO靠我trict on update restrict; alter table Bookstock add constraint Stock_Book_FK foreign key(BooSEO靠我k_ISBN) references Book(Book_ISBN) on delete restrict on update restrict;运行操作界面:
结果说明:
成功创建了数据库表对象。创建索SEO靠我引SQL程序代码:
create index Author_Name_Idx on Author(Author_Name); create index Publisher_Name_IdSEO靠我x on Publisher(Publisher_Name); create index Book_Name_Idx on Book(Book_Name); creatSEO靠我e index Customer_ID_Idx on Customer(Customer_ID); create index Sale_Date_Idx on Sale(Sale_DaSEO靠我te); create index Stock_ID_Idx on Bookstock(Stock_ID);结果说明:
成功创建了各个数据库表对应的索引。插入操作SQL程序代码:
insert into Author values(111111111111111111,张伟,男); insert intoSEO靠我 Author values(222222222222222222,王秀英,女); insert into Author values(333333333333333333,李军,男)SEO靠我; insert into Publisher values(PUB00000001,人民文学出版社,010-11111111); insert into PublisSEO靠我her values(PUB00000002,上海译文出版社,010-22222222); insert into Publisher values(PUB00000003,中华书局出SEO靠我版社,010-33333333); insert into Book values(9787121021961,操作系统:精髓与设计原理,2006-02-01,58,20,111111SEO靠我111111111111,PUB00000001); insert into Book values(9787559202253,西方建筑史,2019-12-02,328,30,222SEO靠我222222222222222,PUB00000002); insert into Book values(9787532786831,克拉拉与太阳,2021-03-03,68,98,SEO靠我222222222222222222,PUB00000002); insert into Book values(9787555911425,字母表谜案,2021-05-07,42,1SEO靠我50,333333333333333333,PUB00000003); insert into Customer values(777777777777777777,刘洋,123789SEO靠我52016); insert into Customer values(888888888888888888,李桂英,19618346059); insert intoSEO靠我 Customer values(999999999999999999,王涛,19204562817); select * from Publisher;运行操作界面:
样本数据插入完成SEO靠我后,对出版社表内的数据进行查询操作,查看插入操作是否正确:
结果说明:
成功在各个数据库表中插入了样本数据,经过查询结果验证,插入操作无误。
增加了新的一行数据SQL程序代码:
insert into PubSEO靠我lisher values(PUB00000004,电子科技大学出版社,010-44444444); select * from Publisher;运行操作界面:
结果说明:
成功在PuSEO靠我blisher表中增加了新的一行数据,经过查询输出后证明增加数据操作无误。
修改操作SQL程序代码:
update Publisher set Publisher_Phone = 010-55555555SEO靠我 where Publisher_ID = PUB00000004; select * from Publisher;运行操作界面:
结果说明:
成功在Publisher表中实现了修改数据SEO靠我的操作,经过查询输出后证明修改操作无误。
删除操作SQL程序代码:
delete from Publisher where Publisher_ID = PUB00000004; seleSEO靠我ct * from Publisher;运行操作界面:
结果说明:
成功在Publisher表中删除了新添加的一行数据,经过查询输出后证明删除操作无误。
SQL程序代码:
create or replace function Pro_CurrentSale(out amount int4, out allmoney mSEO靠我oney) as $count$beginselect sum(Sale_Number) into amount from Sale where Sale_Date = 2022-05-05;seleSEO靠我ct sum(Sale_Amount) into allmoney from Sale where Sale_Date = 2022-05-05;end; $count$ LANGUASEO靠我GE plpgsql;运行操作界面:
结果说明:
成功创建了Pro_CurrentSale存储过程。SQL程序代码SEO靠我:
select * from Pro_CurrentSale();运行操作界面:
结果说明:
调用存储过程后输出数据符合实际情况,输出的总销售量和总金额数据正确,创建存储过程Pro_CurrentSaleSEO靠我正确。
创建InsertSale()触发器函数SQL程序代码:
create or replace funSEO靠我ction InsertSale()returns trigger as $$begininsert into Bookstock values(new.Sale_ID,new.Sale_Date,出SEO靠我库,new.Sale_Number,new.Book_ISBN);update Book set Book_Stock = Book_Stock - new.Sale_Number where BooSEO靠我k.Book_ISBN = new.Book_ISBN;return new;end; $$ language plpgsql;运行操作界面:
结果说明:
成功创建了InsertSale(SEO靠我)触发器函数。
创建了Tri_InsertSale触发器SQL程序代码:
create trigger Tri_InsertSale after insert on Sale for eaSEO靠我ch row execute procedure InsertSale();运行操作界面:
结果说明:
成功创建了Tri_InsertSale触发器。
SQL程序代码:
insert into Sale values(SA00001,2022-05-02,8,464,9787121021961,7777777SEO靠我77777777777); insert into Sale values(SA00002,2022-05-03,5,1640,9787559202253,888888SEO靠我888888888888); insert into Sale values(SA00003,2022-05-05,10,680,9787532786831,99999SEO靠我9999999999999); insert into Sale values(SA00004,2022-05-05,22,924,9787555911425,9999SEO靠我99999999999999); insert into Sale values(SA00005,2022-05-05,33,2244,9787532786831,88SEO靠我8888888888888888); insert into Sale values(SA00006,2022-05-05,2,656,9787559202253,77SEO靠我7777777777777777); select * from Bookstock; select * from Book;运行操作界面:
查看Bookstock表的数SEO靠我据:
对比Book表的数据:
结果说明:
Bookstock表中本来没有数据,在Sale表插入数据之后,将库存数据进行记录,记录数据正确,同时发现 Book表中库存量都发生了变化,
分别减少了与销售表中记录的SEO靠我销售件数相应值的大小,更新数据正确,可以证明触发器的功能都实现了,触发器功能正确。
通过这次学习,发现自己的相关SQL语句的标准模式掌握的不够熟练,还需要查找相关资料才能完成,通过这次练习SEO靠我,提高了相关语句的熟练度,所以勤学多练才是掌握一门技能的好方法
了解该DBMS系统对数据库管理的内容与方法,特别是理解数据库安全机制和作用,
以及PostgrSEO靠我eSQL数据库角色管理、用户管理、权限管理的基本方法,培养数据库管理能力。
在图书销售管理系统数据库中,创建必要的角色和用户,并完成上述角色与用户的权限管理。
设计数据存取权限控制模型,对各角色进SEO靠我行不同权限的赋予,保证数据库数据的安全性。
使用SQL语句进行角色、用户的创建、对角色进行权限赋予、对用户分派角色。
用户的创建:
SQL提供了专门创建用户的SQL命令语句,在数据库中,只能通过特定权限的用SEO靠我户创建其他用户,如系统管理员或超级用户。
因此为每个用户分配合适的权限,尤为重要。
Create user < 用 户 账 号 >[[with] option […]]; SuperuseSEO靠我r|nosuperuser:指定创建的用户是否为超级用户 Createdb|nocreatedb:指定创建的用户是否具有创建数据库的权限 Createrole|nocrSEO靠我eaterole:指定创建的用户是否具有创建角色的权限 Inherit|noinherit:指定创建的用户是否具有继承角色的权限 Login|nologin:指定创建的SEO靠我用户是否具有登陆权限 Replication|noreplication:指定创建的用户是否具有复制权限 Bypassrls|nobypassrls 指定创建的用户是否SEO靠我具有绕过安全策略的权限 Connection limit 指定创建的用户访问数据连接的数目限制 [encrypted|unencrypted] password ‘xxSEO靠我x’:指定创建的用户密码是否需要加密 Valid until ‘timestamp’:指定创建的用户密码失效时间 In role role_name[,…]:指定创建的SEO靠我用户成为那些角色的成员角色的创建:
角色的创建与用户创建过程相似,但没有密码等设置要求。
权限赋予:
可以直接将对表的操作权限直接赋予用户。
Grant select,update,alter,delete SEO靠我on table_name to userxx;但是这样需要为每个用户单独赋予权限,当用户数量较多时,工作量大,且难以管理。通过创建角色,赋予角色相应权限,用户采用继承角色的方式获得相应的权限。
使用不SEO靠我同的用户登录时,就拥有了该用户的权限,在对数据库表进行操作的时候就会受到不同程度的制约。
使用pgAdmin4数据库管理工具对图书销售管理系统数据库进行数据库安全管理,具体实验内容如下:
1.针对SEO靠我图书销售管理系统数据库应用需求,设计数据存取权限控制模型。
2.在数据库中,创建客户(R_Customer)、商家(R_Seller)角色。
3.在数据库中,根据业务规则为客户(R_Customer)、商SEO靠我家(R_Seller)角色赋予数据库对象权限。
4.在数据库中,分别创建客户用户U_Customer、商家用户U_Seller。
5.分别为客户用户U_Customer、商家用户U_Seller分派客户(SEO靠我R_Client)、商家(R_Seller)角色。
6.分别以客户用户U_Customer、商家用户U_Seller身份访问图书销售管理数据库,验证所实现数据存取权限控制模型的正确性。
在实验计算机上,利SEO靠我用pgAdmin4数据库管理工具及SQL语句,完成图书销售管理系统数据库安全管理,同时记录实验过程的步骤、操作、运行结果界面等数据,为撰写实验报告提供素材。
“数据库原理及应用”实验所涉及SEO靠我的机房硬件设备为pc计算机、服务器以及网络环境,pc计算机与服务器在同一局域网络。
操作系统: Windows10 / Windows 11
管理工具: pgAdmin4
DBMS系统: PostgreSQSEO靠我L 14
(1)针对图书销售管理系统数据库,设计数据存取权限控制模型。
根据业务逻辑及实际需要为商家、客户、系统管理员分配在每个数据库表上的操作权限。
(2)在数据库中,创建客户(R_CustomeSEO靠我r)、商家(R_Seller)角色。
采用SQL语句执行方式,创建客户R_Customer、商家R_Seller角色。
(3)在数据库中,根据业务规则为客户(R_Customer)、商家(R_SellerSEO靠我)角色赋予数据库对象权限。
利用Grant关键词对客户R_Customer、商家R_Seller角色,参考设计的数据存取权限控制模型,赋予对应的所定义的数据库对象权限。
(4)在数据库中,分别创建客户用户SEO靠我U_Customer、商家用户U_Seller。
(5)分别为客户用户U_Customer、商家用户U_Seller分派客户(R_Client)、商家(R_Seller)角色。
(6)分别以客户用户U_CSEO靠我ustomer、商家用户U_Seller身份访问图书销售管理数据库,验证所实现数据存取权限控制模型的正确性。
分别以客户用户U_Customer、商家用户U_Seller身份访问图书销售管理数据库,并分SEO靠我别以这两个用户对各数据库表进行操作,以验证是否正确分配了两用户不同的角色权限。
商家角色安全模型:
客户角色安全模型:
结果说明SEO靠我:
成功设计了客户角色和商家角色的数据库存取访问权限控制模型。SQL程序代码:
create role R_CustomeSEO靠我r with login nosuperuser nocreatedb nocreaterole noinherit SEO靠我 noreplication connection limit -1 password 123456; create role R_SellSEO靠我er with login nosuperuser nocreatedb nocreaterole noinherit SEO靠我 noreplication connection limit -1 password 123456;运行操作界面:
结果说明:
成功创建了客户角色R_CusSEO靠我tomer和商家角色R_Seller。(3)在数据库中,根据业务规则为客户(R_Customer)、商家(R_Seller)角色赋予数据库对象权限。
SQL程序代码:
grant select on AuSEO靠我thor to R_Customer; grant select on Publisher to R_Customer; grant select on Book toSEO靠我 R_Customer; grant select,insert,update,delete on Author to R_Seller; grant select,iSEO靠我nsert,update,delete on Publisher to R_Seller; grant select,insert,update,delete on Book to RSEO靠我_Seller; grant select,insert,update,delete on Customer to R_Seller; grant select,insSEO靠我ert,update,delete on Sale to R_Seller; grant select on Bookstock to R_Seller;运行操作界面:
结果说明:
成功将SEO靠我定义的权限赋予给了客户角色R_Customer和商家角色R_Seller。SQL程序代码:
create user U_Customer with login cSEO靠我onnection limit -1 in role R_Customer password 123456; create user U_Seller SEO靠我with login connection limit -1 in role R_Seller password 123456;运行操作SEO靠我界面:
结果说明:
成功创建了客户用户U_Customer和商家用户U_Seller。
SQL程序代码:
select * from Book; delete from Book where Book_ISBN = 9787121021961; SEO靠我 select * from Author; insert into Author values(444444444444444444,大锤,男); select *SEO靠我 from Sale;以客户用户身份登录数据库:
查看Book表:
删除Book表中的数据:
查看Author表:
向Author表中插入一条数据:
查看Sale表:
结果说明:
客户用户U_Customer可查看SEO靠我Book表中的数据。
在删除Book表中的数据时,结果显示失败,客户用户不能完成删除操作。
客户用户可以查看Author表中的数据。
在向Author表中插入一条数据时,结果显示失败,客户用户不能完成插入操SEO靠我作。
在查看Sale表中的数据时,结果显示失败,客户用户不能完成查看操作。
客户用户对数据库表的权限的测试都正确,即创建的客户用户赋予权限的操作是正确的。
SQL程序代码:
seleSEO靠我ct * from Customer; insert into Author values(444444444444444444,大锤,男); select * froSEO靠我m Author; select * from Bookstock; delete from Bookstock where Stock_ID = SA00001; SEO靠我 update Sale set Sale_Date = 2022-05-01 where Sale_ID = SA00001; select * from Sale;查看SEO靠我Customer表:
向Author表中插入一条数据:
从Bookstock表中删除一条数据:
更新Sale表中的一条数据:
结果说明:
商家用户U_Seller可查看Customer表中的数据。
通过与客户用户SEO靠我步骤中查询的作家信息表进行对比,Author表中的数据新增加了一条,商家用户具有对Author表的插入权限。
在从Bookstock表中删除一条数据时,结果显示失败,商家用户不能完成删除操作。
通过对比,SEO靠我Sale表中的数据发生了更新,商家用户具有对Sale表的更新权限。
商家用户对数据库表的权限的测试都正确,即创建的商家用户赋予权限的操作是正确的。
在SQL Shell (psql)的使用中SEO靠我遇到了多次报错,在网上搜索实践后解决,例如postgreSQL: password authentication failed for user "postgres",从postgreSQL: pasSEO靠我sword authentication failed for user "postgres"_森林先森的博客-CSDN博客获得解决。我从中理解到,互联网可以帮助解决大部分问题
网站备案号:浙ICP备17034767号-2