oraclo教務(wù)管理數(shù)據(jù)庫

1、期末作業(yè)科目： 大型數(shù)據(jù)庫系統(tǒng)設(shè)計 信 息 工 程 學(xué)院 計算機(jī)科學(xué)與技術(shù)（軟件工程）專業(yè)XXX級XXX班學(xué)號：XXXX 姓名：XXX 成績：一、按照相關(guān)背景和所給知識，完成相關(guān)內(nèi)容。背景：教務(wù)處是學(xué)校教學(xué)及日常教務(wù)管理的核心部門。該處主要包括：考試中心、教研科、教務(wù)科、學(xué)籍管理科、教學(xué)實踐科以及高教研究等科室。根據(jù)其職能劃分，教學(xué)管理主要分為：l 學(xué)生學(xué)籍管理l 教學(xué)計劃管理l 排課管理l 成績管理l 考務(wù)管理l 教學(xué)評估管理l 教研項目管理l 該作業(yè)針對教學(xué)管理中的學(xué)生成績管理模塊。學(xué)生成績管理模塊有如下的表（完成表中填充的部分）：表1 學(xué)院表（db_college）字段標(biāo)識含義數(shù)據(jù)類型長

2、度主鍵外鍵（被參照表）college_no學(xué)院編碼varchar210是否college_name學(xué)院名稱varchar220否否setting_quota定編人數(shù)number4否否current_quota現(xiàn)在編人數(shù)number4否否major_number學(xué)院的專業(yè)數(shù)目number2否否表2 專業(yè)表（db_major）字段標(biāo)識含義數(shù)據(jù)類型長度主鍵外鍵（被參照表）major_no專業(yè)編碼varchar210是否major_name專業(yè)名稱varchar220否否college_no學(xué)院編碼varchar210否是（db_college）表3 學(xué)生表（db_student）字段標(biāo)識含義數(shù)據(jù)類型

3、長度主鍵外鍵（被參照表）register_no學(xué)生學(xué)號varchar220是否major_no專業(yè)編碼varchar210否是（db_major）s_name學(xué)生姓名varchar210否否s_gender性別varchar24否否s_dateofbirth出生年月date否否s_class班級號number1否否s_address家庭住址varchar220否否s_postcode郵政編碼number6否否s_mail_address通信地址varchar250否否s_tele聯(lián)系電話number11否否s_email電子郵箱varchar230否否表4 教師表（db_teacher）字段標(biāo)

4、識含義數(shù)據(jù)類型長度主鍵外鍵（被參照表）work_id教師編號varchar210是否t_name教師姓名varchar210否否t_title職稱varchar210否否major_no專業(yè)編碼varchar210否是（db_major）college_no學(xué)院編碼varchar210否是（db_college）t_address家庭住址varchar250否否t_telephone聯(lián)系電話number11否否t_position現(xiàn)任職務(wù)varchar210否否t_email電子郵箱varchar230否否表5 課程信息表（db_course）字段標(biāo)識含義數(shù)據(jù)類型長度主鍵外鍵（被參照表）cou

5、rse_no課程編碼varchar210是否major_no專業(yè)編碼varchar210否是（db_major）term_no開課學(xué)期number1否否year_no開課學(xué)年number4否否course_name課程名稱varchar220否否credit學(xué)分?jǐn)?shù)number2否否planned_hour計劃學(xué)時數(shù)number3否否lab_hour實驗學(xué)時數(shù)number3否否week_hour周學(xué)時數(shù)number3否否course_type課程性質(zhì)varchar22否否exam_type考試類別varchar24否否remarks備注varchar210否否表6 學(xué)生成績表（db_grade）

6、字段標(biāo)識含義數(shù)據(jù)類型長度主鍵外鍵（被參照表）register_no學(xué)號編碼varchar220是是（db_student）work_id教師編號varchar210是是（db_teacher）course_no課程編碼varchar210是是（db_course）college_no學(xué)院編碼varchar210否是（db_college）registered_date課程注冊日期date否否registered_year選課學(xué)年number4否否registered_term選課學(xué)期number1否否final_grade最終成績varchar25否否makeup_flag補(bǔ)考標(biāo)志char2否

7、否注：補(bǔ)考標(biāo)志makeup_flag的值為0或空表示沒有補(bǔ)考，為1表示補(bǔ)考。表7 教學(xué)任務(wù)表（db_teach_course）字段標(biāo)識含義數(shù)據(jù)類型長度主鍵外鍵（被參照表）course_no課程編碼varchar210是是（db_course）work_id教師編號varchar210是是（db_teacher）course_name課程名稱varchar220否是（db_course）launch_year實際開課學(xué)年number4否否launch_term實際開課學(xué)期number1否否executed_plan完成課程情況varchar23否否表8 教師登錄賬戶信息表（db_faculty_

8、per）字段標(biāo)識含義數(shù)據(jù)類型長度主鍵外鍵（被參照表）work_id登錄賬戶即教師編號varchar210是是（db_teacher）login_pwd_f登錄口令varchar220否否表 9 學(xué)生登錄賬戶信息表（db_student_per）字段標(biāo)識含義數(shù)據(jù)類型長度主鍵外鍵（被參照表）register_id學(xué)生學(xué)號varchar220是是（db_student）login_pwd_s登錄口令varchar220否否按照對學(xué)生成績管理系統(tǒng)的領(lǐng)域知識，完成下面的內(nèi)容（請將解答填寫在橫線上，有運行結(jié)果的請給出截圖）：1、繪制出學(xué)生成績管理系統(tǒng)的E_R圖。2、在現(xiàn)有數(shù)據(jù)庫ORCL或其它數(shù)據(jù)庫中創(chuàng)建表

9、空間，表空間信息和表空間對應(yīng)數(shù)據(jù)文件見表10和表11。表10 表空間分配表名所屬模式對應(yīng)所屬部門對應(yīng)表空間初始大小增長方式學(xué)院表staffuser全校各學(xué)院tbs_main500M自動專業(yè)表staffuser各學(xué)院專業(yè)tbs_main500M自動學(xué)生表staffuser全校學(xué)生tbs_main500M自動教師表staffuser全校教師tbs_main500M自動課程信息表staffuser全校所有課程tbs_main500M自動教學(xué)任務(wù)表staffuser所有專業(yè)tbs_main500M自動學(xué)生成績表staffuser生物工程學(xué)院食品科學(xué)與工程學(xué)院tbs_bio_foo200M自動信息科學(xué)與

10、工程學(xué)院材料科學(xué)與工程學(xué)院tbs_infor_mati200M自動藝術(shù)設(shè)計學(xué)院服裝學(xué)院商務(wù)學(xué)院tbs_art_fash_busi200M自動表11 表空間與數(shù)據(jù)文件的對應(yīng)關(guān)系表空間類型對應(yīng)文件文件大小增長幅度增長方式tbs_main永久/聯(lián)機(jī)t250MB10%自動t250MB10%自動tbs_bio_foo永久/聯(lián)機(jī)t100MB10%自動t100MB10%自動tbs_infor_mati永久/聯(lián)機(jī)t100MB10%自動t100MB10%自動tbs_art_fash_busi永久/聯(lián)機(jī)t100MB10%自動t100MB10%自動tbs_teach_std永久/聯(lián)機(jī)t100MB10%自動t100M

11、B10%自動tbs_index永久/聯(lián)機(jī)100MB10%自動100MB10%自動tbs_temp臨時/聯(lián)機(jī)100MB10%自動100MB10%自動各個表空間均創(chuàng)建成功，以上是局部截圖 3、創(chuàng)建數(shù)據(jù)庫用戶。學(xué)生成績管理系統(tǒng)中的用戶有5個：（1）教師用戶teauser：可瀏覽學(xué)院、專業(yè)、課程及承擔(dān)教學(xué)任務(wù)學(xué)習(xí)；可更新自己的部分字段信息，根據(jù)學(xué)生選課注冊的信息，錄入所承擔(dān)課程的學(xué)生成績、查詢該課程成績，在得到教務(wù)部門的授權(quán)許可后可修改學(xué)生的成績。例如用戶teauser對學(xué)生成績表db_grade的更新（update）操作是在一定時間內(nèi)由管理部門用戶staffuser授予的，主要發(fā)生在教師錄入學(xué)生成績

12、完畢提交后。（2）學(xué)生用戶stduser：可對學(xué)生自己的信息、課程信息、課程成績及教師承擔(dān)的教學(xué)任務(wù)進(jìn)行查詢，也可修改自己的部分信息。（3）管理部門staffuser：擁有所有表及其全部權(quán)限。（4）數(shù)據(jù)庫管理員dbadatauser：可對數(shù)據(jù)庫表數(shù)據(jù)進(jìn)行備份和恢復(fù)，完成對表的備份/恢復(fù)，表空間的備份/恢復(fù)以及用戶級別備份恢復(fù)。（5）數(shù)據(jù)庫管理員dbsysuser：可創(chuàng)建數(shù)據(jù)庫用戶，并為用戶授權(quán)、創(chuàng)建數(shù)據(jù)庫對象，創(chuàng)建表空間，刪除數(shù)據(jù)庫對象等。以上全部用戶均有執(zhí)行存儲過程、函數(shù)、包等數(shù)據(jù)庫對象的權(quán)限。用戶表空間和表之間所屬關(guān)系如圖1所示。圖1 用戶、表空間和表之間的關(guān)系表空間、用戶和表的對應(yīng)關(guān)系如

13、表12所示。表12 表空間、用戶及表的對應(yīng)關(guān)系數(shù)據(jù)庫用戶擁有的表表的默認(rèn)表空間程序?qū)ο髎taffuserdb_collegedb_majordb_studentdb_teacherdb_teach_coursedb_coursetbs_main觸發(fā)器、存儲過程、函數(shù)和包db_grade(分區(qū)表)tbs_bio_footbs_infor_matitbs_art_fash_busidbdatauserdbsysusertbs_mainteauserdb_faculty_pertbs_teach_std部分應(yīng)用程序?qū)ο髎tduserdb_student_per用戶與表的權(quán)限關(guān)系矩陣如表13所示。表1

14、3 用戶與表的權(quán)限關(guān)系矩陣 數(shù)據(jù)庫用戶表數(shù)據(jù)teauserstduserstaffuserdbdatauserdbsysuserdb_collegeSSIUDSSXdb_majorSSIUDSSXdb_studentSUSIUDSSXdb_teacherSUSIUDSSXdb_courseSSSIUDSSXdb_gradeSUSUSUIDSSXdb_teach_courseSSSIUDSSXdb_faculty_perSUSIUDSSXdb_student_perSUSIUDSSX表13中的權(quán)限縮寫如表14所示。表14 表級權(quán)限列表表級權(quán)限含義縮寫ALTER修改表結(jié)構(gòu)ADELETE刪除記錄DI

15、NDEX索引XINSERT插入數(shù)據(jù)IREFERENCES關(guān)聯(lián)RSELECT查詢SUPDATE更新U請按照以上信息，給出創(chuàng)建5個用戶的語句及其授權(quán)語句，授權(quán)語句請說明是哪個用戶給哪個用戶授權(quán)。（1）staffuser用戶 創(chuàng)建staffuser用戶create user staffuseridentified by 123123default tablespace tbs_main;alter user staffuser quota unlimited on tbs_bio_foo;alter user staffuser quota unlimited on tbs_infor_mati;a

16、lter user staffuser quota unlimited on tbs_art_fash_busi;alter user staffuser account unlock; 為用戶staffuser授權(quán)grant create session to staffuser;grant create table to staffuser;grant select,update,insert,delete on db_college to staffuser;grant select,update,insert,delete on db_major to staffuser;grant

17、select,update,insert,delete on db_student to staffuser;grant select,update,insert,delete on db_teacher to staffuser;grant select,update,insert,delete on db_course to staffuser;grant select on db_grade to staffuser;grant select,update,insert,delete on db_teach_course to staffuser;grant select,update,

18、insert,delete on db_faculty_per to staffuser;grant select,update,insert,delete on db_student_per to staffuser;（2）teaser用戶 創(chuàng)建teauser用戶create user teauseridentified by 123123default tablespace tbs_teach_std;alter user teauseraccount unlock; 為用戶teauser授權(quán)grant create session to teauser;grant select on d

19、b_college to teauser;grant select on db_major to teauser;grant select,update on db_teacher to teauser;grant select on db_course to teauser;grant select,update on db_grade to teauser;grant select on db_teach_course to teauser;grant select,update on db_faculty_per to teauser;（3）stduser用戶 創(chuàng)建stduser用戶cr

20、eate user stduseridentified by 123123;alter user stduseraccount unlock; 為用戶stduser授權(quán)grant create session to stduser;grant select,update on db_student to dbdatauser;grant select on db_course to dbdatauser;grant select,update on db_grade to dbdatauser;grant select on db_teach_course to dbdatauser;gran

21、t select,update on db_student_per to dbdatauser;（4）dbdatauser用戶 創(chuàng)建dbdatauser用戶create user dbdatauseridentified by 123123default tablespace tbs_main;alter user dbdatauseraccount lock; 為用戶dbdatauser授權(quán)grant create session to dbdatauser;grant select on db_college to dbdatauser;grant select on db_major t

22、o dbdatauser;grant select on db_student to dbdatauser;grant select on db_teacher to dbdatauser;grant select on db_course to dbdatauser;grant select on db_grade to dbdatauser;grant select on db_teach_course to dbdatauser;grant select on db_faculty_per to dbdatauser;grant select on db_student_per to d

23、bdatauser;（5）dbsysuser用戶 創(chuàng)建dbsysuser用戶create user dbsysuseridentified by 123123default tablespace tbs_main;alter user dbsysuseraccount unlock; 為用戶dbsysuser授權(quán)grant create session to dbsysuser;grant select,index on db_college to dbsysuser;grant select,index on db_major to dbsysuser;grant select,index

24、on db_student to dbsysuser;grant select,index on db_teacher to dbsysuser;grant select,index on db_course to dbsysuser;grant select,index on db_grade to dbsysuser;grant select,index on db_teach_course to dbsysuser;grant select,index on db_faculty_per to dbsysuser;grant select,index on db_student_per

25、to dbsysuser;4、完成如下項目，并給出運行結(jié)果的截圖。（1）以staffuser登錄，創(chuàng)建表7個表：db_college, db_major, db_student, db_teacher, db_course, db_grade和db_teach_course，并向表插入數(shù)據(jù)。插入的數(shù)據(jù)見附件。注意創(chuàng)建表時，需要考慮E-R圖中或表1-表6中定義的主鍵、外鍵和相關(guān)約束。數(shù)據(jù)庫表視圖數(shù)據(jù)插入（2）使用staffuser用戶登錄查詢“計算機(jī)科學(xué)與技術(shù)專業(yè)”學(xué)生的平均分。select avg(final_grade) from system.db_course c,system.db_major m,system.db_grade g where c.major_no=m.major_no and m.college_no=g.college_no and m.major_name=計算機(jī)科學(xué)與技術(shù);（3）使用teauser登錄，查詢專業(yè)名稱為“通信工程”學(xué)生的所選的課程名稱。select course_name from system.db_course c, system.db_major mwhere c.major_no = m.major_no and m.major_name