数据库实验报告.docx
《数据库实验报告.docx》由会员分享,可在线阅读,更多相关《数据库实验报告.docx(26页珍藏版)》请在冰豆网上搜索。
数据库实验报告
数据库实验报告
10信管胡芬琴101104009
题目及需求分析
题目:
教务管理信息系统
一.需求分析
要求开发一个教务管理信息系统,对教师,学生的情况,学生成绩,课程设置等进行管理。
1.学籍管理
对学生,课程,成绩等信息进行管理,实现综合测评的功能;产生学生成绩表;
掌握全校每个学生的基本情况,其中包括学号,姓名,出生日期,性别,家庭,家庭住址,电话等。
掌握每学期的课程设置情况;
掌握每个学生每门课程的学习成绩;
2.教师管理
对教师,部门,教学等信息进行管理;制定教师培训计划
掌握全校每个教师的基本情况,其中包括姓名,出生日期,职称,参加工作日期,文化程度,工作经历等;
掌握全校教师的工作情况,包括教师所承担的课程和课时数;
提供相关的汇总,统计报表。
二.概念数据模型(CDM)的创建(如图所示)
三.逻辑结构设计
物理数据模型(PDM)的创建(如图所示)
四.各表相关代码
4.1代码TABEL-TEACHER
ifexists(select1fromsys.sysforeignkeywhererole='FK_COURSE_TEACH_TEACHER')then
altertablecourse
deleteforeignkeyFK_COURSE_TEACH_TEACHER
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_STUDYEX_STUDYEXP_TEACHER')then
altertable"studyexperience"
deleteforeignkey"FK_STUDYEX_STUDYEXP_TEACHER"
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_T-FAMILY_T-FAMILY_TEACHER')then
altertable"t-family"
deleteforeignkey"FK_T-FAMILY_T-FAMILY_TEACHER"
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_TEACHER_LISHU_XY')then
altertableteacher
deleteforeignkeyFK_TEACHER_LISHU_XY
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_TRAIN-PL_MAKE_TEACHER')then
altertable"train-plan"
deleteforeignkey"FK_TRAIN-PL_MAKE_TEACHER"
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_WORKEXS_WORK-EXPE_TEACHER')then
altertable"workexsperience"
deleteforeignkey"FK_WORKEXS_WORK-EXPE_TEACHER"
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='lishu_FK'
andt.table_name='teacher'
)then
dropindexteacher.lishu_FK
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='teacher_PK'
andt.table_name='teacher'
)then
dropindexteacher.teacher_PK
endif;
ifexists(
select1fromsys.systable
wheretable_name='teacher'
andtable_typein('BASE','GBLTEMP')
)then
droptableteacher
endif;
createtableteacher
(
"t-no"char(6)notnull,
"xy-no"smallintnull,
"t-name"char(10)notnull,
"t-sex"char
(2)notnull,
"attend-time"datenotnull,
"t-xy"varchar(20)notnull,
zhichenchar(6)notnull,
"s-borndate"datenotnull,
"marry-condition"char
(2)notnull,
"t-phone"char(12)notnull,
"t-m-phone"char(11)notnull,
"t-yb"char(6)notnull,
"t-address"varchar(50)notnull,
constraintPK_TEACHERprimarykey("t-no")
);
createuniqueindexteacher_PKonteacher(
"t-no"ASC
);
createindexlishu_FKonteacher(
"xy-no"ASC
);
altertableteacher
addconstraintFK_TEACHER_LISHU_XYforeignkey("xy-no")
referencesxy("xy-no")
onupdaterestrict
ondeleterestrict;
4.2代码table-student
ifexists(select1fromsys.sysforeignkeywhererole='FK_COURSE-C_RELATIONS_STUDENT')then
altertable"course-c"
deleteforeignkey"FK_COURSE-C_RELATIONS_STUDENT"
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_S-FAMILY_S-FAMILY_STUDENT')then
altertable"s-family"
deleteforeignkey"FK_S-FAMILY_S-FAMILY_STUDENT"
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_S-JIANLI_STUDENT-S_STUDENT')then
altertable"s-jianli"
deleteforeignkey"FK_S-JIANLI_STUDENT-S_STUDENT"
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_STUDENT_GUISHU_XY')then
altertablestudent
deleteforeignkeyFK_STUDENT_GUISHU_XY
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='guishu_FK'
andt.table_name='student'
)then
dropindexstudent.guishu_FK
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='student_PK'
andt.table_name='student'
)then
dropindexstudent.student_PK
endif;
ifexists(
select1fromsys.systable
wheretable_name='student'
andtable_typein('BASE','GBLTEMP')
)then
droptablestudent
endif;
createtablestudent
(
"s-no"char(9)notnull,
"xy-no"smallintnull,
"s-name"char(10)notnull,
"s-sex"char
(2)notnull,
"s-borntime"datenotnull,
"attend-year"datenotnull,
"s-major"char(16)notnull,
"s-xy"varchar(20)notnull,
"s-idcard"char(18)notnull,
"s-phone"char(12)notnull,
"s-address"varchar(50)notnull,
"s-youbian"char(6)notnull,
"s-picture"longbinarynotnull,
constraintPK_STUDENTprimarykey("s-no")
);
createuniqueindexstudent_PKonstudent(
"s-no"ASC
);
createindexguishu_FKonstudent(
"xy-no"ASC
);
altertablestudent
addconstraintFK_STUDENT_GUISHU_XYforeignkey("xy-no")
referencesxy("xy-no")
onupdaterestrict
ondeleterestrict;
4.3代码table-course-c
ifexists(select1fromsys.sysforeignkeywhererole='FK_COURSE-C_RELATIONS_STUDENT')then
altertable"course-c"
deleteforeignkey"FK_COURSE-C_RELATIONS_STUDENT"
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_COURSE-C_RELATIONS_COURSE')then
altertable"course-c"
deleteforeignkey"FK_COURSE-C_RELATIONS_COURSE"
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='Relationship_9_FK'
andt.table_name='course-c'
)then
dropindex"course-c".Relationship_9_FK
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='Relationship_8_FK'
andt.table_name='course-c'
)then
dropindex"course-c".Relationship_8_FK
endif;
ifexists(
select1fromsys.systable
wheretable_name='course-c'
andtable_typein('BASE','GBLTEMP')
)then
droptable"course-c"
endif;
createtable"course-c"
(
"s-no"char(9)null,
"c-no"char(6)null,
greadsmallintnull
);
createindexRelationship_8_FKon"course-c"(
"s-no"ASC
);
createindexRelationship_9_FKon"course-c"(
"c-no"ASC
);
altertable"course-c"
addconstraint"FK_COURSE-C_RELATIONS_STUDENT"foreignkey("s-no")
referencesstudent("s-no")
onupdaterestrict
ondeleterestrict;
altertable"course-c"
addconstraint"FK_COURSE-C_RELATIONS_COURSE"foreignkey("c-no")
referencescourse("c-no")
onupdaterestrict
ondeleterestrict;
4.4代码table-course
ifexists(select1fromsys.sysforeignkeywhererole='FK_COURSE_TEACH_TEACHER')then
altertablecourse
deleteforeignkeyFK_COURSE_TEACH_TEACHER
endif;
ifexists(select1fromsys.sysforeignkeywhererole='FK_COURSE-C_RELATIONS_COURSE')then
altertable"course-c"
deleteforeignkey"FK_COURSE-C_RELATIONS_COURSE"
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='teach_FK'
andt.table_name='course'
)then
dropindexcourse.teach_FK
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='course_PK'
andt.table_name='course'
)then
dropindexcourse.course_PK
endif;
ifexists(
select1fromsys.systable
wheretable_name='course'
andtable_typein('BASE','GBLTEMP')
)then
droptablecourse
endif;
createtablecourse
(
"c-no"char(6)notnull,
"t-no"char(6)null,
"c-name"char(24)notnull,
"c-xs"smallintnotnull,
constraintPK_COURSEprimarykey("c-no")
);
createuniqueindexcourse_PKoncourse(
"c-no"ASC
);
createindexteach_FKoncourse(
"t-no"ASC
);
altertablecourse
addconstraintFK_COURSE_TEACH_TEACHERforeignkey("t-no")
referencesteacher("t-no")
onupdaterestrict
ondeleterestrict;
4.5代码table-teacherfamily
ifexists(select1fromsys.sysforeignkeywhererole='FK_T-FAMILY_T-FAMILY_TEACHER')then
altertable"t-family"
deleteforeignkey"FK_T-FAMILY_T-FAMILY_TEACHER"
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='t-familymember_FK'
andt.table_name='t-family'
)then
dropindex"t-family"."t-familymember_FK"
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='t-family_PK'
andt.table_name='t-family'
)then
dropindex"t-family"."t-family_PK"
endif;
ifexists(
select1fromsys.systable
wheretable_name='t-family'
andtable_typein('BASE','GBLTEMP')
)then
droptable"t-family"
endif;
createtable"t-family"
(
"tf-lineno"integernotnull,
"t-no"char(6)null,
"tf-name"char(10)notnull,
"tf-relationship"char(6)notnull,
"tf-wplace"varchar(20)notnull,
"tf-position"varchar(20)notnull,
"tf-phone"char(12)notnull,
constraint"PK_T-FAMILY"primarykey("tf-lineno")
);
createuniqueindex"t-family_PK"on"t-family"(
"tf-lineno"ASC
);
createindex"t-familymember_FK"on"t-family"(
"t-no"ASC
);
altertable"t-family"
addconstraint"FK_T-FAMILY_T-FAMILY_TEACHER"foreignkey("t-no")
referencesteacher("t-no")
onupdaterestrict
ondeleterestrict;
4.6代码table"workexsperience"
ifexists(select1fromsys.sysforeignkeywhererole='FK_WORKEXS_WORK-EXPE_TEACHER')then
altertable"workexsperience"
deleteforeignkey"FK_WORKEXS_WORK-EXPE_TEACHER"
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='work-experience_FK'
andt.table_name='workexsperience'
)then
dropindex"workexsperience"."work-experience_FK"
endif;
ifexists(
select1fromsys.sysindexi,sys.systablet
wherei.table_id=t.table_id
andi.index_name='workexsperience_PK'
andt.table_name='workexsperience'
)then
dropindex"workexsperience"."workexsperience_PK"
endif;
ifexists(
select1fromsys.systable
wheretable_name='workexsperience'
andtable_typein('BASE','GBLTEMP')
)then
droptable"workexsperience"
endif;
createtable"workexsperience"
(
"we-lineno"integernotnull,
"t-no"ch