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

1、數(shù)據(jù)庫(kù)大作業(yè)學(xué)生管理系統(tǒng)組 長(zhǎng)：趙呈亮00101303設(shè)計(jì)成員：湯雨00101304 桑迪00101308 指導(dǎo)老師：祁建軍 設(shè)計(jì)時(shí)間：2013/6/20 組長(zhǎng):趙呈亮00101303主要任務(wù):1、根據(jù)語(yǔ)義畫出分E-R圖,并集成基本E-R圖 將E-R圖轉(zhuǎn)換為關(guān)系模式，并進(jìn)行模式優(yōu)化 2、linux中MYSQL下編寫SQL代碼創(chuàng)建視圖、觸 發(fā)器、存儲(chǔ)過(guò)程、游標(biāo)等 3、全部文檔撰寫 4、答辯主講主要成員1：湯雨00101304主要任務(wù)：1、 linux環(huán)境下用PHP語(yǔ)言編寫前端，實(shí)現(xiàn)查 詢、修改、插入等操作的可視化。2、參與后臺(tái)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ù)庫(kù)設(shè)計(jì)7步(1) 規(guī)劃階段建立數(shù)據(jù)庫(kù)的必要性和可行性分析。(2) 需求分析全面、準(zhǔn)確了解用戶的實(shí)際要求。 (3) 概念結(jié)構(gòu)設(shè)計(jì)設(shè)計(jì)數(shù)據(jù)庫(kù)的概念結(jié)構(gòu)，形成一個(gè)獨(dú)立于具體DBMS的概念模型。今要建立關(guān)于系、學(xué)生、班級(jí)、學(xué)會(huì)等諸信息的一個(gè)關(guān)系數(shù)據(jù)庫(kù)。一個(gè)系有若干專業(yè)，每個(gè)專業(yè)每年只招一個(gè)班，每個(gè)班有若干學(xué)生。一個(gè)系的學(xué)生住在同一宿舍區(qū)。每個(gè)學(xué)生可參加若干學(xué)會(huì)，每個(gè)學(xué)會(huì)有若干學(xué)生。學(xué)生參加某學(xué)會(huì)有一個(gè)入會(huì)年份。描述各個(gè)實(shí)體的屬性（加下劃線者為主碼）如下： 學(xué)生：學(xué)號(hào)、姓名、年齡、系名、班號(hào)、宿舍

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

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

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

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

7、意點(diǎn): 1. 在E-R圖的綜合的過(guò)程中，同名實(shí)體只能出現(xiàn)一次，還要去掉不必要的聯(lián)系，且不能出現(xiàn)環(huán)路，這樣才能消除冗余數(shù)據(jù)和冗余聯(lián)系。 2. 一般來(lái)說(shuō)，從總體E-R圖必須能導(dǎo)出原來(lái)所有局部E-R視圖，包括所有的實(shí)體、屬性和聯(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è)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì)的任務(wù)就是將E-R圖轉(zhuǎn)換為關(guān)系模式。隨后應(yīng)該進(jìn)行的工作是對(duì)關(guān)系模式的規(guī)范化和優(yōu)化，以及面向應(yīng)用程序設(shè)計(jì)的用戶子模式設(shè)計(jì)。F 邏輯結(jié)構(gòu)設(shè)計(jì)過(guò)程示意圖：1、E-R圖向關(guān)系模式的轉(zhuǎn)換1）. 一個(gè)實(shí)體型轉(zhuǎn)換為一個(gè)關(guān)系模式Ø 實(shí)體型的屬

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

11、相連的各實(shí)體的標(biāo)識(shí)符以及 聯(lián)系本身的屬性Ø 關(guān)系的碼：n端實(shí)體的標(biāo)識(shí)符 與n端對(duì)應(yīng)的關(guān)系模式合并Ø 合并后關(guān)系的屬性：在n端關(guān)系中加入1端關(guān)系的 碼和聯(lián)系本身的屬性Ø 合并后關(guān)系的碼：不變c) 一個(gè)m:n聯(lián)系轉(zhuǎn)換為一個(gè)關(guān)系模式。Ø 關(guān)系的屬性：與該聯(lián)系相連的各實(shí)體的標(biāo)識(shí)符以及 聯(lián)系本身的屬性Ø 關(guān)系的碼：各實(shí)體標(biāo)識(shí)符的組合d) 三個(gè)或三個(gè)以上實(shí)體間的一個(gè)多元聯(lián)系轉(zhuǎn)換為一個(gè)關(guān)系模式。Ø 關(guān)系的屬性：與該多元聯(lián)系相連的各實(shí)體的標(biāo)識(shí)符 以及聯(lián)系本身的屬性Ø 關(guān)系的碼：各實(shí)體標(biāo)識(shí)符的組合轉(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)階段由概念模型到邏輯模型的過(guò)程通常就是將E-R圖轉(zhuǎn)換為關(guān)系模式，而經(jīng)過(guò)這一轉(zhuǎn)換過(guò)程之后得到的一組關(guān)系模式未必是最“好”的，還需要經(jīng)過(guò)進(jìn)一步的優(yōu)化。F 關(guān)系模式的優(yōu)化應(yīng)該從以下幾方面實(shí)施：(1) 關(guān)系模式規(guī)范化

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

14、XY， Y X ，YZ 成立，則稱R3NF。即：若R3NF，則R中的每一個(gè)非主屬性既不部分依賴于碼，也不傳遞依賴于碼。經(jīng)檢驗(yàn)， 7個(gè)模式均滿足第三范式要求。所以，經(jīng)關(guān)系范式規(guī)范后，7個(gè)模式為：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) 對(duì)關(guān)系模式進(jìn)行必要的合并對(duì)具有關(guān)聯(lián)的關(guān)系模式進(jìn)行合并，通常這類關(guān)系模式會(huì)經(jīng)常被查詢而頻繁地進(jìn)行連接運(yùn)算而降低查詢的效率，合并后的關(guān)系模式可能會(huì)帶來(lái)冗余(存在部分依賴或傳遞依賴)，但這樣做還是值得的。經(jīng)考察，不需要合并。(3) 進(jìn)行合理的分解(不是為了達(dá)到更高范式)¯ 水平分解 把(基本)關(guān)系的元組分解為若干子集合，定義每個(gè)子集合為一個(gè)子關(guān)系(相同結(jié)構(gòu)的關(guān)系模式，但名稱不同)，以提高系統(tǒng)的效率。¯ 垂直分解把關(guān)系模式R的屬性分解為若干子集合，形成若干關(guān)系模式。Ø 垂直分解的原則：把經(jīng)常在一起使用的屬性從R中分解出來(lái)形成一個(gè)關(guān)系模式。垂直分解注意事項(xiàng)：Ø

16、; 應(yīng)從所有事務(wù)執(zhí)行的總效率出發(fā)考慮分解的必要性Ø 垂直分解必須不損失關(guān)系模式的語(yǔ)義(保持無(wú)損連接性和保持函數(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ù)庫(kù)物理設(shè)計(jì)為數(shù)

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

18、175; 如果該結(jié)構(gòu)不符合用戶需求，則需要修改設(shè)計(jì)(6) 數(shù)據(jù)庫(kù)實(shí)施 編寫與調(diào)試應(yīng)用程序（后臺(tái)數(shù)據(jù)庫(kù)、前端界面），組織數(shù)據(jù)入庫(kù)，并進(jìn)行試運(yùn)行。 1、定義數(shù)據(jù)庫(kù)結(jié)構(gòu)確定了數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)與物理結(jié)構(gòu)后，就可以用所選用的DBMS提供的數(shù)據(jù)定義語(yǔ)言（DDL）來(lái)嚴(yán)格描述數(shù)據(jù)庫(kù)結(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);常用命令實(shí)例：1、導(dǎo)出數(shù)據(jù)和表結(jié)構(gòu)：mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫(kù)名 > 數(shù)據(jù)庫(kù)名.sql2、文本數(shù)據(jù)應(yīng)符合的格式：字段數(shù)據(jù)之間用tab鍵隔開(kāi)，例： 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)建存儲(chǔ)過(guò)程：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)實(shí)現(xiàn)了m

27、ysql的存儲(chǔ)過(guò)程中循環(huán)讀取數(shù)據(jù)表中的對(duì)象的過(guò)程。聲明游標(biāo)： DECLARE cursor_name CURSOR FOR select_statement這個(gè)語(yǔ)句聲明一個(gè)光標(biāo)。也可以在子程序中定義多個(gè)光標(biāo)，但是一個(gè)塊中的每一個(gè)光標(biāo)必須有唯一的名字。打開(kāi)游標(biāo): OPEN cursor_name游標(biāo)FETCH：FETCH cursor_name INTO var_name , var_name .這個(gè)語(yǔ)句用指定的打開(kāi)光標(biāo)讀取下一行（如果有下一行的話），并且前進(jìn)光標(biāo)指針。 關(guān)閉游標(biāo)CLOSE： CLOSE cursor_name這個(gè)語(yǔ)句關(guān)閉先前打開(kāi)的光標(biāo)。如果未被明確地關(guān)閉，光標(biāo)在它被聲明的復(fù)合語(yǔ)句的末尾被關(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