逻辑数据库设计示例.docx

1、逻辑数据库设计示例StudentRelation schemaStudent(Student_ID,Student_Name,Student_Password,Student_State,Student_Sex,Enrolling_Date)Primary Key Student_IDAttributes字段中文名称字段英文名称数据类型是否为空缺省值说明学员编号Student_IDIntN0主关键字(PK) 学员名称Student_NameVarchar(20)N无默认情况下与人员名称保持一致学员口令Student_passwordVarchar(10)N无采用des加密学员状态Student

2、_StatusChar(1)N无Y ：正常 N：禁用学员性别Student_SexChar(1) MorF N无入学日期Enrolling_DateDatetimeN0个人邮箱E-mailVarchar(30)Y无TeacherRelation schemaTeacher(Teacher_ID,Teacher_Name,Teacher_Password,Teacher_State,Teacher_Sex,TelNO,E-mail)Primary Key Teacher_IDAlternate KeysTelNoAttributes字段中文名称字段英文名称数据类型是否为空缺省值说明教师编号Tea

3、cher_idIntnnPK姓名Teacher_namevarchar(30)nn性别Teacher_sexchar(1)nn从标准代码表中取密码Teacher_passwordvarchar(255)nn单向加密电话TelNovarchar(30)yn电子信箱E-mailvarchar(50)yn状态Teacher_Statechar(1)nn教师状态为上课教师（y）和非上课教师（n）CourseRelation schemaCourse(Course_ID,Course_Name,Major_ID,Course_Type,Course_Created,Course_Start,Course

4、_End,Course_Time,Course_Score,Course_State,Course_Intro)Primary KeyCourse_IDAttributes字段中文名称字段英文名称数据类型能否为空缺省值说明课程编号Course_IDIntNPK课程名称Course_NameVarchar(100)N课程专业Major_IDInt课程类别Course_TypeInt创建日期Course_CreatedDatetimeN开课日期Course_StartDatetimeN结课日期Course_EndDatetimeN课程学时Course_TimeIntN0课程学分Course_Sco

5、reIntN课程状态Course_StateChar(1)N课程简介Course_IntroText说明：课程类别分为限定上课时间（以0表示）和不限定上课时间（以1表示）课程状态为未上课（N）、正上课（Y）、已结束（O）、已撤销（C），已备份（B）ClassroomRelation schemaClassroom (Serial_no,Class_begin,Class_end, online_begin, online_end)Primary KeySerial_noAttributes字段中文名称字段英文名称数据类型是否为空缺省值说明在线流水serial_nointn0主关键字(PK)课堂

6、开始时间class_begindatetimey如果课堂是要求固定日期时间上课的话，记录上课的时间课堂结束时间class_enddatetimey如果课堂是要求固定日期时间上课的话，记录下课的时间开始时间online_begindatetimen记录学员登录进入系统的时间结束时间online_enddatetimey记录学员离开系统的时间ApplicationRelation schemaApplication (Apply_ID, Student_ID, Apply_Date, Course_ID, Apply_Type, Apply_State, Apply_Content)Primary

7、 Key Apply_IDForeign KeyStudent_ID references Student (Student_ID)Attributes字段中文名称字段英文名称数据类型能否为空缺省值说明申请编号Apply_IDIntNNPK学员编号Student_IDIntNN课程编号Course_IDIntNN申请日期Apply_DateDatetimeN申请类型Apply_TypeChar(1)N申请状态Apply_StateChar(1)N申请内容Apply_ContentVarchar(200)YCourseStudentRelation schemaCourseStudent (Co

8、urse_ID, Student_ID, Serial_no, Student_type, Student_Score, Student_Grade, Student_Result)Primary Key Course_ID, Student_ID,Serial_noForeign KeyCourse_ID references Course (Course_ID)Student_ID references Student (Student_ID)Serial_no references Classroom (Serial_no)Attributes字段中文名称字段英文名称数据类型能否为空缺省

9、值说明课程编号Course_IDIntNNPK,FK学员编号Student_IDIntNNPK,FK在线流水serial_nointn0PK,FK学员类型Student_typeChar(1)NN考试成绩Student_ScoreNumber(3)学分成绩Student_GradeNumber(3)综合成绩Student_ResultNumber(3)CourseTeacherRelation schemaCourseTeacher (Course_ID, Teacher_ID, Serial_no, Start_Date, End_Date, Teacher_State)Primary Ke

10、y Course_ID, Teacher_ID,Serial_noForeign KeyCourse_ID references Course (Course_ID)Teacher_ID references Teacher (Teacher_ID)Serial_no references Classroom (Serial_no)Attributes字段中文名称字段英文名称数据类型能否为空缺省值说明课程编号course_idintnnPK,FK教师编号teacher_idintnnPK,FK在线流水serial_nointn0PK,FK开始日期start_datedatetime结束日期en

11、d_datedatetime教师状态teacher_statechar(1)nTestRelation schemaTest(Test_ID,Test_Name,Course_ID ,Test_Date,Test_Time,Test_Type,Teacher_ID, Test_State, Paper_ID)Primary Key Test_IDForeign KeyCourse_ID references Course (Course_ID)Attributes字段中文名称字段英文名称数据类型能否为空缺省值说明考试编号Test_IDIntNNPK考试名称Test_NameVarchar(10

12、0)NN课程编号Course_IDIntNNFK考试日期Test_DateDatetimeNN考试时间Test_TimeIntN0分钟考试类型Test_TypeIntN试卷编号Paper_IDIntN监控教师Teacher_IDInt考试状态Test_StateChar(1)N已安排TestPaperRelation schemaTestPaper (Paper_ID, Test_ID, Subject_ID, Object_Anwser ,Subject_Answer, Subject_Score)Primary KeyPaper_IDForeign KeyTest_ID reference

13、s Test (Test_ID)Subject_ID references Subject (Subject_ID)Attributes字段中文名称字段英文名称数据类型能否为空缺省值说明试卷编号paper_idintPK考试编号test_idintnnFK试题编号Subject_idintnnFK客观题答案object_anwservarchar(255)nn主观题答案subject_anwsertext试题成绩subject_scorenumber(3)n0TestStudentRelation schemaTestStudent (Student_ID, Paper_ID)Primary

14、KeyStudent_ID, Paper_ID Foreign KeyStudent_ID references Student (Student_ID)Paper_ID references TestPaper (Paper_ID)Attributes字段中文名称字段英文名称数据类型能否为空缺省值说明学员编号Student_IDIntN0PK,FK 试卷编号Paper_IDIntNPK,FKTestResultRelation schemaTestResult(Test_ID,Student_ID,Course_IDTest_Done,Object_Score,Subject_Score,T

15、est_Score Test_Memo)Primary KeyTest_ID, Student_ID, Course_IDForeign KeyTest_ID references Test (Test_ID)Student_ID references Student (Student_ID)Course_ID references Course (Course_ID)Attributes字段中文名称字段英文名称数据类型能否为空缺省值说明考试编号Test_IDIntNNPK,FK学员编号Student_IDIntNNPK,FK课程编号Course_IDIntNNPK,FK是否考试Test_Do

16、neChar(1)N客观题成绩Object_ScoreNumber(4)NN主观题成绩Subject_ScoreNumber(4)N0PK总成绩Test_ScoreNumber(4)N0是否补考Test_MemoChar(1)N正常SubjectRelation schemaSubject(Subject_ID,Subject_Type,Subject_Lever,Subject_Contents, Object_Anwser，Subject_Answer)Primary KeySubject_IDAttributes字段中文名称字段英文名称数据类型能否为空缺省值说明试题编号Subject_I

17、DIntNPK试题类型Subject_TypeChar(1)O（客观），S（主观）试题难度Subject_LeverIntN0（易），1（中），2（难）试题内容Subject_ContentsVarchar(255)NN客观题答案Object_AnwserVarchar(255)NN主观题答案Subject_AnwserTextExerciseRelation schemaExercise (Exercise_ID,Student_ID,Subject_ID,Teacher_ID,Handin_Time,Judge_State, Judge_Time, Grade ,Student_Answe

18、r)Primary KeyExercise_IDForeign KeyStudent_ID references Student (Student_ID)Subject_ID references Subject (Subject_ID)Teacher_ID references Teacher (Teacher_ID)Attributes字段中文名称字段英文名称数据类型是否为空缺省值说明作业编号Exercise_idintnnPK学员编号student_idintnnFK试题编号Subject_idintnnFK上交时间Handin_timedatetimenn学员上交作业的时间批改状态judge_statechar(1)n“N”U（未批改）,D（已批改)批改时间judge_timedatetimeyn批改教师teacher_idintynFK成绩gradeintyn学员答案Student_answerTextnn