Oracle是應(yīng)用最廣的大型數(shù)據(jù)庫，而在范式下進(jìn)行Oracle數(shù)據(jù)庫設(shè)計(jì)則可以大大減少數(shù)據(jù)冗余，使數(shù)據(jù)庫維護(hù)更方便，可惜范式下的數(shù)據(jù)表一般不能直接輸出。今天我們就來探討一下范式下的數(shù)據(jù)表的動態(tài)交叉表生成的方法。
范式下的Oracle數(shù)據(jù)庫設(shè)計(jì)
數(shù)據(jù)關(guān)系的復(fù)雜性導(dǎo)致了表中數(shù)據(jù)冗余的存在，數(shù)據(jù)冗余增加了維護(hù)數(shù)據(jù)庫的負(fù)擔(dān)，也占用了大量的磁盤空間,直接造成性能下降。為了消除這些負(fù)面影響，就應(yīng)該對數(shù)據(jù)庫表格進(jìn)行規(guī)范化，使其遵守一定的規(guī)則的，尤其是數(shù)據(jù)庫設(shè)計(jì)范式。
關(guān)系必須是規(guī)范化的，簡單說來，就是在結(jié)構(gòu)表設(shè)計(jì)時(shí)，消除冗余性和不協(xié)調(diào)的從屬關(guān)系。即每一個分量必須是不可分的數(shù)據(jù)項(xiàng)，但是這只是最基本的規(guī)范化。規(guī)范化理論就是研究如何將一個不好的關(guān)系模式轉(zhuǎn)化為好的關(guān)系模式的理論，規(guī)范化理論是圍繞范式而建立的。規(guī)范化理論認(rèn)為，一個關(guān)系數(shù)據(jù)庫中所有的關(guān)系，都應(yīng)滿足一定的規(guī)范(約束條件)。規(guī)范化理論把關(guān)系應(yīng)滿足的規(guī)范要求分為幾級，滿足最低要求的一級叫做第一范式(1NF)，在第一范式的基礎(chǔ)上提出了第二范式(2NF)，在第二范式的基礎(chǔ)上又提出了第三范式(3NF)，以后又提出了BCNF范式，4NF，5NF，以及“域/關(guān)鍵字”范式。范式的等級越高，應(yīng)滿足的約束集條件也越嚴(yán)格。規(guī)范的每一級別都依賴于它的前一級別，例如若一個關(guān)系模式滿足2NF，則一定滿足1NF。
在Oracle上設(shè)計(jì)數(shù)據(jù)庫時(shí)更要符合范式的要求，如果把一個不符合規(guī)范的數(shù)據(jù)庫放在Oracle中，是不會突出Oracle的性能的，甚至是非常糟糕。
例如：學(xué)生的成績表，我們一般都要求打印一目了然。
這也是符合1NF的，但如果是在數(shù)據(jù)庫中定義的表結(jié)構(gòu)也這樣，則是不完善的，是有潛在沖突的。如要增加考試科目，就得更改表結(jié)構(gòu)，特別是大學(xué)，專業(yè)多、科目多，而有些科目是選學(xué)的，這將會使表結(jié)構(gòu)變得相當(dāng)復(fù)雜，有多少科目就得有多少個科目的字段，有部分字段值必然為空；這個表是指某次測驗(yàn)的還是期中或期末考試的成績呢？分辨不出，于是每一次成績都要造一張類似的表，必然表格較多。不僅浪費(fèi)大量的磁盤空間，還會給程序的編寫帶來極大的困難。
在數(shù)據(jù)范式理論的指導(dǎo)下，對數(shù)據(jù)庫表格進(jìn)行規(guī)范化，使其結(jié)構(gòu)更合理，消除存儲異常，使數(shù)據(jù)冗余盡量最小，便于插入、刪除和更新，進(jìn)一步保持了數(shù)據(jù)的完整性。經(jīng)過探索，我在成績管理系統(tǒng)的設(shè)計(jì)上采用了如下的表結(jié)構(gòu)，這個表結(jié)構(gòu)能以不變應(yīng)用多變，不管是科目的增加，還是教師的變動，都能適應(yīng)，符合數(shù)據(jù)的規(guī)范要求。
由此看出，經(jīng)數(shù)據(jù)規(guī)范化的數(shù)據(jù)雖然使數(shù)據(jù)冗余小，便于插入、刪除和更新，但如果直接輸出是不符合人們觀看習(xí)慣的，必需要把其輸出為上面表1的格式才行，這就是列向表生成橫向表的問題，即交叉表的生成。
動態(tài)交叉表的生成
為了簡述起見，在學(xué)生基本信息表中，只建兩個字段，學(xué)號、姓名，其他的諸如性別、科代碼等則略。其中班、教師代碼庫、考試次數(shù)標(biāo)志（即第幾次測驗(yàn)，還是期中、期末考試）等也略，只保留下面數(shù)據(jù)結(jié)構(gòu)足以能說明交叉表生成的過程。
各表結(jié)構(gòu)簡化如下：
學(xué)生基本信息表：JBXX
xh char(13) //學(xué)號
xm char(8) //姓名，針對不同情況，可用變長字符。
科目代碼表：KMDM
no number(3) //科目代號，現(xiàn)可用900多科目可用，若不夠，可定義四位。
mc varchar(20) //科目中文名稱。
成績表： CJ
xh char(13) //學(xué)號，關(guān)聯(lián)JBXX的XH。
xq number(2) //學(xué)期，指該學(xué)生所在校的學(xué)期。
km number(3) //科目代號。
cj number(3) //該科成績。
至此，數(shù)據(jù)表結(jié)構(gòu)已全部建好，此時(shí)的任務(wù)是把下面表3的數(shù)據(jù)進(jìn)行生成交叉表，表4。
交叉表的生成，在Oracle中可以用SQL語句實(shí)現(xiàn)。
select jbxx.xh,jbxx.xm , (select cj.cj from cj where cj.xh=jbxx.xh and cj.xq=1 and cj.km=1) as km1 , (select cj.cj from cj where cj.xh=jbxx.xh and cj.xq=1 and cj.km=2) as km2 , (select cj.cj from cj where cj.xh=jbxx.xh and cj.xq=1 and cj.km==3) as km3 from jbxx where <班級或?qū)I(yè)條件> order by jbxx.xh
Java語言有“編寫一次，隨處運(yùn)行”的跨平臺能力，具有強(qiáng)大的網(wǎng)絡(luò)能力。Oracle是一種關(guān)系型的大型數(shù)據(jù)庫，可在多種硬件平臺上運(yùn)行，支持多種操作系統(tǒng)，支持大數(shù)據(jù)庫、多用戶的高性能的事務(wù)處理，以其強(qiáng)大的功能和穩(wěn)定性而著稱。因此建議用Java結(jié)合Oracle編寫程序。下面給出在Java語言中的具體實(shí)現(xiàn)過程。
注：為了簡述方便，下面的程序已簡略，在實(shí)踐應(yīng)用中，還要考慮很多問題，并且一般把它做成bean來用。
程序如下：
import java.sql.*;//導(dǎo)入類庫
public class sjk{
public static void main(String[] args) throws Exception {
Connection conn;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
String sourceURL="jdbc:oracle:thin:@server:1521:orcl";
String user="scott";
String password="tiger";
conn=DriverManager.getConnection(sourceURL,user,password);
Statement stmt = conn.createStatement();
Statement stmt1 = conn.createStatement();
String sql_km="select no,mc from km";
// String bb_tj="0441010101";以后實(shí)際使用要加上班或級或?qū)I(yè)條件.
ResultSet rs_km = stmt.executeQuery(sql_km);
String title=" 學(xué)號 姓名 ";
String sql1="( select cj.cj from cj where cj.xh=jbxx.xh and cj.xq=1 and cj.km=";
String sql=" select jbxx.xh,jbxx.xm ,";
while (rs_km.next())
{
String sql_sum=" select sum(cj) as s1 from cj where "+
" cj.xq=1 and cj.km="; //在實(shí)際使用中要加上班級條件
sql_sum=sql_sum+rs_km.getString(1);//統(tǒng)計(jì)該班該科目的總成線。
ResultSet rs_sum = stmt1.executeQuery(sql_sum);
rs_sum.next();
//統(tǒng)計(jì)符合班級條件的成績CJ總和，如果為0則認(rèn)為該班不開設(shè)該科目，略掉。
if (rs_sum.getInt(1)>0)
{
title = title + rs_km.getString(2);
sql = sql + sql1 + rs_km.getString(1) + ") as km" + rs_km.getString(1)+" ,";
//構(gòu)造動態(tài)語句.
}
rs_sum.close();
} //獲取動態(tài)科目及名稱
sql=sql.substring(1,sql.length()-1); //去掉最后一個逗號。
sql=sql+"from jbxx order by jbxx.xh"; //在實(shí)際使用中要加上班級條件
ResultSet rs=stmt.executeQuery(sql);
ResultSetMetaData data = rs.getMetaData();
int col=data.getColumnCount(); //獲取所有曾生成的字段，實(shí)行動態(tài)輸出。
System.out.println(title);
while (rs.next())
{
for (int i=1;i<=col;i++)
{
if (i==col)
System.out.println(rs.getString(i));
else
System.out.print(rs.getString(i)+" ");
}
}
System.out.println("數(shù)據(jù)已打印完成！");
rs_km.close();
rs.close();
stmt1.close();
stmt.close();
conn.close();
///////////////////////////
}
catch (Exception e) {
System.err.println(e);
}
}
}
以上代碼已在j2sdk1.4.2,Oracle 8.1.7編譯通過，在應(yīng)用中，一般需要把其做成bean去使用，還可加入學(xué)期、班級的動態(tài)變量，即可獲得全動態(tài)的的數(shù)據(jù)了。