學(xué)生檔案管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)書-閱讀頁(yè)
2025-05-24 22:11本頁(yè)面
【正文】 k_Sno primary key(Sno)goUsers表約束(實(shí)體完整性)use StudentRecordDBgoalter table Users add constraint pk_Uid primary key(Uid)goweiji表約束(實(shí)體完整性)use StudentRecordDBgoalter table WeiJi add constraint pk_weiji primary key(Sno)go參照完整性u(píng)se StudentRecordDBgoALTER TABLE SC WITH NOCHECK ADD CONSTRAINT FK_student_sc FOREIGN KEY(Sno) REFERENCES Student(Sno) ON DELETE CASCADEALTER TABLE SC WITH NOCHECK ADD CONSTRAINT FK_course_sc FOREIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE CASCADEALTER TABLE HuoJiang WITH NOCHECK ADD CONSTRAINT FK_student_huojiang FOREIGN KEY(Sno) REFERENCES Student(Sno) ON DELETE CASCADEALTER TABLE HuoJiang WITH NOCHECK ADD CONSTRAINT FK_reward_huojiang FOREIGN KEY(RewardID) REFERENCES Reward(RewardID) ON DELETE CASCADEALTER TABLE WeiJi WITH NOCHECK ADD CONSTRAINT FK_student_weiji FOREIGN KEY(Sno) REFERENCES Student(Sno) ON DELETE CASCADEALTER TABLE WeiJi WITH NOCHECK ADD CONSTRAINT FK_punish_weiji FOREIGN KEY(PunishID) REFERENCES Punish(PunishID) ON DELETE CASCADE通過姓名和課程名查詢?cè)撋撜n程成績(jī)IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=39。 AND TYPE=39。)DROP PROCEDURE Pro_S_nameGOcreate procedure Pro_QScore S_name_in char(8),Cname_in char(10),Score_out int outputAs select Score_out=Score from Student,Course,SC where = and = and S_name=S_name_in and Cname=Cname_in建立一個(gè)觸發(fā)器，如果修改Student中的學(xué)號(hào)，則SC、HouJiang、WeiJi中的學(xué)號(hào)自動(dòng)修改。Sno_student_tri39。tr39。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=39。 AND TYPE=39。)DROP TRIGGER Sno_sc_triGOcreate trigger Sno_sc_tri on SC for update as if update(Sno) begin declare Sno_new char(10),Sno_old char(10),Sno_t int select Sno_old=Sno from deleted select Sno_t=count(*) from Student where Sno=Sno_old if Sno_t0 rollback transactionendgo建立一個(gè)觸發(fā)器，確保成績(jī)?cè)凇g。Score_sc_tri39。tr39。成績(jī)超出~100！請(qǐng)重新輸入。 Rollback TransactionENDGO建立一個(gè)觸發(fā)器，確保選課表中的記錄和對(duì)應(yīng)的課程記錄一致。Dc_course_tri39。tr39。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=39。 AND TYPE=39。)DROP TRIGGER DeptID_dept_triGOcreate trigger DeptID_dept_tri on Dept for update as if update(DeptID) begin declare DeptID_new char(8),DeptID_old char(8) select DeptID_new=DeptID from inserted select DeptID_old=DeptID from deleted update Class set DeptID=DeptID_new where DeptID=DeptID_oldendgo建立一個(gè)觸發(fā)器，確保學(xué)院號(hào)保持一致。CollegeID_college_tri39。tr39。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=39。 AND TYPE=39。)DROP TRIGGER RewardID_reward_triGOcreate trigger RewardID_reward_tri on Reward for update as if update(RewardID) begin declare RewardID_new char(8),RewardID_old char(8) select RewardID_new=RewardID from inserted select RewardID_old=RewardID from deleted update HuoJiang set RewardID=RewardID_new where RewardID=RewardID_oldendgo建立一個(gè)觸發(fā)器，確保PunishID號(hào)保持一致。PunishID_punish_tri39。t
教學(xué)教案相關(guān)推薦
鄂ICP備17016276號(hào)-1