2025年信息系統(tǒng)項目管理師考試數(shù)據(jù)庫設計試卷考試時間：______分鐘總分：______分姓名：______一、關(guān)系數(shù)據(jù)庫設計要求：根據(jù)給定的實體-聯(lián)系(E-R)圖，完成以下關(guān)系模式的設計。1.實體名稱：學生（Student），屬性：學號（StudentID）、姓名（Name）、性別（Gender）、年齡（Age）、班級（ClassID）。2.實體名稱：課程（Course），屬性：課程號（CourseID）、課程名稱（CourseName）、學分（Credit）。3.實體名稱：教師（Teacher），屬性：教師號（TeacherID）、姓名（Name）、性別（Gender）、職稱（Title）。4.實體名稱：班級（Class），屬性：班級號（ClassID）、班級名稱（ClassName）、班主任（TeacherID）。5.實體名稱：成績（Score），屬性：學號（StudentID）、課程號（CourseID）、成績（Grade）。請根據(jù)上述實體-聯(lián)系(E-R)圖，完成以下關(guān)系模式的設計。二、SQL語句編寫要求：根據(jù)以下SQL語句的要求，完成相應的SQL語句編寫。1.查詢所有學生的姓名、性別和年齡。2.查詢所有課程名稱和學分。3.查詢所有教師的姓名和職稱。4.查詢所有班級的班級名稱和班主任姓名。5.查詢所有學生的學號、姓名和所在班級名稱。6.查詢所有課程的課程名稱和任課教師姓名。7.查詢所有學生的學號、姓名和成績。8.查詢所有課程的課程名稱、學分和平均成績。9.查詢所有班級的班級名稱、班主任姓名和平均成績。10.查詢所有教師的教學課程數(shù)。三、關(guān)系數(shù)據(jù)庫規(guī)范化要求：對以下關(guān)系模式進行規(guī)范化處理。1.關(guān)系模式：教師（教師號、姓名、性別、職稱、課程號、課程名稱、學分）。2.關(guān)系模式：課程（課程號、課程名稱、學分、教師號、姓名、性別、職稱）。3.關(guān)系模式：班級（班級號、班級名稱、班主任、教師號、姓名、性別、職稱）。4.關(guān)系模式：學生（學號、姓名、性別、年齡、班級號、班級名稱、班主任、教師號、姓名、性別、職稱）。5.關(guān)系模式：成績（學號、姓名、性別、年齡、班級號、班級名稱、班主任、課程號、課程名稱、學分、教師號、姓名、性別、職稱、成績）。請根據(jù)規(guī)范化理論，對上述關(guān)系模式進行規(guī)范化處理。四、視圖設計要求：根據(jù)以下需求，設計相應的視圖。1.創(chuàng)建一個視圖，顯示所有學生的姓名、班級名稱和成績。2.創(chuàng)建一個視圖，顯示所有課程的課程名稱、學分和任課教師姓名。3.創(chuàng)建一個視圖，顯示所有教師的姓名、職稱和負責的班級名稱。4.創(chuàng)建一個視圖，顯示所有班級的班級名稱、班主任姓名和班級人數(shù)。5.創(chuàng)建一個視圖，顯示所有學生的姓名、性別和所在班級的平均成績。6.創(chuàng)建一個視圖，顯示所有課程的課程名稱、學分和學生的最高成績。五、SQL查詢優(yōu)化要求：對以下SQL查詢語句進行優(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ā)器設計要求：根據(jù)以下需求，設計相應的觸發(fā)器。1.設計一個觸發(fā)器，在插入新的成績記錄時，自動檢查該學生的該課程成績是否超過90分，如果超過則觸發(fā)異常。2.設計一個觸發(fā)器，在刪除教師記錄時，自動檢查該教師是否還有負責的班級，如果有則觸發(fā)異常。3.設計一個觸發(fā)器，在更新課程記錄時，自動檢查課程學分是否在1到10之間，如果不在則觸發(fā)異常。4.設計一個觸發(fā)器，在插入新的班級記錄時，自動檢查班級名稱是否已存在，如果存在則觸發(fā)異常。5.設計一個觸發(fā)器，在更新學生記錄時，自動檢查學生年齡是否在18到25歲之間，如果不在則觸發(fā)異常。6.設計一個觸發(fā)器，在刪除成績記錄時，自動檢查該成績是否為該學生的唯一記錄，如果是則觸發(fā)異常。本次試卷答案如下：一、關(guān)系數(shù)據(jù)庫設計1.學生（StudentID,Name,Gender,Age,ClassID）2.課程（CourseID,CourseName,Credit）3.教師（TeacherID,Name,Gender,Title）4.班級（ClassID,ClassName,TeacherID）5.成績（StudentID,CourseID,Grade）解析思路：-根據(jù)實體-聯(lián)系(E-R)圖，每個實體轉(zhuǎn)換為關(guān)系模式。-實體的屬性轉(zhuǎn)換為關(guān)系的屬性。-實體間的關(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é)果進行分組。-使用AVG函數(shù)計算平均值。-使用COUNT函數(shù)計算數(shù)量。三、關(guān)系數(shù)據(jù)庫規(guī)范化1.教師關(guān)系模式規(guī)范化為教師（教師號、姓名、性別、職稱）和課程關(guān)系模式（課程號、課程名稱、學分、教師號）。2.課程關(guān)系模式規(guī)范化為課程（課程號、課程名稱、學分、教師號、姓名、性別、職稱）。3.班級關(guān)系模式規(guī)范化為班級（班級號、班級名稱、班主任）和教師關(guān)系模式（教師號、姓名、性別、職稱）。4.學生關(guān)系模式規(guī)范化為學生（學號、姓名、性別、年齡、班級號）和班級關(guān)系模式（班級號、班級名稱、班主任）。5.成績關(guān)系模式規(guī)范化為成績（學號、課程號、成績）和學生關(guān)系模式（學號、姓名、性別、年齡、班級號）。解析思路：-根據(jù)規(guī)范化理論，分析每個關(guān)系模式中的函數(shù)依賴。-將關(guān)系模式分解為2NF、3NF或BCNF，以消除部分依賴和傳遞依賴。-為每個分解的關(guān)系模式創(chuàng)建新的關(guān)系。四、視圖設計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é)果進行分組。五、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;解析思路：-使用索引提高查詢效率。-使用適當?shù)腤HERE子句過濾結(jié)果。-使用JOIN子句代替子查詢。-使用GROUPBY子句對結(jié)果進行分組。六、觸發(fā)器設計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