数据库设计

• 软件的研发步骤
  

• 数据库设计概念

  • 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
  • 建立数据库中的表结构以及表与表之间的关联关系的过程。
  • 有哪些表？表里有哪些字段？表和表之间有什么关系？
• 数据库设计的步骤

  • 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）

  • 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
    如下图就是ER(Entity/Relation)图：
    

  • 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）

  • 维护设计（1.对新的需求进行建表；2.表优化）

• 一对多
  • 如：部门 和 员工
  • 一个部门对应多个员工，一个员工对应一个部门。
• 实现方式
  在多的一方建立外键，指向一的一方的主键
• 案例
  我们还是以 员工表 和 部门表 举例:
  
  经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）：
  

建表语句如下：

     -- 删除表  
     DROP TABLE IF EXISTS tb_emp;  
     DROP TABLE IF EXISTS tb_dept;  
       
     -- 部门表  
     CREATE TABLE tb_dept(  
      id int primary key auto_increment,  
      dep_name varchar(20),  
      addr varchar(20)  
     );  
     -- 员工表   
     CREATE TABLE tb_emp(  
      id int primary key auto_increment,  
      name varchar(20),  
      age int,  
      dep_id int,  
       
      -- 添加外键 dep_id,关联 dept 表的id主键  
      CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)   
     );
复制

查看表结构模型图：

​

• 多对多
  • 如：商品 和 订单
  • 一个商品对应多个订单，一个订单包含多个商品
• 实现方式
  建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，还可以包含其他属性
• 案例
  我们以 订单表 和 商品表 举例：
  

经过分析发现，订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键指向两张表的主键：

建表语句如下：

     -- 删除表  
     DROP TABLE IF EXISTS tb_order_goods;  
     DROP TABLE IF EXISTS tb_order;  
     DROP TABLE IF EXISTS tb_goods;  
       
     -- 订单表  
     CREATE TABLE tb_order(  
      id int primary key auto_increment,  
      payment double(10,2),  
      payment_type TINYINT,  
      status TINYINT  
     );  
       
     -- 商品表  
     CREATE TABLE tb_goods(  
      id int primary key auto_increment,  
      title varchar(100),  
      price double(10,2)  
     );  
      
     -- 订单商品中间表  
     CREATE TABLE tb_order_goods(  
      id int primary key auto_increment,  
      order_id int,  
      goods_id int,  
      count int  
     );  
     
     -- 建完表后，添加外键  
     alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);  
     alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);
复制

查看表结构模型图：

​

• 一对一
  • 如：用户 和 用户详情
  • 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
• 实现方式
  在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)
• 案例
  我们以 用户表 举例：
  

而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。

建表语句如下：

create table tb_user_desc (  
  id int primary key auto_increment,  
  city varchar(20),  
  edu varchar(10),  
  income int,  
  status char(2),  
  des varchar(100)  
 );  
  
 create table tb_user (  
  id int primary key auto_increment,  
  photo varchar(100),  
  nickname varchar(50),  
  age int,  
  gender char(1),  
  desc_id int unique,  
  -- 添加外键  
  CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)   
 );
复制

查看表结构模型图：

如何分析，怎么设计
根据下图设计表及表和表之间的关系：

经过分析，我们分为 专辑表 曲目表 短评表 用户表 4张表。

一个专辑可以有多个曲目，一个曲目只能属于某一张专辑，所以专辑表和曲目表的关系是一对多。

一个专辑可以被多个用户进行评论，一个用户可以对多个专辑进行评论，所以专辑表和用户表的关系是 多对多。

一个用户可以发多个短评，一个短评只能是某一个人发的，所以用户表和短评表的关系是 一对多。

1. 概念

   • 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。要遵循后边的范式要求，必须先遵循前边的所有范式要求
   • 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
2. 分类：

   • 第一范式（1NF）：每一列都是不可分割的原子数据项
   • 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
   • 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

   几个概念：
   1. 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
   例如：学号-->姓名。 （学号，课程名称） --> 分数
   2. 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
   例如：（学号，课程名称） --> 分数
   3. 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
   例如：（学号，课程名称） -- > 姓名
   4. 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
   例如：学号-->系名，系名-->系主任
   5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
   例如：该表中码为：（学号，课程名称）
   主属性：码属性组中的所有属性
   非主属性：除过码属性组的属性
   图解概念：