數(shù)據(jù)庫-學(xué)生管理系統(tǒng)設(shè)計

1、數(shù)據(jù)庫大作業(yè)學(xué)生管理系統(tǒng)組 長：趙呈亮00101303設(shè)計成員：湯雨00101304 桑迪00101308 指導(dǎo)老師：祁建軍 設(shè)計時間：2013/6/20 組長:趙呈亮00101303主要任務(wù):1、根據(jù)語義畫出分E-R圖,并集成基本E-R圖 將E-R圖轉(zhuǎn)換為關(guān)系模式，并進行模式優(yōu)化 2、linux中MYSQL下編寫SQL代碼創(chuàng)建視圖、觸 發(fā)器、存儲過程、游標(biāo)等 3、全部文檔撰寫 4、答辯主講主要成員1：湯雨00101304主要任務(wù)：1、 linux環(huán)境下用PHP語言編寫前端，實現(xiàn)查 詢、修改、插入等操作的可視化。2、參與后臺sql代碼編寫的討論3、帶電腦參與答辯、展示。主要成員2：桑迪0010

2、1308主要任務(wù)：1、根據(jù)優(yōu)化后的關(guān)系模式創(chuàng)建基本表，并填充 數(shù)據(jù)。2、參與優(yōu)化模式討論。學(xué)生管理系統(tǒng)-數(shù)據(jù)庫設(shè)計7步(1) 規(guī)劃階段建立數(shù)據(jù)庫的必要性和可行性分析。(2) 需求分析全面、準(zhǔn)確了解用戶的實際要求。 (3) 概念結(jié)構(gòu)設(shè)計設(shè)計數(shù)據(jù)庫的概念結(jié)構(gòu)，形成一個獨立于具體DBMS的概念模型。今要建立關(guān)于系、學(xué)生、班級、學(xué)會等諸信息的一個關(guān)系數(shù)據(jù)庫。一個系有若干專業(yè)，每個專業(yè)每年只招一個班，每個班有若干學(xué)生。一個系的學(xué)生住在同一宿舍區(qū)。每個學(xué)生可參加若干學(xué)會，每個學(xué)會有若干學(xué)生。學(xué)生參加某學(xué)會有一個入會年份。描述各個實體的屬性（加下劃線者為主碼）如下： 學(xué)生：學(xué)號、姓名、年齡、系名、班號、宿舍

3、區(qū)。 班級：班號、專業(yè)名、入校年份、系名、人數(shù)。 系 ：系號、系名、系辦公室地點、人數(shù)。 學(xué)會：學(xué)會號、學(xué)會名、成立年份、地點。1、實體及屬性E-R圖描述在設(shè)計E-R圖時，能作為屬性的就不作為實體，這樣有利于E-R圖的簡化。clyearclmjnamesdnamesageclno sclnocldnamesnamesdor cltotalsno class student stuunionuplacedno deptubiryeardnamedtotaldwkplaceunouname2、設(shè)計子E-R圖確定實體與屬性的兩條準(zhǔn)則：(1) 屬性是不可再分的數(shù)據(jù)項，屬性不可以再有屬性；(2) 屬性不

4、能與其他實體發(fā)生聯(lián)系，聯(lián)系只能存在于實體與實體之間。sdorsclnosdnamesagesnamesnosdorsdnamesagesnamesno studentcltotalcldnameclmjnameclnojoinyearsjoin student n nbelong m stuunionuplace 1 class ubiryearunameunoclyear student屬性"sdor"和"dept"有聯(lián)系,故將"sdor"屬性轉(zhuǎn)變成實體"dormitory"同理,class屬性"clm

5、jname"與dept有聯(lián)系,故將"clmjname"轉(zhuǎn)變成實體"major"。clno classmjname majorestablishdnodname deptdwkplacedtotalclyearcldnamesagesnamesclno studentdnodname deptdtotaldormitorysnobelongcltotalopen n 1 1 1dwkplace p 1live 1 1dorplace3、視圖集成1)視圖集成的作用： 一方面可以完整地描述企業(yè)的信息及其聯(lián)系，另一方面在集成過程中可以 解決沖突和消除冗

6、余。2)視圖集成中E-R圖之間的三類沖突： (1) 屬性沖突：同一屬性可能會存在于不同的分E-R圖，由于設(shè)計人員不同或是出發(fā)點不同，對屬性的類型、取值范圍、數(shù)據(jù)單位等可能會不一致，這些屬性對應(yīng)的數(shù)據(jù)將來只能以一種形式在計算機中存儲，這就需要在設(shè)計階段進行統(tǒng)一。 (2) 命名沖突：相同意義的屬性，在不同的分E-R圖上有著不同的命名（異名同義），或是名稱相同的屬性在不同的分E-R圖中代表著不同的意義（同名異義），這些也要進行統(tǒng)一。 (3) 結(jié)構(gòu)沖突：同一實體在不同的分E-R圖中有不同的屬性，同一對象在某一分E-R圖中被抽象為實體而在另一分 E-R圖中又被抽象為屬性，需要統(tǒng)一。3)視圖集成消除冗余注

7、意點: 1. 在E-R圖的綜合的過程中，同名實體只能出現(xiàn)一次，還要去掉不必要的聯(lián)系，且不能出現(xiàn)環(huán)路，這樣才能消除冗余數(shù)據(jù)和冗余聯(lián)系。 2. 一般來說，從總體E-R圖必須能導(dǎo)出原來所有局部E-R視圖，包括所有的實體、屬性和聯(lián)系?？紤]沖突和冗余如下:ubiryearunameuplaceuno stuunionsjoin studentjoinyear m sagesclno nsdorsname sdnamesno nbelongcltotalcldnamebelong 1clyear class n mjname 1open 1clnoclmjname major 1establish pdn

8、odname dept 1livedormitorydorplace 1 1dwkplacedtotal消除沖突和冗余后得基本E-R圖如下：uplaceubiryearunameuno stuunion stuunion ( uno，uname，ubiryear，uplace)joinyear Msjoinsagesnamesno student join (uno，sno，joinyear) N student ( sno ，sname，sage，clno) belong n classclyearcltotal 1 class ( clno，clyear，cltotal) openclno

9、 1 mjname 1 major major ( mjname，clno，dno) pestablish 1dnodname deptlivedorplacedormitory 1 1dtotal dormitory ( dorplace，dno)dwkplacedept ( dno，dname，dwkplace，dtotal)(4) 邏輯結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計的任務(wù)就是將E-R圖轉(zhuǎn)換為關(guān)系模式。隨后應(yīng)該進行的工作是對關(guān)系模式的規(guī)范化和優(yōu)化，以及面向應(yīng)用程序設(shè)計的用戶子模式設(shè)計。F 邏輯結(jié)構(gòu)設(shè)計過程示意圖：1、E-R圖向關(guān)系模式的轉(zhuǎn)換1）. 一個實體型轉(zhuǎn)換為一個關(guān)系模式Ø 實體型的屬

10、性轉(zhuǎn)換為關(guān)系的屬性Ø 實體標(biāo)識符轉(zhuǎn)換為關(guān)系的碼2）. 聯(lián)系的轉(zhuǎn)換a) 一個 1:1 聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式，也可以與任意一端對應(yīng)的關(guān)系模式合并。 轉(zhuǎn)換為一個獨立的關(guān)系模式Ø 關(guān)系的屬性：與該聯(lián)系相連的各實體的標(biāo)識符以及 聯(lián)系本身的屬性Ø 關(guān)系的候選碼：每個實體標(biāo)識符均是該關(guān)系的候選 碼 與某一端對應(yīng)的關(guān)系模式合并Ø 合并后關(guān)系的屬性：加入另一關(guān)系的碼和聯(lián)系本身 的屬性Ø 合并后關(guān)系的碼：不變b) 一個 1:n 聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式，也可以與n端對應(yīng)的關(guān)系模式合并。 轉(zhuǎn)換為一個獨立的關(guān)系模式Ø 關(guān)系的屬性：與該聯(lián)系

11、相連的各實體的標(biāo)識符以及 聯(lián)系本身的屬性Ø 關(guān)系的碼：n端實體的標(biāo)識符 與n端對應(yīng)的關(guān)系模式合并Ø 合并后關(guān)系的屬性：在n端關(guān)系中加入1端關(guān)系的 碼和聯(lián)系本身的屬性Ø 合并后關(guān)系的碼：不變c) 一個m:n聯(lián)系轉(zhuǎn)換為一個關(guān)系模式。Ø 關(guān)系的屬性：與該聯(lián)系相連的各實體的標(biāo)識符以及 聯(lián)系本身的屬性Ø 關(guān)系的碼：各實體標(biāo)識符的組合d) 三個或三個以上實體間的一個多元聯(lián)系轉(zhuǎn)換為一個關(guān)系模式。Ø 關(guān)系的屬性：與該多元聯(lián)系相連的各實體的標(biāo)識符 以及聯(lián)系本身的屬性Ø 關(guān)系的碼：各實體標(biāo)識符的組合轉(zhuǎn)換結(jié)果如下：stuunion ( uno，u

12、name，ubiryear，uplace)sjoin (uno，sno，joinyear)student ( sno ，sname，sage，clno) class ( clno，clyear，cltotal)major ( mjname，clno，dno)dept ( dno，dname，dwkplace，dtotal)dormitory ( dorplace，dno)2、關(guān)系模式優(yōu)化現(xiàn)階段由概念模型到邏輯模型的過程通常就是將E-R圖轉(zhuǎn)換為關(guān)系模式，而經(jīng)過這一轉(zhuǎn)換過程之后得到的一組關(guān)系模式未必是最“好”的，還需要經(jīng)過進一步的優(yōu)化。F 關(guān)系模式的優(yōu)化應(yīng)該從以下幾方面實施：(1) 關(guān)系模式規(guī)范化

13、規(guī)范化的目的在于解決更新異常和數(shù)據(jù)冗余，應(yīng)該分解關(guān)系模式使其達到3NF或BCNF，對于存在MVD的關(guān)系模式應(yīng)達到4NF。1NF定義：如果一個關(guān)系模式R的所有屬性都是不可分的基本數(shù)據(jù)項，則稱關(guān)系R為第一范式的關(guān)系模式(First Normal Form)，簡稱關(guān)系R屬于一范式，記為：R1NF。 經(jīng)檢驗，以上7個關(guān)系模式，均滿足第一范式要求。2NF定義：若關(guān)系模式R1NF，并且每一個非主屬性都完全函數(shù)依賴于R的碼，則R2NF。 經(jīng)檢驗， 7個關(guān)系模式均滿足滿足第二范式。3NF定義： 關(guān)系模式R<U,F> 中若不存在這樣的碼X、屬性組Y及非主屬性 Z (Z Í Y ), 使得

14、XY， Y X ，YZ 成立，則稱R3NF。即：若R3NF，則R中的每一個非主屬性既不部分依賴于碼，也不傳遞依賴于碼。經(jīng)檢驗， 7個模式均滿足第三范式要求。所以，經(jīng)關(guān)系范式規(guī)范后，7個模式為：stuunion ( uno，uname，ubiryear，uplace)sjoin (uno，sno，joinyear)student ( sno ，sname，sage，clno) class ( clno，clyear，cltotal)major ( mjname，clno，dno)dept ( dno，dname，dwkplace，dtotal)dormitory ( dorplace，dno)(

15、2) 對關(guān)系模式進行必要的合并對具有關(guān)聯(lián)的關(guān)系模式進行合并，通常這類關(guān)系模式會經(jīng)常被查詢而頻繁地進行連接運算而降低查詢的效率，合并后的關(guān)系模式可能會帶來冗余(存在部分依賴或傳遞依賴)，但這樣做還是值得的。經(jīng)考察，不需要合并。(3) 進行合理的分解(不是為了達到更高范式)¯ 水平分解 把(基本)關(guān)系的元組分解為若干子集合，定義每個子集合為一個子關(guān)系(相同結(jié)構(gòu)的關(guān)系模式，但名稱不同)，以提高系統(tǒng)的效率。¯ 垂直分解把關(guān)系模式R的屬性分解為若干子集合，形成若干關(guān)系模式。Ø 垂直分解的原則：把經(jīng)常在一起使用的屬性從R中分解出來形成一個關(guān)系模式。垂直分解注意事項：Ø

16、; 應(yīng)從所有事務(wù)執(zhí)行的總效率出發(fā)考慮分解的必要性Ø 垂直分解必須不損失關(guān)系模式的語義(保持無損連接性和保持函數(shù)依賴)經(jīng)考察，不需要分解。最終優(yōu)化后的關(guān)系模式如下：stuunion ( uno，uname，ubiryear，uplace)sjoin (uno，sno，joinyear)student ( sno ，sname，sage，clno) major(mjname，clno，dno)class ( clno，clyear，cltotal)dept ( dno，dname，dwkplace，dtotal)dormitory ( dorplace，dno)(5) 數(shù)據(jù)庫物理設(shè)計為數(shù)

17、據(jù)庫設(shè)計一個合理的存儲結(jié)構(gòu)和存取方法。F 關(guān)系數(shù)據(jù)庫物理設(shè)計的內(nèi)容¯ 為關(guān)系模式選擇存取方法(建立存取路徑)¯ 2. 設(shè)計關(guān)系、索引等數(shù)據(jù)庫文件的物理存儲結(jié)構(gòu)F 確定數(shù)據(jù)庫存儲結(jié)構(gòu)的基本原則 根據(jù)應(yīng)用情況將易變部分與穩(wěn)定部分、存取頻率較高部分與存 取頻率較低部分分開存放，以提高系統(tǒng)性能F 評價內(nèi)容 對數(shù)據(jù)庫物理設(shè)計過程中產(chǎn)生的多種方案進行細致的評價，從 中選擇一個較優(yōu)的方案作為數(shù)據(jù)庫的物理結(jié)構(gòu)。F 評價方法¯ 定量估算各種方案Ø 存儲空間Ø 存取時間Ø 維護代價¯ 對估算結(jié)果進行權(quán)衡、比較，選擇出一個較優(yōu)的合理的物理結(jié)構(gòu)&#

18、175; 如果該結(jié)構(gòu)不符合用戶需求，則需要修改設(shè)計(6) 數(shù)據(jù)庫實施 編寫與調(diào)試應(yīng)用程序（后臺數(shù)據(jù)庫、前端界面），組織數(shù)據(jù)入庫，并進行試運行。 1、定義數(shù)據(jù)庫結(jié)構(gòu)確定了數(shù)據(jù)庫的邏輯結(jié)構(gòu)與物理結(jié)構(gòu)后，就可以用所選用的DBMS提供的數(shù)據(jù)定義語言（DDL）來嚴(yán)格描述數(shù)據(jù)庫結(jié)構(gòu)。1.表stuunion ( uno，uname，ubiryear，uplace)create table stuunion(uno char(6) primary key,uname varchar(20) not null unique,ubiryear int,uplace varchar(20) );2.表sjoin (

19、uno，sno，joinyear)create table sjoin(uno char(6) references stuunion(uno),sno char(8) references student(sno),joinyear int,primary key (uno,sno);3.表student ( sno ，sname，sage，clno) create table student(sno char(8) primary key,sname varchar(20) not null unique,sage smallint,clno char(6) references clas

20、s(clno) ;4.表major( mjname，clno， dno)create table major(mjname varchar(20) primary key,clno char(6) references class(clno),dno char(2) references dept(dno);5.表class ( clno，clyear，cltotal)create table class (clno char(6) primary key,clyear int,cltotal int );6.表dept ( dno，dname，dwkplace，dtotal)create t

21、able dept(dno char(2) primary key,dname varchar(20) not null unique,dwkplace varchar(20),dtotal int);7.表dormitory ( dorplace，dno)create table dormitory(dorplace varchar(20),dno char(2) references dept(dno);常用命令實例：1、導(dǎo)出數(shù)據(jù)和表結(jié)構(gòu)：mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫名 > 數(shù)據(jù)庫名.sql2、文本數(shù)據(jù)應(yīng)符合的格式：字段數(shù)據(jù)之間用tab鍵隔開，例： name du

22、ty 2006-11-23數(shù)據(jù)傳入命令 load data local infile "文件名" into table 表名;3、插入記錄：insert into sjoin values(100001,00101313,2011);4、修改記錄值：update sjoin set sno=00101303 where sno=101303;5、修改屬性：alter table class change column cltoal cltotal int;創(chuàng)建視圖：create view uname_utotal(uname,utotal) -> as -> s

23、elect uname,count(*) -> from stuunion,sjoin -> where stuunion.uno=sjoin.uno -> group by stuunion.uno / -> with check option;創(chuàng)建觸發(fā)器：DELIMITER |create trigger triinsert_cltotal_dtotal after inserton student for each rowbegindeclare c char(2);update class set cltotal = cltotal + 1 where clno

24、= new.clno;set c=(select dept.dno from major,dept where major.dno =dept.dno and major.clno=new.clno);update dept set dtotal=dtotal+1 where dno=c;end|DELIMITER ;DELIMITER |create trigger tridelete_cltotal_dtotal after deleteon student for each rowbegindeclare c char(2);update class set cltotal = clto

25、tal - 1 where clno= old.clno;set c=(select dept.dno from major,dept where major.dno =dept.dno and major.clno=old.clno);update dept set dtotal=dtotal-1 where dno=c;end|DELIMITER ;創(chuàng)建存儲過程：delimiter #create procedure modifycno(in oldcno char(6),in newcno char(6),out class_total int)beginupdate student s

26、et clno=newcno where clno=oldcno;update major set clno=newcno where clno=oldcno;update class set clno=newcno where clno=oldcno;select cltotal into class_total from class where clno=newcno;end #delimiter ; 調(diào)用：call modifycno('001013','123456',class_total);select class_total;創(chuàng)建游標(biāo)：游標(biāo)實現(xiàn)了m

27、ysql的存儲過程中循環(huán)讀取數(shù)據(jù)表中的對象的過程。聲明游標(biāo)： DECLARE cursor_name CURSOR FOR select_statement這個語句聲明一個光標(biāo)。也可以在子程序中定義多個光標(biāo)，但是一個塊中的每一個光標(biāo)必須有唯一的名字。打開游標(biāo): OPEN cursor_name游標(biāo)FETCH：FETCH cursor_name INTO var_name , var_name .這個語句用指定的打開光標(biāo)讀取下一行（如果有下一行的話），并且前進光標(biāo)指針。 關(guān)閉游標(biāo)CLOSE： CLOSE cursor_name這個語句關(guān)閉先前打開的光標(biāo)。如果未被明確地關(guān)閉，光標(biāo)在它被聲明的復(fù)合語句的末尾被關(guān)閉。delimiter #create procedure checkdtotal()begindeclare done2 int default 0;declare student_no char(8);declare class_no char(6);declare dept_no char(2);declare dept_name varchar(20);declare a,b,total int;declare cur1 cursor for select sno,clno from student;declare cur2 cursor for select dno,d