数据库设计

我们先来看这样一个问题
假如遇到下面这样的设计需求，我们如何完成数据库的设计呢？

设计需求：

假设商品经销商有基本实体集：供应商Vendors、产品目录Products、顾客信息Customers和顾客订单Orders。

各个实体的属性集：

(1)供应商Vendors：

供应商ID(vend_id),供应商名(vend_name),供应商地址(vend_address),供应商所在城市(vend_city),供应商所在州(vend_state),供应商地址邮政编码(vend_zip)
,供应商国家(vend_country)

(2)产品目录Products：

产品ID(prod_id),产品名(prod_name),产品价格(prod_price),产品描述(prod_desc)

(3)顾客列表Customers：

顾客ID(cust_id),顾客名(cust_name),顾客地址(cust_address),顾客城市(cust_city),顾客所在州(cust_state),顾客地址邮政编码(cust_zip),顾客所在国家(cust_country),顾客联系名(cust_contact),顾客电子邮箱(cust_email)

(4)顾客订单Orders:

顾客订单号(order_num),订单日期(order_date)

实体间的联系：

每个供应商能供应多个产品；顾客可以下多个订单，订单可以预定多件商品，而商品可以对应不同订单。

问题

请根据以上需求完成如下操作：
（1）对以上描述进行分析，进行数据库概念模型的设计（即确定各个实体、属性及联系并绘制E-R图）。
（2）将（1）中概念模型转换成关系型逻辑模型，并标出各个关系模式的主码和外码。
（3）将所转换成的关系型数据模型在SQL Server中进行实现。

我想可能有人看到这里脑子就有点疼了，这是啥玩意，我们究竟要怎么开始动手写

注意上面的红色关键字
不用担心，现在跟我一起开始学习

完成步骤：

根据题目要点我们先讲讲一些必要知识
1、什么是数据库概念模型
2、什么是关系型逻辑模型，什么是主码和外码
3、如何从概念模型转换成关系型逻辑模型
4、如何利用SQL语句在DBMS中实现（不一定是SQL Server 你也可以使用mysql等）

1，什么是数据库概念模型？

谈到这个，得先知道什么是概念模型

抽象意义上
在这里插入图片描述
虽然现实中的信息不能直接转化为计算机中的数据

但是我们可以通过概念模型向DBMS支持的数据模型的转换来实现这一步骤（概念模型向DBMS支持的数据模型的转换，本质上是对信息世界的进一步抽象，是信息世界的形式化和数据化，反映了数据之间的联系和数据的共性特征，由此形成由数据模型描述的数据世界）

而数据模型包括了概念模型，逻辑模型，物理模型三部分
数据模型
也就是说概念模型是数据模型的一类
而在数据库中我们讨论最多的概念模型便是ER模型（Entity-relationship model），E-R模型在数据库设计中被广泛用作数据建模的工具
E-R模型
相关概念解释：
（1）实体：是存在于用户组织中的抽象的但有意义的“事物”
在上面问题中 指的是供应商Vendors、产品目录Products、顾客信息Customers和顾客订单Orders
（2）属性：是指实体集中所有试题所具有的共同特征
在上面问题中 指的是供应商ID(vend_id),供应商名(vend_name),供应商地址(vend_address)等
（3）码：一般把能够唯一地标识实体集中每一个不同实体的一个或一组属性称为实体集的标识码。（其实这便是主码或叫做主键）
在上面问题中 指的是比如供应商的实体集中供应商ID(vend_id)便是标识码
（4）联系：实体之间的联系，是指实体集之间有意义的相互作用及相对应关系。
在上面问题中 指的是每个供应商能供应多个产品；顾客可以下多个订单，订单可以预定多件商品，而商品可以对应不同订单这体现了1对多，多对1 ，多对多等对应关系

第一步，数据库概念模型就解释完成了
简略的说，数据库概念模型一般指的就是E-R模型，它其中与实体，属性，码，联系等相关
可能我解释的可能很抽象 ，但是我们已经可以根据我们对数据库概念模型的理解完成第一小题啦！，接下来就是实现过程，可以跟着接下来对上面内容进一步理解

（1）对以上描述进行分析，进行数据库概念模型的设计（即确定各个实体、属性及联系并绘制E-R图）

画之前看看E-R图注意要点：
在这里插入图片描述
即E-R图里面得包含以上四点
在这里插入图片描述
实体在E-R图画成矩形
联系在E-R图里画成菱形
两实体联系比值在连线上标识
属性在E-R图里画成椭圆
在这里插入图片描述

第一小题的E-R图如下：
E-R图

我们可以发现每个实体，比如vendors供应商上连接着他的属性集的每个属性，同时与其他实体比如products产品目录有联系——“供应”,一个供应商能提供多件商品，所以在连线上是1：n
这样就把我们上面解释的实体 属性 联系等都体现出来了

但是不知道你们有没有发现在products和orders之间的联系预定上居然连接着三个属性，这是因为什么呢？？？
这个我们接下来讲完 3、概念模型转换到关系模型 再进一步解释

2、什么是关系型逻辑模型，什么是主码和外码

在我们介绍数据模型的时候我们说数据模型包含概念模型，逻辑模型，物理模型
而逻辑模型便包括了我们要说的关系模型在这里插入图片描述
在关系模型中，一个二维表格及其数据就构成了一个关系。
而我们除了用表格表示，我们一般会用关系模式表示
例如：
在这里插入图片描述
而其中主码是一个能唯一标识一个元组的属性。
这里比如是学生中的学号S#
而关于我们的题目主码则是vend_id，prod_id，cust_id等

而其中外码是如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
比如这里学号S#是学习关系中的一个属性，但是S#同时也是学生关系的主码，所以这个属性为学习关系中的外码
而关于我们的题目外码则是vend_id，cust_id等

3、如何从概念模型转换成关系型逻辑模型

ok，我们已经到了最重要的转换过程，离解决第二小问只有一小步，在此之前我们先结合我们学的前两点了解之前留下的小问题
为什么在products和orders的联系“预定”上会有属性与它连接？

这得先从联系预定的n：m说起
关于联系的比例
1:1

遇到1:1 关系的话在两个实体任选一个添加另一个实体的主键即可。

1:N

1:N 遇到 1:N 关系的话在N端添加另一端的主键，假如有学生和班级两个实体，一个班级可以容纳多个学生，但是一个学生只能选择一个班级， 因此班级和学生是1:N的关系，现在要转换为关系模型， 我们只需在学生的这端加上班级的唯一标识即可，这样做的原因是，因为一个学生只能有一个班级，班级是相对学生唯一的。

N:M

遇到N:M我们需要将联系转换为实体（问题的关键！！！！解释了为什么联系能够连接属性，因为他已经变成了一个实体），然后在该实体上加上另外两个实体的主键，作为联系实体的主键，然后再加上该联系自身带的属性即可。例如有学生和老师两个实体， 一个学生可以由多名老师来授课，一名老师也可以授课多名学生，它们是M:N关系的，假如联系为授课，该联系上有成绩属性，因此当我们把它转换为关系模型时，我们把联系转换为联系实体，并添加学生实体的主键（学号）和教师实体的主键（教师编号）作为自己的主键，值得注意的是，授课实体的外键分别是学号和教师编号，但是它的主键是（学号，教师编号），另外它还拥有自己的一个属性成绩。

1:1:N

这是三元联系的对应关系，但是当转换为关系模型时，和1:N的情况是差不多的。我们只需将N端添加另外两端的主键即可。

M:N:P

这种三元联系的三种多对应关系，看上去很复杂，其实转换起来并不是那么复杂了，我们要做的仅仅是将其中的联系转换为联系实体，然后在联系实体上添加M端N端P端的主键，然后加上联系实体自身的属性，就行了。

举个例子：
在这里插入图片描述

这是一份关于商店商品仓库的ER图。

先看仓库和商品之间是M:N的关系，于是我们首先想到的应该是
把联系 库存转换为库存实体。
库存 （仓库号，商品号，日期，库存量）
然后是商品实体和仓库实体
商品（商品号，商品名，单价）
仓库（仓库号，仓库名，地址）
这样就实现了从概念模型到关系模型的转换（注意联系库存除了把两个实体的主键属性带上了以外还带上了原本自身就有的属性）

搞定上面内容后
我们来看看第二题怎么实现

（2）将（1）中概念模型转换成关系型逻辑模型，并标出各个关系模式的主码和外码。

第二步：将（1）中概念模型转换成关系型逻辑模型

在这里插入图片描述

根据E-R图进行转换

其中红色为主键，绿色为外键

(1)供应商Vendors：
（vend_id，vend_name ，vend_address，vend_city，vend_state，vend_zip，vend_country）

(2)产品目录Products：
(prod_id, vend_id ,prod_name, prod_price, prod_desc)

(3)顾客列表Customers：
(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

(4)顾客订单Orders:
(order_num,cust_id order_date)

(5)预定OrderItems:
(order_num,order_item,prod_id,quantity,item_price)

希望大家可以认真对着E-R图和关系认真比对看看，理解转换原理

4、如何利用SQL语句在DBMS中实现（不一定是SQL Server 你也可以使用mysql等）

这里实际上就是解决第3小问

（3）将所转换成的关系型数据模型在SQL Server中进行实现。

首先建成数据库
在这里插入图片描述
在这里插入图片描述
之后就是插入数据

CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  primary key(cust_id)
);
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL ,
  primary key(order_num),
  foreign key(cust_id)
  REFERENCES Customers(cust_id)
);

CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  primary key(vend_id)
);

CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL,
  primary key(prod_id),
  foreign key(vend_id)
  references Vendors(vend_id)

);

CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  primary key(order_num,order_item),
  foreign key(order_num)
  references Orders(order_num),
  foreign key(prod_id)
  references Products(prod_id)
);

第三问就轻松搞定啦

结语

通过上面我们解决了一个如何设计数据库的一个具体例子，希望我能帮到你们，如果有什么不对的地方，欢迎指正，也欢迎在评论区提问，谢谢大家！

# sql # mysql # 数据库 # sqlserver
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

世界读书日 | 开发者必读书单重磅来袭，华为云DTSE专家天团力荐

avatar 华为云开发者联盟
cover

【项目实战经验】基于openEuler22.03搭建openGauss Datakit 5.1.1

avatar 华为云开发者联盟
cover

重磅新品发布！云耀数据库HRDS，享受轻量级的极致体验

avatar 华为云开发者联盟