Oracle筆記（十六） 資料庫設計範式

資料庫設計範式是一個很重要的概念，但是這個重要程度只適合於參考。使用資料庫設計範式，可以讓資料表更好的進行資料的儲存，因為再合理的設計，如果資料量一大也肯定會存在效能上的問題。所以在開發之中，唯一可以稱為設計的寶典 —— 設計的時候盡量避免日後的程式出現多表關聯查詢。

一、第一範式

所謂的第一範式指的就是資料表中的資料列不可再分。

例如，現在有如下一張資料表：

CREATE TABLE member (　　mid NUMBER PRIMARY KEY,　　name VARCHAR2(200) NOT NULL,　　contact VARCHAR2(200));

這個時候設計的就不合理，因為連絡方式由多種資料所組成：電話、地址、email、手機，郵遞區號，所以這種設計是不符合的，現在可以修改設計：

CREATE TABLE member (　　mid NUMBER PRIMARY KEY,　　name VARCHAR2(200) NOT NULL,　　address VARCHAR2(200),　　zipcode VARCHAR2(6),　　mobile VARCHAR2(20),　　tel VARCHAR2(20));

但是在這裡面有兩點需要說明：

• 第一點，關于姓名，在國外的表設計中，姓名也分為姓和名兩類，但是在中國就是姓名儲存；
• 第二點，關於生日，生日有專門的資料類型（DATE），所以不能將其設定為生日年，生日月，生日日；

所謂不可分割指的是所有的資料類型都使用資料庫提供好的各個資料類型。

二、第二範式：多對多

第二範式：資料表中的非關鍵字段存在對任一候選關鍵字段的部分函數依賴；

第二範式分為兩種方式理解：

• 理解一：列之間不應該存在函數關係，現在有如下一個設計：

CREATE TABLE orders (　　oid NUMBER PRIMARY KEY,　　amount NUMBER,　　price NUMBER,　　allprice NUMBER);

現在的商品總價（allprice）=商品單價（price）*商品數量（amount），所以存在了函數的依賴關係；

• 理解二：通過一個資料表的設計體現一下，完成一個學生選課系統，如果說現在按照第一範式，則如下：

CREATE TABLE studentcourse (　　stuid NUMBER PRIMARY KEY,　　stuname VARCHAR2(20) NOT NULL,　　cname VARCHAR2(50) NOT NULL,　　credit NUMBER NOT NULL,　　score NUMBER);INSERT INTO studentcourse (stuid,stuname,cname,credit,score) VALUES (1,'張三','Java',3,89);INSERT INTO studentcourse (stuid,stuname,cname,credit,score) VALUES (2,'李四','Java',3,99);INSERT INTO studentcourse (stuid,stuname,cname,credit,score) VALUES (3,'王五','Java',3,78);INSERT INTO studentcourse (stuid,stuname,cname,credit,score) VALUES (1,'張三','Oracle',1,79);INSERT INTO studentcourse (stuid,stuname,cname,credit,score) VALUES (2,'李四','Oracle',1,89);

這種設計符合於第一設計範式，但是不符合於第二範式，因為程式會存在如下的錯誤：

• 資料重複：學生和課程的資料都處於重複的狀態，而且最為嚴重的是主鍵的設定問題；
• 資料更新過多：如果說現在一門課程已經有了3000人蔘加的話，則更改一門課程學分的時候需要修改3000條記錄，肯定效能上會有影響；
• 如果一門課程沒有一個學生參加，這門課程就從學校徹底消失了；

如果要想解決此問題，則可以將資料表的設計修改如下：

CREATE TABLE student (　　stuid NUMBER PRIMARY KEY,　　stuname VARCHAR2(20) NOT NULL);CREATE TABLE course (　　cid NUMBER PRIMARY KEY,　　cname VARCHAR2(50) NOT NULL,　　credit NUMBER NOT NULL);CREATE TABLE studentcourse (　　stuid NUMBER REFERENCES student(stuid),　　cid NUMBER REFERENCES course(cid),　　score NUMBER);INSERT INTO student (stuid,stuname) VALUES (1,'張三');INSERT INTO student (stuid,stuname) VALUES (2,'李四');INSERT INTO student (stuid,stuname) VALUES (3,'王五');INSERT INTO course (cid,cname,credit) VALUES (10,'Java',3);INSERT INTO course (cid,cname,credit) VALUES (11,'Oracle',1);INSERT INTO course (cid,cname,credit) VALUES (12,'Linux',2);INSERT INTO studentcourse (stuid,cid,score) VALUES (1,10,89);INSERT INTO studentcourse (stuid,cid,score) VALUES (2,10,99);INSERT INTO studentcourse (stuid,cid,score) VALUES (3,10,78);INSERT INTO studentcourse (stuid,cid,score) VALUES (1,11,79);INSERT INTO studentcourse (stuid,cid,score) VALUES (2,11,89);

這種設計與之前講解運動會-項目-成績的設計是一樣的。

三、第三範式：一對多

例如，現在一個學校有多個學生，如果用第一範式無法實現，而如果用第二範式則表示多對多的關係，即：一個學校有多個學生，一個學生在多個學校，不符合於要求，所以此時可以使用第三範式，參考之前的部門和僱員操作實現，一個部門有多個僱員，所以按照設計編寫如下：

CREATE TABLE school (　　sid NUMBER PRIMARY KEY,　　sname VARCHAR2(20) NOT NULL);CREATE TABLE student (　　stuid NUMBER PRIMARY KEY,　　stuname VARCHAR2(20) NOT NULL,　　sid NUMBER REFERENCES school(sid));

而在實際的工作之中，第三範式的使用是最多的。

以上的三個範式只是作為參考使用。