Oracle学习笔记(数据库设计范式 PowerDesigner工具)

Oracle学习笔记(数据库设计范式 PowerDesigner工具)

/*
Oracle学习笔记(数据库设计范式 PowerDesigner工具)
三大范式
第一范式 1NF:字段不可拆分;
第二范式 2NF:有主键，非主键字段依赖主键;
第三范式 3NF:非主键字段不能相互依赖;

1NF:原子性 字段不可再拆分,否则就不是关系数据库; (原子性，不可拆分)
2NF:唯一性 一个表只说明一个事物; (唯一性，不可重复)
3NF:每列都与主键有直接关系，不存在传递依赖; (直接依赖性、不可间接依赖)
*/

/*

数据库唯一的原则就是数据库表的关联查询越少越好，sql语句的复杂度越低越好，
三大范式只是作为参考，不一定要完全的按照三大范式来设计数据库表
三大范式只作为一个参考，如果完全按照三大范式原则来设计数据库表，会很累，所以我
们在设计数据库表的时候不要拘泥于形式，可以打开思维，要做到灵活变通

*/

我就直接po代码了

/*
Oracle学习笔记(数据库设计范式 PowerDesigner工具)
三大范式
第一范式 1NF:字段不可拆分;
第二范式 2NF:有主键，非主键字段依赖主键;
第三范式 3NF:非主键字段不能相互依赖; 

1NF:原子性 字段不可再拆分,否则就不是关系数据库; (原子性，不可拆分)
2NF:唯一性 一个表只说明一个事物; (唯一性，不可重复)
3NF:每列都与主键有直接关系，不存在传递依赖; (直接依赖性、不可间接依赖)
*/

--创建一个people表
CREATE TABLE people(
	pid NUMBER(12) primary key NOT NULL,
	pname VARCHAR2(50) NOT NULL,
	info VARCHAR2(200)
);
--插入数据
INSERT INTO people(pid, pname, info) VALUES(666, '囧囧', '2003年8月27日出生，籍贯在江西省赣州市于都县渡江大道666号');
--查询数据
SELECT * FROM people;

--以上的people表设计上有些问题，info列实际上可以进行再次拆分

/*
第一范式:1NF
表中的每个列不可再分
但是要注意一点，有些列其实也没太必要再分了，比如：姓名这一列，就没必要再拆分成姓和名两列了，因
为意义不大

*/
--改成如下设计
CREATE TABLE people2(
	pid NUMBER(12) primary key NOT NULL,
	pname VARCHAR2(50) NOT NULL,
	--将原来的info列拆分成birthday出生日期列、province省列、city市列、county县列
	birthday DATE,
	province VARCHAR2(50), --省份
  city VARCHAR2(50), --市区
	county VARCHAR2(50), --县
	address VARCHAR2(120) --住址
);

--
SELECT SYSDATE FROM dual;

/*
插入数据(下面这条sql语句在Navicat Premium软件工具中执行没问题，但
是在PLSQL Developer软件工具中执行确显示无效的月份)
*/
INSERT INTO people2 VALUES(888, '张三', '01-JAN-98', '江西省', '赣州市', '于都县', '渡江大道666号');

/*
见鬼了，以下这2行sql语句，在Navicat Premium软件工具中执行会报错：not a valid month
但是在PLSQL Developer软件工具中执行确没问题，可见，各个第三
方软件工具跟Oracle数据库服务器的兼容性，各有差异
*/
--插入日期
INSERT INTO people2 VALUES(889, '李四', '17-3月-1999', '江西省', '赣州市', '于都县', '渡江大道888号');
--插入日期
INSERT INTO people2 VALUES(890, '王五', '21-8月-2008', '江西省', '赣州市', '于都县', '渡江大道999号');


--(插入日期)
INSERT INTO people2 VALUES(891, '赵六', TO_DATE('2005-06-21', 'yyyy-mm-dd'), '江西省', '赣州市', '于都县', '渡江大道1号'); 

--查询数据
SELECT * from people;
SELECT * from people2;

--创建学生选课信息表
CREATE TABLE selectcourse(
	stuno NUMBER(10),
	stuName VARCHAR2(50),
	age NUMBER,
	courseName VARCHAR2(50),
	--成绩
	score NUMBER(4), 
	--学分
	credit NUMBER(4) 
);

-- drop table selectcourse;

--插入数据
insert into selectcourse values (666, '令狐冲', 16, 'java', 92, 3);
insert into selectcourse values (668, '韦小宝', 17, 'oracle', 83, 3);
insert into selectcourse values (888, '杨过', 16, 'C#', 81, 3);
insert into selectcourse values (889, '段誉', 18, 'oracle', 73, 3);
insert into selectcourse values (890, '乔峰', 19, 'oracle', 75, 3);
--以上暴露出了课程的信息冗余(重复)的问题

--查询数据
SELECT * from selectcourse;

/*
使用第二范式2NF改造selectcourse学生选课信息表

*/

--创建学生表
create table student(
	stuno NUMBER(10) primary key,
	stuName VARCHAR2(50),
	age NUMBER
);
--插入数据
insert into student values(168, '令狐冲', 16);
insert into student values(169, '韦小宝', 15);
insert into student values(170, '杨过', 16);
insert into student values(171, '张无忌', 18);
--查询数据
select * from student;

--创建课程表
create table course(
	cid number(6) primary key,
	cname VARCHAR2(50),
	--学分
	credit NUMBER(4) 
);
--插入数据
insert into course values(1, 'java', 3);
insert into course values(2, 'oracle', 3);
insert into course values(3, 'mysql', 3);
insert into course values(4, 'C#', 3);
--查询数据
select * from course;

/*
一个学生可以选择多门课程，一门课程也可以被多个学生选择(多对多的关系)，所
以我们可以建立一张关系表，表示这种关系
*/
--创建选课表
create table selectcourse2(
	--学生编号
	stuno NUMBER(10),
	--课程编号
	cid number(6),
	--成绩
	score NUMBER(4)
	--加入外键关联，所有的关联关系在关系表中体现，这是一个多对多的关系
);

--插入数据
insert into selectcourse2 values(168, 3, 75);
insert into selectcourse2 values(169, 2, 79);
insert into selectcourse2 values(170, 1, 83);
insert into selectcourse2 values(171, 3, 89);
--查询数据
select * from selectcourse2;

--查询数据(3表联查)
select stu.*, sc.*, c.* from student stu LEFT JOIN selectcourse2 
sc on stu.stuno = sc.stuno
LEFT JOIN course c on sc.cid = c.cid;


--在Oracle数据库中，数据表别名是不能加as的
--正确
select sysdate from dual;
--正确
select sysdate from dual du;

--列名后面可以跟as关键字
--正确
select sysdate as "date" from dual;
--正确
select sysdate as mydate from dual;
--正确(列后面的as关键字可以省略)
select sysdate mydate from dual;
--报错(在Oracle数据库中，表的别名是不能加as的)
--select sysdate from dual as du;

--设计一张学生表，学号,姓名，年龄和所在院校，学院地址，学院电话
--如下：我们使用第二范式来设计表
--学生表
CREATE table student2(
	stuno NUMBER(10) primary key,
	stuName VARCHAR2(50),
	age NUMBER
);

--院校表
create table college(
	cid NUMBER(10) primary key,
	cName VARCHAR2(50),
	address VARCHAR2(50),
	tel VARCHAR2(30)
);

--创建学生表和院校表的关系表
create table studentcollege(
stuno NUMBER(10),
cid NUMBER(10)
--设置主外键关系
);
/*
以上设计存在问题，一个学生可以同时在多个学院上课，多个学院会同时有同一个学生，这显
然不太合理，最好的做法是，一个学院包含多个学生，一个学生属于一个学院，就完全类似于部门
表和员工表的设计结构

*/
--接下来，我们对以上存在的问题，做下改造，重新设计表结构
/*
第三范式3NF
*/
--创建院校表
create table college2(
	cid NUMBER(10) primary key,
	cName VARCHAR2(50),
	address VARCHAR2(50),
	tel VARCHAR2(30)
);

--创建学生表
CREATE table student3(
	stuno NUMBER(10) primary key,
	stuName VARCHAR2(50),
	age NUMBER,
	--学生所在的院校id,建立主外键关联
	cid NUMBER(10)
);
--以上设计，是一个很明确的一对多的关系


/*

数据库唯一的原则就是数据库表的关联查询越少越好，sql语句的复杂度越低越好，
三大范式只是作为参考，不一定要完全的按照三大范式来设计数据库表
三大范式只作为一个参考，如果完全按照三大范式原则来设计数据库表，会很累，所以我
们在设计数据库表的时候不要拘泥于形式，可以打开思维，要做到灵活变通

*/

/*
PowerDesigner工具其实就是一个设计数据库表的工具，图形化界面设计表，可
以自动生成建表的sql脚本以及自动生成测试数据的sql脚本
*/