信息系统分析与设计（数据库阶段设计）
程序员文章站
2022-06-24 16:42:38
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档信息系统分析与设计（数据库阶段设计）前言一、核心功能描述1.产品信息管理2.订单信息管理3.款项收付管理二、ER图三、逻辑数据库1.摄影师表2.约拍者表3.产品表4.订单表5.款项表6.匹配表四、功能实现描述1.产品信息管理2.订单信息管理3.款项收付管理五、DDL语句实现1.建表语句2.核心功能语句：填写订单、查询待确认订单、取消订单、结束订单、转出款项、更新KX信息3.权限语句前言以项目成员分析过需求后设计的ER图以及逻辑数据库为...
信息系统分析与设计（数据库阶段设计）
前言
以项目成员分析过需求后设计的ER图以及逻辑数据库为基础，独立完成产品信息管理、订单信息管理、款项收付管理核心功能设计以及权限设置的DDL语句（触发器）实现
一、核心功能描述
1.产品信息管理
| 系统功能模块 | 功能描述 |
|---|---|
| 匹配产品 | 约拍者键入检索条件，如：价格、所在地、性别、风格，平台通过关键词在数据库中为约拍者匹配目标产品 |
| 发布产品 | 摄影师将具体拍摄时间、准确的省市区、多个擅长的拍摄风格、拍摄照片单价定义为标签列入产品信息中，并于平台发布 |
| 下架产品 | 摄影师发布产品后，若想撤销已经发布产品，则可以在平台选择下架产品 |
2.订单信息管理
| 系统功能模块 | 功能描述 |
|---|---|
| 填写订单 | 约拍者选定某位摄影师的产品后，输入产品ID，系统生成一条订单记录 |
| 确认订单 | 约拍者填写好订单后，摄影师查阅确认订单，以备开拍 |
| 取消订单 | 在开拍前，约拍者和摄影师若有一方/双方因特殊原因不能履约，在双方协商后，可以选择取消订单 |
| 成片管理 | (1)上传成片：摄影师修图完成之后在平台上上传成片 (2)接受成片：约拍者收到成片进行查阅确认。 |
3.款项收付管理
| 系统功能模块 | 功能描述 |
|---|---|
| 收取价款 | 款项转入款项。约拍者可通过微信、支付宝、银行卡等多种方式进行支付款项至平台 |
| 转出价款 | 平台转出款项。订单期间，平台将作为第三方保管价款，约拍者确认订单状态为已完成后，平台将款项打入至摄影师账户 |
二、ER图
使用VISIO进行绘制
注：“平台”实体即为款项实体
三、逻辑数据库
此处仅展示各表的字段名称、类型、大小和主码以及外码，以便于理解DDL语句实现。
1.摄影师表
| 字段名称 | 字段类型及大小 | 主码或索引 |
|---|---|---|
| 摄影师ID | int | 主码 |
| 身份证号 | char(18) | |
| 真实姓名 | char(30) | |
| 年龄 | int | |
| 性别 | char(2) | |
| 联系方式 | int | |
| 常驻地 | nchar(30) |
2.约拍者表
| 字段名称 | 字段类型及大小 | 主码或索引 |
|---|---|---|
| 约拍者ID | int | 主码 |
| 身份证号 | char(18) | |
| 真实姓名 | char(30) | |
| 年龄 | int | |
| 性别 | char(2) | |
| 联系方式 | int | |
| 常驻地 | nchar(30) |
3.产品表
| 字段名称 | 字段类型及大小 | 主码或索引 |
|---|---|---|
| 产品ID | int | 主码 |
| 摄影师ID | int | 外码 |
| 时间 | date | |
| 地点 | nchar(30) | |
| 单价 | money | |
| 风格 | vchar(16) |
4.订单表
| 字段名称 | 字段类型及大小 | 主码或索引 |
|---|---|---|
| 订单ID | int | 主码 |
| 产品ID | int | 外码 |
| 约拍者ID | int | 外码 |
| 款项ID | int | 外码 |
| 订单日期 | date | |
| 订单状态 | char(8) | |
| 成片文档 | file | |
| 成片数量 | int | |
| 总价款 | money |
5.款项表
| 字段名称 | 字段类型及大小 | 主码或索引 |
|---|---|---|
| 款项ID | int | 主码 |
| 约拍者ID | int | 外码 |
| 摄影师ID | int | 外码 |
| 收到款项 | money | |
| 转出款项 | money |
6.匹配表
| 字段名称 | 字段类型及大小 | 主码或索引 |
|---|---|---|
| 产品ID | int | 主码 |
| 约拍者ID | int | 主码 |
四、功能实现描述
1.产品信息管理
-
匹配产品
①基础表：【产品表】
②具体实现：以【产品表】为基础，以“单价”、“时间”、“地点”、“风格”为检索条件，运用SELECT操作语句，模糊查询出符合检索条件的“产品ID”，以备订单填写时使用 -
发布产品
①基础表：【产品表】
②相关表：【摄影师表】
③具体实现：以【产品表】为基础，摄影师向其中INSERT一条记录。
其中“产品ID”为主码，由系统顺序编号生成；“摄影师ID”为外码，参照【摄影师表】中的“摄影师ID”属性，由摄影师个人填写 -
下架产品
①基础表：【产品表】
②相关表：【订单表】
③具体实现：
A.以【产品表】为基础，摄影师以“产品ID”为检索条件，DELETE该条记录行。
B.以【产品表】为基础，摄影师以“摄影师ID”为检索条件，检索并选中待删的记录行，DELETE该条记录行。
④注意：
在对【产品表】中记录行进行DELETE操作时，考虑到产品ID为【产品表】主码，而为【订单表】外码。
根据业务逻辑，摄影师下架的产品，“产品ID”不能出现在【订单表】记录行的外码属性中。
因此，在对【产品表】“产品ID”属性进行定义时，应该定义为RESTRICT受限删除。
2.订单信息管理
-
填写订单
①基础表：【订单表】
②相关表：【产品表】
③连接条件：【订单表】.产品ID=【产品表】.产品ID
④具体实现：
以【订单表】为基础，约拍者向订单表中INSERT一条新记录。其中，订单ID为自动编码，约拍者ID、产品ID、成片数量为约拍者填写键入的内容。
⑤触发器：
触发类型：after insert
触发表：【订单表】
触发实现：
当约拍者向订单表中插入一条记录后，触发【订单表】的“总价款”=【产品表】的“单价”*【订单表】的“成片数量”；同时触发【订单表】的“订单日期”=Getdate()函数的函数值 -
确认订单（三个触发器连续触发）
第一步触发：
①基础表：【订单表】
②相关表：【款项表】
③具体实现：摄影师UPDATE【订单表】.订单状态为“确认订单”
④触发器：
触发类型：After update
触发表：【订单表】
具体实现：
当摄影师UPDATE【订单表】.订单状态为“确认订单”，触发向【款项表】插入一条新记录，即自动编码生成款项ID。
订单确认属性值改变（摄影师改变该属性值）触发款项表生成新的款项记录（款项ID）；
第二步触发：
①基础表：【款项表】
②相关表：【产品表】、【订单表】
③连接条件：【产品表】.产品ID=【订单表】.产品ID AND【产品表】.摄影师ID=【款项表】.摄影师ID
④具体实现：
⑤触发器：
触发器类型：after insert
触发表：【款项表】
当【款项表】中插入一条新记录，UPDATE【款项表】.约拍者ID=【订单表】.约拍者ID；【款项表】.摄影师ID=【产品表】.摄影师ID；【款项表】.收到款项=【订单表】.总价款。
款项ID插入了触发款项表的其他信息更新（约拍者ID，摄影师ID，收到款项）；
第三步触发：
①基础表：【订单表】
②相关表：【款项表】
③连接条件：【订单表】.款项ID=【款项表】.款项ID
④触发器：
触发器类型：after update
触发表：【款项表】
当【款项表】信息经由上一触发器UPDATE后，UPDATE【订单表】.款项ID=【款项表】.款项ID。（注：此处借用【款项表】的inserted表以索引新插入的款项ID，同时赋值给【订单表】.款项ID）
更新款项表触发将订单表里的款项ID更新为刚插入的款项ID； -
取消订单（触发器）
①基础表：【订单表】
②相关表：【款项表】
③连接条件：【订单表】.款项ID=【款项表】.款项ID
④触发器：
触发器类型：after update
触发表：【订单表】
⑤具体实现：
当约拍者/摄影师一方或者双方取消订单，UPDATE【订单表】.订单状态=“订单取消”。
由此触发【款项表】.“收到款项”=0 -
上传成片
①基础表：【订单表】
②具体实现：
以【订单表】为基础，摄影师通过检索“订单ID”直接或者“摄影师ID”间接找到对应的记录行，使用UPDATE语句对原记录的“成片文档”属性值进行修改，上传成片
** -
接受成片
①基础表：【订单表】
②具体实现：
以【订单表】为基础，约拍者以“订单ID”为直接或者“约拍者ID”为间接检索条件，使用SELECT语句，查阅对应订单中的成片文档
3.款项收付管理
-
收取价款（触发器）
①基础表：【款项表】
②相关表：【产品表】、【订单表】
③连接条件：【产品表】.产品ID=【订单表】.产品ID AND【产品表】.摄影师ID=【款项表】.摄影师ID
④具体实现：
触发器类型：after insert
触发表：【款项表】
当【款项表】中插入一条新记录，UPDATE【款项表】.约拍者ID=【订单表】.约拍者ID；【款项表】.摄影师ID=【产品表】.摄影师ID；【款项表】.收到款项=【订单表】.总价款 -
转出价款（触发器）
①基础表：【订单表】
②相关表：【款项表】
③连接条件：【订单表】.款项ID=【款项表】.款项ID
④具体实现：
触发类型：after update
触发表：【订单表】
触发实现：
当约拍者UPDATE【订单表】.订单状态=“订单结束”，触发【款项表】.收到款项=【款项表】.转出款项，转款完成
五、DDL语句实现
为了方便Coding，这里我们把所有属性都用其首字母大写代替，避免出现错误
1.建表语句
--约拍者（YPZ）
CREATE TABLE YPZ
(
YPZID int NOT NULL PRIMARY KEY,
SFZH char(18),
ZSXM nvarchar(20),
NL int,
XB nchar(2) ,
LXFS int,
CZD nvarchar(30),
Constraint fk_PerYPZ CHECK(XB='男' or XB='女')
)
--摄影师（SYS）
CREATE TABLE SYS
(
SYSID int NOT NULL PRIMARY KEY,
SFZH char(18),
ZSXM nvarchar(20),
NL int,
XB nchar(2),
LXFS int,
CZD nvarchar(30),
Constraint fk_PerSYS CHECK(XB='男' or XB='女')
)
--产品（CP）
CREATE TABLE CP
(
CPID int NOT NULL PRIMARY KEY,
SYSID int,
SJ date,
DD nvarchar(30),
DJ money,
FG nvarchar(20),
Constraint fk_PerCP FOREIGN KEY(SYSID) REFERENCES SYS(SYSID)
)
--平台（PT）
CREATE TABLE PT
(
KXID int NOT NULL PRIMARY KEY,
YPZID int,
SYSID int,
SDKX money,
ZCKX money,
Constraint fk_PerPT FOREIGN KEY (YPZID) REFERENCES YPZ(YPZID),
Constraint fk_PerPT1 FOREIGN KEY (SYSID) REFERENCES SYS(SYSID)
)
--订单（DD）
CREATE TABLE DD
(
DDID int NOT NULL PRIMARY KEY,
YPZID int,
CPID int,
KXID int,
DDRQ date,
CPWD image,
CPSL int,
ZJK money DEFAULT '0',
DDZT nchar(10) DEFAULT '等待确认',
Constraint fk_PerDD1 FOREIGN KEY (YPZID) REFERENCES YPZ(YPZID),
Constraint fk_PerDD2 FOREIGN KEY (CPID) REFERENCES CP(CPID),
Constraint fk_PerDD3 FOREIGN KEY (KXID) REFERENCES PT(KXID),
Constraint fk_PerDD4 CHECK(DDZT='订单确认' or DDZT='订单取消' or DDZT='订单结束' or DDZT='等待确认')
)
--匹配（PP）
CREATE TABLE PP
(
CPID int,
YPZID int,
PRIMARY KEY(CPID,YPZID),
Constraint fk_PerPP FOREIGN KEY(CPID) REFERENCES CP(CPID),
Constraint fk_PerPP1 FOREIGN KEY(YPZID) REFERENCES YPZ(YPZID)
)
2.核心功能语句：填写订单、查询待确认订单、取消订单、结束订单、转出款项、更新KX信息
/*1、约拍者填写订单：
约拍者输入约拍者ID、产品ID、成片数量，然后通过编写的触发器，自动更新当前的订单日期、总价款*/
--约拍者填写新订单（DDID先不设置自动编码便于后期进行测试修改数据）
INSERT INTO DD(DDID,YPZID,CPID,CPSL)
VALUES(21,3,5,15)
--订单填写后触发计算总价款并更新订单日期
CREATE TRIGGER 计算总价款并更新订单日期 ON DD
AFTER INSERT
AS IF(SELECT COUNT(*) FROM inserted WHERE inserted.ZJK=0)>0
UPDATE DD
SET ZJK=CP1.DJ * inserted.CPSL ,inserted.DDRQ=GETDATE()
FROM CP CP1,inserted
WHERE inserted.CPID=CP1.CPID AND inserted.DDID=DD.DDID
/*2、摄影师确认订单：（三个触发器）
摄影师通过自己得摄影师ID，联接订单表和产品表从中SELECT等待确认的订单ID，确认订单ID后进行UPDATE操作，更新订单状态为“订单确认”，同时触发编写的触发器，在平台表中生成新的款项ID，再触发另一个触发器更新平台表中摄影师ID，约拍者ID，收到价款属性值，最后再触发一个传回当前款项ID到订单表里的触发器*/
--摄影师查询等待确认的订单
SELECT DDID
FROM CP,DD
WHERE CP.SYSID=4 AND CP.CPID=DD.CPID AND DD.DDZT='等待确认'
--摄影师确认订单
UPDATE DD
SET DDZT='订单确认'
WHERE DDID=21
--订单状态一旦变为‘订单确认’则触发平台表生成一条新记录，当订单状态变为‘订单结束’后自动转款给摄影师，当变为‘订单取消’后则将收到款项变为0
CREATE TRIGGER 生成新款项记录并在订单结束后转款摄影师 ON DD
AFTER UPDATE
AS IF(UPDATE(DDZT))
BEGIN
IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单确认')=1
INSERT INTO PT(SYSID) VALUES(1)
ELSE IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单结束') =1
UPDATE PT
SET ZCKX=SDKX
FROM PT,deleted,DD
WHERE deleted.DDID=PT.KXID
ELSE IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单取消') =1
UPDATE PT
SET SDKX=0 FROM PT,deleted,DD
WHERE deleted.DDID=PT.KXID
END
--触发完成后需要再次触发更新PT基本信息
CREATE TRIGGER 自动更新基本信息 ON PT
AFTER INSERT
AS IF(SELECT COUNT(*) FROM inserted WHERE inserted.SYSID=1)>0
UPDATE PT
SET YPZID=DDD.YPZID ,SYSID=CPP.SYSID ,SDKX=DDD.ZJK
FROM PT PTT,DD DDD,CP CPP,inserted
WHERE PTT.KXID=DDD.DDID AND DDD.CPID=CPP.CPID AND inserted.KXID=PTT.KXID
--更新完PT基本信息后将KXID自动填入订单
CREATE TRIGGER 自动传回KXID ON PT
AFTER UPDATE
AS IF(UPDATE(YPZID))
BEGIN
UPDATE DD
SET DD.KXID=inserted.KXID
FROM inserted,DD,CP
WHERE DD.YPZID=inserted.YPZID AND inserted.SYSID=CP.SYSID AND DD.DDZT='订单确认'
END
/*3、双方任意一方取消订单，通过已编写的触发器进行操作
订单状态一旦变为‘订单确认’则触发平台表生成一条新记录，当订单状态变为‘订单结束’后自动转款给摄影师，当变为‘订单取消’后则将收到款项变为0*/
CREATE TRIGGER 生成新款项记录并在订单结束后转款摄影师 ON DD
AFTER UPDATE
AS IF(UPDATE(DDZT))
BEGIN
IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单确认')=1
INSERT INTO PT(SYSID) VALUES(1)
ELSE IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单结束') =1
UPDATE PT
SET ZCKX=SDKX
FROM PT,deleted,DD
WHERE deleted.DDID=PT.KXID
ELSE IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单取消') =1
UPDATE PT
SET SDKX=0
FROM PT,deleted,DD
WHERE deleted.DDID=PT.KXID
END
/*4、约拍者结束订单，通过已编写的触发器进行操作
订单状态一旦变为‘订单确认’则触发平台表生成一条新记录，当订单状态变为‘订单结束’后自动转款给摄影师，当变为‘订单取消’后则将收到款项变为0*/
CREATE TRIGGER 生成新款项记录并在订单结束后转款摄影师 ON DD
AFTER UPDATE
AS IF(UPDATE(DDZT))
BEGIN
IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单确认')=1
INSERT INTO PT(SYSID) VALUES(1)
ELSE IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单结束') =1
UPDATE PT
SET ZCKX=SDKX
FROM PT,deleted,DD
WHERE deleted.DDID=PT.KXID
ELSE IF(SELECT COUNT(*) FROM DD,inserted,deleted WHERE inserted.DDID=DD.DDID AND inserted.DDZT='订单取消') =1
UPDATE PT SET SDKX=0 FROM PT,deleted,DD WHERE deleted.DDID=PT.KXID
END
3.权限语句
/*建立新的登录名*/
exec sp_addlogin user1,123,约拍
exec sp_addlogin user2,123,约拍
/*建立登录名与用户名之间的映射*/
exec sp_adduser user1,"YPZ1"
exec sp_adduser user2,"SYS1"
/*匹配产品*/
grant select on CP to YPZ1
/*发布产品*/
grant select,insert on cp1 to SYS1
/*保证摄影师只能够修改和删除自己的产品*/
GO
create view cp1
as
(select * from CP
where SYSID=1)
GO
/*修改、下架产品*/
grant select,delete,update(SJ,DD,DJ,FG) on cp1 to SYS1
/*填写订单*/
/*当约拍者想要直接对DD操作，而不是间接通过DD1操作
grant insert on DD to YPZ1
GO
create trigger tr_update on DD
for update
as
if update(DDID) rollback tran
if update(KXID) rollback tran
if update(DDRQ) rollback tran
if update(CPWD) rollback tran
if update(ZJK) rollback tran
GO
*/
/*通过设计视图，从DD表里筛选出只属于该约拍者的订单，保证约拍者只能间接通过DD1增、删、改、查自己的订单，而不是对DD中的所有记录行都有操作权限*/
GO
create view dd1
as
(select * from DD
where YPZID='1')
GO
select * from dd1
grant select,insert,update(YPZID,CPID,CPSL,DDZT) on dd1 to YPZ1
/*确认订单*/
/*通过设计视图，从DD表里筛选出只属于该摄影师的订单，保证摄影师只能间接通过DD2增、删、改、查自己的订单，而不是对DD中的所有记录行都有操作权限*/
GO
create view dd2
as
(select * from DD
where SYSID='1')
GO
grant update(DDZT) on dd2 to SYS1
/*取消订单*/
grant update(DDZT) on dd1 to YPZ1
grant update(DDZT) on dd2 to SYS1
/*上传成片*/
grant select,update(CPWD) on dd2 to SYS1
/*接受成片*/
grant select(CPWD) on dd1 to YPZ1
/*收取价款*/
/*转出价款*/
grant update(DDZT) on dd1 to YPZ1
/*收回权限*/
revoke INSERT,DELETE,UPDATE,SELECT on DD from YPZ1
revoke INSERT,DELETE,UPDATE,SELECT on DD from SYS1
revoke INSERT,DELETE,UPDATE,SELECT on CP from SYS1
本文地址：https://blog.csdn.net/qq_45826139/article/details/111045272
下一篇： MySQL原理之MVCC详解