1、XXXXXXXX20172018學年度第二學期期末作業科目： 大型數據庫系統設計 信 息 工 程 學院 計算機科學與技術（軟件工程）專業 XXX級 XXX 班學號： XXXX 姓名： XXX 成績： 一、按照相關背景和所給知識，完成相關內容。背景：教務處是學校教學及日常教務管理的核心部門。該處主要包括：考試中心、教研科、教務科、學籍管理科、教學實踐科以及高教研究等科室。根據其職能劃分，教學管理主要分為：l 學生學籍管理l 教學計劃管理l 排課管理l 成績管理l 考務管理l 教學評估管理l 教研項目管理l 該作業針對教學管理中的學生成績管理模塊。學生成績管理模塊有如下的表（完成表中填充的部分）：

2、表1 學院表（db_college）字段標識含義數據類型長度主鍵外鍵（被參照表）college_no學院編碼varchar210是否college_name學院名稱varchar220否否setting_quota定編人數number4否否current_quota現在編人數number4否否major_number學院的專業數目number2否否表2 專業表（db_major）字段標識含義數據類型長度主鍵外鍵（被參照表）major_no專業編碼varchar210是否major_name專業名稱varchar220否否college_no學院編碼varchar210否是（db_college

3、）表3 學生表（db_student）字段標識含義數據類型長度主鍵外鍵（被參照表）register_no學生學號varchar220是否major_no專業編碼varchar210否是（db_major）s_name學生姓名varchar210否否s_gender性別varchar24否否s_dateofbirth出生年月date否否s_class班級號number1否否s_address家庭住址varchar220否否s_postcode郵政編碼number6否否s_mail_address通信地址varchar250否否s_tele聯系電話number11否否s_email電子郵箱varc

4、har230否否表4 教師表（db_teacher）字段標識含義數據類型長度主鍵外鍵（被參照表）work_id教師編號varchar210是否t_name教師姓名varchar210否否t_title職稱varchar210否否major_no專業編碼varchar210否是（db_major）college_no學院編碼varchar210否是（db_college）t_address家庭住址varchar250否否t_telephone聯系電話number11否否t_position現任職務varchar210否否t_email電子郵箱varchar230否否表5 課程信息表（db_cou

5、rse）字段標識含義數據類型長度主鍵外鍵（被參照表）course_no課程編碼varchar210是否major_no專業編碼varchar210否是（db_major）term_no開課學期number1否否year_no開課學年number4否否course_name課程名稱varchar220否否credit學分數number2否否planned_hour計劃學時數number3否否lab_hour實驗學時數number3否否week_hour周學時數number3否否course_type課程性質varchar22否否exam_type考試類別varchar24否否remarks備注v

6、archar210否否表6 學生成績表（db_grade）字段標識含義數據類型長度主鍵外鍵（被參照表）register_no學號編碼varchar220是是（db_student）work_id教師編號varchar210是是（db_teacher）course_no課程編碼varchar210是是（db_course）college_no學院編碼varchar210否是（db_college）registered_date課程注冊日期date否否registered_year選課學年number4否否registered_term選課學期number1否否final_grade最終成績var

7、char25否否makeup_flag補考標志char2否否注：補考標志makeup_flag的值為0或空表示沒有補考，為1表示補考。表7 教學任務表（db_teach_course）字段標識含義數據類型長度主鍵外鍵（被參照表）course_no課程編碼varchar210是是（db_course）work_id教師編號varchar210是是（db_teacher）course_name課程名稱varchar220否是（db_course）launch_year實際開課學年number4否否launch_term實際開課學期number1否否executed_plan完成課程情況varcha

8、r23否否表8 教師登錄賬戶信息表（db_faculty_per）字段標識含義數據類型長度主鍵外鍵（被參照表）work_id登錄賬戶即教師編號varchar210是是（db_teacher）login_pwd_f登錄口令varchar220否否表 9 學生登錄賬戶信息表（db_student_per）字段標識含義數據類型長度主鍵外鍵（被參照表）register_id學生學號varchar220是是（db_student）login_pwd_s登錄口令varchar220否否按照對學生成績管理系統的領域知識，完成下面的內容（請將解答填寫在橫線上，有運行結果的請給出截圖）：1、繪制出學生成績管理系

9、統的E_R圖。 2、在現有數據庫ORCL或其它數據庫中創建表空間，表空間信息和表空間對應數據文件見表10和表11。表10 表空間分配表名所屬模式對應所屬部門對應表空間初始大小增長方式學院表staffuser全校各學院tbs_main500M自動專業表staffuser各學院專業tbs_main500M自動學生表staffuser全校學生tbs_main500M自動教師表staffuser全校教師tbs_main500M自動課程信息表staffuser全校所有課程tbs_main500M自動教學任務表staffuser所有專業tbs_main500M自動學生成績表staffuser生物工程學院食

10、品科學與工程學院tbs_bio_foo200M自動信息科學與工程學院材料科學與工程學院tbs_infor_mati200M自動藝術設計學院服裝學院商務學院tbs_art_fash_busi200M自動表11 表空間與數據文件的對應關系表空間類型對應文件文件大小增長幅度增長方式tbs_main永久/聯機tbs_main1.dbf250MB10%自動tbs_main2.dbf250MB10%自動tbs_bio_foo永久/聯機tbs_bio_foo1.dbf100MB10%自動tbs_bio_foo2.dbf100MB10%自動tbs_infor_mati永久/聯機tbs_infor_mati1.

11、dbf100MB10%自動tbs_infor_mati2.dbf100MB10%自動tbs_art_fash_busi永久/聯機tbs_art_fash_busi1.dbf100MB10%自動tbs_art_fash_busi2.dbf100MB10%自動tbs_teach_std永久/聯機tbs_teach_std1.dbf100MB10%自動tbs_teach_std2.dbf100MB10%自動tbs_index永久/聯機tbs_index1.dbf100MB10%自動tbs_index2.dbf100MB10%自動tbs_temp臨時/聯機tbs_temp1.dbf100MB10%自動

12、tbs_temp2.dbf100MB10%自動各個表空間均創建成功，以上是局部截圖 3、創建數據庫用戶。學生成績管理系統中的用戶有5個：（1）教師用戶teauser：可瀏覽學院、專業、課程及承擔教學任務學習；可更新自己的部分字段信息，根據學生選課注冊的信息，錄入所承擔課程的學生成績、查詢該課程成績，在得到教務部門的授權許可后可修改學生的成績。例如用戶teauser對學生成績表db_grade的更新（update）操作是在一定時間內由管理部門用戶staffuser授予的，主要發生在教師錄入學生成績完畢提交后。（2）學生用戶stduser：可對學生自己的信息、課程信息、課程成績及教師承擔的教學任務

13、進行查詢，也可修改自己的部分信息。（3）管理部門staffuser：擁有所有表及其全部權限。（4）數據庫管理員dbadatauser：可對數據庫表數據進行備份和恢復，完成對表的備份/恢復，表空間的備份/恢復以及用戶級別備份恢復。（5）數據庫管理員dbsysuser：可創建數據庫用戶，并為用戶授權、創建數據庫對象，創建表空間，刪除數據庫對象等。以上全部用戶均有執行存儲過程、函數、包等數據庫對象的權限。用戶表空間和表之間所屬關系如圖1所示。圖1 用戶、表空間和表之間的關系表空間、用戶和表的對應關系如表12所示。表12 表空間、用戶及表的對應關系數據庫用戶擁有的表表的默認表空間程序對象staffus

14、erdb_collegedb_majordb_studentdb_teacherdb_teach_coursedb_coursetbs_main觸發器、存儲過程、函數和包db_grade(分區表)tbs_bio_footbs_infor_matitbs_art_fash_busidbdatauserdbsysusertbs_mainteauserdb_faculty_pertbs_teach_std部分應用程序對象stduserdb_student_per用戶與表的權限關系矩陣如表13所示。表13 用戶與表的權限關系矩陣 數據庫用戶表數據teauserstduserstaffuserdbdat

15、auserdbsysuserdb_collegeSSIUDSSXdb_majorSSIUDSSXdb_studentSUSIUDSSXdb_teacherSUSIUDSSXdb_courseSSSIUDSSXdb_gradeSUSUSUIDSSXdb_teach_courseSSSIUDSSXdb_faculty_perSUSIUDSSXdb_student_perSUSIUDSSX表13中的權限縮寫如表14所示。表14 表級權限列表表級權限含義縮寫ALTER修改表結構ADELETE刪除記錄DINDEX索引XINSERT插入數據IREFERENCES關聯RSELECT查詢SUPDATE更新U請

16、按照以上信息，給出創建5個用戶的語句及其授權語句，授權語句請說明是哪個用戶給哪個用戶授權。（1）staffuser用戶 創建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;alter user staffuser quota unlimited on tbs_art_fas

17、h_busi;alter user staffuser account unlock; 為用戶staffuser授權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 select,update,insert,delete on db_student to staff

18、user;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,insert,delete on db_faculty_per to staffuser;grant

19、 select,update,insert,delete on db_student_per to staffuser; （2）teaser用戶 創建teauser用戶 create user teauseridentified by 123123default tablespace tbs_teach_std;alter user teauser account unlock; 為用戶teauser授權grant create session to teauser;grant select on db_college to teauser;grant select on db_major t

20、o 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用戶 創建stduser用戶 create user stduseridentified by 123123;alter u

21、ser stduser account unlock; 為用戶stduser授權 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;grant select,update on db_student_per to dbdata

22、user; （4）dbdatauser用戶 創建dbdatauser用戶 create user dbdatauseridentified by 123123default tablespace tbs_main;alter user dbdatauser account lock; 為用戶dbdatauser授權 grant create session to dbdatauser;grant select on db_college to dbdatauser;grant select on db_major to dbdatauser;grant select on db_student

23、 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 dbdatauser; （5）dbsysuser用戶 創建dbsysuser用戶

24、 create user dbsysuseridentified by 123123default tablespace tbs_main;alter user dbsysuser account unlock; 為用戶dbsysuser授權grant create session to dbsysuser;grant select,index on db_college to dbsysuser;grant select,index on db_major to dbsysuser;grant select,index on db_student to dbsysuser;grant sel

25、ect,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 to dbsysuser; 4、完成如下項目，并給出運行結果的截圖。（1

26、）以staffuser登錄，創建表7個表：db_college, db_major, db_student, db_teacher, db_course, db_grade和db_teach_course，并向表插入數據。插入的數據見附件。注意創建表時，需要考慮E-R圖中或表1-表6中定義的主鍵、外鍵和相關約束。數據庫表視圖數據插入 （2）使用staffuser用戶登錄查詢“計算機科學與技術專業”學生的平均分。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='計算機科學與技術' （3）使用teauser登錄，查詢專業名稱為“通信工程”學生的所選的課程名稱。 select course_name from system.db_course c, system.db_major mwhere c.major_no = m.major_no and m.major_name=