數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告

1、精選優(yōu)質(zhì)文檔-傾情為你奉上學(xué)生管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)報(bào)告一：實(shí)驗(yàn)小組成員：組長： 主要工作：設(shè)計(jì)關(guān)系模式圖，設(shè)計(jì)存儲(chǔ)過程和檢測(cè)腳本組員： 主要工作：設(shè)計(jì)ER圖，觸發(fā)器 主要工作：設(shè)計(jì)表，視圖，并完成系統(tǒng)測(cè)試二：數(shù)據(jù)庫設(shè)計(jì)要求建立關(guān)于系、學(xué)生、班級(jí)、學(xué)會(huì)等諸信息的一個(gè)關(guān)系數(shù)據(jù)庫。一個(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)，性別，宿舍區(qū)。 班級(jí)：班號(hào)、專業(yè)名、入校年份、系名、人數(shù)。 系 ：系號(hào)、系名、系辦

2、公室地點(diǎn)、人數(shù)。 學(xué)會(huì)：學(xué)會(huì)號(hào)、學(xué)會(huì)名、成立年份、地點(diǎn)。三：ER圖設(shè)計(jì)1：學(xué)生實(shí)體的ER圖2：班級(jí)實(shí)體的ER圖3：系實(shí)體的ER圖4：學(xué)會(huì)實(shí)體的ER圖5：實(shí)體及聯(lián)系ER圖6：完整的ER圖四：關(guān)系模式1：由ER圖直接得到的關(guān)系模型學(xué)生（學(xué)號(hào)，姓名，年齡，系名，班號(hào)，性別，宿舍區(qū)）班級(jí)（班號(hào)，專業(yè)名，人數(shù)，系名，入校年份）系（系號(hào)，系名，人數(shù)，系辦公室地點(diǎn)）學(xué)會(huì)（學(xué)會(huì)號(hào)，學(xué)會(huì)名，地點(diǎn)，成立年份）參加學(xué)會(huì)（學(xué)號(hào)，學(xué)會(huì)號(hào)，入會(huì)年份）2：關(guān)系模型的合并和分解（1） 學(xué)生模型中的系名屬性是冗余的，可以由學(xué)生所在的班級(jí)查到該同學(xué)所在的 系。（2） 班級(jí)中的專業(yè)名屬性是冗余的，由于一個(gè)專業(yè)可以有多名學(xué)生，如果專

3、業(yè)名 發(fā)生變化，則要求改很多的學(xué)生信息，不符合數(shù)據(jù)庫的規(guī)范化原理，所以分 解這個(gè)模型為兩個(gè)模型。班級(jí)（班號(hào)，專業(yè)號(hào)，人數(shù)，系名，入校年份）專業(yè)（專業(yè)號(hào)，系號(hào)，專業(yè)名）（3） 經(jīng)過2的分解后，班級(jí)關(guān)系中的系名是冗余項(xiàng)，可以由專業(yè)查到，刪除。（4） 經(jīng)過以上的分解合并后的關(guān)系模型如下： 學(xué)生（學(xué)號(hào)，班號(hào)，姓名，年齡，性別，宿舍區(qū)）班級(jí)（班號(hào)，專業(yè)號(hào)，人數(shù)，入校年份）專業(yè)（專業(yè)號(hào)，系號(hào)，專業(yè)名）系（系號(hào)，系名，人數(shù)，系辦公室地點(diǎn)）學(xué)會(huì)（學(xué)會(huì)號(hào)，學(xué)會(huì)名，地點(diǎn)，成立年份）參加學(xué)會(huì)（記錄號(hào)，學(xué)號(hào)，學(xué)會(huì)號(hào)，入會(huì)年份）五：建表1：代碼如下CREATE TABLE class ( class_id varcha

4、r(8) collate utf8_unicode_ci NOT NULL, major_id varchar(8) collate utf8_unicode_ci NOT NULL, class_count int(11) default '0', year_in date NOT NULL, PRIMARY KEY (class_id), KEY major_id (major_id), FOREIGN KEY (major_id) REFERENCES major (major_id) ON DELETE CASCADE ON UPDATE CASCADE) CREATE

5、 TABLE dept ( dept_id varchar(8) collate utf8_unicode_ci NOT NULL, dept_name varchar(20) collate utf8_unicode_ci NOT NULL, dept_count int(11) default '0', dept_dest varchar(20) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (dept_id) CREATE TABLE major ( major_id varchar(8) collate utf8_unico

6、de_ci NOT NULL, dept_id varchar(8) collate utf8_unicode_ci NOT NULL, major_name varchar(20) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (major_id), KEY dept_id (dept_id), FOREIGN KEY (dept_id) REFERENCES dept (dept_id) ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE org ( org_id varchar(8) colla

7、te utf8_unicode_ci NOT NULL, org_name varchar(20) collate utf8_unicode_ci NOT NULL, org_des varchar(20) collate utf8_unicode_ci NOT NULL, org_year date NOT NULL, PRIMARY KEY (org_id) CREATE TABLE org_stu ( org_stu_id varchar(8) collate utf8_unicode_ci NOT NULL, s_id varchar(8) collate utf8_unicode_c

8、i NOT NULL, org_id varchar(8) collate utf8_unicode_ci NOT NULL, year_in date NOT NULL, PRIMARY KEY (org_stu_id), KEY s_id (s_id), KEY org_id (org_id), FOREIGN KEY (s_id) REFERENCES student (s_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (org_id) REFERENCES org (org_id) ON DELETE CASCADE ON U

9、PDATE CASCADE) CREATE TABLE student ( s_id varchar(8) collate utf8_unicode_ci NOT NULL, class_id varchar(8) collate utf8_unicode_ci NOT NULL, name varchar(20) collate utf8_unicode_ci NOT NULL, age tinyint(3) unsigned NOT NULL, sex tinyint(1) NOT NULL, area varchar(20) collate utf8_unicode_ci NOT NUL

10、L, PRIMARY KEY (s_id), KEY class_id (class_id), FOREIGN KEY (class_id) REFERENCES class (class_id) ON DELETE CASCADE ON UPDATE CASCADE) 六：創(chuàng)建視圖1：創(chuàng)建視圖 org_count(org_name,org_count) 這個(gè)視圖能顯示每個(gè)學(xué)會(huì)的學(xué)會(huì)名，學(xué)生數(shù)。CREATE VIEW org_count AS select _name AS org_name,count(org_stu.s_id) AS org_count from (org j

11、oin org_stu) where (_id = org__id) group by org__id;七：創(chuàng)建觸發(fā)器觸發(fā)器根據(jù)每個(gè)班的學(xué)生變動(dòng)情況自動(dòng)增減班級(jí)表和系表的人數(shù)字段的值?？偣踩齻€(gè)觸發(fā)器，在學(xué)生人數(shù)發(fā)生增減，或者學(xué)生所轉(zhuǎn)班之后都會(huì)觸發(fā)觸發(fā)器，更新數(shù)據(jù)DELIMITER ;CREATE TRIGGER student_insert AFTER INSERT ON student FOR EACH ROW begin Update class set class_count=class_count+1 where class_id=new.cl

12、ass_id; update dept set dept_count=dept_count+1 where dept_id=(select dept_id from major where major_id=(select major_id from class where class_id=new.class_id); end;DELIMITER ;DELIMITER ;CREATE TRIGGER student_update AFTER UPDATE ON student FOR EACH ROW begin update class set class_count=class_coun

13、t+1 where class_id=new.class_id; update class set class_count=class_count-1 where class_id=old.class_id; update dept set dept_count=dept_count+1 where dept_id=(select dept_id from major where major_id=(select major_id from class where class_id=new.class_id); update dept set dept_count=dept_count-1 w

14、here dept_id=(select dept_id from major where major_id=(select major_id from class where class_id=old.class_id);end;DELIMITER ;DELIMITER ;CREATE TRIGGER student_delete AFTER DELETE ON student FOR EACH ROW begin update class set class_count=class_count-1 where class_id=old.class_id;update dept set de

15、pt_count=dept_count-1 where dept_id=(select dept_id from major where major_id=(select major_id from class where class_id=old.class_id);end;DELIMITER ;八：創(chuàng)建存儲(chǔ)過程 這個(gè)存儲(chǔ)過程實(shí)現(xiàn)如下功能：給定一個(gè)班的舊班號(hào)和新班號(hào)，把所有相關(guān)表中此班的舊班號(hào)改為新班號(hào)，并返回此班的人數(shù)（使用輸出參數(shù)）。調(diào)用方法： call new_class_id(arg1,arg2,arg3);其中arg1和arg2分別輸入舊班號(hào)和新班號(hào)，arg3輸出這個(gè)班的人數(shù)DRO

16、P PROCEDURE IF EXISTS new_class_id;DELIMITER ;CREATE PROCEDURE new_class_id(IN old_idvarchar(8), INnew_id varchar(8),OUT counts int)BEGINdeclare temp varchar(8);declare class_counts int;declare fetcherro int default 0;declare cur cursor for select class_id,class_count from class where class_id=old_i

17、d;declare continue handler for not found set fetcherro=1;open cur;fetch cur into temp,class_counts;if fetcherro=0 thenupdate class set class_id=new_id where class_id=old_id;select class_counts as "本班人數(shù)" ;set counts=class_counts;elseselect "這個(gè)班不存在" as "參數(shù)錯(cuò)誤"end if;END;DE

18、LIMITER ;九：腳本本腳本實(shí)現(xiàn)如下功能，檢查系中的人數(shù)是否與實(shí)際情況相同，若不同，則改為實(shí)際情況，并返回該記錄，包括改前的記錄和改后的記錄。腳本中使用了臨時(shí)表保存查到的不正確的數(shù)據(jù)！delimiter /create table temp(dept_id varchar(8),dept_name varchar(20),dept_count_old int(11),dept_count_new int(11)/drop procedure if exists counts_check/create procedure counts_check()BEGINdeclare temp1,te

19、mp2 varchar(8);declare name varchar(20);declare class_counts,dept_counts,students_counts1, students_counts2 int;declare cur_class cursor for select class_id,class_count from class;declare cur_dept cursor for select dept_id,dept_name,dept_count from dept;select count(*) into class_counts from class;s

20、elect count(*) into dept_counts from dept;open cur_class;while class_counts>0 dofetch cur_class into temp1,students_counts1;select count(*) into students_counts2 from student where class_id=temp1;if students_counts1!=students_counts2 then update class set class_count=students_counts2 where class_

21、id=temp1;end if;set class_counts=class_counts-1;end while;close cur_class;open cur_dept;while dept_counts>0 dofetch cur_dept into temp2,name,students_counts1;select count(*) into students_counts2 from student where class_id=(select class_id from class where major_id=(select major_id from major where dept_id=temp2);if students_counts1!=students_counts2 thenupdate dept set dept_count=students_counts2 where dept_id=temp2;insert into temp values(temp2,name,students_counts1,students_counts