數據庫實驗報告

1、精選優(yōu)質文檔-傾情為你奉上學生管理系統數據庫設計報告一：實驗小組成員：組長： 主要工作：設計關系模式圖，設計存儲過程和檢測腳本組員： 主要工作：設計ER圖，觸發(fā)器 主要工作：設計表，視圖，并完成系統測試二：數據庫設計要求建立關于系、學生、班級、學會等諸信息的一個關系數據庫。一個系有若干專業(yè)，每個專業(yè)每年只招一個班，每個班有若干學生。一個系的學生住在同一宿舍區(qū)。每個學生可參加若干學會，每個學會有若干學生。學生參加某學會有一個入會年份。描述各個實體的屬性（加下劃線者為主碼）如下： 學生：學號、姓名、年齡、系名、班號，性別，宿舍區(qū)。 班級：班號、專業(yè)名、入校年份、系名、人數。 系 ：系號、系名、系辦

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

3、業(yè)名 發(fā)生變化，則要求改很多的學生信息，不符合數據庫的規(guī)范化原理，所以分 解這個模型為兩個模型。班級（班號，專業(yè)號，人數，系名，入校年份）專業(yè)（專業(yè)號，系號，專業(yè)名）（3） 經過2的分解后，班級關系中的系名是冗余項，可以由專業(yè)查到，刪除。（4） 經過以上的分解合并后的關系模型如下： 學生（學號，班號，姓名，年齡，性別，宿舍區(qū)）班級（班號，專業(yè)號，人數，入校年份）專業(yè)（專業(yè)號，系號，專業(yè)名）系（系號，系名，人數，系辦公室地點）學會（學會號，學會名，地點，成立年份）參加學會（記錄號，學號，學會號，入會年份）五：建表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) 這個視圖能顯示每個學會的學會名，學生數。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ā)器根據每個班的學生變動情況自動增減班級表和系表的人數字段的值?？偣踩齻€觸發(fā)器，在學生人數發(fā)生增減，或者學生所轉班之后都會觸發(fā)觸發(fā)器，更新數據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)建存儲過程 這個存儲過程實現如下功能：給定一個班的舊班號和新班號，把所有相關表中此班的舊班號改為新班號，并返回此班的人數（使用輸出參數）。調用方法： call new_class_id(arg1,arg2,arg3);其中arg1和arg2分別輸入舊班號和新班號，arg3輸出這個班的人數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 "本班人數" ;set counts=class_counts;elseselect "這個班不存在" as "參數錯誤"end if;END;DE

18、LIMITER ;九：腳本本腳本實現如下功能，檢查系中的人數是否與實際情況相同，若不同，則改為實際情況，并返回該記錄，包括改前的記錄和改后的記錄。腳本中使用了臨時表保存查到的不正確的數據！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