資料庫正規化實作練習

如下的資料,我們應該如何進行正規化呢?
如果無正規化,對於處理上有何問題呢?

資料表1: 問題在於欄位並非atomic(基元值),學號、學生姓名、成績欄位中有多個數值,所以並不符合1NF的條件。改成資料表3,所有記錄中的項目都是基元值,即無重覆資料項目,就可符合1NF的條件。

資料表2: 問題在於無法決定學生1、學生2、學生3 ... 到底需要多少個欄位,成績亦同,所以這個資料表無法決定相依關係。


資料表3: 問題在於不符合2NF的條件,也就是每一個非鍵屬性無法完全相依於主鍵。

所以必須拆開成幾個表單~ 如資料表4,以符合2NF的條件。

課程資料表 (課程編號, 課程名稱, 教師編號, 教師姓名) ,
學生資料表 (學號, 學生姓名) ,
成績資料表 (課程編號學號, 成績)。





資料表4:
但是上面的課程資料表,尚不符合3NF,所以再拆成如下資料表5。

資料表5:



所以就得到 .... 
課程資料表 (課程編號, 課程名稱, 教師編號) ,
老師資料表 (教師編號, 教師姓名) ,
學生資料表 (學號, 學生姓名) ,
成績資料表 (課程編號, 學號, 成績)。

再以SQL指令來處理資料表 ...

到mysql的bin目錄 (假設在Z磁碟的easyphp目錄下)
Z:
cd /easyphp/binaries/mysql/bin
mysql -u root -p

(1) 建立資料庫
create database mycourse
character set utf8
collate utf8_general_ci; 

(2) 開始使用 mycourse 這個資料庫。
use mycourse;

(3) 建立資料表
CREATE TABLE mystudent (
stud_no char(10) NOT NULL,
stud_name varchar(255) NOT NULL,
PRIMARY KEY (stud_no) );

CREATE TABLE myteacher (
tea_no char(10) NOT NULL,
tea_name varchar(255) NOT NULL,
PRIMARY KEY (tea_no) );

CREATE TABLE myclass (
class_no char(10) NOT NULL,
class_name varchar(255) NOT NULL,
tea_no char(10), 
PRIMARY KEY (class_no) );

CREATE TABLE myscore (
class_no char(10) NOT NULL,
stud_no char(10) NOT NULL,
score int(2), 
PRIMARY KEY (class_no, stud_no) );

(4) 使用 desc 來看各表單結構

desc mystudent;
desc myteacher;
desc myclass;
desc myscore;

(5) 插入資料

INSERT INTO mystudent (stud_no, stud_name) VALUES ('0001', 'Tom');
INSERT INTO mystudent (stud_no, stud_name) VALUES ('0002', 'John');
INSERT INTO mystudent (stud_no, stud_name) VALUES ('0003', 'Helen');
INSERT INTO mystudent (stud_no, stud_name) VALUES ('0004', 'Mary');
INSERT INTO mystudent (stud_no, stud_name) VALUES ('0005', 'Lee');

INSERT INTO myteacher (tea_no, tea_name) VALUES ('0001', 'teacher-1');
INSERT INTO myteacher (tea_no, tea_name) VALUES ('0002', 'teacher-2');
INSERT INTO myteacher (tea_no, tea_name) VALUES ('0003', 'teacher-3');

INSERT INTO myclass(class_no, class_name, tea_no) VALUES ('1000', 'Math', '0001');
INSERT INTO myclass(class_no, class_name, tea_no) VALUES ('1001', 'Chem', '0001');
INSERT INTO myclass(class_no, class_name, tea_no) VALUES ('1002', 'English', '0002');
INSERT INTO myclass(class_no, class_name, tea_no) VALUES ('1003', 'Music', '0003');

INSERT INTO myscore (class_no, stud_no, score) VALUES ('1000', '0001', 80);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1000', '0003', 70);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1001', '0002', 81);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1001', '0003', 90);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1001', '0004', 85);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1002', '0005', 83);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1002', '0001', 70);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1003', '0002', 81);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1003', '0004', 70);
INSERT INTO myscore (class_no, stud_no, score) VALUES ('1003', '0005', 62);

依此類推,如下資料表應該如何正規化呢?



留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理