[Database Course Design] Phase Four: Database Design

create database RetailDB;
use RetailDB;

-- 供应商表
create table t_doc_supplier
(
	supplierNumber varchar(40) primary key comment '供应商号',
	supplierName varchar(40) not null  comment '供应商名称',
	contact varchar(40) not null comment '联系方式',
	address varchar(40) not null comment '地址'
)engine=innodb default charset=utf8;

-- 员工表
create table t_doc_staff
(
	staffNumber varchar(40) primary key comment '员工编号',
	staffName varchar(40) not null comment '员工名',
	contact varchar(40) not null comment '联系方式',
	address varchar(40) not null comment '家庭住址',
	workDate varchar(40) not null comment '入职日期',
	salary   decimal(20) not null comment '工资'
)engine=innodb default charset=utf8;

-- 订单表
create table t_doc_order
(
	orderNumber varchar(80) comment '采购单号',
	staffNumber varchar(40) not null comment '员工号',
	supplierNumber varchar(40) not null  comment '供应商号',
    primary key(orderNumber,staffNumber,supplierNumber),
	state varchar(10) not null comment '审核状态',
	orderDate varchar(40) not null comment '采购日期',
	remarks varchar(100)  comment '备注', 
  foreign key (staffNumber) references t_doc_staff(staffNumber),
  foreign key (supplierNumber) references t_doc_supplier(supplierNumber)
 
)engine=innodb default charset=utf8;

-- 商品表
create table t_doc_commodity
(
	commodityNumber varchar(40) primary key comment '商品编号',
	commodityName varchar(40) comment '商品名称',
	retailPrice decimal(20) comment '零售价',
	memberPrice decimal(20) comment '会员价',
	createDate varchar(40) comment '添加日期'
)engine=innodb default charset=utf8;

-- 供应价格对照表
create table t_doc_supplyControl
(
	commodityNumber varchar(40) comment '商品编号',
	supplierNumber varchar(40) comment '供应商号',
	purchasePrice decimal(20) comment '进价',
	primary key(commodityNumber,supplierNumber),
	foreign key (commodityNumber) references t_doc_commodity(commodityNumber),
    foreign key (supplierNumber) references t_doc_supplier(supplierNumber)
)engine=innodb default charset=utf8;

-- 仓库表
create table t_doc_warehouse
(
	warehouseNumber varchar(40) primary key comment '仓库号',
	warehouseName varchar(40) not null comment '仓库名称',
	wareAddress varchar(40) not null comment '仓库地址',
	staffName varchar(40) not null comment '仓库管理员名',
	contact varchar(40) not null comment '联系方式'
)engine=innodb default charset=utf8;

-- 入库表
create table t_doc_inventory
(
	inventoryNumber varchar(80)  comment '入库单号',
	warehouseNumber varchar(40) comment '仓库号',
	staffNumber varchar(40) comment '员工号',
	primary key(inventoryNumber,warehouseNumber,staffNumber),
	state varchar(10) comment '审核状态',
	inventoryDate varchar(40) comment '入库日期',
	remarks varchar(100)  comment '备注',
  foreign key (staffNumber) references t_doc_staff(staffNumber),
  foreign key (warehouseNumber) references t_doc_warehouse(warehouseNumber)
)engine=innodb default charset=utf8;

3.1 Logic design

Three models of logical design: relational data model, hierarchical data model, network data model, and object-oriented data model
Insert picture description here

图3.1.1 整体关系模型

Insert picture description here


图3.1.2 供应商和商品的局部关系模型

Insert picture description here


图3.1.3 供应商和员工的局部关系模型

Insert picture description here

图3.1.4 员工和仓库的局部关系模型

3.2 Physical design

Physical model: a higher-level description of storage structure and access mechanism, describing how data is stored in a computer, how to express information such as record structure, record sequence, and access path.
Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here

3.3 Concept design

Conceptual model: It only describes the characteristics of information and emphasizes semantics, and does not involve the representation of information in the computer. It is the first level of abstraction from the real world to the information world. The most commonly used is the Entity Relationship Model. (ER diagram)
Insert picture description here


Insert picture description here


Insert picture description here
Insert picture description here


Insert picture description here


网上的一些设计模糊不清,终于在我查了无数资料以后,知道了这三个设计的本质区别,希望对您有用!

[In addition, due to some previous mistakes, there is a problem with the typesetting order here. It should be conceptual design first, then logical design, and finally physical design]