数据库设计（电商平台）

数据库课题设计（老师要2月1号前完成，这边交作业的同时顺便同步博客了，好久没写了，就水一篇！嘿嘿）

使用软件 ：pdshell（cdm、pdm设计）、draw.io（ER图设计）、sqlserver2008

draw.io下载地址：

https://github.com/jgraph/drawio-desktop/releases/tag/v20.8.10

pdshell下载地址

链接：https://pan.baidu.com/s/1260uOUX76ThOMyVsZbRn5A?pwd=qw9u
提取码：qw9u
–来自百度网盘超级会员V5的分享

1、需求分析

1.1关系表（粗体为主码）

l 主播：主播工号、姓名、工资、电话、主管

l 直播：直播编号、商品编号、主播工号、观看人数、时间、时长

l 供应商：供货商编号、公司名、地址、负责人、电话、货源地

l 供应：供货编号、商品编号、供货商编号、供货时间、供货量

l 商品：商品编号、名称、单价、描述、图片

l 浏览：客户编号、商品编号、浏览量

l 客户：客户编号、姓名、地址、账号、密码

l 评价：评价编号、客户编号、时间、内容

l 运营：运营工号、姓名、工资、电话、主管

l 广告投放：广告编号、运营工号、商品编号、投放平台、投放时间、费用、投放量

l 策划：团队号、组织人、场地、电话

l 发布活动：活动编号、团队号、商品编号、发布时间、结束时间、参与人数、备注

l 风控：风控工号、姓名、工资、电话、主管

l 检查活动：风控工号、活动编号、检查编号、是否恶意刷单、是否亏损、终止活动、备注

l 检查商品：风控工号、商品编号、检查编号、商品合法性、终止商品、备注

l 仓库：仓库编号、名称、地址

l 出入库登记：操作编号、商品编号、仓库编号、时间、操作内容（出入库）

l 仓库管理：管理编号、仓库编号、管理工号、管理时间

l 仓库管理员：管理工号、姓名、工资、电话、负责人

l 订单：订单编号、客户编号、时间、金额、付款方式

l 购买：订单编号、商品编号、数量

l 快递：订单编号、菜鸟驿站编号、快递单号、快递员、电话、签收状态

l 菜鸟驿站：菜鸟驿站编号、店主、电话、地址

l 售后：售后工号、姓名、工资、电话、主管

l 申请售后：工单号、客户编号、售后工号、申请时间、原因、解决方式、是否闭环、闭环时间

l 查询：售后工号、订单编号、查询时间

1.2实体集

​ 主播、直播、供应商、供货单、商品、举办活动、策划、风控、出入库登记、仓库、库房管理、仓库管理员、广告投放、客户、订单、菜鸟驿站、运营、评价、申请售后、售后

1.3数据项

主播（anchor）

属性	存储代码	类型	长度
主播工号	An_r_no	char	20
姓名	An_r_name	Varchar	15
工资	An_r_salary	Int
电话	An_r_phone	char	11
主管编号	An_r_ admin	Char	20

直播（live）

属性	存储代码	类型	长度
直播编号	Li_e_no	char	10
观看人数	Li_e_number	int
时间	Li_e_time	varchar	15
时长	Li_e_times	varchar	10
主播工号	An_r_no	char	20
商品编号	Co_y_no	char	10

供应商（supplier）

属性	存储代码	类型	长度
供应商编号	Su_p_no	char	10
公司名	Su_p_name	varchar	20
地址	Su_p_site	varchar	20
负责人	Su_p_principal	varchar	15
电话	Su_p_phone	char	11
货源地	Su_p_supply	varchar	20

供应（supply）

属性	存储代码	类型	长度
供货编号	Su_y_no	Char	10
供货时间	Su_y_time	varchar	15
供货量	Su_y_quantity	varchar	15
商品编号	Co_y_no	char	10
供货商编号	Su_p_no	char	10

商品（commodity）

属性	存储代码	类型	长度
商品编号	Co_y_no	char	10
名称	Co_y_name	Varchar	20
单价	Co_y_unit	Varchar	20
描述	Co_y_describe	Varchar	200
图片链接地址	Co_y_pircture	Varchar	30

浏览（browse）

属性	存储代码	类型	长度
浏览量	Br_e_view	int
客户编号	Cl_t_no	char	10
商品编号	Co_y_no	char	10

客户（client）

属性	存储代码	类型	长度
客户编号	Cl_t_no	char	10
姓名	Cl_t_name	varchar	15
地址	Cl_t_site	varchar	20
账号	Cl_t_user	varchar	20
密码	Cl_t_password	varchar	20

评价（evaluate）

属性	存储代码	类型	长度
评价编号	Ev_e_no	char	10
时间	Ev_e_time	varchar	15
内容	Ev_e_content	varchar	100
客户编号	Cl_t_no	char	10

运营（operation）

属性	存储代码	类型	长度
运营工号	Op_n_no	char	20
姓名	Op_n_name	Varchar	15
工资	Op_n_salary	Int
电话	Op_n_phone	char	11
主管编号	Op_n_admin	Char	20

广告投放（adcertising）

属性	存储代码	类型	长度
广告编号	Ad_g_no	char	10
投放平台	Ad_g_platform	varchar	20
投放时间	Ad_g_time	varchar	15
费用	Ad_g_cost	varchar	15
投放量	Ad_g_supply	int
运营工号	Op_n_no	char	20
商品编号	Co_y_no	char	10

策划（activity）

属性	存储代码	类型	长度
团队号	Ac_y_no	char	10
组织人	Ac_y_person	varchar	15
场地	Ac_y_site	varchar	20
电话	Ac_y_phone	Char	11

发布活动（issue）

属性	存储代码	类型	长度
活动编号	Ls_e_no	char	10
发布时间	Ls_e_f_time	varchar	15
结束时间	Ls_e_l_time	varchar	15
参与人数	Ls_e_participant	Int
备注	Ls_e_remark	varchar	50
商品编号	Co_y_no	char	10
团队号	Ac_y_no	char	10

风控（risk-assessment）

属性	存储代码	类型	长度
风控工号	Ri_t_no	char	20
姓名	Ri_t_name	Varchar	15
工资	Ri_t_salary	Int
电话	Ri_t_phone	char	11
主管编号	Ri_t_admin	Char	20

检查活动（inactivity）

属性	存储代码	类型	长度
检查编号	Ln_ay_no	char	10
是否恶意刷单	Ln_ay_scalp	char	2
是否亏损	Ln_ay_loss	char	2
是否终止活动	Ln_ay_stop	char	2
备注	Ln_ay_remark	varchar	50
风控工号	Ri_t_no	char	20
活动编号	Ls_e_no	char	10

检查商品（incommodity）

属性	存储代码	类型	长度
检查编号	Ln_cy_no	char	10
商品合法性	Ln_cy_validity	char	5
是否终止商品	Ln_cy_stop	char	2
备注	Ln_cy_remark	varchar	50
风控工号	Ri_t_no	char	20
商品编号	Co_y_no	char	10

仓库（entrepot）

属性	存储代码	类型	长度
仓库编号	En_y_no	char	10
名称	En_y_name	varchar	20
地址	En_y_site	varchar	20

出入库单（warehouse）

属性	存储代码	类型	长度
操作编号	Wa_e_no	char	10
商品编号	Co_y_no	char	10
仓库编号	En_y_no	char	10
时间	Wa_e_time	varchar	15
操作内容（出入库）	Wa_e_set	Char	5

仓库管理（store）

属性	存储代码	类型	长度
仓库管理编号	St_e_no	char	10
仓库编号	En_y_no	char	10
管理员工号	St_k_no	char	20
管理时间	St_e_time	varchar	15

仓库管理员（stock）

属性	存储代码	类型	长度
管理员工号	St_k_no	char	20
姓名	St_k_name	Varchar	15
工资	St_k_salary	Int
电话	St_k_phone	char	11
负责人	St_k_admin	Char	20

订单（order）

属性	存储代码	类型	长度
订单编号	Or_r_no	char	10
时间	Or_r_time	varchar	15
金额	Or_r_money	varchar	20
付款方式	Or_r_payment	varchar	15
客户编号	Cl_t_no	char	10

购买（purchase）

属性	存储代码	类型	长度
订单编号	Or_r_no	char	10
客户编号	Cl_t_no	char	10
数量	Pu_e_quantity	varchar	20

快递（expressage）

属性	存储代码	类型	长度
快递单号	Ex_ge_no	varchar	20
快递员	Ex_ge_name	varchar	15
电话	Ex_ge_phone	char	11
签收人	Ex_ge_signer	varchar	15
订单编号	Or_r_no	char	10
菜鸟驿站编号	Ro_e_no	char	10

菜鸟驿站（rookie）

属性	存储代码	类型	长度
菜鸟驿站编号	Ro_e_no	char	10
店主	Ro_e_name	varchar	15
电话	Ro_e_phone	char	11
地址	Ro_e_site	varchar	20

售后（after）

属性	存储代码	类型	长度
售后工号	Af_r_no	char	10
姓名	Af_r_name	Varchar	15
工资	Af_r_salary	Int
电话	Af_r_phone	char	11
主管	Af_r_ admin	Char	20

售后申请（putin）

属性	存储代码	类型	长度
工单号	Pu_n_no	char	20
申请时间	Pu_n_p_time	varchar	15
原因	Pu_n_cause	varchar	50
解决方式	Pu_n_solution	varchar	20
是否闭环	Pu_n_stop	char	2
闭环时间	Pu_n_s_time	varchar	15inquire
售后工号	Af_r_no	char	10
客户编号	Cl_t_no	char	10

查询（inquire）

属性	存储代码	类型	长度
查询时间	Ln_re_time	varchar	15
客户编号	Cl_t_no	char	10
售后工号	Af_r_no	char	10

2、ER图

3、CDM

4、PDM

5、代码清单

/==============================================================/
/* DBMS name: Microsoft SQL Server 2008 /
/ Created on: 2023/1/29 9:13:47 /
/==============================================================*/

if exists (select 1
from sysobjects
where id = object_id(‘activity’)
and type = ‘U’)
drop table activity
go

if exists (select 1
from sysindexes
where id = object_id(‘adcertising’)
and name = ‘Relationship_6_FK’
and indid > 0
and indid < 255)
drop index adcertising.Relationship_6_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘adcertising’)
and name = ‘Relationship_5_FK’
and indid > 0
and indid < 255)
drop index adcertising.Relationship_5_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘adcertising’)
and type = ‘U’)
drop table adcertising
go

if exists (select 1
from sysindexes
where id = object_id(‘after’)
and name = ‘Relationship_21_FK’
and indid > 0
and indid < 255)
drop index after.Relationship_21_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘after’)
and type = ‘U’)
drop table after
go

if exists (select 1
from sysindexes
where id = object_id(‘anchor’)
and name = ‘Relationship_19_FK’
and indid > 0
and indid < 255)
drop index anchor.Relationship_19_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘anchor’)
and type = ‘U’)
drop table anchor
go

if exists (select 1
from sysindexes
where id = object_id(‘“browse”’)
and name = ‘browse2_FK’
and indid > 0
and indid < 255)
drop index “browse”.browse2_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘“browse”’)
and name = ‘browse_FK’
and indid > 0
and indid < 255)
drop index “browse”.browse_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘“browse”’)
and type = ‘U’)
drop table “browse”
go

if exists (select 1
from sysobjects
where id = object_id(‘client’)
and type = ‘U’)
drop table client
go

if exists (select 1
from sysobjects
where id = object_id(‘commodity’)
and type = ‘U’)
drop table commodity
go

if exists (select 1
from sysobjects
where id = object_id(‘entrepot’)
and type = ‘U’)
drop table entrepot
go

if exists (select 1
from sysindexes
where id = object_id(‘evaluate’)
and name = ‘Relationship_7_FK’
and indid > 0
and indid < 255)
drop index evaluate.Relationship_7_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘evaluate’)
and type = ‘U’)
drop table evaluate
go

if exists (select 1
from sysindexes
where id = object_id(‘expressage’)
and name = ‘expressage2_FK’
and indid > 0
and indid < 255)
drop index expressage.expressage2_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘expressage’)
and name = ‘expressage_FK’
and indid > 0
and indid < 255)
drop index expressage.expressage_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘expressage’)
and type = ‘U’)
drop table expressage
go

if exists (select 1
from sysindexes
where id = object_id(‘inactivity’)
and name = ‘inactivity2_FK’
and indid > 0
and indid < 255)
drop index inactivity.inactivity2_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘inactivity’)
and name = ‘inactivity_FK’
and indid > 0
and indid < 255)
drop index inactivity.inactivity_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘inactivity’)
and type = ‘U’)
drop table inactivity
go

if exists (select 1
from sysindexes
where id = object_id(‘incommodity’)
and name = ‘incommodity2_FK’
and indid > 0
and indid < 255)
drop index incommodity.incommodity2_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘incommodity’)
and name = ‘incommodity_FK’
and indid > 0
and indid < 255)
drop index incommodity.incommodity_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘incommodity’)
and type = ‘U’)
drop table incommodity
go

if exists (select 1
from sysindexes
where id = object_id(‘inquire’)
and name = ‘inquire2_FK’
and indid > 0
and indid < 255)
drop index inquire.inquire2_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘inquire’)
and name = ‘inquire_FK’
and indid > 0
and indid < 255)
drop index inquire.inquire_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘inquire’)
and type = ‘U’)
drop table inquire
go

if exists (select 1
from sysindexes
where id = object_id(‘issue’)
and name = ‘Relationship_9_FK’
and indid > 0
and indid < 255)
drop index issue.Relationship_9_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘issue’)
and name = ‘Relationship_8_FK’
and indid > 0
and indid < 255)
drop index issue.Relationship_8_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘issue’)
and type = ‘U’)
drop table issue
go

if exists (select 1
from sysindexes
where id = object_id(‘live’)
and name = ‘Relationship_2_FK’
and indid > 0
and indid < 255)
drop index live.Relationship_2_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘live’)
and name = ‘Relationship_1_FK’
and indid > 0
and indid < 255)
drop index live.Relationship_1_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘live’)
and type = ‘U’)
drop table live
go

if exists (select 1
from sysindexes
where id = object_id(‘operation’)
and name = ‘Relationship_20_FK’
and indid > 0
and indid < 255)
drop index operation.Relationship_20_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘operation’)
and type = ‘U’)
drop table operation
go

if exists (select 1
from sysindexes
where id = object_id(‘“order”’)
and name = ‘Relationship_14_FK’
and indid > 0
and indid < 255)
drop index “order”.Relationship_14_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘“order”’)
and type = ‘U’)
drop table “order”
go

if exists (select 1
from sysindexes
where id = object_id(‘purchase’)
and name = ‘purchase2_FK’
and indid > 0
and indid < 255)
drop index purchase.purchase2_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘purchase’)
and name = ‘purchase_FK’
and indid > 0
and indid < 255)
drop index purchase.purchase_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘purchase’)
and type = ‘U’)
drop table purchase
go

if exists (select 1
from sysindexes
where id = object_id(‘putin’)
and name = ‘Relationship_16_FK’
and indid > 0
and indid < 255)
drop index putin.Relationship_16_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘putin’)
and name = ‘Relationship_15_FK’
and indid > 0
and indid < 255)
drop index putin.Relationship_15_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘putin’)
and type = ‘U’)
drop table putin
go

if exists (select 1
from sysindexes
where id = object_id(‘“risk-assessment”’)
and name = ‘Relationship_17_FK’
and indid > 0
and indid < 255)
drop index “risk-assessment”.Relationship_17_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘“risk-assessment”’)
and type = ‘U’)
drop table “risk-assessment”
go

if exists (select 1
from sysobjects
where id = object_id(‘rookie’)
and type = ‘U’)
drop table rookie
go

if exists (select 1
from sysindexes
where id = object_id(‘stock’)
and name = ‘Relationship_18_FK’
and indid > 0
and indid < 255)
drop index stock.Relationship_18_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘stock’)
and type = ‘U’)
drop table stock
go

if exists (select 1
from sysindexes
where id = object_id(‘store’)
and name = ‘Relationship_13_FK’
and indid > 0
and indid < 255)
drop index store.Relationship_13_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘store’)
and name = ‘Relationship_12_FK’
and indid > 0
and indid < 255)
drop index store.Relationship_12_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘store’)
and type = ‘U’)
drop table store
go

if exists (select 1
from sysobjects
where id = object_id(‘supplier’)
and type = ‘U’)
drop table supplier
go

if exists (select 1
from sysindexes
where id = object_id(‘supply’)
and name = ‘Relationship_4_FK’
and indid > 0
and indid < 255)
drop index supply.Relationship_4_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘supply’)
and name = ‘Relationship_3_FK’
and indid > 0
and indid < 255)
drop index supply.Relationship_3_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘supply’)
and type = ‘U’)
drop table supply
go

if exists (select 1
from sysindexes
where id = object_id(‘warehouse’)
and name = ‘Relationship_11_FK’
and indid > 0
and indid < 255)
drop index warehouse.Relationship_11_FK
go

if exists (select 1
from sysindexes
where id = object_id(‘warehouse’)
and name = ‘Relationship_10_FK’
and indid > 0
and indid < 255)
drop index warehouse.Relationship_10_FK
go

if exists (select 1
from sysobjects
where id = object_id(‘warehouse’)
and type = ‘U’)
drop table warehouse
go

/==============================================================/
/* Table: activity /
/==============================================================*/
create table activity (
Ac_y_no char(10) not null,
Ac_y_person varchar(15) not null,
Ac_y_site varchar(20) null,
Ac_y_phone char(11) not null,
constraint PK_ACTIVITY primary key nonclustered (Ac_y_no)
)
go

/==============================================================/
/* Table: adcertising /
/==============================================================*/
create table adcertising (
Ad_g_no char(10) not null,
Co_y_no char(10) null,
Op_n_no char(20) null,
Ad_g_platfirm varchar(20) not null,
Ad_g_time varchar(15) not null,
Ad_g_cost varchar(15) not null,
Ad_g_supply int not null,
constraint PK_ADCERTISING primary key nonclustered (Ad_g_no)
)
go

/==============================================================/
/* Index: Relationship_5_FK /
/==============================================================*/
create index Relationship_5_FK on adcertising (
Co_y_no ASC
)
go

/==============================================================/
/* Index: Relationship_6_FK /
/==============================================================*/
create index Relationship_6_FK on adcertising (
Op_n_no ASC
)
go

/==============================================================/
/* Table: after /
/==============================================================*/
create table after (
Af_r_no char(10) not null,
Af_r_admin char(10) null,
Af_r_name varchar(15) not null,
Af_r_salary int not null,
Af_r_phone char(11) not null,
constraint PK_AFTER primary key nonclustered (Af_r_no)
)
go

/==============================================================/
/* Index: Relationship_21_FK /
/==============================================================*/
create index Relationship_21_FK on after (
Af_r_admin ASC
)
go

/==============================================================/
/* Table: anchor /
/==============================================================*/
create table anchor (
An_r_no char(20) not null,
An_r_admin char(20) null,
An_r_name varchar(15) not null,
An_r_salary int not null,
An_r_phone char(11) not null,
constraint PK_ANCHOR primary key nonclustered (An_r_no)
)
go

/==============================================================/
/* Index: Relationship_19_FK /
/==============================================================*/
create index Relationship_19_FK on anchor (
An_r_admin ASC
)
go

/==============================================================/
/* Table: “browse” /
/==============================================================*/
create table “browse” (
Co_y_no char(10) not null,
Cl_t_no char(10) not null,
Br_e_view int not null,
constraint PK_BROWSE primary key (Co_y_no, Cl_t_no)
)
go

/==============================================================/
/* Index: browse_FK /
/==============================================================*/
create index browse_FK on “browse” (
Co_y_no ASC
)
go

/==============================================================/
/* Index: browse2_FK /
/==============================================================*/
create index browse2_FK on “browse” (
Cl_t_no ASC
)
go

/==============================================================/
/* Table: client /
/==============================================================*/
create table client (
Cl_t_no char(10) not null,
Cl_t_name varchar(15) not null,
Cl_t_site varchar(20) not null,
Cl_t_yser varchar(20) not null,
Cl_t_password varchar(20) not null,
constraint PK_CLIENT primary key nonclustered (Cl_t_no)
)
go

/==============================================================/
/* Table: commodity /
/==============================================================*/
create table commodity (
Co_y_no char(10) not null,
Co_y_name varchar(20) not null,
Co_y_unit varchar(20) not null,
Co_y_describe varchar(200) not null,
Co_y_pircture varchar(30) not null,
constraint PK_COMMODITY primary key nonclustered (Co_y_no)
)
go

/==============================================================/
/* Table: entrepot /
/==============================================================*/
create table entrepot (
En_y_no char(10) not null,
En_y_name varchar(20) not null,
En_y_site varchar(20) not null,
constraint PK_ENTREPOT primary key nonclustered (En_y_no)
)
go

/==============================================================/
/* Table: evaluate /
/==============================================================*/
create table evaluate (
Ev_e_no char(10) not null,
Cl_t_no char(10) null,
Ev_e_time varchar(15) not null,
Ev_e_content varchar(100) not null,
constraint PK_EVALUATE primary key nonclustered (Ev_e_no)
)
go

/==============================================================/
/* Index: Relationship_7_FK /
/==============================================================*/
create index Relationship_7_FK on evaluate (
Cl_t_no ASC
)
go

/==============================================================/
/* Table: expressage /
/==============================================================*/
create table expressage (
Ro_e_no char(10) not null,
Or_r_no char(10) not null,
Ex_ge_no varchar(20) not null,
Ex_ge_name varchar(15) not null,
Ex_ge_phone char(11) not null,
Ex_ge_signer varchar(15) not null,
constraint PK_EXPRESSAGE primary key (Ro_e_no, Or_r_no)
)
go

/==============================================================/
/* Index: expressage_FK /
/==============================================================*/
create index expressage_FK on expressage (
Ro_e_no ASC
)
go

/==============================================================/
/* Index: expressage2_FK /
/==============================================================*/
create index expressage2_FK on expressage (
Or_r_no ASC
)
go

/==============================================================/
/* Table: inactivity /
/==============================================================*/
create table inactivity (
Ri_t_no char(20) not null,
Ls_e_no char(10) not null,
Ln_ay_no char(10) not null,
Ln_ay_scalp char(2) not null,
Ln_ay_loss char(2) not null,
Ln_ay_stop char(2) not null,
Ln_ay_remark varchar(50) not null,
constraint PK_INACTIVITY primary key (Ri_t_no, Ls_e_no)
)
go

/==============================================================/
/* Index: inactivity_FK /
/==============================================================*/
create index inactivity_FK on inactivity (
Ri_t_no ASC
)
go

/==============================================================/
/* Index: inactivity2_FK /
/==============================================================*/
create index inactivity2_FK on inactivity (
Ls_e_no ASC
)
go

/==============================================================/
/* Table: incommodity /
/==============================================================*/
create table incommodity (
Ri_t_no char(20) not null,
Co_y_no char(10) not null,
Ln_cy_no char(10) not null,
Ln_cy_validity char(5) not null,
Ln_cy_stop char(2) not null,
Ln_cy_remark varchar(50) not null,
constraint PK_INCOMMODITY primary key (Ri_t_no, Co_y_no)
)
go

/==============================================================/
/* Index: incommodity_FK /
/==============================================================*/
create index incommodity_FK on incommodity (
Ri_t_no ASC
)
go

/==============================================================/
/* Index: incommodity2_FK /
/==============================================================*/
create index incommodity2_FK on incommodity (
Co_y_no ASC
)
go

/==============================================================/
/* Table: inquire /
/==============================================================*/
create table inquire (
Or_r_no char(10) not null,
Af_r_no char(10) not null,
Ln_re_time varchar(15) not null,
constraint PK_INQUIRE primary key (Or_r_no, Af_r_no)
)
go

/==============================================================/
/* Index: inquire_FK /
/==============================================================*/
create index inquire_FK on inquire (
Or_r_no ASC
)
go

/==============================================================/
/* Index: inquire2_FK /
/==============================================================*/
create index inquire2_FK on inquire (
Af_r_no ASC
)
go

/==============================================================/
/* Table: issue /
/==============================================================*/
create table issue (
Ls_e_no char(10) not null,
Ac_y_no char(10) null,
Co_y_no char(10) null,
Ls_e_f_time varchar(15) not null,
Ls_e_l_time varchar(15) not null,
Ls_e_remark varchar(50) not null,
Ls_e_participant int null,
constraint PK_ISSUE primary key nonclustered (Ls_e_no)
)
go

/==============================================================/
/* Index: Relationship_8_FK /
/==============================================================*/
create index Relationship_8_FK on issue (
Co_y_no ASC
)
go

/==============================================================/
/* Index: Relationship_9_FK /
/==============================================================*/
create index Relationship_9_FK on issue (
Ac_y_no ASC
)
go

/==============================================================/
/* Table: live /
/==============================================================*/
create table live (
Li_e_no char(10) not null,
An_r_no char(20) null,
Co_y_no char(10) null,
Li_e_number int null,
Li_e_time varchar(15) null,
Li_e_times varchar(10) null,
constraint PK_LIVE primary key nonclustered (Li_e_no)
)
go

/==============================================================/
/* Index: Relationship_1_FK /
/==============================================================*/
create index Relationship_1_FK on live (
An_r_no ASC
)
go

/==============================================================/
/* Index: Relationship_2_FK /
/==============================================================*/
create index Relationship_2_FK on live (
Co_y_no ASC
)
go

/==============================================================/
/* Table: operation /
/==============================================================*/
create table operation (
Op_n_no char(20) not null,
Op_n_admin char(20) null,
Op_n_name varchar(15) not null,
Op_n_salary int not null,
Op_n_phone char(11) not null,
constraint PK_OPERATION primary key nonclustered (Op_n_no)
)
go

/==============================================================/
/* Index: Relationship_20_FK /
/==============================================================*/
create index Relationship_20_FK on operation (
Op_n_admin ASC
)
go

/==============================================================/
/* Table: “order” /
/==============================================================*/
create table “order” (
Or_r_no char(10) not null,
Cl_t_no char(10) null,
Or_r_time varchar(15) not null,
Or_r_money varchar(20) not null,
Or_r_payment varchar(15) not null,
constraint PK_ORDER primary key nonclustered (Or_r_no)
)
go

/==============================================================/
/* Index: Relationship_14_FK /
/==============================================================*/
create index Relationship_14_FK on “order” (
Cl_t_no ASC
)
go

/==============================================================/
/* Table: purchase /
/==============================================================*/
create table purchase (
Or_r_no char(10) not null,
Co_y_no char(10) not null,
Pu_e_quantity varchar(20) not null,
constraint PK_PURCHASE primary key (Or_r_no, Co_y_no)
)
go

/==============================================================/
/* Index: purchase_FK /
/==============================================================*/
create index purchase_FK on purchase (
Or_r_no ASC
)
go

/==============================================================/
/* Index: purchase2_FK /
/==============================================================*/
create index purchase2_FK on purchase (
Co_y_no ASC
)
go

/==============================================================/
/* Table: putin /
/==============================================================*/
create table putin (
Pu_n_no char(20) not null,
Cl_t_no char(10) null,
Af_r_no char(10) null,
Pu_n_p_time varchar(15) not null,
Pu_n_cause varchar(50) not null,
Pu_n_solution varchar(50) null,
Pu_n_s_stop char(2) null,
Pu_n_s_time varchar(15) null,
constraint PK_PUTIN primary key nonclustered (Pu_n_no)
)
go

/==============================================================/
/* Index: Relationship_15_FK /
/==============================================================*/
create index Relationship_15_FK on putin (
Cl_t_no ASC
)
go

/==============================================================/
/* Index: Relationship_16_FK /
/==============================================================*/
create index Relationship_16_FK on putin (
Af_r_no ASC
)
go

/==============================================================/
/* Table: “risk-assessment” /
/==============================================================*/
create table “risk-assessment” (
Ri_t_no char(20) not null,
Ri_t_admin char(20) null,
Ri_t_name varchar(15) not null,
Ri_t_salary int not null,
Ri_t_phone char(11) not null,
constraint “PK_RISK-ASSESSMENT” primary key nonclustered (Ri_t_no)
)
go

/==============================================================/
/* Index: Relationship_17_FK /
/==============================================================*/
create index Relationship_17_FK on “risk-assessment” (
Ri_t_admin ASC
)
go

/==============================================================/
/* Table: rookie /
/==============================================================*/
create table rookie (
Ro_e_no char(10) not null,
Ro_e_name varchar(15) not null,
Ro_e_phone char(11) not null,
Ro_e_site varchar(20) not null,
constraint PK_ROOKIE primary key nonclustered (Ro_e_no)
)
go

/==============================================================/
/* Table: stock /
/==============================================================*/
create table stock (
St_k_no char(20) not null,
St_k_admin char(20) null,
St_k_name varchar(15) not null,
St_k_salary int not null,
St_k_phone char(11) not null,
constraint PK_STOCK primary key nonclustered (St_k_no)
)
go

/==============================================================/
/* Index: Relationship_18_FK /
/==============================================================*/
create index Relationship_18_FK on stock (
St_k_admin ASC
)
go

/==============================================================/
/* Table: store /
/==============================================================*/
create table store (
St_e_no char(10) not null,
En_y_no char(10) null,
St_k_no char(20) null,
St_e_time varchar(15) not null,
constraint PK_STORE primary key nonclustered (St_e_no)
)
go

/==============================================================/
/* Index: Relationship_12_FK /
/==============================================================*/
create index Relationship_12_FK on store (
En_y_no ASC
)
go

/==============================================================/
/* Index: Relationship_13_FK /
/==============================================================*/
create index Relationship_13_FK on store (
St_k_no ASC
)
go

/==============================================================/
/* Table: supplier /
/==============================================================*/
create table supplier (
Su_p_no char(10) not null,
Su_p_name varchar(20) not null,
Su_p_site varchar(20) not null,
Su_p_principal varchar(15) not null,
Su_p_phone char(11) not null,
Su_p_supply varchar(20) not null,
constraint PK_SUPPLIER primary key nonclustered (Su_p_no)
)
go

/==============================================================/
/* Table: supply /
/==============================================================*/
create table supply (
Su_y_no char(10) not null,
Co_y_no char(10) null,
Su_p_no char(10) null,
Su_y_time varchar(15) not null,
Su_y_quantity varchar(15) not null,
constraint PK_SUPPLY primary key nonclustered (Su_y_no)
)
go

/==============================================================/
/* Index: Relationship_3_FK /
/==============================================================*/
create index Relationship_3_FK on supply (
Su_p_no ASC
)
go

/==============================================================/
/* Index: Relationship_4_FK /
/==============================================================*/
create index Relationship_4_FK on supply (
Co_y_no ASC
)
go

/==============================================================/
/* Table: warehouse /
/==============================================================*/
create table warehouse (
Wa_e_no char(10) not null,
Co_y_no char(10) null,
En_y_no char(10) null,
Wa_e_time varchar(15) not null,
Wa_e_set char(5) not null,
constraint PK_WAREHOUSE primary key nonclustered (Wa_e_no)
)
go

/==============================================================/
/* Index: Relationship_10_FK /
/==============================================================*/
create index Relationship_10_FK on warehouse (
Co_y_no ASC
)
go

/==============================================================/
/* Index: Relationship_11_FK /
/==============================================================*/
create index Relationship_11_FK on warehouse (
En_y_no ASC
)
go

6、运行结果

7、遇到的问题

问题：

主播-直播-商品的逻辑是多对多的，如果不把直播使用实体表示，而只是使用联系表示的话，直播表中会出现主码重复问题。

分析：

起始原因是当同一个主播多次带货同一件商品时，属性的主码An_r_no、Co_y_no会重复。

解决方法：

我把直播变成实体，并以直播编号为主码，An_r_no、Co_y_no设置为属性，让直播这个实体与主播和商品呈现一对多的关系，解决了这个问题

广告投放、举办活动等问题跟上面类似