信息系统分析与设计（数据库阶段设计）

• 生活家

信息系统分析与设计（数据库阶段设计）

  • 前言
  • 一、核心功能描述
    • 1.产品信息管理
    • 2.订单信息管理
    • 3.款项收付管理
  • 二、ER图
  • 三、逻辑数据库
    • 1.摄影师表
    • 2.约拍者表
    • 3.产品表
    • 4.订单表
    • 5.款项表
    • 6.匹配表
  • 四、功能实现描述
    • 1.产品信息管理
    • 2.订单信息管理
    • 3.款项收付管理
  • 五、DDL语句实现
    • 1.建表语句
    • 2.核心功能语句：填写订单、查询待确认订单、取消订单、结束订单、转出款项、更新KX信息
    • 3.权限语句

前言

以项目成员分析过需求后设计的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

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐