MySQL数据分析进阶（十二）设计数据库——PART3

※食用指南：文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记，笔记整理比较粗糙，主要目的自存为主，记录完整的学习过程。（图片超级多，慎看！）

【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2

目录：

第十二章：设计数据库——PART3

19、PROJECT（1）：FLIGHT BOOKING SYSTEM——项目一航班订票系统

20、PROJECT（1）：CONCEPTUAL MODEL——项目一：概念模型

21、PROJECT（1）：LOGICAL MODEL——项目一：逻辑模型

22、PROJECT（2）：VIDEO RENTAL APPLICATION——项目二：视频租赁应用

23、PROJECT（2）：——CONCEPTUAL MODEL——项目二：概念模型

24、PROJECT（2）：——LOGICAL MODEL——项目二：逻辑模型

---

第十二章：设计数据库——PART3

19、PROJECT（1）：FLIGHT BOOKING SYSTEM——项目一航班订票系统

为航班订票系统设计一个数据库

在附件PDF中可以看到这个系统要生成的机票的一个示例

根据这些信息，为系统设计一个数据库

20、PROJECT（1）：CONCEPTUAL MODEL——项目一：概念模型

自己做的：

Mosh答案：

先在实体之间建立一种关系，在逻辑模型的时候再明细它们的关系，都先设定为多对多

先不用管标准化问题，随时可以回来修改，先把基础建对

围绕四个实体建立概念模型

21、PROJECT（1）：LOGICAL MODEL——项目一：逻辑模型

优化元素建立逻辑模型

①实体之间的关系

乘客&机票：一对多

机票&航班：一对多

机场&航班：多对多

修改箭头即可

②添加属性

Flight number有数字和字母，使用字符串

飞行时间最好用分钟来计时，更加直观——飞行分钟

③新增Ariline

航空公司&航班：一对多

舱位&机票：一对多

两个问题：

①机场实体

这种设计可能导致重复的城市、州、国家，是否应该把属性拎出来

减少重复但是数据会变得很零碎，也总需要把三四张表连接到一起，才能查到某个城市的机场，这样的连接会影响应用程序的性能

对设计作“去”标准化处理，将经常连接到一起的表，合并成一张表

就不必总是把表连接到一起

结合项目，世界也不会有几百万个机场，也不是每个城市也有机场，数量有限，这里出现的重复不算很大问题

只添加城市和州属性就可以

②机场&航班：多对多关系

关系型数据库中没有多对多关系，只有一对一、一对多

出现多对多的关系，要分解成2组一对多关系

通过一张链接表实现，一张代表两个实体或者两张表之间关系的表

但链接表的问题是：允许一个航班与两个以上的机场有联系。因为链接表可以有多对多的关系（不建议使用）

添加两个属性：起飞机场、到达机场

当我们基于这个逻辑模型建构一个实体模型时

需要为DepartureAirportId（interger）、ArrivalAirportId（interger）这两列添加外键

以上就是基于我们对这个问题的了解所做的逻辑模型，不要把它当作这类问题的最好办法或者唯一办法，每种解决方案都是有利有弊

随着越了解这个问题，这个业务领域，模型也会变

（实体模型自行建立）

22、PROJECT（2）：VIDEO RENTAL APPLICATION——项目二：视频租赁应用

应用程序将在一个视频出租商店使用（Vidly）

使用附件PDF，阅读要求，为应用程序设计数据模型

 

①不同级别的权限给不同的用户：

商店经理：添加/更新/删除电影列表；设置每部电影的库存、每日租金

收银员：对电影清单有一个只读的视图，管理顾客名单、租的电影

②业务方面：

结账：顾客要一部或者多部电影，搜索电话号码查询顾客ID

第一次来的需要在系统中登记全名、电子邮件、电话号码；扫描所要电影，记录在系统中

每部电影有个10位的条形码

归还：如果丢失，收取电影日租金的5倍金额

收银员标记电影丢失，减少库存（重点在库存中的电影数量、收取顾客多少费用）

其他电影收费：天数*每日租金

不定期发优惠券，顾客归还电影时可以带一张优惠券

顾客可能分次归还所租电影

需要能追踪

-- 热门电影

-- 优质顾客

-- 每日/每月/每年收入

23、PROJECT（2）：——CONCEPTUAL MODEL——项目二：概念模型

自己做的：

Mosh答案：

24、PROJECT（2）：——LOGICAL MODEL——项目二：逻辑模型

现在需要考虑存储数据

①权限问题

收银员和店长无法拥有一致的权限集，每个用户的权限集不同，必须确保我们的用户拥有一致的权限

以上模型对应用来说要求了过高的细节度，更使用与需要完全控制到每个用户权限的应用程序

引入新实体——root

在下次注册新用户时，只需要把用户添加到特定组或身份里，用户就能自动获取身份的所有权限，用户就能够拥有一致的权限集

②管理权限

对电影的管理权限有点太细化，不需要那样的控制度，Permissions这个实体没有必要，只需要知道用户身份

在应用程序中写一个简单的IF语句，如果用户是店长，将开启所有功能；如果是收银员，或许需要隐藏或禁用某些功能

🔺例如在旅行，需要一个房间住一晚，预算100元，假设一个人来，只需要一间简单的房间，一张单人床

刚好现在就有一间房间，有大床、阳台、浴缸、wifi、游戏机，但一晚要500元

你不会为那些不需要的功能多花400元

🔺回到项目，所有未来可能会用上的附加功能，总有人要先为它买单——公司

不要过度设计，不要因为认为必要的功能浪费别人的钱，也是管理复杂性的问题

复杂的设计会一致贯穿于这个应用程序，每次需要修复一个bug或引入一个新功能时，都必须处理这个应用程序设计里不需要的复杂性

③实体关系

租赁&优惠券：一次租赁最多可以用一张优惠券，一张优惠券可以用于不同租赁（优惠券是可空类型）

租赁&电影：每次租赁只能租一部电影，一部电影可以被租很多次

租赁&顾客：同上

————TBC