数据库设计

数据库中存放很多表,设计数据库就是设计表。表是对现实生活中一些事务的抽象。

要设计数据库就要明白表的关系,也要知道数据库设计的一些准则,也称范式。

数据库的设计直接影响了项目开发的难易程度,也直接影响了项目开发完后运行的性能。

多表之间的关系:

1、一对多(或多对一),如部门和员工,一个部门有多个员工,一个员工只能对应一个部门。

2、多对多,学生和课程,一个学生可以选择很多课程,一个课程也可以被很多学生选择。

3、一对一,如人和身份证,一个人只有一个身份证,一个身份证只能对应一个人。开发中,一对一的关系很少存在,了解即可。

一、多表关系_一对多关系实现

如:部门和员工

创建表时如何实现一对多的关系呢?在多的一方建立外键去指向一的一方的主键。

二、多表关系_多对多关系实现

如:学生和课程

创建表时如何实现多对多的关系呢?需要借助第三张中间表,中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键。

注意:每一对不应该重复,故需要设置联合主键。

联合主键,如下所示:

ALTER TABLE sys_user_role  ADD  CONSTRAINT PK_user_role PRIMARY KEY( user_id, role_id);

三、多表关系_一对一关系实现

如:人和身份证

创建表时如何实现一对一的关系呢?在任意一方添加唯一外键去指向一的一方的主键。注意:需要让外键唯一,唯一约束。

 如果是一对一关系,一般都会合成一张表。

案例:

旅游线路分类、旅游线路、用户

一个旅游线路分类对应多个旅游线路,一个线路对应一个分类。分类表和线路表是一对多的关系。故在多的一方添加外键指向一的一方的主键。

一个用户对应多个旅游线路,一个线路可以被多个用户所收藏。故线路表和用户表是多对多的关系。

-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游线路分类名称非空,唯一,字符串 100
CREATE TABLE tab_category (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键,自动增长
rname 旅游线路名称非空,唯一,字符串 100
price 价格
rdate 上架时间,日期类型
cid 外键,所属分类
*/
CREATE TABLE tab_route(
    rid INT PRIMARY KEY AUTO_INCREMENT,
    rname VARCHAR(100) NOT NULL UNIQUE,
    price DOUBLE,
    rdate DATE,
    cid INT,
    FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
/*创建用户表 tab_user
uid 用户主键,自增长
username 用户名长度 100,唯一,非空
password 密码长度 30,非空
name 真实姓名长度 100
birthday 生日
sex 性别,定长字符串 1
telephone 手机号,字符串 11
email 邮箱,字符串长度 100
*/
CREATE TABLE tab_user (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    PASSWORD VARCHAR(30) NOT NULL,
    NAME VARCHAR(100),
    birthday DATE,
    sex CHAR(1) DEFAULT '男',
    telephone VARCHAR(11),
    email VARCHAR(100)
);
/*
创建收藏表 tab_favorite
rid 旅游线路 id,外键
date 收藏时间
uid 用户 id,外键
rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
    rid INT, -- 线路id
    DATE DATETIME,
    uid INT, -- 用户id
    -- 创建复合主键
    PRIMARY KEY(rid,uid), -- 联合主键
    FOREIGN KEY (rid) REFERENCES tab_route(rid),
    FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

在sqlyog中的架构设计器中展示如下:

数据库设计

四、数据库设计的范式

1、概念

设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求。

设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。一般情况下,遵守了前三种范式,数据库的设计就没有什么问题了。

2、分类

第一范式(1NF):每一列都是不可分割的原子数据项。

第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)。

第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)。

数据库设计

由于系这一列可以分割为两列,故不满足每一列都是不可分割的原子数据项的要求。修改如下就变成原子列了。

数据库设计

如上,遵循了第一范式。第一范式是数据库设计表的基本要求,所有的表都遵循第一范式。

存在的问题:

(1)、存在非常严重的数据冗余。

(2)、数据添加存在问题,如果现在新增了一个系,如计算机系,数据不合法。如下所示:

数据库设计

(3)、数据删除存在问题,张无忌同学毕业了,删除数据时,会将系得数据一起删除。

数据库设计

上述三个问题导致数据库的设计不合适。

3、几个概念

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)、:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码。

学号没有被其他所有属性依赖,因为不能通过学号属性确定唯一的其他属性,因为通过学号不能确定唯一的分数,所以码为学号和课程名称。

第二范式要消除部分依赖,由于分数是完全依赖于码(学号和课程名称),而姓名、系名、系主任只依赖于学号,故是部分依赖。

现在需要做表的拆分

数据库设计

分数(非主属性)完全依赖与码(学号和课程名称),这样,非主属性完全依赖于主属性。

学号为主属性,姓名、系名、系主任完全依赖学号。

例如:该表中码为:(学号,课程名称)

主属性:码属性组中的所有属性。

非主属性:除过码属性组的属性。

现在虽然解决了非常严重的数据冗余,但是数据添加和数据删除都存在问题。张无忌毕业了会将系的数据一起删除。

数据库设计

第三范式可以解决上述问题。第三范式消除传递依赖。

数据库设计

学生表中,现在姓名和系名完全依赖与学号,不存在传递依赖。

系表中,系主任完全依赖于系名。

现在的三种表遵循第三范式。解决了添加数据的问题,即在系表中添加系名(计算机系)和系主任没有问题。也解决了删除数据的问题,张无忌毕业了,经济学仍然被保留着。

数据库设计

在实际工作中,应用这些范式来验证你设计的表是否合理。

上一篇:03-语法解析


下一篇:TotalCommander 使用笔记