数据库设计：
数据建模和数据库设计
软件开发的步骤可大致分为:
1.需求分析 需求分析
↓
2.系统设计 包含 数据库设计 包含 数据建模
3.编码实现
4.系统测试
5.运行维护
系统设计中一个重要的环节就是数据库设计
数据库设计的时候需要先进行数据建模(实体关系图 E-R图)
数据建模的依据就是前期所做的需求分析
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ER图：
在软件开发的过程中一般是先构建ER图，而后再将ER图转化为关系模型，这个属于数据建模的范畴
实体-关系图
实体-关系图(Entity Relationship Diagram),也称为E-R图,提供了表示实体、属性和关系的方法，用来描述现实世界的概念模型。
构成E-R图的基本要素是实体、属性和关系
实体(Entity)：实体用来表示具有相同特征和性质的事物(类似于java的类)，实体由实体名和实体属性来表示。
属性(Attribute)：实体所具有的某一特性，一个实体可以有若干个属性
关系(Relationship)：实体彼此之间相互连接的方式称为关系。一般可分为以下 3 种类型:
一对一关系 (1 ∶ 1)
一对多关系 (1 ∶ N)
多对多关系 (M ∶ N)
may-be 和 must-be
在实体与实体之间的关系中,都会存在着may-be和must-be这俩种情况,例如:
系统中有顾客和订单俩个实体(1:N关系)
一个顾客对应多个订单,一个订单对应一个顾客
而且一个顾客可以(may be)没有订单和他对应
一个订单一定(must be)会有顾客和它对应.
ER图中符号的表示
1) # : 唯一, 以后可能表示为主键
2) * : 非空
3) o : 可有可无
4) 虚线: may be 顾客这边虚线，顾客可能没有订单
5) 实线: must be 订单这边实线，订单一定是属于某个客户。
6) 竖杠(|): 代表要强制在(|)一方建立一个联合主键，将对方ID拿过来做联合主键
7) 伞状图标代表多的一方,不是伞状图标则代表一的一方
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
关系模型与关系模式：
关系模型:关系模型由关系数据结构,关系操作集合,关系完整性约束三部分组成.
关系模型的基本概念和基本术语共有十三个，它们分别是：
（1）关系(Relation)：一个关系对应着一个二维表，二维表就是关系名。
（2）元组(Tuple)：在二维表中的一行，称为一个元组。
（3）属性(Attribute)：在二维表中的列，称为属性。属性的个数称为关系的元或度。列的值称为属性值；
（4）（值）域(Domain)：属性值的取值范围为值域。
（5）分量：每一行对应的列的属性值，即元组中的一个属性值。 [2]
（6）关系模式：在二维表中的行定义，即对关系的描述称为关系模式。一般表示为（属性1，属性2，......,属性n）
如老师的关系模型可以表示为教师（教师号，姓名，性别，年龄，职称，所在系）。
（7）键(码)：如果在一个关系中存在唯一标识一个实体的一个属性或属性集称为实体的键，即使得在该关系的任何一个关系状态中的两个元组，在该属性上的值的组合都不同。
（8）候选键(候选码)：若关系中的某一属性的值能唯一标识一个元组
如果在关系的一个键中不能移去任何一个属性，否则它就不是这个关系的键，则称这个被指定的候选键为该关系的候选键或者候选码。
例如下列学生表中“学号”或“图书证号”都能唯一标识一个元组，则“学号”和“图书证号”都能唯一地标识一个元组
则“学号”和“图书证号”都可作为学生关系的候选键。
学号 姓名 性别 年龄 图书证号 所在系
S3001 张明 男 22 B20050101 外语
S3002 李静 女 21 B20050102 外语
S4001 赵丽 女 21 B20050301 管理
而在选课表中，只有属性组“学号”和“课程号”才能唯一地标识一个元组，则候选键为（学号，课程号）。
学号 课程号
S3001 C1
S3001 C2
S3002 C1
S4001 C3
（8）主键（主码）：在一个关系的若干候选键中指定一个用来唯一标识该关系的元组，则称这个被指定的候选键称为主关键字，或简称为主键、关键字、主码。每一个关系都有并且只有一主键，通常用较小的属性组合作为主键。例如学生表，选定“学号”作为数据操作的依据，则“学号”为主键。而在选课表中，主键为（学号，课程号）。
（9）主属性和非主属性：关系中包含在任何一个候选键中的属性称为主属性，不包含在任何一个候选键中的属性为非主属性。
（10）全键或者全码：一个关系模式中的所有属性的集合。
（11）外键或者外码：关系中的某个属性虽然不是这个关系的主键，或者只是主键的，但它却是另外一个关系的主键时，则称之为外键或者外码。
（12）超键或者超码：如果在关系的一个键中移去某个属性，它仍然是这个关系的键，则称这样的键为关系的超键或者超码。
（13）参照关系与被参照关系：是指以外键相互联系的两个关系，可以相互转化。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ER图，以及转化成关系模式
1.找出条件中的实体（矩形），属性（椭圆），关系（菱形）关系分为1:1,1：N，M:N，列出ER图
-1:1联系的转换方法
-1：n联系的转化方法
-m:n联系的转化方法
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据库范式
设计关系数据库时，遵从不同的规范要求，才能设计出合理的关系型数据库
这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：
第一范式(1NF)
第二范式(2NF)
第三范式(3NF)
巴斯-科德范式(BCNF)
第四范式(4NF)
第五范式(5NF，又称完美范式)
注:满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)，其余范式以次类推。
一般说来，数据库只需满足第三范式(3NF)就行了
第一范式:
一个表中,每个列里面的值是不能再分割的.
eg：
学号 姓名 性别 家庭信息 学校信息
101 王正 男 3口人，北京 兰州理工，大一
这就不属于第一范式，更改为：
学号 姓名 性别 家庭人口 户籍 学校 年级
101 王正 男 3口人 北京 兰州理工 大一
第二范式:
第二范式是在满足第一范式的基础上，消除部分依赖关系
(
简单来说：就是对于联合主键来说分4种情况：
符合2NF：非主键列全部依赖于主键
不符合2NF：
非主键列 全部依赖于 部分主键
非主键列 部分依赖于 部分主键
非主键列 全部依赖于 全部主键
)
eg：
订单号 产品号 产品数量 产品折扣 产品价格 订单金额 订单时间
101 102 5 0.85 500 3000 18/05/06
101 103 6 0.85 500 3000 18/05/09
这就不属于第二范式，更改为：
表1：
订单号 订单金额 订单时间
101 3000 18/05/06
表2：
订单号 产品号 产品数量 产品折扣 产品价格
101 102 5 0.85 500
第三范式:
第三范式是在满足第二范式的基础上
表中的非主键列都必须直接依赖于主键列,而不能间接的依赖.不可以存在依赖传递
eg：
学号 姓名 年纪 班级 教师ID 教师姓名
101 李凯 20 001 102 小于
这就不属于第二范式，更改为：
表一：
学号 姓名 年纪 班级
101 李凯 20 001
表二：
教师ID 教师姓名
102 小于
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
主键和外键
主键:
1.非空唯一
3.可以让表中一个有意义的列做主键,比如说学号
4.也可以找一个没有意义的列做主键,就是用来唯一标识一行记录的，例如ID
5.我们可以让多个列联合在一起做表中的主键,那么它就是联合主键,要求这几个列的值联合在一起是非空唯一的
外键:
1.表中的某一个列声明为外键列,一般这个外键列的值都会引用于另外一张表的主键列的值
(有唯一约束的列就可以,不一定非要引用主键列)
2.另外一张表的主键列中出现过的值都可以在外键列中使用,没有出现过的值,都不能使用
3.外键列值也可以为空的,提前是这个外键列在表中不做主键,因为我们也可以把表中的外键列当做主键来使用
(只有满足非空唯一的要求就可以)
4.如果把B表中的联合主键的值引用到A表中做外键,因为是俩个列在B表中做联合主键,那么A表引用过来的时候也要把俩个列的值都引用过来,那么它们在A表中就会作为一个联合外键出现
注意：主键是非空唯一的，但是外键一定是外键列中出现过的值，外键可以不唯一也可以为空
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
总结下：数据库设计的大概步骤
1.需求分析 2.数据建模(ER) 3.ER图转化为关系模型(调整至少到第三范式) 4.根据关系模型建表编码
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
建表的语句和过程
根据以上格式,可以看出,建表过程中,需要以下几种东西:
1.关键字 create table
2.表名
3.列名
4.数据类型
5.约束
6.固定格式
其中,约束分为列级约束(因为是跟在列的声明后面写的)和表级约束(因为是在全部列声明完之后写的),
列级约束和表级约束都是对列中的值进行约束的,例如:列的值不能为空,列的值必须是唯一的等等
注:列级约束也称为行级约束
同时,列级约束和表级约束都是可选的,也就是都可以写也可以不写。例如:
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
列名4 数据类型
);
表名的要求:
1.必须是字母开头
2.必须是1-30个字符之间的长度
3.表名中只能出现A–Z, a–z, 0–9, _, $, #
4.不能和数据库中已有对象的名字重复
5.不能是数据库中的关键字
列的常用数据类型
1.char 长度固定但是效率高
2.varchar 长度可变但是效率不高
3.varchar2 VARCHAR2是Oracle自己开发了一个数据类型VARCHAR2 ，VARCHAR2将数据库中varchar列可以存储空字符串的特性改为存储NULL值。
4.number 数字类型
5.date a.sysdate
b.oracle系统默认的日期格式'dd-mm-yy',这个与当前的会话环境有关
其他类型 BLOB CLOB
BLOB和CLOB都是大字段类型，BLOB是按二进制来存储的，而CLOB是可以直接存储文字的
列的约束
列的约束就是对这个列中的值的要求
1.主键约束 PRIMARY KEY primary key
2.外键约束 FOREIGN KEY foreign key
3.唯一约束 UNIQUE unique
4.非空约束 NOT NULL not null
5.check约束 CHECK check
列级约束/行级约束:在列的后面直接加上的约束
表级约束:在所有列声明以后加的约束
建表实例
例如1:普通的建表例子
create table student(
id number primary key,
name varchar2(200) not null,
age number,
birthday date
);
drop table student;
例如2:使用四种列级约束 主键约束 非空约束 唯一约束 check约束
create table student(
id number primary key,
name varchar2(100) not null,
email varchar2(100) unique,
gender char(1) check(gender in('f','m')),
age number,
birthday date
);
drop table student;
例如3:使用列级约束 声明 外键约束
create table t_customer(
id number primary key,
name varchar2(200) not null
);
create table t_order(
id number primary key,
content varchar2(200) not null,
customer_id number references t_customer(id)
);
drop table t_customer;
drop table t_order;
注意:订单表中的外键列customer_id的值,是引用自顾客表t_customer中的主键列id的值
1.这时候直接删除顾客表是不行的,因为t_customer的主键列的值被别的表给引用了.
2.我们可以先删除订单表t_order,然后再删除t_customer就可以了
3.如果非要想直接删除到顾客表t_customer,就需要使用下面的语句:
drop table t_customer cascade constraints;
4.该语句表示,删除t_customer表的同时,也级联删除与表相关的约束,外键约束没有了,这个表自然可以被删除掉
5.cascade是级联的意思
例如4:使用表级约束
create table student(
id number,
name varchar2(20) not null,
age number default 20,
email varchar2(100),
gender char,
primary key(id),
unique(email),
check(gender in('f','m'))
);
drop table student;
注:非空约束(not null)不能声明成表级约束
例如5:使用表级约束 声明 外键约束
create table t_customer( id number primary key, name varchar2(200) not null ); create table t_order( id number primary key, content varchar2(200) not null, customer_id number, foreign key(customer_id) references t_customer(id) ); drop table t_order; drop table t_customer;
例如6:使用表级约束 声明 联合唯一约束
create table student( id number primary key, class varchar2(50) not null, name varchar2(50) not null, unique(class,name) ); drop table student;
注意:学生的班级和学生的名字联合起来必须是唯一的(联合唯一)
例如7:使用表级约束 声明 联合主键
create table t_customer( id number, name varchar2(50), primary key(id,name) ); drop table t_customer;
例如8:使用表级约束 声明 联合外键
create table t_customer( id number, name varchar(50), primary key(id,name) ); create table t_order( id number, price number not null, customer_id number, customer_name varchar(50), foreign key(customer_id,customer_name) references t_customer(id,name) ); drop table t_order; drop table t_customer;
表级约束和列级约束对比
1.表级约束和列级约束所写的位置不一样
2.not null约束不能用表级约束来声明
3.表级约束和列级约束声明语法稍有所不同
4.如果要声明的约束为联合主键、联合外键、联合唯一的时候,就一定要用表级约束.
constraint关键字
1.constraint是约束的意思
2.建表的时候可以给约束起一个名字,这个名字起的规律一般会是:表名_列名_约束类型
3.如果没有给约束起名字,那么系统也会给这个约束起一个默认的名字,这不过这个默认的名字对我们来说并不友好(我们看不懂)
4.将来我们可以根据我们之前给约束起好的名字而找到这个约束,然后进行修改
例如1:列级约束 起约束名字
create table student( id number constraint student_id_pk primary key, name varchar2(100) constraint student_name_nn not null, email varchar2(100) constraint student_email_un unique, gender char(1) constraint student_gender_ck check(gender in('f','m')), age number, birthday date ); drop table student;
例如2:表级约束 起约束名字
create table t_customer( id number, name varchar2(20) not null, age number, email varchar2(100), gender char, constraint cus_id_pk primary key(id), constraint cus_email_un unique(email), constraint cus_gender_ck check(gender in('f','m')) ); create table t_order( id number, price number not null, customer_id number, constraint order_cid_fk foreign key(customer_id) references t_customer(id) ); drop table t_order; drop table t_customer; 联合唯一： create table t_user( id number , name char(50), constraint user_id_pk primary key(id), constraint user_id_name_un unique(name,id) );
特殊的建表:建立一张表和s_dept一模一样
//s_dept的表结构和表中的数据全部复制过来 create table test1 as select * from s_dept; //只拿来s_dept的表结构,没有数据 create table test2 as select * from s_dept where 1=2; //只复制表中某几个列以及数据 create table test3 as select id,last_name,salary from s_emp;
ON DELETE CASCADE
on delete cascade
这个语句是在建表中,声明外键约束的时候一个可选项,我们后面的DML章节中再进一步讨论