Oracle从零开始20——数据库设计

转载

imcz 2013-01-23 17:19:24

文章标签 Oracle 20 数据库设计从零开始 文章分类 数据库

数据库设计

高级开发部分：游标、触发器、包、函数，基本上很少直接调用了。

1.数据库设计范式（了解）

实际上非常重要，但是从开发来看，如果真的全部按照范式去做，则程序没法写，查询语句也会变得非常复杂。

在Oracle中的scott用户的全部表，很好的体现了一种设计思路，雇员-部门的关系。

1）第一范式

数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括×××、实数、字符型、逻辑性、日期型等。

CREATE TABLE person

(

pid NUMBER(4) PRIMARY KEY,

name VARCHAR2(50) NOT NULL,

info VARCHAR2(200) NOT NULL

);

可以看到人员的信息由生日、地址等构成，可以再分，违反了第一范式。修改为：

CREATE TABLE person

(

pid NUMBER(4) PRIMARY KEY,

name VARCHAR2(50) NOT NULL,

birthday VARCHAR2(200),

area VARCHAR2(200),

subarea VARCHAR2(200),

address VARCHAR2(200)

);

当然，以上的划分也有一些注意点、现在假设有如下一种设计，将姓名拆成姓和名。这样的拆分就没有意义。

2）第二范式

定义：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖是指存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖与任意一组候选关键字。

第一范式要求很简单，就是保证每列都有意义，但如果都是用第一范式也会存在问题。

现在建立一张学生选课表：学号、姓名、年龄、课程名称、成绩、学分

CREATE TABLE selectcourse

(

stuno VARCHAR2(50),

stuname VARCHAR2(50),

stuage NUMBER(2),

cname VARCHAR2(20),

grade NUMBER(2),

credit NUMBER(2)

);

以上的脚本符合第一范式，但是按这样设计，也会存在问题。

INSERT INTO selectcourse VALUES('s001','张三',20,'JAVA',89,0.3);

INSERT INTO selectcourse VALUES('s002','李四',20,'JAVA',79,0.3);

INSERT INTO selectcourse VALUES('s003','王五',20,'JAVA',99,0.3);

从以上的数据库脚本可以发现，所有的课程信息冗余了，而且还存在以下问题：

如果一个课程没有学生选择，则此课程就从学校彻底消失了
课程中本身也应该包含一个课程编号，但是按照以上设计，课程编号肯定重复。
如果要更改课程信息，则要更改许多条记录

使用第二范式修改数据库脚本：

学生应该是一个实体表的信息

CREATE TABLE student

(

    stuno        VARCHAR2(50)    PRIMARY KEY,

    stuname        VARCHAR2(50),

    stuage        NUMBER(2)

);

课程也应该是一个实体表

CREATE TABLE course

(

    cid        NUMBER(2)    PRIMARY KEY,

    cname        VARCHAR2(20),

    credit        NUMBER(2)

);

学生选课：一个学生可能选多个课程，一门课也会有多个学生参加，课程也应是一个实体表

CREATE TABLE selectcourse

(

    stuno        VARCHAR2(50),

    cid        NUMBER(2),

    grade        NUMBER(2),

    加入外键关联，因为学生或者课程，缺一个，成绩都没有了

);

INSERT INTO student VALUES('s001','张三',20);

INSERT INTO student VALUES('s001','李四',20);

INSERT INTO student VALUES('s001','王五',20);

INSERT INTO course VALUES('ca','JAVA',3);

INSERT INTO course VALUES('cb','ORACLE',5);

INSERT INTO selectcourse VALUES('s001','ca',89.0);

INSERT INTO selectcourse VALUES('s002','ca',79.0);

INSERT INTO selectcourse VALUES('s003','ca',77.0);

以上设计解决了以下问题：
- 学生不选课，课程信息不会消失
- 更新课程的时候直接更新课程表即可
- 所有的关联关系在关系表中提现
也就是现在完成了多-多的关系。

3）第三范式

在实际开发中使用的频率最多。

定义：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖，则符合第三范式。因此，满足第三范式的数据库表应该不存在如下的依赖关系：

关键字段——》非关键字段a——》非关键字段b

例如现在要求设计一个学生表，包含学号、姓名、年龄、所在院校、学院地址、学院电话，此时肯定不能第一范式，但是如果使用第二范式呢？

CREATE TABLE student

(

    stuno        VARCHAR2(50)    PRIMARY KEY,

    stuname        VARCHAR2(50),

    stuage        NUMBER(2)

);

CREATE TABLE collage

(

    cid            VARCHAR2(50)    PRIMARY KEY,

    cname        VARCHAR2(50),

    caddress        VARCHAR2(50),

    ctel            VARCHAR2(50)

);

CREATE TABLE studentcollage

(

    stuno        VARCHAR2(50),

    cid            VARCHAR2(50),

    设置主外键关系

);

按照这种设计，一个学生可以在多个学院同时上课。此时最好的做法，一个学院包含多个学生，一个学生属于一个学院。实际上，此设计就类似于部门和雇员表的设计结构。

CREATE TABLE collage

(

    cid            VARCHAR2(50)    PRIMARY KEY,

    cname        VARCHAR2(50),

    caddress        VARCHAR2(50),

    ctel            VARCHAR2(50)

);

CREATE TABLE student

(

    stuno        VARCHAR2(50)    PRIMARY KEY,

    stuname        VARCHAR2(50),

    stuage        NUMBER(2)，

    cid            VARCHAR2(50)，

    建立主外键关联

);

这就是一个很明确的一对多关系设计。

以上三个范式只能参考，如果真的按照这样方式设计，则很累。数据库唯一原则：

数据库表的关连查询越少越好，SQL复杂度越低越好。

2.数据库设计工具（重点）

常用的是Sybase的PowerDesigner开发工具

点击新建——》物理数据模型Physical Data Model——》选择类型ORACLE VERSION 10g

    下面使用PowerDesigner工具将Oracle中的dept和emp表进行还原

Name是表在工具中的显示，而Code是该表在数据库中真正的名字。然后定义列

这两张表存在关系，所以为了维护主外键关系，可以直接在工具中进行拖拽操作。

双击打开

该键名称太长，改短一些：

得到关系之后就可以通过工具进行数据库脚本的创建。

在桌面生成数据库脚本：

可以看到桌面的脚本：

下面生成数据库的测试数据：

    放在桌面：

3.数据库设计实例（重点）

1）要求

设计一个网上购物程序（使用powerdesigner建立模型并编写测试数据）。有一下需求：

管理员可以在后台添加商品，每个商品属于一个商品组
可以对管理员进行分组，对每一组进行分别授权，即一个管理员组可以有多个管理员，一个管理员组有多个权限，一个管理员可以在多个组
用户可以自己购买商品，购买商品时要在订单表中添加信息，一个用户可以同时购买多个商品，用户可以选择自己所在地区进行商品的派送
用户可以根据自己的购买积分，对商品进行折扣。

2）实现

根据第一个要求，一个商品属于一个商品组，则此时应该建立一个一对多的关系

Oracle从零开始20——数据库设计 _从零开始_14

根据第二个要求，可以对管理员进行分组，需要一个管理员表和一个管理员组，权限表，组-权限关系表

Oracle从零开始20——数据库设计 _Oracle_15

实际上此时，管理员和商品表也应该存在关系

Oracle从零开始20——数据库设计 _Oracle_16

根据第三个要求，需要一个用户表

Oracle从零开始20——数据库设计 _数据库设计_17

第四个要求，商品折扣可以参考oracle的salgrade表

Oracle从零开始20——数据库设计 _20_18

正常情况下，一份定单会按照下面的格式显示，这样一来就要查询多少张表？用户表，地区表-子地区表，定单表、定单详情表，商品表。本查询需要同时查询六张表

定单详情
定单编号	100001	用户姓名	Xxx	用户电话	Xxxxxxxx
用户地区	北京	用户地址	XXXXX
商品总价	Xxxx	订单日期	Xxxx年xx月xx日	邮政编码	Xxxxxx
商品信息
No.	商品名称	商品数量	商品单价	商品总价	折扣价
1	Xxxx	3	90	270	20
2	Xxxx	4	78	314	300
3	Xxxx	1	10	10	5
4	Xxxx	2	20	40	8