数据库设计方案学生成绩管理系统

1、广西工学院studentmanagersystem学生管理系统数据库设计方案目录一 需求分析.31 数据需求.32 功能需求.3二 逻辑数据库设计.41 标识实体.42 标识关系.43 标识实体和关系的属性.44 确定er模型中实体的属性域和关系属性域.65 确定候选键，主键和备用键属性.76 特化和泛化实体.87 检察模型的数据冗余.88 模型是否支持用户事务.99 创建表1010 表规范化检查.1111 再次检察模型是否支持用户事务.1312 检察业务规则.1513 用户参与讨论逻辑数据库设计.16三 物理数据库设计.161. 设计基本表 .162. 设计派生数据的表示.173. 设计其他

2、业务规则.184. 分析事务.185. 选择文件的组织方式.206. 选择索引.207. 设计用户视图.208. 设计访问规则.229. 考虑引入受控冗余.24四 附录.241数据字典表汇总.242sql查询的作业.263总结和感想.27一需求分析1数据需求11学生整个学生管理系统的主体，每一个学生有自己的详细的信息。学生的个人信息，包括：姓名，年龄，身份证号码。学生的家庭信息，包括：家庭住址，邮政编码，家庭电话号码。学生的学校信息，包括学号，登陆密码，学院，班别，各科的成绩，所选的课程。12班级每个班级有学生的个数，班级名称，班级代号，以及该班级的班主任。1学院每个系别有的学院代号，学院名字

3、，班级数量，学生的数量，老师的数量。1教师分为一般老师和班主任老师。每个教师有姓名，教师号，登陆密码，所教课程集合，是否是班主任，所带的班级，所属于学院，个人电话。一个班主任老师可以管理多个班级，一个班级只有一个班主任老师。15课程每个课程有课程号，授课的教师号，上课的地点，上课的时间，该课程的人数。16成绩每个成绩，有所属于的学生，所属于的课程。2功能需求21学生可以使用的功能：(a)每个学生可以查询到自己不同课程的不同成绩，也可以查询所有课程的成绩。(b)每个学生可以查询到自己所选择的某个课程的任课老师，上课地点和上课时间。也可以查询到自己的课程表。(c)每个学生可以查询到自己的个人的信息

4、，姓名，家庭住址，家庭电话，邮政编码，学号，系别，班别，班主任的姓名，电话。并且可以打印自己的个人信息。(d)每个学生可以修改一些自己的个人信息，如：家庭住址，家庭电话，邮政编码，登陆密码。22老师可以使用的功能：(e)每个老师可以查询自己的所教的某个课程的上课地点，上课时间，上课人数，和上课学生名单。并且可以打印出学生的名单表。(f)每个老师也可以查询到自己一周的授课表。并且可以打印该授课表。(g)每个老师可以对自己所教课程的学生成绩进行录入，修改，删除。(h)每个老师可以查询到自己所教课程的所有的学生的成绩，可以按升序，降序来显示，并且可以打印该成绩表。(i)每个老师可以计算出自己所教课程

5、的及格人数，不及格人数，通过率，及格率。(j)每个班主任可以统计自己班学生数，查询自己所带的班全部学生，全部课程的成绩，并且可以打印该成绩表。(k)每个班主任可以查询自己所带的班的全部学生的个人信息：姓名，家庭住址，家庭电话，邮政编码，学号，系别，班别。并且可以打印自己的个人信息。(l)家校联系，每个班主任可以查询和打印某个学生的全部成绩，并且将该表邮寄给学生家长。23系统管理员可以使用的功能：(m)对学生的个人信息进行录入，删除和修改。(n)对老师的个人信息进行录入，删除和修改。(o)对老师的操作权限进行分配。(p)对数据库进行功能维护。二逻辑数据库设计1 标识实体由数据需求的得出如下实体：

6、student（学生）teacher（老师）class（班级）college(学院)course（课程）entiyname（实体名称）description(实体描述)occurrence(事件)student学生选课，上课，考试等teacher一般老师班主任老师上课，管理班级，管理学生，class班级隶属于一个学院college学院开课程，拥有多个专业班级course课程一个学院可以开始多门课程表数据字典中的实体部分2标识关系21实体关系的多样性约束实体关系类型实体student（学生）chiose(选择)多对多course（课程）has(拥有)一对多course（课程成绩）teacher（

7、老师）(班主任)managed(管理)一对多student（学生）(班主任)managed(管理)一对多class（班级）teach(教)一对多course（课程）class（班级）has(拥有)一对多student（学生）college(学院)has(拥有)多对多student（学生）has(拥有)一对多class（班级）has(拥有)一对多teacher（老师）open(开设)一对多course（课程）course（课程）表2.学生管理系统的实体关系粗表实体多样性关系多样性实体student（学生）1* chiose(选择)1*course（课程）11has(拥有)1*course（课程成

8、绩）teacher（老师）11(班主任)managed(管理)0*student（学生）11(班主任)managed(管理)0*class（班级）11teach(教)0*course（课程）class（班级）11has(拥有)1*student（学生）college(学院)11has(拥有)1*student（学生）11has(拥有)1*class（班级）11has(拥有)1*teacher（老师）11open(开设)1*course（课程）course（课程）表3.学生管理系统的实体关系约束表22实体关系(er)模型图1.学生管理系统的实体-关系(er)模型初稿3 标识实体或关系的有关属性实

9、体属性student（学生）studentname(姓名),studentcardid(身份证号码) studentsex(性别)studentaddress (地址),studentzipcode(邮政编码),studentno(学号) teacher（老师）teachername(姓名),teacherno(教师号),teachersex(性别),teachertel(电话号码)class（班级）classname(班级名称),classno(班级号) college(学院)collegename(学院名称), collegeno(学院号)course（课程）courseno(课程号),c

10、oursename(课程名), coursetime(上课时间),courseaddress(上课地址) , coursemark(分数)表4.实体属性表实体关系实体属性studenthascoursecoursemark(分数)表5.关系属性表4 确定er模型中实体的属性域和关系属性域entiyname(实体)attributes(属性)description(描述)null(可否为空)same(可否相同)multivalued(可否多值)restrains(约束)student（学生）studentname学生姓名noyesno小于10位studentcardid身份证号nonono18位

11、studentsex性别noyesnomale/femalestudentaddress地址noyesno小于50位studentzipcode邮政编码noyesno6位studentno学号nonono10位teacher（老师）teachername教师姓名noyesno小于10位teacherno教师号nonono5位teachersex性别noyesnomale/femaleteachertel电话号码nonono小于20位class（班级）classname班级名称nonono小于20位collegeno班级号nonono3位college(学院)collegename学院名称non

12、ono小于20位collegeno学院号nonono3位course（课程）courseno课程号nonono5位coursename课程名nonono小于20位coursetime上课时间noyesno小于20位courseaddress上课地址nonono小于20位表6.数据字典中的实体属性域connection(关系)attributes(属性)description(描述)null(可否为空)same(可否相同)multivalued(可否多值)restrains(约束)hascoursemark分数学生拥有成绩noyesno0-100表7.数据字典中的关系属性域5确定候选键，主键和备

13、用键属性51stayhome主键图2.具有主键的学生管理系统的实体-关系(er)模型52将候选键，主键和备用键存档entiyname(实体)attributes(属性)description(描述)key(键值)student（学生）studentname学生姓名 studentcardid身份证号码altermate keystudentsex性别 studentaddress地址 studentzipcode邮政编码 studentno学号primart keyteacher（老师）teachername教师姓名 teacherno教师号primart keyteachersex性别tea

14、chertel电话号码class（班级）classname班级名称altermate keyclassno班级号primart keycollege(学院)collegename学院名称altermate keycollegeno学院号primart keycourse（课程）courseno课程号primart keycoursename课程名altermate keycoursetime上课时间courseaddress上课地址coursemark分数表8.数据字典中的候选键，主键和备用键存档6特化和泛化实体本系统将学校中的班主任老师和一般教学的老师都模型化为实体teacher。7检察模型

15、的数据冗余71检察一对一(1:1)关系最初设计实体模型时候，出现实体课程和成绩，两者是一一对应的关系，经过分析，两者其实是一个包含于的概念。成绩实际是输入课程中的一部分。72检察冗余关系student与class和college之间出现冗余关系，class属于某一个college,student也属于某一个college，但是同时student属于某一个class。我们可以通过student在哪个班就可以判断，student属于哪个学院，所以student与college的关系是冗余关系，但是为了方便数据查询我考虑继续使用这条冗余关系。teacher与course和college之间出现冗余关

16、系,course可以通过是哪个teacher教的，从而得到course是哪个学院开设的课程。所以course与college之间的关系是冗余的，在后面建立course表的时候需要注意。原来设计模型中,teacher与学生还有一条teach关系，但是因为teacher可以通过上某course，学生选某course，就可以得到哪个老师教什么学生，所以teacher teach student这条关系冗余，已经将其删除掉了。73当访问冗余时间尺度考虑本系统属于作业，系统太小，暂时不考虑冗余时间的尺度。图3.删除冗余关系的学生管理系统的实体-关系(er)模型8模型是否支持用户事务81数据模型中是否存在

17、必需的属性表6.数据字典中的实体属性域，表6的数据字典的实体的属性是必需的属性。82实体中是否有通路图4.学生管理系统的事务路径表9创建表91 student表由学生实体属性得到的原始学生表student(studentno,studentname,studentcardid,studentsex,studentaddress,studentzipcode）由学生实体相关的关系class（班级）11has(拥有)1*student（学生）college(学院)11has(拥有)1*student（学生）将collegeno, classno作为外键加入学生表中。得到student(studen

18、tno,studentname,studentcardid,studentsex,studentaddress,studentzipcode,classno,collegeno）primary key studentno alternate key studentcardidforegin key classno references class(classno)foregin key collegeno references college (collegeno)92 teacher表由教师实体属性得到的原始教师表teacher(teacherno,teachername,teacherse

19、x teachertel)由教师实体相关的关系college(学院)11has(拥有)1*teacher（老师）将collegeno作为外键添加到教师表中teacher(teacherno,teachername,teachersex,teachertel,collegeno)primary key teachernoforegin key collegeno references college (collegeno)93 class表由班级实体属性得到的原始班级表class(classno, classname)由class相关的关系college(学院)11has(拥有)1*class（

20、班级）teacher（老师）11(班主任)managed(管理)0*class（班级）将collegeno和teacherno作为外键加入到class表中class(classno, classname,teacherno,collegeno)primary key classnoalternate key classnameforegin key collegeno references college (collegeno)foregin key teacherno references teacher (teacherno)94 college表由学院实体属性得到的原始学院表colleg

21、e(collegeno,collegename)primary key collegenoalternate key collegename95 course表 由course属性和关系得到原始course表course(courseno, coursename, coursetime, courseaddress,coursemark)由课程相关的关系student（学生）1* chiose(选择)1*course（课程）11has(拥有)1*course（课程成绩）teacher（老师）11teach(教)0*course（课程）college(学院)11open(开设)1*course（

22、课程）将studentno,teaacherno,colleageno作为外键加入到course表course(courseno,coursename,coursetime,courseaddress,coursemark,studentno,teacherno,collegeno)primary key coursenoalternate key coursenameforegin key studentno references student (studentno)foregin key teacherno references teacher (teacherno)foregin ke

23、y collegeno references college (collegeno)有根据学生student和课程course多对多的关系，将上表拆分了两表course(courseno, coursename, coursetime, courseaddress, teacherno)primary key coursenoalternate key coursenameforegin key teacherno references teacher (teacherno)studentcourse(studentno,courseno, coursemark)primary key stu

24、dentno,coursenoforegin key studentno references student (studentno)foregin key courseno references course (courseno)10表规范化检查101检察student表student(studentno,studentname,studentcardid,studentsex,studentaddress,studentzipcode,classno,collegeno）looegeno可以从 classno中得到，所以该表不符合3nf，但是为了查询方便，决定牺牲存储空间。102检察tea

25、cher表teacher(teacherno,teachername,teachersex,teachertel,collegeno)符合3nf。103 检察class表class(classno, classname,teacherno,collegeno)由于colleageno可以用teacherno得到，所以不满足3nf。但是为了查询方便，决定牺牲存储空间。104 检察college表college(collegeno,collegename)符合3nf。105 检察course表course(courseno, coursename, coursetime, courseaddres

26、s, teacherno) 课程表因为一个老师可以教多门课程，所以上表不满足3nf，但是为了查询效率，决定牺牲存储空间。studentcourse(studentno,courseno, coursemark)满足3nf。106将所有表进行存档tablename表名称tabledefine表定义studentstudent(studentno,studentname,studentcardid,studentsex,studentaddress,studentzipcode,classno,collegeno）primary key studentnoalternate key student

27、cardidforegin key classno references class(classno)foregin key collegeno references college (collegeno)teacherteacher(teacherno,teachername,teachersex,teachertel,collegeno)primary key teachernoforegin key collegeno references college (collegeno)classclass(classno, classname,teacherno,collegeno)prima

28、ry key classnoalternate key classnameforegin key collegeno references college (collegeno)foregin key teacherno references teacher (teacherno)collegecollege(collegeno,collegename)primary key collegenoalternate key collegenamecoursecourse (courseno, coursename, coursetime, courseaddress, teacherno)pri

29、mary key coursenoalternate key coursenameforegin key teacherno references teacher (teacherno)studentcoursestudentcourse(studentno,courseno, coursemark)primary key studentno,coursenoforegin key studentno references student (studentno)foregin key courseno references course (courseno)表9.数据字典中的表的定义11再次检

30、察模型是否支持用户事务事务需求的表(a)每个学生可以查询到自己不同课程的不同成绩，也可以查询所有课程的成绩。studentcourse(studentno,courseno, coursemark)primary key studentno,courseno(b)每个学生可以查询到自己所选择的某个课程的任课老师，上课地点和上课时间。也可以查询到自己的课程表。studentcourse(studentno,courseno, coursemark)primary key studentno,coursenocourse (courseno, coursename, coursetime, cou

31、rseaddress, teacherno)primary key coursenoforegin key teacherno references teacher (teacherno)(c)每个学生可以查询到自己的个人的信息，姓名，家庭住址，家庭电话，邮政编码，学号，系别，班别，班主任的姓名，电话。并且可以打印自己的个人信息。student(studentno,studentname,studentcardid,studentsex,studentaddress,studentzipcode,classno,collegeno）primary key studentnoforegin ke

32、y classno references class(classno)foregin key collegeno references college (collegeno)(d)每个学生可以修改一些自己的个人信息，如：家庭住址，家庭电话，邮政编码。student(studentno,studentname,studentcardid,studentsex,studentaddress,studentzipcode,classno,collegeno）primary key studentnoforegin key classno references class(classno)foregi

33、n key collegeno references college (collegeno)(e)每个老师可以查询自己的所教的某个课程的上课地点，上课时间，上课人数，和上课学生名单。并且可以打印出学生的名单表。course (courseno, coursename, coursetime, courseaddress, teacherno)primary key coursenoforegin key teacherno references teacher (teacherno)studentcourse(studentno,courseno, coursemark)primary key

34、 studentno,courseno(f)每个老师也可以查询到自己一周的授课表。并且可以打印该授课表。course (courseno, coursename, coursetime, courseaddress, teacherno)primary key coursenoforegin key teacherno references teacher (teacherno)studentcourse(studentno,courseno, coursemark)primary key studentno,courseno(g)每个老师可以对自己所教课程的学生成绩进行录入，修改，删除。st

35、udentcourse(studentno,courseno, coursemark)primary key studentno,courseno(h)每个老师可以查询到自己所教课程的所有的学生的成绩，可以按升序，降序来显示。并且可以打印该成绩表。studentcourse(studentno,courseno, coursemark)primary key studentno,courseno(i)每个老师可以计算出自己所教课程的及格人数，不及格人数，通过率，及格率。studentcourse(studentno,courseno, coursemark)primary key studen

36、tno,courseno(j)每个班主任可以统计自己班学生数，查询自己所带的班全部学生，全部课程的成绩，并且可以打印该成绩表。studentcourse(studentno,courseno, coursemark)primary key studentno,coursenostudent(studentno,studentname,studentcardid,studentsex,studentaddress,studentzipcode,classno,collegeno）primary key studentnoforegin key classno references class(c

37、lassno)foregin key collegeno references college (collegeno)(k)每个班主任可以查询自己所带的班的全部学生的个人信息：姓名，家庭住址，家庭电话，邮政编码，学号，系别，班别。并且可以打印自己的个人信息。student(studentno,studentname,studentcardid,studentsex,studentaddress,studentzipcode,classno,collegeno）primary key studentnoforegin key classno references class(classno)fo

38、regin key collegeno references college (collegeno)class(classno, classname,teacherno,collegeno)primary key classnoforegin key collegeno references college (collegeno)foregin key teacherno references teacher (teacherno)(l)家校联系，每个班主任可以查询和打印某个学生的全部成绩，并且将该表邮寄给学生家长。student(studentno,studentname,studentca

39、rdid,studentsex,studentaddress,studentzipcode,classno,collegeno）primary key studentnoforegin key classno references class(classno)foregin key collegeno references college (collegeno)class(classno, classname,teacherno,collegeno)primary key classnoforegin key collegeno references college (collegeno)fo

40、regin key teacherno references teacher (teacherno)(m)对学生的个人信息进行录入，删除和修改。student(studentno,studentname,studentcardid,studentsex,studentaddress,studentzipcode,classno,collegeno）primary key studentnoforegin key classno references class(classno)foregin key collegeno references college (collegeno)(n)对老师的

41、个人信息进行录入，删除和修改。teacher(teacherno,teachername,teachersex,teachertel,collegeno)primary key teachernoforegin key collegeno references college (collegeno)(o)对老师的操作权限进行分配。teacher(teacherno,teachername,teachersex,teachertel,collegeno)primary key teachernoforegin key collegeno references college (collegeno

42、)(p)对数据库进行功能维护。全部表表10.学生管理系统更新/删除事务所需的表12检察业务规则121需要的数据见表6.数据字典中的实体属性域。122列的值域约束见表6.数据字典中的实体属性域。123实体完整性见表8.数据字典中的候选键，主键和备用键存档。124实体多样性见表3.学生管理系统的实体关系约束表。125参照完整性studentforegin key classno references class(classno) on update cascade on delete no actionforegin key collegeno references college (colleg

43、eno) on update cascade on delete no actionteacherforegin key collegeno references college (collegeno) on update cascade on delete no actionclassforegin key collegeno references college (collegeno) on update cascade on delete no actionforegin key teacherno references teacher (teacherno) on update cas

44、cade on delete no actioncourseforegin key teacherno references teacher (teacherno) on update cascade on delete no action表11. 学生管理系统的表的参照完整性约束表12.6其他业务规则本系统太小，其他业务规则不予添加。13用户参与讨论逻辑数据库设计 本系统太小,没有用户参与其中。三物理数据库设计1设计基本表在sqlserver2000中创建表：student表:teacher表class 表college表course表studentcourse表在sqlserver2000

45、中创建表关系图：图5学生管理系统中各表在sql2000中的关系图2设计派生数据的表示本系统给定的需求表明只有一个派生项目，就是老师统计自己所带的班级的学生人数，可以通过以下sql语句获得:select sum(studentno)from student, classwhere student.classno = class.classno and class.teacherno = 某某老师；如果老师经常要自己班级学生的人数的话，可以将每个班级人数作为一列加入到class表中，但是考虑到这个频率是很少的，老师并不用很频繁的查询自己班上的学生数目，所以在这种情况下，只要在需要的时候使用以上sql语句查询就可以了。3设计其他业务规则这个步骤的目标是设计应用到数据上的其他的业务规则 ，这些规则的设计同样要依赖于所选的dbms。本学生管理系统太小，该部分忽略不写。4分析事务41将所有事务路径映射到表事务表(a)(b)(c)(d)irudirudirudirudstudentteacherclasscollegecoursestudentcourse事务表(e)(f)(g)(h)irudirudirudirudstudentteacherclasscollege