2025年信息系統(tǒng)項(xiàng)目管理師考試數(shù)據(jù)庫設(shè)計(jì)試卷

2025年信息系統(tǒng)項(xiàng)目管理師考試數(shù)據(jù)庫設(shè)計(jì)試卷考試時(shí)間：______分鐘總分：______分姓名：______一、關(guān)系數(shù)據(jù)庫設(shè)計(jì)要求：根據(jù)給定的實(shí)體-聯(lián)系(E-R)圖，完成以下關(guān)系模式的設(shè)計(jì)。1.實(shí)體名稱：學(xué)生（Student），屬性：學(xué)號(hào)（StudentID）、姓名（Name）、性別（Gender）、年齡（Age）、班級(jí)（ClassID）。2.實(shí)體名稱：課程（Course），屬性：課程號(hào)（CourseID）、課程名稱（CourseName）、學(xué)分（Credit）。3.實(shí)體名稱：教師（Teacher），屬性：教師號(hào)（TeacherID）、姓名（Name）、性別（Gender）、職稱（Title）。4.實(shí)體名稱：班級(jí)（Class），屬性：班級(jí)號(hào)（ClassID）、班級(jí)名稱（ClassName）、班主任（TeacherID）。5.實(shí)體名稱：成績（Score），屬性：學(xué)號(hào)（StudentID）、課程號(hào)（CourseID）、成績（Grade）。請根據(jù)上述實(shí)體-聯(lián)系(E-R)圖，完成以下關(guān)系模式的設(shè)計(jì)。二、SQL語句編寫要求：根據(jù)以下SQL語句的要求，完成相應(yīng)的SQL語句編寫。1.查詢所有學(xué)生的姓名、性別和年齡。2.查詢所有課程名稱和學(xué)分。3.查詢所有教師的姓名和職稱。4.查詢所有班級(jí)的班級(jí)名稱和班主任姓名。5.查詢所有學(xué)生的學(xué)號(hào)、姓名和所在班級(jí)名稱。6.查詢所有課程的課程名稱和任課教師姓名。7.查詢所有學(xué)生的學(xué)號(hào)、姓名和成績。8.查詢所有課程的課程名稱、學(xué)分和平均成績。9.查詢所有班級(jí)的班級(jí)名稱、班主任姓名和平均成績。10.查詢所有教師的教學(xué)課程數(shù)。三、關(guān)系數(shù)據(jù)庫規(guī)范化要求：對以下關(guān)系模式進(jìn)行規(guī)范化處理。1.關(guān)系模式：教師（教師號(hào)、姓名、性別、職稱、課程號(hào)、課程名稱、學(xué)分）。2.關(guān)系模式：課程（課程號(hào)、課程名稱、學(xué)分、教師號(hào)、姓名、性別、職稱）。3.關(guān)系模式：班級(jí)（班級(jí)號(hào)、班級(jí)名稱、班主任、教師號(hào)、姓名、性別、職稱）。4.關(guān)系模式：學(xué)生（學(xué)號(hào)、姓名、性別、年齡、班級(jí)號(hào)、班級(jí)名稱、班主任、教師號(hào)、姓名、性別、職稱）。5.關(guān)系模式：成績（學(xué)號(hào)、姓名、性別、年齡、班級(jí)號(hào)、班級(jí)名稱、班主任、課程號(hào)、課程名稱、學(xué)分、教師號(hào)、姓名、性別、職稱、成績）。請根據(jù)規(guī)范化理論，對上述關(guān)系模式進(jìn)行規(guī)范化處理。四、視圖設(shè)計(jì)要求：根據(jù)以下需求，設(shè)計(jì)相應(yīng)的視圖。1.創(chuàng)建一個(gè)視圖，顯示所有學(xué)生的姓名、班級(jí)名稱和成績。2.創(chuàng)建一個(gè)視圖，顯示所有課程的課程名稱、學(xué)分和任課教師姓名。3.創(chuàng)建一個(gè)視圖，顯示所有教師的姓名、職稱和負(fù)責(zé)的班級(jí)名稱。4.創(chuàng)建一個(gè)視圖，顯示所有班級(jí)的班級(jí)名稱、班主任姓名和班級(jí)人數(shù)。5.創(chuàng)建一個(gè)視圖，顯示所有學(xué)生的姓名、性別和所在班級(jí)的平均成績。6.創(chuàng)建一個(gè)視圖，顯示所有課程的課程名稱、學(xué)分和學(xué)生的最高成績。五、SQL查詢優(yōu)化要求：對以下SQL查詢語句進(jìn)行優(yōu)化，提高查詢效率。1.原查詢語句：SELECT*FROMStudentWHEREAge>20;2.原查詢語句：SELECTCourseID,CourseName,CreditFROMCourseWHERETeacherIDIN(SELECTTeacherIDFROMTeacherWHERETitle='教授');3.原查詢語句：SELECTCOUNT(*)FROMScoreWHEREGrade>=90;4.原查詢語句：SELECTStudentID,Name,SUM(Credit)ASTotalCreditsFROMStudentJOINScoreONStudent.StudentID=Score.StudentIDGROUPBYStudentID;5.原查詢語句：SELECTClassID,ClassName,COUNT(StudentID)ASStudentCountFROMClassJOINStudentONClass.ClassID=Student.ClassIDGROUPBYClassID;6.原查詢語句：SELECTCourseID,CourseName,MAX(Grade)ASHighestGradeFROMScoreGROUPBYCourseID;六、觸發(fā)器設(shè)計(jì)要求：根據(jù)以下需求，設(shè)計(jì)相應(yīng)的觸發(fā)器。1.設(shè)計(jì)一個(gè)觸發(fā)器，在插入新的成績記錄時(shí)，自動(dòng)檢查該學(xué)生的該課程成績是否超過90分，如果超過則觸發(fā)異常。2.設(shè)計(jì)一個(gè)觸發(fā)器，在刪除教師記錄時(shí)，自動(dòng)檢查該教師是否還有負(fù)責(zé)的班級(jí)，如果有則觸發(fā)異常。3.設(shè)計(jì)一個(gè)觸發(fā)器，在更新課程記錄時(shí)，自動(dòng)檢查課程學(xué)分是否在1到10之間，如果不在則觸發(fā)異常。4.設(shè)計(jì)一個(gè)觸發(fā)器，在插入新的班級(jí)記錄時(shí)，自動(dòng)檢查班級(jí)名稱是否已存在，如果存在則觸發(fā)異常。5.設(shè)計(jì)一個(gè)觸發(fā)器，在更新學(xué)生記錄時(shí)，自動(dòng)檢查學(xué)生年齡是否在18到25歲之間，如果不在則觸發(fā)異常。6.設(shè)計(jì)一個(gè)觸發(fā)器，在刪除成績記錄時(shí)，自動(dòng)檢查該成績是否為該學(xué)生的唯一記錄，如果是則觸發(fā)異常。本次試卷答案如下：一、關(guān)系數(shù)據(jù)庫設(shè)計(jì)1.學(xué)生（StudentID,Name,Gender,Age,ClassID）2.課程（CourseID,CourseName,Credit）3.教師（TeacherID,Name,Gender,Title）4.班級(jí)（ClassID,ClassName,TeacherID）5.成績（StudentID,CourseID,Grade）解析思路：-根據(jù)實(shí)體-聯(lián)系(E-R)圖，每個(gè)實(shí)體轉(zhuǎn)換為關(guān)系模式。-實(shí)體的屬性轉(zhuǎn)換為關(guān)系的屬性。-實(shí)體間的關(guān)系轉(zhuǎn)換為關(guān)系之間的關(guān)聯(lián)。二、SQL語句編寫1.SELECTName,Gender,AgeFROMStudent;2.SELECTCourseName,CreditFROMCourse;3.SELECTName,TitleFROMTeacher;4.SELECTClassName,TeacherID,NameFROMClass;5.SELECTStudentID,Name,ClassNameFROMStudentJOINClassONStudent.ClassID=Class.ClassID;6.SELECTCourseName,Credit,NameFROMCourseJOINTeacherONCourse.TeacherID=Teacher.TeacherID;7.SELECTStudentID,Name,GradeFROMStudentJOINScoreONStudent.StudentID=Score.StudentID;8.SELECTCourseName,Credit,AVG(Grade)ASAverageGradeFROMCourseJOINScoreONCourse.CourseID=Score.CourseIDGROUPBYCourseID;9.SELECTClassName,ClassName,AVG(Grade)ASAverageGradeFROMClassJOINScoreONClass.ClassID=Score.StudentClassIDGROUPBYClassName;10.SELECTCOUNT(*)FROMTeacherJOINCourseONTeacher.TeacherID=Course.TeacherIDGROUPBYTeacherID;解析思路：-使用SELECT語句選擇所需的列。-使用FROM子句指定表名。-使用JOIN子句連接相關(guān)表。-使用WHERE子句篩選特定條件。-使用GROUPBY子句對結(jié)果進(jìn)行分組。-使用AVG函數(shù)計(jì)算平均值。-使用COUNT函數(shù)計(jì)算數(shù)量。三、關(guān)系數(shù)據(jù)庫規(guī)范化1.教師關(guān)系模式規(guī)范化為教師（教師號(hào)、姓名、性別、職稱）和課程關(guān)系模式（課程號(hào)、課程名稱、學(xué)分、教師號(hào)）。2.課程關(guān)系模式規(guī)范化為課程（課程號(hào)、課程名稱、學(xué)分、教師號(hào)、姓名、性別、職稱）。3.班級(jí)關(guān)系模式規(guī)范化為班級(jí)（班級(jí)號(hào)、班級(jí)名稱、班主任）和教師關(guān)系模式（教師號(hào)、姓名、性別、職稱）。4.學(xué)生關(guān)系模式規(guī)范化為學(xué)生（學(xué)號(hào)、姓名、性別、年齡、班級(jí)號(hào)）和班級(jí)關(guān)系模式（班級(jí)號(hào)、班級(jí)名稱、班主任）。5.成績關(guān)系模式規(guī)范化為成績（學(xué)號(hào)、課程號(hào)、成績）和學(xué)生關(guān)系模式（學(xué)號(hào)、姓名、性別、年齡、班級(jí)號(hào)）。解析思路：-根據(jù)規(guī)范化理論，分析每個(gè)關(guān)系模式中的函數(shù)依賴。-將關(guān)系模式分解為2NF、3NF或BCNF，以消除部分依賴和傳遞依賴。-為每個(gè)分解的關(guān)系模式創(chuàng)建新的關(guān)系。四、視圖設(shè)計(jì)1.CREATEVIEWStudentClassScoreASSELECTStudent.Name,Class.ClassName,Score.GradeFROMStudentJOINClassONStudent.ClassID=Class.ClassIDJOINScoreONStudent.StudentID=Score.StudentID;2.CREATEVIEWCourseDetailsASSELECTCourse.CourseName,Course.Credit,Teacher.Name,Teacher.TitleFROMCourseJOINTeacherONCourse.TeacherID=Teacher.TeacherID;3.CREATEVIEWTeacherClassResponsibilityASSELECTTeacher.Name,Teacher.Title,Class.ClassNameFROMTeacherJOINClassONTeacher.TeacherID=Class.TeacherID;4.CREATEVIEWClassDetailsASSELECTClass.ClassName,Teacher.Name,COUNT(Student.StudentID)ASStudentCountFROMClassJOINTeacherONClass.TeacherID=Teacher.TeacherIDJOINStudentONClass.ClassID=Student.ClassIDGROUPBYClass.ClassName;5.CREATEVIEWStudentAgeAverageScoreASSELECTStudent.Name,Student.Gender,Class.ClassName,AVG(Score.Grade)ASAverageGradeFROMStudentJOINClassONStudent.ClassID=Class.ClassIDJOINScoreONStudent.StudentID=Score.StudentIDGROUPBYStudent.Name,Student.Gender,Class.ClassName;6.CREATEVIEWCourseHighestGradeASSELECTCourse.CourseName,Course.Credit,MAX(Score.Grade)ASHighestGradeFROMCourseJOINScoreONCourse.CourseID=Score.CourseIDGROUPBYCourse.CourseName,Course.Credit;解析思路：-使用CREATEVIEW語句創(chuàng)建視圖。-使用SELECT語句選擇所需的列。-使用FROM子句指定表名。-使用JOIN子句連接相關(guān)表。-使用GROUPBY子句對結(jié)果進(jìn)行分組。五、SQL查詢優(yōu)化1.SELECTName,Gender,AgeFROMStudentWHEREAge>20;2.SELECTCourseID,CourseName,CreditFROMCourseWHERETeacherIDIN(SELECTTeacherIDFROMTeacherWHERETitle='教授');3.SELECTCOUNT(*)FROMScoreWHEREGrade>=90;4.SELECTStudentID,Name,SUM(Credit)ASTotalCreditsFROMStudentJOINScoreONStudent.StudentID=Score.StudentIDGROUPBYStudentID;5.SELECTClassID,ClassName,COUNT(StudentID)ASStudentCountFROMClassJOINStudentONClass.ClassID=Student.ClassIDGROUPBYClassID;6.SELECTCourseID,CourseName,MAX(Grade)ASHighestGradeFROMScoreGROUPBYCourseID;解析思路：-使用索引提高查詢效率。-使用適當(dāng)?shù)腤HERE子句過濾結(jié)果。-使用JOIN子句代替子查詢。-使用GROUPBY子句對結(jié)果進(jìn)行分組。六、觸發(fā)器設(shè)計(jì)1.CREATETRIGGERCheckGradeBeforeInsertONScoreINSTEADOFINSERTASBEGINIFEXISTS(SELECT*FROMScoreWHEREStudentID=NEW.StudentIDANDCourseID=NEW.CourseIDANDGrade>90)BEGINRAISERROR('Gradeexceeds90',16,1);ROLLBACK;ENDELSEBEGININSERTINTOScore(StudentID,CourseID,Grade)VALUES(NEW.StudentID,NEW.CourseID,NEW.Grade);ENDEND;2.CREATETRIGGERCheckTeacherBeforeDeleteONTeacherINSTEADOFDELETEASBEGINIFEXISTS(SELECT*FROMClassWHERETeacherID=OLD.TeacherID)BEGINRAISERROR('Teacherhasresponsibleclasses',16,1);ROLLBACK;ENDELSEBEGINDELETEFROMTeacherWHERETeacherID=OLD.TeacherID;ENDEND;3.CREATETRIGGERCheckCreditBeforeUpdateONCourseINSTEADOFUPDATEASBEGINIFEXISTS(SELECT*FROMCourseWHERECredit<1ORCredit>10ANDCourseID=NEW.CourseID)BEGINRAISERROR('Creditisoutofrange',16,1);ROLLBACK;ENDELSEBEGINUPDATECourseSETCredit=NEW.CreditWHERECourseID=NEW.CourseID;ENDEND;4.CREATETRIGGERCheckClassNameBeforeInsertONClassINSTEADOFINSERTASBEGINIFEXISTS(SELECT*FROMClassWHEREClassName=NEW.ClassName)BEGINRAISERROR('Classnamealreadyexists',16,1);ROLLBACK;ENDELSEBEGININSERTINTOClass(ClassName,TeacherID)VALUES(NEW.ClassName,NEW.TeacherID);ENDEND;5.CREATETRIGGERChe