数据库基础Database4-数据库设计

2018-04-16 本文已影响0人 sunblog

数据库基础Database4-数据库设计

六关系设计库设计

一个关系模式： R(U, F)

其中：

关系名R是符号化的元组定义
U为一组属性
F为属性组U上的一组数据依赖

函数依赖和码

6.1.1 函数依赖

数据依赖是一个关系内部属性与属性之间的一种约束关系。这种约束关系是通过属性间值的相等与否体现出来的数据间相关联系。数据依赖包括：函数依赖(Functional Dependency, FD)和多值依赖(Multi-Value Dependency, MVD)。

函数依赖非常普遍，我们可以用y=f(x)来表示。当x确定的时候，y也就确定。那么我们称y函数依赖于x，记作: x->y

比如: 对于关系r(id, name, dept_name, address)。根据id可以确定唯一的一个人。那么显而易见: id->name, id->dept_name, id->address

定义1 设R(U)是属性集U上的关系模式，X，Y是U的子集。若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖于X，记作X -> Y （参照前面的y=f(x))。

下面介绍一些术语和记号：

X->Y, 但Y⊈X，则称X->Y是非平凡的函数依赖。
X->Y, 但Y⊆X，则称X->Y是平凡的函数依赖。（对任何非空集都成立，因为X真包含Y）
若X->Y，则X称为这个函数依赖的决定属性组，也称决定因素（determinant）。
若X->Y, Y->X，则记作X<-->Y.
若Y不依赖于X，则记作Y↛X.

定义2 在R(U)中，如果X->Y，并且对与X的任何一个真子集X’，都有X’↛Y，则称Y对X完全函数依赖，记作X^{->^F}Y. （每一个y=f(x)，这种映射是一一对应）

若X->Y, 但Y不完全函数依赖于X，则称Y对X部分依赖（partial functional dependency),记作：X^{->^P}Y.

注意：完全依赖和不完全依赖两个符号有问题，F和P是在->正上面，请搜一下。

6.1.2 码（后面我都称作键）

定义4 设K为R(U,F)的属性或属性组合，若K^{->^F}U，则K为R的候选键（Candidate Key). 如果U部分依赖于K，即K^{->^P}U，则称K为超键(Superkey）。候选键是最小的超键，即K的任意一个真子集都不能成为一个候选键。

若候选键多于一个，则选取其中一个为主键(Primary Key)。否则主键就是候选键。

包含在任何一个候选键中的属性成为主属性（prime attribute)；其他的称为非主属性（nonprime attribute）。最极端的情况是，整个属性组都是主属性，成为全键（all-key)。

后面，把主键或者候选键简称为键。

定义5 关系模式R中属性或属性组X并非R的键，但X是另一个关系模式的键，则称X是R的外键（Foreign Key).

6.2 第一范式（First Normal Form, 1NF)

关系数据库中的设计是要满足一定要求的，满足不同程度要求的为不同范式。

一个低一级范式的关系模式通过模式分解（schema decomposition）可以转换为若干个高一级范式的关系模式的集合，这种过程就叫规范化（Normalization）。

各范式之间：5NF⊂4NF⊂3NF⊂2NF⊂1NF

对于关系模式R，一个域是原子的，如果该域的元素被认为是不可分割的单元。我们称一个关系模式R属于第一范式： R∈1NF。

组合单元由不可分割单元或组合单元构成。比如street, city, provice 和 zipcode是不可分的，但是包含了这些属性的Address是可分的，是组合单元。

6.3 第二范式（Second Normal Form， 2NF)

若R∈1NF,且每一个非主属性完全函数依赖于任何一个候选键，则称R属于第二范式：R∈2NF。

一个关系模式如果不属于2NF，就会出现如下问题：

插入异常
删除异常
修改复杂

6.4 第三范式（Third Normal Form, 3NF)

设关系模式R<U,F>∈1NF,若R中不存在这样的键X，属性组Y及非主属性Z（Z⊉Y）是的X->Y, Y->Z成立，Y↛X,则称R<U,F>∈3NF。

6.5 BCNF(Boycee Codd Normal Form)

关系模式R<U,F>∈1NF，若X->Y且Y⊈X时必含有键，则R<U,F>∈BCNF。

也就是说关系模式R<U,F>中，每一个决定因素都包含键，则R<U,F>∈BCNF。

由BCNF的定义可以得到结论，一个满足BCNF的关系模式有：

所有非主属性对每一个键都是完全函数依赖
所有主属性对每一个不包含它的键也是完全函数依赖
没有任何属性完全函数依赖于非键的任何一组属性

BCNF⊆3NF。

注意：对范式的讲解，这里是摘抄自《数据库系统概论》5th（王珊，萨师煊）。我觉得《数据库系统概念》6th, (Abraham Silberschatz ...)例子更多，更容易理解一点。

6.5 多值依赖(Multi-Value Dependency)

暂时省略

以后会修改关于范式的讲解

七 E-R模型

7.1 实体-联系（entity relationship, E-R)

实体-联系数据模型的提出旨在方便数据库的设计，它是通过允许定义代表数据库全局逻辑结构的企业模式实现的。

E-R数据模型采用了三个基本概念：

实体集
联系集
属性集

E-R模型的相关图形表示是E-R图。

实体（entity）是现实世界中可区别所有其他对象的一个“事务”或“对象”。实体集（entity set）是相同类型即具有相同性质（或属性）的一个实体集合。

实体通过属性(attibute)来表示。属性是实体集中每个成员所拥有的描述性性质。每个实体的每个属性都有一个值。

属性分为简单(simple)属性和复合(composite)属性。简单属性不可再划分为更小的部分。复合属性可以再划分为更小的部分。

属性也可以被分为单值属性、多值属性。

联系(relationship)是指多个实体间的相互关联。联系集(relationship set)。实体集间的关联称为参与(participate)。

7.2 约束

7.2.1 映射基数(mapping cardinality)

映射基数或基数比率，表示一个实体通过一个联系能关联的实体的个数。

对于实体集A和B之间的二元联系集R来说，映射基数必然是以下情况之一：

一对一(one-to-one)。A中的一个实体至多与B中的一个实体相关联，并且B中的一个实体也至多与A中的一个实体相关联。
一对多(one-to-many)。A中一个实体可以与B中任意数目（零个或多个）实体相关联，而B中的一个实体至多与A中的一个实体相关联。
多对一(many-to-one)。A中一个实体至多与B中的一个实体相关联，而B中的一个实体可以与A中任意数目（零个或多个）实体相关联。
多对多(many-to-many)。A中的一个实体可以与B中的任意数目实体相关联，B中的一个实体可以与A中的任意数目实体相关联。

7.2.2 参与约束

如果实体集E中的每个实体都参与到联系集R中的至少一个联系中，实体集E在联系集R中的参与称为全部的(total)。如果E中只有部分实体参与到联系集R中，则<称实体集E在联系集R中的参与是部分的(partial)。

7.3 实体联系图

举个例子：

实体集：（主键用下划线标出）

instructor(<u>ID</u>, name, salary)

student(<u>ID</u>, name, tot_cred)

属性集：

advisor：关联教师和学生

分别用一对一、一对多、多对多来表示：

er_stud

《数据库系统概念》中用的是箭头。有的用的是数字（比如维基百科）。有不同的表示方法.

映射基数：

![er_stud_cardinality]

advisor左边0..*表示，每个instructor对应0个或多个学生。advisor右边1..1表示每个学生对应1个（最少1个，最大1个）教师。这个图还表示了，student在advisor中的参与是完全的。、

下面是用MySQL Workbench根据上面副图(映射基数)来画的：

workbench_er_stud

黄色的是主键。student中instructor_ID是外键，引用instructor的主键ID。

7.4 转化为SQL语句：

下面是前一节MySQL Workbench导出的SQL:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`instructor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`instructor` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `salary` INT NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`student` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `tot_cred` INT NULL,
  `instructor_ID` INT NOT NULL,
  PRIMARY KEY (`ID`),
  INDEX `fk_student_instructor_idx` (`instructor_ID` ASC),
  CONSTRAINT `fk_student_instructor`
    FOREIGN KEY (`instructor_ID`)
    REFERENCES `mydb`.`instructor` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[er_stud_cardinality]:https://img.haomeiwen.com/i11086962/2a904c3849dc7330.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

数据库基础Database4-数据库设计