（P123）mysql数据库(十五)：数据库设计三范式，PowerDesigner使用，ABCBank数据结构

文章目录

• 1.数据库设计三范式
• 2.PowerDesigner使用
• 3.ABCBank数据结构

1.数据库设计三范式

• 1NF，属性不可分
  1NF，数据库表的每一列都是不可分割的基本数据项
  （1）因为将关系数据库看成是一个二维表，是由行和列组成的
  （2）假设新开了一个系：艺术系，但是没有学生，会出现插入异常。
  此外还有数据冗余：1 张三 计算机系；
  数据冗余会造成更新复杂，比如计算机系改名为IT系，需要更改2条记录；
  此外，如果计算机系的张三和王五毕业了，需要删除相应记录，会导致计算机系也被删除了，出现删除异常。

学号	姓名	系		系主任	课程		成绩
1		张三	计算机系	陈老师	数据结构		90
2		李四	经管系	李老师	国际金融		88
3		王五	计算机系	陈老师	数据结构		80
4		赵六	数学系	黄老师	基础数学		68
1		张三	计算机系	成老师	操作系统		80
				艺术系	刘老师

• 2NF，所有的非主属性需要完全依赖于码（不能部分依赖于码）
  （1）2NF,首先满足1NF，要求数据库表中的每个实体必须可以被唯一地区分（要有主关键字），要求实体的非主属性完全依赖于主关键字
  （2）主关键字是（学号 课程），因为只有学号不能确定成绩，但是学号+课程可以确定哪个人，哪个系。
  除了（学号 课程），其他都是非主属性关键字。
  学号决定->姓名，即姓名依赖学号；
  （学号 课程）P->姓名，由于学号决定->姓名，所以这里的姓名部分依赖于主关键字；
  （学号 课程）P->系，与上类似；
  只有成绩完全依赖于主关键字；
  上面的问题就不满足主关键字了，就会造成插入异常等问题。
  解决办法：投影，即将他们分割开来。

变成2张表
学号	姓名	系	系主任
学号	课程	成绩

• 3NF，所有的非主属性既不部分依赖于码，也不传递依赖于码
  （1）3NF，首先满足2NF，每一列数据都和主键直接相关，而不能间接相关，即不能传递依赖于主关键字
  上面的表格中的系主任不直接依赖于学号，而依赖于系
  传递依赖：由于学号->系，系->系主任，所以学号->系主任
  解决办法：投影

一般在存储系的时候，不会在学生表中存储系名称，而是存储系号
变成以下4张表格就满足3NF了
学号	姓名	系号	系主任
系号	系	系主任
学号	课程号	成绩
课程号	课程

所以如果新增艺术系，只需要在系表中添加艺术系就行，就不会出现插入异常。若更改学生表，则不会影响系表，不会出现删除异常。
系名称在系表中保存，而不用在学生表中保存了，减少了数据冗余。

• 数据库设计的目的：
  减少数据冗余，易于扩展，易于编程
• 数据库设计步骤
  （1）需求分析
  （2）概念模型，将每个数据库中的表看成是一个实体，与现实生活中的每一个实体相对应，实体与实体之间的关系：1对1（1:1），1对n（1:n），多对多(m:n)
  （3）物理模型
  （4）数据库实施
  （5）数据库运行和维护

2.PowerDesigner使用

• eg：做一个学生选课系统
  需要学生实体，课程实体
• 学生实体



• 
  



• 
  



• D表示是否显示
  P表示是否是主键
  M表示是否是空的，若是主键，他一定是非空的



• 该学生就是实体，实体需要主键



• 课程实体



• 
  



• 
  



• 
  



• 学生实体和课程实体的关系：多对多的关系
  一个学生可以选多个课程，一门课程也可以被多个学生选



• 
  



• 最终的概念模型



• 概念模型转物理模型



• 若使用ER图（Reality Relation，实体关系模型）
  椭圆形代表实体，实体下面有属性，eg：学生有姓名，年龄等等，关系就是：1对1，1对多，多对多的关系



• 
  



• 可以看到多了一张关系表，这张关系表是我们做数据库设计未考虑到的，实际上他对应的就是成绩表，因为成绩表要在学生参加考试过后才知道，所以这张表的产生会逼迫我们去思考这张表的含义。
  学号和课程号这两个关键字构成的主键，fk是外键的意思。



• 修改成绩表



• 
  



• 
  



• 生成sql脚本



• 
  



• 生成的sql如下：



• 成绩表的主键是SNO和CNO，是联合主键，SNO是外键，CNO也是外键



• 外键约束



• STU学生表，主键是SNO



• 外键名称的修改



• 
  



• 
  



• 重新生成sql脚本



• on delete restrict表示限制级联删除；
  on update restrict表示限制级联更新；
  级联更新含义：若课程表中课程名称发生了改变，课程号更新了，那么成绩表中的课程号记录也会更新，这就是级联更新；
  级联删除含义：若课程表中课程号和课程名称被删除掉，那么成绩表中的该课程的成绩也会被删除，这就是级联删除；
• 此外，简单的数据模型可以直接新建物理模型



• 
  



• 学生表



• 
  



• 选择主键P，那么生成的代码就是not null，这里的Preview可以直接看到



• 
  



• 类似的，课程表



• 
  



• 类似的，成绩表



• 
  



• 
  



• 用reference引用连起来，会自动出现外键SNO，外键CNO



• 设置下成绩表的主键



• 
  



• 类似的，生成一下sql脚本

3.ABCBank数据结构

• 在需求分析的基础上，得到的下面的表：
  BANK_ACCOUNT账户表：FLAG=0表示没有销户，FLAG=1表示销户状态，销户并没有将表删除，仅仅是打个标记，标记为1；
  交易TRANS表：什么时间进行的什么交易，存款？取款？，TRANS表中多了一个BALANCE余额字段，目的是易于编程统计利息，因为这个余额字段在BANK_ACCOUNT已经有了；
  摘要表ABSTRACT：存款还是取款，转账？ABSTRACT_ID是外键，引用到了TRANS表；
  柜员表TELLER：柜员ID，柜员名称，柜员密码；



• 可用PowerDesigner使用直接打开P123\abc.pdm



• 生成的sql脚本如下，P123\crebas.sql

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2013/8/5 15:56:57                            */
/*==============================================================*/


drop table if exists ABSTRACT;

drop table if exists BANK_ACCOUNT;

drop table if exists TELLER;

drop table if exists TRANS;

/*==============================================================*/
/* Table: ABSTRACT                                              */
/*==============================================================*/
create table ABSTRACT
(
   ABSTRACT_ID          int not null auto_increment,
   NAME                 varchar(16),
   primary key (ABSTRACT_ID)
);

/*==============================================================*/
/* Table: BANK_ACCOUNT                                          */
/*==============================================================*/
create table BANK_ACCOUNT
(
   ACCOUNT_ID           int not null auto_increment,
   NAME                 varchar(16),
   PASS                 varchar(16),
   ID                   char(18),
   BALANCE              decimal(10,2),
   OPEN_DATE            datetime,
   FLAG                 tinyint,
   primary key (ACCOUNT_ID)
);

/*==============================================================*/
/* Table: TELLER                                                */
/*==============================================================*/
create table TELLER
(
   TELLER_ID            int not null auto_increment,
   TELLER_NAME          varchar(16),
   TELLER_PASS          varchar(16),
   primary key (TELLER_ID)
);

/*==============================================================*/
/* Table: TRANS                                                 */
/*==============================================================*/
create table TRANS
(
   TRANS_ID             int not null auto_increment,
   ACCOUNT_ID           int,
   OTHER_ACCOUNT_ID     int,
   ABSTRACT_ID          int,
   MONEY                decimal(10,2),
   BALANCE              decimal(10,2),
   TRANS_DATE           datetime,
   primary key (TRANS_ID)
);

alter table TRANS add constraint FK_ACCOUNT_ID foreign key (ACCOUNT_ID)
      references BANK_ACCOUNT (ACCOUNT_ID) on delete restrict on update restrict;

alter table TRANS add constraint FK_ABSTRACT_ID foreign key (ABSTRACT_ID)
      references ABSTRACT (ABSTRACT_ID) on delete restrict on update restrict;

alter table TRANS add constraint FK_OTHER_ACCOUNT_ID foreign key (OTHER_ACCOUNT_ID)
      references BANK_ACCOUNT (ACCOUNT_ID) on delete restrict on update restrict;

/* 设置字符集，允许插入中文 */
set names gbk;

/*==============================================================*/
/* 初始数据,下面初始数据是自己写的，并不是生成的  
账户明细，不涉及到账户金额变动的， 不会插入到ABSTRACT交易表                                         */
/*==============================================================*/
insert into TELLER values(1, 'admin', '123456');
insert into ABSTRACT values(1, '开户');
insert into ABSTRACT values(2, '存款');
insert into ABSTRACT values(3, '取款');
insert into ABSTRACT values(4, '转入');
insert into ABSTRACT values(5, '转出');

auto_increment自动增长的设置如下

• 将P123\crebas.sql导入至mysql
  （1）将P123\crebas.sql拷贝到C盘根目录
  
  （2）创建一个数据库bank，然后把crebas.sql脚本导入
  
  
  （3）用Navicat工具查看数据库