《sql數(shù)據(jù)庫管理系統(tǒng)大作業(yè)【教學作業(yè)】》由會員分享，可在線閱讀，更多相關《sql數(shù)據(jù)庫管理系統(tǒng)大作業(yè)【教學作業(yè)】（7頁珍藏版）》請在裝配圖網上搜索。

1、 學生信息管理系統(tǒng)數(shù)據(jù)庫設計與實現(xiàn) 一、 系統(tǒng)需求 1、該“學生信息管理系統(tǒng)”是對數(shù)據(jù)庫應用技術的一個樣本數(shù)據(jù)庫的實例，其中包括從數(shù)據(jù)庫的概念模型到ER圖的繪制，再到數(shù)據(jù)庫模式的建立（即為數(shù)據(jù)庫及其基本表的建立），而后數(shù)據(jù)的錄入（在此從略），最后完成一些簡單的關系運算表達式的表達和相關的Select 查詢語句的使用。 2、系統(tǒng)結構簡述： 本系統(tǒng)包括三個實體：院系；學生檔案；課程； 實體之間的關系轉換為其他表的有：選修； 實體“院系”與“學生檔案”之間有一對多的關系，把院系表的主碼轉放到表“學生檔案”中； 實體“學生檔案”與“課程”之間有多對多的關系，因此獨立形成一個表并共同

2、存儲“學生檔案”與“課程”表的主碼和一些自己的屬性列； 3、該數(shù)據(jù)庫需要進行如下數(shù)據(jù)處理： ◎從選修表中查詢成績的函數(shù)： 輸入：學號和課程編號 輸出：對應的學號和課程編號的分數(shù)即成績； ◎從學生檔案表中查詢學生基本信息的函數(shù)： 輸入：學號 輸出：對應的學號的基本信息，即學號，姓名，性別，專業(yè)，聯(lián)系方式，聯(lián)系地址，院系編號； ◎從學生檔案表中刪除對應的學生記錄： 輸入：學號 輸出：刪除對應的學生記錄，若沒有對應的學號則輸出“沒有該學生!”的提示； ◎ 向學生檔案表插入數(shù)據(jù)的函數(shù)： 輸入：學生檔案表的基本信息，包括所有屬性列；即學號，姓名，性別，專業(yè)，聯(lián)系方式

3、，聯(lián)系地址，院系編號； ◎級聯(lián)更新（從院系表到學生檔案表、課程表）： 輸入：院系編號，院系名稱 ◎成績表上的觸發(fā)器，當刪除某一個學生的成績時觸發(fā)： 當從學生檔案表中刪除學生記錄時，若該學號的學生記錄在選修表中也有的話，則提示 二、概念設計（設計E – R模型） 學生檔案 選修 課程 局部E-R模型Ⅱ 局部E-R模型Ⅰ 所屬院系 院系 局部E-R模型Ⅲ 屬于 課程 院系 學生檔案

4、 表1.實體間的相互聯(lián)系 實體 聯(lián)系 實體 院系 屬于 課程 學生檔案 選修 課程 院系 所屬院 學生檔案 學生檔案 選修 課程 分數(shù) m n n 1 課程編號# 課程名稱 所屬院系 院系 n 1 院系編號# 院系名稱 學號# 姓名 性別 專業(yè) 聯(lián)系方式 聯(lián)系地址 學時 屬于 學生信息管理系統(tǒng)的E-R模型 三、邏輯設計（轉換成關系模型） 系統(tǒng)中的實體有：學

5、生檔案；課程；院系； 關系模式如下： 學生檔案（學號＃，姓名，性別，專業(yè)，聯(lián)系方式，聯(lián)系地址） PK=學號，NOT NULL 課程（課程編號＃，課程名稱，學時，院系編號） PK=課程編號，NOT NULL FK=院系編號，參照院系表 選修（學號＃，課程編號＃，分數(shù)） PK=< 學號，課程編號 >，NOT NULL FK=學號，參照學生檔案表 FK=課程編號，參照課程表 院系（院系編號＃，院系名稱） PK=院系編號，NOT NULL 四、物理設計 表1 學生檔案表結構 字段名 類型 特殊屬性 學號 char(12) Not null 姓名 char(2

6、2) null 性別 char(2) null 專業(yè) char(16) Null 聯(lián)系方式 char(12) Null 聯(lián)系地址 char(40) null 院系編號 smallint null 表2 院系表結構 字段名 類型 特殊屬性 院系編號 Smallint Not null 院系名稱 char(16) null 表3 課程表結構 字段名 類型 特殊屬性 課程編號 char(10) Not null 課程名稱 char(20) null 學時 Smallint null 院系編號 small

7、int Null 表4 選修表結構 字段名 類型 特殊屬性 學號 char(12) Not null 課程編號 char(10) Not null 分數(shù) Numeric(4,1) null 五、SQL源代碼 1、create database 學生信息管理 create table 院系 ( 院系編號 smallint, 院系名稱 char(16), primary key(院系編號) ) 2、create table 學生檔案 ( 學號 char(12), 姓名 char(22), 性別

8、char(2)check(性別 in ('男','女')), 專業(yè) char(16), 聯(lián)系方式 char(12), 聯(lián)系地址 char(40), 院系編號 smallint, primary key(學號), foreign key(院系編號)references 院系(院系編號)on update cascade ) 3、create table 課程 ( 課程編號 char(10), 課程名稱 char(20), 學時 smallint, 院系編號 smallint, primary key(課程編號), foreign key(

9、院系編號)references 院系(院系編號)on update cascade ) 4、create table 選修 ( 學號 char(12), 課程編號 char(10), 分數(shù) numeric(4,1)check(分數(shù)<='100.0' and 分數(shù) >='0'), primary key(學號,課程編號), foreign key(學號)references 學生檔案(學號) on update no action, foreign key(課程編號)references 課程(課程編號) on update no action ) 5、

10、從選修表中查詢成績的函數(shù) if exists(select name from sysobjects where name='成績查詢'and type='p') drop procedure 成績查詢 go create procedure 成績查詢 @Sno char(12),@Cno char(10) as if exists(select 學號,課程編號 from 選修 where 學號=@Sno and 課程編號=@Cno) select 學號,課程編號,分數(shù) from 選修 where 學號=@Sno and 課程編號=@Cno else

11、 print '沒有該學號的學生或課程' go execute 成績查詢 '08080605227','06232023' 6、從學生檔案表中查詢學生基本信息的函數(shù) if exists(select name from sysobjects where name='學生基本信息查詢' and type='p') drop procedure 學生基本信息查詢 go create procedure 學生基本信息查詢 @Sno char(12) as if exists(select 學號 from 學生檔案 where 學號=@Sno) select *

12、 from 學生檔案 where 學號=@Sno else print '不存在這樣的學生!' go execute 學生基本信息查詢 '08080605227' 7、從學生檔案表中刪除對應的學生 if exists(select name from sysobjects where name='刪除學生記錄'and type='p') drop procedure 刪除學生記錄 go create procedure 刪除學生記錄 @Sno char(12) as if exists(select * from 學生檔案 where 學號=@S

13、no) delete from 學生檔案 where 學號=@Sno else print '沒有該學生!' go execute 刪除學生記錄 '12345678910' execute 刪除學生記錄 '09080605227' 8、向學生檔案表插入數(shù)據(jù)的函數(shù) if exists(select name from sysobjects where name='向學生檔案表輸入'and type='p') drop procedure 向學生檔案表輸入 go create procedure 向學生檔案表輸入 @Sno char(12),@Sname c

14、har(22),@Ssex char(2),@Szhuanye char(16),@Scontact char(12),@Saddress char(40),@Dno smallint as insert into 學生檔案 values(@Sno,@Sname,@Ssex,@Szhuanye,@Scontact,@Saddress,@Dno) go execute 向學生檔案表輸入 '09080605227','jack','男','計算機','15012345652','abbbb','1010' execute 向學生檔案表輸入 '12345678910','john','男

15、','計算機','15292768459','abcdefg','1010' 9、級聯(lián)更新（從院系表到學生檔案表、課程表） if exists(select name from sysobjects where name='更新院系編號'and type='p') drop procedure 更新院系編號 go create procedure 更新院系編號 @Dno smallint,@Dname char(16) as update 院系 set 院系編號=@Dno where 院系名稱=@Dname go execute 更新院系編號 1010,'計算

16、機科學系' 10、成績表上的觸發(fā)器，當刪除某一個學生的成績時觸發(fā) if exists(select name from sysobjects where name='選修表上的觸發(fā)器'and type='tr') drop trigger 選修表上的觸發(fā)器 go create trigger 選修表上的觸發(fā)器 on 學生檔案 for delete as if exists(select 學號 from deleted where 學號 in(select 學號 from 選修)) begin print '該學生有成績，所以不能刪除!' rollback end go 7 教育高等a