第五章数据建模和设计10分设计题

5.1 语境关系图

Q：为什么要加强数据模型的评审？
A：数据模型是数据架构的一部分，数据架构是打地基工作，一旦上线很难改动，模型审批减少遗漏需求，提高模型的质量。
Q：概念逻辑模型由谁牵头？物理模型由谁牵头？
A：概念逻辑模型由业务人员牵头，物理模型由技术人员牵头。

难点：
1.选择合适的建模方法：根据使用场景、数据量、使用偏好选择适当的模型。
2.清晰记录血缘关系：血缘关系对之后的变更进行分析和影响至关重要。
3.明确模型边界：事先明确建设模型的范围以及边界。
4.满足数据架构：符合企业架构和数据架构要求，并且满足数据质量标准。
5.加强模型评审：模型评审减少遗漏需求，提高模型的质量。
6.加强模型维护：细致的模型维护有效减少版本覆盖，误操作等带来的风险。

5.1 建模评分标准

三范式为物理模型使用，不在逻辑模型中，但最好在逻辑模型中事先考虑。

例子：
按照下列场景，请设计相关的 ER 逻辑模型：
初次访问网购网站的访客，如试图在网站上购物，则需要申请会员。申请会员时需要填写会员姓名、性别、身份证号码、联系电话、会员ID、密码等信息。会员申请成功后，通过会员 ID 和密码便可登录网购网站，在网站上随意选择心仪的商品，在网站上可以看到的商品分为三大类，分别是电子产品、服装、生活日用品等；可以看到的产品信息包括，商品图片、名称、价格、产地、型号等信息； 价格信息中会显示折扣率，针对不同的商品，折扣率不同。产品销售方式包括单品销售和套餐销售，套餐是任意单品的组合产品，且套餐产品的价格不等于单品价格的和。客户选择自己心仪的商品后，可以直接订购，也可以将其放入购物车中，以便暂时保存。在直接订购界面和购物车中，客户可以随意删除已选定的商品，同时也可以随意调整待购买商品的数量。客户对最终确定需要购买的商品可以订购，在订购的过程中，需要填写快递地址信息，其中包括，收件人、地址、联系电话等信息，填写完成后，直接进入支付环节。在支付环节中，提供多种支付方式，分别为信用卡、支付宝、储蓄卡、微信等方式。

1.识别实体 9-12 个会员、商品分类、产品信息、价格信息、产品销售方式、套餐、购物车、订购、支付、支付方式。
2.属性会员：
姓名、性别、身份证号码、联系电话、会员ID、密码；商品分类：电子产品、服装、生活日用品；产品信息：商品图片、名称、价格、产地、型号等；价格信息：产品、折扣率；产品销售方式：单品销售和套餐销售；套餐：单品组合、金额；购物车：产品、数量；订购：收件人、地址、联系电话等信息；支付方式：信用卡、支付宝、储蓄卡、微信等方式。
3.关系
假定商品分类和商品是 1 对多关系，商品不可属于多个分类。
4.标识符

1.实体&Q 如何来选择和建立实体？（只能多，不能少）

基于流程，识别实体，先画流程图。

2.属性（拆分成最小单位，检验是否存在唯一值）（概念模型没有属性，逻辑模型有属性）

构成事物的本质的一些固有的特性或者性质（年龄不应该作为属性），姓名拆分→姓+名。
如果在数仓，年龄可以作为单独属性判断。
3.关系（表与表之间的关系，4 种关系，1 对多，多对1，1 对1，多对多）70%分数


4.标识符/主键
标识符（也称为键）是唯一标识实体实例的一个或多个属性的集合。根据键的结构（单一键、组合键、复合键、代理键）和功能（候选键、主键、备用键）进行分类。

Q：身份证号码可否作为标识符？
A：可以，但最好不要，身份证号码作为隐私数据，用在sql 语句中，可能增加数据泄露的风险。
Q：每个表都应该有标识符？
A：错，在数仓中许多实体不存在标识符（ETL 进入数仓前，可以保证数据的干净），在 OLTP 环境中，都应该有标识符，但不是必须的。

5.命名规则（不写英文不扣分，实体命名准确、清晰、完整）
6.设计说明，备注：假定 A 与 B 是 1 对多关系

5.2 F4 3NF（三范式）

逆规范化（逆三范式）：牺牲空间、换取时间OLTP 遵循三范式，OLAP 逆规范化。
第一范式（1NF）：确保每个实体都有一个有效的主键，每个属性都依赖于主键，而且消除冗余的分组，以确保每个属性的原子性（不能有多个值存在）。
第二范式（2NF）：确保每个实体都有最小的主键，每个属性都依赖于完整的主键。
第三范式（3NF）：确保每一个实体都没有隐藏的主键，每个属性都不依赖于键值以外的任何属性（仅依赖于完整的主键）。

逆规范化原因【DMBOK2 P110】交易型中不会做逆规范化：
1.提前组合来自多个其他表的数据，以避免代价高昂的运行时连接。
2.创建更小的、预先过滤的数据副本，以减少昂贵的运行时计算和/或大型表的扫描。
3.预先计算和存储昂贵的数据计算结果，以避免运行时系统资源竞争。
逆规范化还可以用于根据访问需要将数据划分为多个视图或副本表来加强用户安全性。

5.3 星型设计、雪花设计、星际设计【理解 DMBOK2 P102 P110】

Q：雪花设计、星型设计主要差别
A：星型没有层次结构、网络结构（是大表、宽表），雪花结构有层级结构（年→月→日，华东大区→省→市）。实际数仓情况为星型+雪花。

雪花模型（Snowflaking）：将星型模型中的平面、单表、维度结构规范为相应的组件层次结构或网络结构。在维度数据建模中，逆规范化被称为折叠（Collapsing）或合并（Combining）。如果每个维度都被折叠成一个结构，生成的数据结构被称为星型模型（StarSchema），如果维度没有折叠，则生成的数据结构被称为雪花（Snowflake）。

多维模型通常称为星型模型，由事实表（包含有关业务流程的定量数据，如销售数据）和维度表（存储与事实表数据相关的描述性属性，为数据消费者解答关于事实表的问题，如这个季度产品 X 卖了多少）组成。事实表与许多维表关联，整个图看上去像星星一样。多个事实数据表将通过“总线”共享公共的维度或遵循一致性的维度，类似计算机中的总线。通过插入遵循维度的总线，可以将多个数据集市集成为企业级的数据集市。

星际模型：2 个主题域，应该避免。【不考，就是指跨主题域的】

5.4 F3&Q 如何解决 SCD 问题？【3 种方法 ORC，DMBOK2 P102】

渐变类维度 slow changing dimensions
1. 覆盖 Overwrite，新值覆盖旧值。
2. 新行 New Row，新值写在新行中，旧行被标记为非当前值。
3. 新列 New Column，一个值的多个实例列在同一行的不同列中，而一个新值意味着将系列中的值向下一点写入，以便在前面为新值流出空间。最后一个值被丢弃。

5.5 F5SuperType（超类）和 SubType（子类）

5.6 F1 不同种类的数据建模【6 种模式 关系、多维、面向对象、事实、时间序列、NoSql，3 层次概念、逻辑、物理模型】

数据常见的 6 种模式：关系模式、多维模式、面向对象模式、事实模式、时间序列模式、NoSQL 模式。
每种模式分为 3 层模型：概念模型、逻辑模型、物理模型。
常见的 6 种数据建模方法：

5.7 F2 数据标准如何建立？【命名规则、约束规则】

参考答案：
（1）定义数据模型对象的标准命名格式列表。
（2）定义所有数据模型对象的标准名称。
（3）定义数据建模所有元数据属性的列表和描述。

5.8 F6&Q 如何利用行标来建设数据标准/哪里可以找到各种建模标准？【可参考第十章主数据】

在公开网页上可以找到国家标准（http：//www.sac.gov.cn）、国际标准或行业标准，行业标准需要购买。从文档、当前业务描述材料、访谈、相关专业书籍中收集。医药卫生 ICD、零售 ARTS、运营商 SID、保险行业 ACORD… 联合国→国标→行标→团标→自建。

Q1：OLAP 和 OLTP 差别有哪些？【多选题】
A1：面向应用：OLTP 用于日常交易处理，OLAP 用于查询、分析、决策；访问模式：OLTP 用于简单小事务，操作少量数据，OLAP 用于复杂查询、大量数据；
数据：OLTP 数据一般为当前最新数据（实时），数据规模GB，OLAP一般为39历史数据（批量），数据规模 TB-PB；
数据存储：OLTP 一般满足三范式，OLAP 一般逆规范化，反范式，星型模型；用户：OLTP 用户一般为操作人员、低层管理人员，OLAP 一般为决策人员，高级管理人员；
DB 设计：OLTP 的 DB 设计为面向应用，OLAP 设计为面向主题；
软件技术：OLTP 软件技术为数据库，OLAP 软件技术为数据仓库。

Q2：index 索引（快速搜索）用在 OLAP 还是 OLTP？
A2：OLAP。

Q3：逆规范化，用在 OLAP 还是 OLTP？

A3：OLAP。用作 OLAP 的软件：Netezza，Hadoop，Hiva（开源，不建议用，性能很慢）。

Q 在逻辑模型的基础上，物理模型的开发还需要做哪些工作？
创建物理模型一定要有命名规则，一定要考虑技术实现、技术选型（oracle、sqlserver）白皮书一般到逻辑模型。

建立物理模型的活动包括：
（1）解决逻辑抽象；
（2）添加属性细节；
（3）添加属性引用对象；
（4）指定代理键；
（5）逆规范化；
（6）建立索引；
（7）分区；
（8）创建视图。

Q MOLAP 还是 ROLAP？（ROLAP）

数据量大的情况用 MOLAP 容易出问题，因此用 ROLAP 基于关系型。
（1）关系型联机分析处理（ROLAP）。ROLAP 通过在关系数据库（RDBMS）的二维表使用多维技术来支持 OLAP。星型架构是 ROLAP 环境中常用的数据库设计技术。
（2）多维矩阵联机分析处理（MOLAP）。MOLAP 通过使用专门的多维数据库技术支持 OLAP。
（3）混合联机分析处理（HOLAP）。它是 ROLAP 和MOLAP 的结合。HOLAP实现允许部分数据以 MOLAP 形式存储，而另一部分数据存储在ROLAP中。控件的实现方式各不相同，设计师对分区的组合也各有不同。