学生档案管理系统数据库设计书Word文件下载.docx
《学生档案管理系统数据库设计书Word文件下载.docx》由会员分享,可在线阅读,更多相关《学生档案管理系统数据库设计书Word文件下载.docx(23页珍藏版)》请在冰豆网上搜索。
3.1E-R图
3.2关系图
班级表—Class
ClassID
char(8)
不允许空
主键
className
char(20)
DeptID
允许空
学院表--College
CollegeID
CollegeName
CollegeSummary
nchar(10)
课程表—Course
Cno
char(10)
Cname
keshi
int
xuefen
ksxs
院系表--Dept
DeptName
char(30)
获奖表--HuoJiang
Sno
RewardID
Time
datetime
惩罚表--Punish
PunishID
PunishName
奖励表--Reward
RewardName
RewardLevel
住宿表--Room
RoomID
RoomAdd
RoomNum
成绩表--SC
Score
学生表--Student
S_name
S_xmpy
S_cym
S_sex
char
(2)
S_age
S_mz
S_jg
char(100)
S_csrq
S_sfzh
S_image
image
S_zzmm
char(4)
S_csd
S_syd
S_rxnf
S_byxx
S_pycc
S_jtzz
S_jtlxdh
char(15)
S_sj
char(11)
用户表--Users
Uid
Uname
PassWord
违纪表--WeiJi
chulijieguo
text
3.3创建表、视图、索引脚本
一、数据库创建:
CREATEDATABASEStudentRecordDB
ON
(NAME=StudentRecordDB_data,FILENAME='
f:
\studentrecorddb\StudentRecordDB_data.mdf'
)
LOGON
(NAME=StudentRecordDB_log,FILENAME='
\studentrecorddb\StudentRecordDB_log.ldf'
GO
2、班级表:
CREATETABLEClass(
ClassIDCHAR(8)NOTNULL,
ClassNameCHAR(20)NOTNULL,
DeptIDCHAR(8)NULL,)
3、院系表
CREATETABLECollege(
CollegeIDCHAR(8)NOTNULL,
CollegeNameCHAR(20)NOTNULL,
CollegeSummaryCHAR(10)NULL,)
4、课程表
CREATETABLECourse(
CnoCHAR(10)NOTNULL,
CnameCHAR(10)NOTNULL,
keshiintNOTNULL,
xuefenintNOTNULL,
ksxschar(8)NOTNULL)
5、系表:
CREATETABLEDept(
DeptIDCHAR(8)NOTNULL,
DeptNameCHAR(30)NOTNULL,
CollegeIDCHAR(8)NULL)
6、获奖表:
CREATETABLEHuoJiang(
SnoCHAR(10)NOTNULL,
RewardIDCHAR(8)NOTNULL,
TimedatetimeNULL)
7、处罚表:
CREATETABLEPunish(
PunishIDchar(8)NOTNULL,
PunishNamechar(10)NULL)
8、奖励表:
CREATETABLEReward(
RewardIDchar(8)NOTNULL,
RewardNamechar(10)NOTNULL,
RewardLevelchar(10)NOTNULL)
9、宿舍表:
CREATETABLERoom(
RoomIDchar(8)NOTNULL,
RoomAddchar(20)NOTNULL,
RoomNumintNOTNULL)
10、选课表:
CREATETABLESC(
Snochar(10)NOTNULL,
Cnochar(10)NOTNULL,
ScoreintNULL)
11、学生表:
CREATETABLEStudent(
S_namechar(8)NOTNULL,
S_xmpychar(20)NOTNULL,
S_cymchar(8)NULL,
S_sexchar
(2)NOTNULL,
S_ageintNOTNULL,
S_mzchar(10)NOTNULL,
S_jgchar(100)NOTNULL,
S_csrqdatetimeNOTNULL,
RommIDintNULL,
ClassIDintNULL,
S_sfzhchar(15)NOTNULL,
S_imageimageNULL,
S_zzmmchar(4)NOTNULL,
S_csdchar(100)NULL,
S_sydchar(100)NOTNULL,
S_rxnfdatetimeNOTNULL,
S_byxxchar(20)NOTNULL,
S_pyccchar(10)NOTNULL,
S_jtzzchar(100)NOTNULL,
S_jtlxdhchar(15)NULL,
S_sjchar(11)NULL)
12、用户表:
CREATETABLEUsers(
UidintNOTNULL,
Unamechar(20)NOTNULL,
PassWordchar(20)NOTNULL)
13、违纪记录表:
CREATETABLEWeiJi(
TimedatetimeNOTNULL,
chulijieguotextNULL)
二、视图
--查询成绩
USEStudentRecordDB
CREATEVIEWSCORE_VIEW
ASSELECTStudent.Sno,S_name,Cname,Score
FROMStudent,Course,SC
WHEREStudent.Sno=sc.SnoANDSC.Cno=Course.Cno
--查询基本信息
CREATEVIEWjbxx_VIEW
ASSELECTStudent.Sno,S_name,S_sex,S_age,S_jg,S_zzmm,S_rxnf,
S_jtzz,S_sj,Class.ClassID,Dept.DeptID,Room.RoomID
FROMStudent,Class,Dept,Room
WHEREStudent.ClassID=Class.ClassIDANDClass.DeptID=Dept.DeptIDANDStudent.RoomID=Room.RoomID
--查询获奖情况
CREATEVIEWHJ_VIEW
ASSELECTStudent.Sno,S_name,RewardName
FROMStudent,HuoJiang,Reward
WHEREStudent.Sno=HuoJiang.SnoANDHuoJiang.RewardID=Reward.RewardID
--查询违纪情况
CREATEVIEWCF_VIEW
ASSELECTStudent.Sno,S_name,PunishName,chulijieguo
FROMStudent,WeiJi,Punish
WHEREStudent.Sno=WeiJi.SnoANDWeiJi.PunishID=Punish.PunishID
--通过姓名和课程名查询该生该课程成绩
IFEXISTS(SELECTNAMEFROMsysobjectsWHERENAME='
Pro_S_name'
ANDTYPE='
p'
DROPPROCEDUREPro_S_name
createprocedurePro_QScore
@S_name_inchar(8),@Cname_inchar(10),@Score_outintoutput
Asselect@Score_out=Scorefromstudent,course,sc
wherestudent.Sno=sc.Snoandcourse.Cno=sc.Cno
andS_name=@S_name_inandCname=@Cname_in
三、索引
--创建索引
useStudentRecordDB
go
createindexind_ClassIDonClass(ClassID)
createindexind_CnoonCourse(Cno)
createindexind_DeptIDonDept(DeptID)
createindexind_huojiangonHuoJiang(Sno,RewardID)
createindexind_PunishIDonPunish(PunishID)
createindexind_RewardIDonReward(RewardID)
createindexind_RoomIDonRoom(RoomID)
createindexind_sconSC(Sno,Cno)
createindexind_SnoonStudent(Sno)
createindexind_UidonUsers(Uid)
createindexind_weijionWeiJi(Sno,PunishID)
3.4完整性约束、自定义约束
--class表约束
altertableClass
addconstraintpk_ClassIDprimarykey(ClassID)
--course表约束
altertableCourse
addconstraintpk_Cnoprimarykey(Cno)
--dept表约束
altertableDept
addconstraintpk_DeptIDprimarykey(DeptID)
--huojiang表约束
altertableHuoJiang
addconstraintpk_huojiangprimarykey(Sno,RewardID)
--punish表约束
altertablePunish
addconstraintpk_PunishIDprimarykey(PunishID)
--reward表约束
altertableReward
addconstraintpk_RewardIDprimarykey(RewardID)
--room表约束
altertableRoom
addconstraintpk_RoomIDprimarykey(RoomID)
--sc表约束
altertableSC
addconstraintpk_scprimarykey(Sno,Cno)
--student表约束
altertableStudent
addconstraintpk_Snoprimarykey(Sno)
--Users表约束
altertableUsers
addconstraintpk_Uidprimarykey(Uid)
--weiji表约束
altertableWeiJi
addconstraintpk_weijiprimarykey(Sno)
--参照完整性
ALTERTABLESCWITHNOCHECK
ADDCONSTRAINTFK_student_scFOREIGNKEY(Sno)REFERENCESStudent(Sno)
ONDELETECASCADE
ADDCONSTRAINTFK_course_scFOREIGNKEY(Cno)REFERENCESCourse(Cno)
ALTERTABLEHuoJiangWITHNOCHECK
ADDCONSTRAINTFK_student_huojiangFOREIGNKEY(Sno)REFERENCESStudent(Sno)
ADDCONSTRAINTFK_reward_huojiangFOREIGNKEY(RewardID)REFERENCESReward(RewardID)
ALTERTABLEWeiJiWITHNOCHECK
ADDCONSTRAINTFK_student_weijiFOREIGNKEY(Sno)REFERENCESStudent(Sno)
ADDCONSTRAINTFK_punish_weijiFOREIGNKEY(PunishID)REFERENCESPunish(PunishID)
--class表约束(实体完整性)
--course表约束(实体完整性)
--dept表约束(实体完整性)
--huojiang表约束(实体完整性)
--punish表约束(实体完整性)
--reward表约束(实体完整性)
--room表约束(实体完整性)
--sc表约束(实体完整性)
useSt