第二十八篇（数据库设计）

数据库设计

前言

数据库设计(Database Design)是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求（信息要求和处理要求）。在数据库领域内，常常把使用数据库的各类系统统称为数据库应用系统。

1.了解数据库设计的步骤

数据库设计的作用 ：随着项目规模变大，设计变得越来越重要。

• 良好的数据库设计：能接受数据的存储空间、能够保证数据的完整性、方便进行数据应用系统的开发；
• 糟糕的数据库设计：会产生数据冗余、存储空间浪费、内存空间浪费、数据更新和插入的异常。

数据库设计的步骤（生命周期）：

1. 需求分析：分析客户需求。

   进行数据库设计首先必须准确了解和分析用户需求（包括数据与处理）。
   需求分析是整个设计过程的基础，也是最困难，最耗时的一步。
   需求分析是否做得充分和准确，决定了在其上构建数据库大厦的速度与质量。
   需求分析做的不好，会导致整个数据库设计返工重做。

2. 概要设计：设计E-R模型图，确认需求完整。

   在这阶段，E-R图显得异常重要。大家要学会各个实体定义的属性来画出总体的E-R图。
   各分E-R图之间的冲突主要有三类：属性冲突，命名冲突，和结构冲突。
   E-R图向关系模型的转换，要解决的问题是如何将实体性和实体间的联系转换为关系模式，如何确定这些关系模式的属性和码。

3. 详细设计：利用三大范式进行完善，规范。

   详细设计(物理设计)是为逻辑数据结构模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）。
   首先要对运行的事务详细分析，获得选择物理数据库设计所需要的参数，其次，要充分了解所用的RDBMS的内部特征，特别是系统提供的存取方法和存储结构。

4. 代码编写：构建真实的数据库，添加测试数据。

5. 软件测试：测试数据库是否能满足真实的场景需求。

   软件测试阶段，设计人员运营DBMS提供的数据库语言（如sql）及其宿主语言，根据逻辑设计和物理设计的结果建立数据库，编制和调试应用程序，组织数据入库，并进行试运行。

6. 安装部署：试运行阶段。

7. 生产环境使用。

例如：需求：设计一个酒店管理系统，能够实现旅客入住手续和客房管理。

1. 收集信息：
   与有关人员进行沟通、座谈、详细了解相应的需求。
2. 标识实体 、存储各类信息
   旅客信息、客房信息。
3. 实体属性
   (1)、旅客：编号、姓名、性别、身份证、手机号、入住时间、退房时间、押金、消费总金额、入住客房 …
   (2)、客房：编号、客房号、客房类型、客房床位费、客房入住人数、客房状态 …
4. 标识实体关系
   旅客入住客房

2.掌握绘制数据库的E-R图

1. 首先下载visio，打开visio。

2. 在搜索栏里输入“数据库”，搜索完后选择“数据库 Cheney’s 表示法”。
   
3. 打开后，在左侧选择对应的属性，鼠标左键单击拖动到中间空白区域。
   

酒店管理系统的E-R图

3.掌握数据库设计的三大范式

1. 确保每列的原子性（不可分割） 即每一张表中每个字段存储的信息必须足够单一
2. 一张表只能表示/描述一件事 如果在一张表中描述过多的事情 会导致数据冗余和不完整
3. 表的字段之间不允许出现传递依赖。
   A -> B -> C A和C就是传递依赖
   可以到时候将传递依赖的信息抽取到多张表或者一张数据字典表
   数据字典表：
   类型：大床房、双人间、单人间、豪华双人间…
   客房状态：已预订、已入住、空闲。
   酒店设施：有早餐、有无空调、有无外窗、有无wifi、有无停车位

规范化和实际性能/复杂度等方面的关系

为了降低查询复杂度和提升性能：

1. 允许一定的冗余列。
2. 允许一些特别的计算列。

根据实际情况和需求决定规范和功能的取舍。

4.掌握数据库设计的三大范式

1. PRIMARY KEY:设置主键约束；
2. UNIQUE：设置唯一性约束，不能有重复值；
3. DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2
4. NOT NULL：设置非空约束，该字段不能为空；
5. FOREIGN key :设置外键约束。

【主键】

1. 主键的注意事项？
   主键默认非空，默认唯一性约束，只有主键才能设置自动增长，自动增长一定是主键，主键不一定需要自动增长；
2. 设置主键的方式？
   在定义列时设置：ID INT PRIMARY KEY
   在列定义完之后设置：primary KEY（id）

【外键】

1. 设置外键的注意事项：

只有INNODB的数据库引擎支持外键，修改my.ini文件设置default-storage-engine=INNODB 外键必须与参照列的数据类型必须相同（数值型要求长度和无符号都相同，字符串要求类型相同，长度可以不同）。

2. 设置外键的语法：

   CONSTRAINT 外键名 FOREIGN KEY (外键字段)REFERENCES 参照表 (参照字段) ON DELETE SET NULL ON UPDATE CASCADE 设置参照完整性

3. 外键约束的参照操作

参照表的完整性操作：当对参照表的参照字段进行删除或更新时，外键表中的外键如何应对；

参照操作可选值：

      RESTRICT拒绝参照表删除或更新参照字段；               

      RESTRICT和NO ACTION相同，但这个指令只在mysql生效;                

      CASCADE删除或更新参照表的参照字段时，外键表的记录同步删除更新；               

      SET NULL 删除或更新参照表的参照字段时，外键表的外键设为NULL;

5.掌握用户管理和用户授权

1. 创建用户 %表示任意机器 localhost 表示 本机

CREATE USER 用户名@localhost IDENTIFIED BY '用户密码';

2. 给用户授权（可以只是授权，也可以授权的同时创建对应的用户）

GRANT SESECT ON 数据库名.数据库表名 TO '用户名'@'localhost';

3. 授权时创建用户（ALL 表示 所有权限）（*表示所有）

GRANT ALL ON 数据库名.* TO 用户名@localhost IDENTIFIED BY '密码'

4. 删除用户 可以同时删除多个用户 以，隔开

DROP USER 用户名@localhost;

5. 修改用户密码

(1). 找到mysql的user表 然后修改对应的用户密码（root用户才能修改 修改自己或者普通用户）

use mysql；
update user set password = password（'密码'）where user = xx and host = xx;

(2). 普通用户和root都可以使用

SET PASSWORD = PASSWORD('密码');

(3). root才能改其它的

SET PASSWORD FOR 用户名@主机地址 = PASSWORD（'密码'）;

(4).利用DOS程序mysqladmin

mysqladmin -u用户名 -p旧密码 password'新密码';

(5).当丢失密码如何找回？root

在mysql的配置文件 my.ini 添加一行sip-grant-table
这样的话登陆root不在需要密码，进去之后在按照上方规则可以修改密码
记得用完之后再将选项删除