技术解惑数据库设计的那些事

　　良好的数据库设计能够为用户和各种应用系统提供一个信息基础设施和高效率的运行环境，满足各种用户的需求，并对应用程序的信息管理和数据操作产生重要的影响。作为整个项目搭建中最为复杂的过程，设计出一个良好的数据库结构并不是一件简单的事： 对于小型项目而言，业务需求更易理解。因此，数据库设计者直接就能给出要构建的关系、关系的属性以及其上的约束。 对于复杂的、大型应用而言，业务逻辑往往层层嵌套，通常没有一个人能够理解应用所有的数据需求并直接给出最终的数据库设计。
　　可见，掌握一门数据库设计的方法对于项目搭建而言还是非常必要的。本期葡萄城公开课，我们特别邀请到了《精通SQL Server》的作者——罗会涛先生，就数据库设计的入门概念、实体关系设计、SQL语句等基础知识进行讲解，结合实际案例，让你秒变数据库设计大神。 一、数据库设计的特点&基本概念
　　数据库是指按照数据结构来组织、存储和管理数据的仓库。
　　数据库基本结构
　　数据库一般按照数据模型、存储和访问方式，以及用途进行分类。
　　按数据模型划分： 层次结构模型 网状结构模型 关系结构模型
　　按存储和访问方式划分： 关系型桌面型：SQLite, MS Access网络型：Oracle, MySQL, MS SQL Server 非关系型键值数据库：Amazon Dynamo列式数据库：Hbase文档数据库：MongoDB图形数据库：Neo4j
　　按用途划分： OLTP数据库 讲究事务完整性ACID OLAP数据库 数据仓库 基本只读
　　不管是什么类型的数据库结构 ，在设计数据库时，都会遵循以下基本规律：三分技术、七分管理和十二分基础数据。其中，管理指的是数据库设计的项目管理和企业（即应用部门）的业务管理；基础数据指的是针对数据收集、入库和更新的动作。
　　对于常见数据库而言，设计过程包括以下阶段： 完整的刻画未来数据库用户的数据需求 选择数据模型，并采用所选数据模型的概念将需求转化为数据库的概念模式 将抽象数据模型转化为数据库实现： 逻辑设计：将高层概念模式映射到将使用的数据库系统的实现数据库模型 物理设计：指明数据库的物理特征，包括文件组织格式和索引结构的选择
　　下面，我们将以关系型数据库为例，讲解数据库的实体关系设计以及SQL语句执行。本期课程，对于刻画用户的数据需求和物理设计并不会过多的介绍。因为数据需求来自于需求分析，这在软件工程中是一个很大的过程；而物理设计和所选择的 DBMS 有着很大的关系。 二、关系型数据库中的基本概念
　　表（Table）：表是数据的有序排列，强调其数据必须按照行和列排成一个矩形。
　　行（Row）：=记录（Record）
　　列（Column）：=字段或属性
　　主键（Primary Key，简称PK）：被挑选出来，作表的行的惟一标识的候选关键字。一个表只有一个主关键字。主关键字又可以称为主键。主键可以由一个字段，也可以由多个字段组成，分别成为单字段主键或多字段主键。主键存在的目的是为保证实体的完整性。
　　外键（Foreign Key，简称FK）：外键是指引用另外一个表中的一列或多列数据，被引用的列应该具有主键约束或者唯一性约束。外键用来建立和加强两个表数据之间的连接，用来保证参照完整性/引用完整性。
　　三、实体关系模型
　　实体关系模型（Entity-relationship model，简称ER模型）由美籍华裔计算机科学家陈品山发明，是概念数据模型的高层描述所使用的数据模型或模式图。ER模型常用于信息系统设计中；在概念结构设计阶段用来描述信息需求和/或要存储在数据库中的信息的类型。
　　在基于数据库的信息系统设计的情况下，在逻辑设计阶段，概念模型要映射到逻辑模型如关系模型上；在物理设计期间映射到物理模型上。
　　对于实体关系，有一个非常普遍的错误概念，即认为关系指表之间的关系。实际上，关系与表这个术语几乎是同义词。
　　实体关系可以是一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始单据对应且只对应一个实体，如：
　　1、一对一：一个班主任只属于一个班级，一个班级也只能有一个班主任应用场景：适用于属性过多的实体，但不希望单个表有过多字段，如人员基本信息、人员档案信息不够全面，特殊条件如班级中途更换班主任等无法满足
　　2、一对多：一个顾客对应多个订单，而一个订单只能对应一个客户
　　3、多对多：一个学生有多个课程的成绩，一个课程也有多个学生来上四、业务系统数据库表设计步骤
　　实现从编码数据表（Master Table）到业务数据表（Transaction Table）的转变。
　　1、编码数据表（Master Table）数据库设计之初设计目的是捕捉系统中的实体对象设计Master表就是描述系统中的实体。如：用户、会计科目、客户等
　　2、业务数据表（Transaction Table）一笔业务（或称一笔交易）是指Master表中的实体在系统中的一个活动这些活动会被捕捉（记录）到业务数据表中一般地，业务数据表都有指向（引用）Master表的外键五、典型数据库表设计
　　了解了数据库的基本概念，我们来看几张典型的数据库表。
　　成绩单：
　　1、Master表年级：年级编码、名称班级：班级编码、名称、年级编码学生：学号、姓名、性别考试科目：编码、名称达线情况：不需要考试：不易识别的实体编码、考试名、日期
　　2、Transaction表：成绩表考试编码、学号、科目编码、分数不要【语文】【数学】字段不要【总分】字段
　　销售订单：
　　1、Master表客户，用户（制单人），业务员不易识别：部门商品
　　2、Transaction表
　　订单头表订单编号（主键） 、日期客户ID，制单人（用户），业务员ID不易识别：订单状态（订购，发货，完成）不宜判断：合计金额
　　订单行表订单编号、行号（多字段组合主键）商品ID，数量，单价金额：不要
　　工资表：
　　1、Master表员工，部门数据类型，工资项目工资表模板，工资表模板项目
　　2、Transaction表
　　工资表工资表ID（主键） 、年月模板ID状态（已发放=不能修改）
　　工资表数据记录ID（主键）工资表ID，员工ID，工资项目ID（唯一索引）年月（可选）工资项目数据数值工资项目数据文本工资项目数据日期六、范式标准
　　范式（Normal Form，缩写为NF），数据库设计一般分为六种范式类型，越高的范式代表数据库冗余越小，且高级范式包含了低级范式的要求。
　　数据库设计之所以要遵循范式，是为了确保数据库简洁、结构明晰，且不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之，不遵循范式要求的数据库设计，不仅会给编程人员制造麻烦，还可能存储大量不需要的冗余信息。
　　在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：
　　第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
　　第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
　　第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
　　一般来说，数据库只需满足第三范式（3NF）就行了。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。
　　举例，对一张存放商品的基本表来说。＂金额＂这个字段的存在，表明该表的设计不满足第三范式，因为＂金额＂可以由＂单价＂乘以＂数量＂得到，说明＂金额＂是冗余字段。但是，增加＂金额＂这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。七、SQL语句相关知识
　　结构化查询语言（Structured Query Language，简称SQL）是一种用于数据库查询和程序设计的编程语言，可以存取数据以及管理关系数据库系统。
　　SQL语句分类：
　　1、数据定义语言DDL（Data Definition Language）用于操作对象和对象的属性，这种对象包括数据库本身，以及数据库对象，如：表、视图等等DDL对这些对象和属性的管理和定义具体表现在Create、Drop和Alter上。
　　2、数据操控语言DML（Data Manipulation Language）用于操作数据库对象中包含的数据，也就是说操作的单位是记录。增删改查=CRUD：增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)C=Insert, R=Select, U=Update, D=Delete
　　3、数据控制语言DCL（Data Control Language）用于控制数据库对象的权限，这些操作使数据更加的安全。Grant：给某用户或某组或所有用户授予某些特定的权限。Revoke：废除某用户或某组或所有用户的特定权限
　　SQL语句元素（CRUD）：
　　1、INSERT（添加新记录）添加单条记录：Insert into tablename(column1,column2…) Values(1,＂string’…)批量添加记录：Insert into tablename(column1,column2…) Select…
　　2、Select（获取数据行）Top n （指定最多返回多少行记录）WITH TIES（指定是否将末尾并列排序的额外记录也一并返回）INTO（创建一个新表，并把查询结果的记录插入表中）FROM（指定从哪里查询数据）
　　3、JOINWHEREBETWEENLIKEIN
　　4、聚合函数AVG=取平均值MAX=取最大值MIN=取最小值COUNT=取记录条数SUM=取合计值
　　表之外的数据库对象：视图：保存下来以便反复使用的SELECT查询存储过程：保存下来以便反复使用的CRUD查询触发器：一种特殊的存储过程，对表进行增删改时自动执行自定义函数：函数类似存储过程，但是必须有返回值，且可直接用于FROM子句八、提高数据库运行效率的办法
　　在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。
　　以上就是本期公开课《数据库设计入门》的精选内容，欢迎访问葡萄城官网免费预约在线课程学习，罗老师会亲自为你扫平一切数据库设计过程中的＂坑＂。