《逻辑数据库设计示例》由会员分享，可在线阅读，更多相关《逻辑数据库设计示例（13页珍藏版）》请在金锄头文库上搜索。
1、StudentRelation schemaStudent(Student_ID,Student_Name,Student_Password,Student_State,Student_Sex, Enrolling_Date)Primary KeyStudent_IDAttributes字段中文 名称字段英文名称物上MA -rprl 数据类型是否 为空缺省 值说明学员编号Student IDIntN0主关键字(PK)学员名称Student NameVarchar(20)N无默认情况下与人员名称保持一致学员口令Student passwordVarchar(lO)N无采用des加密学员状态Stu
2、dent StatusChar(l)N无Y :正常N：禁用学员性别Student SexChar(l) MorFN无入学日期Enrolling DateDatetimeN0个人邮箱E-mailVarchar(30)Y无TeacherRelation schemaTeacher(Teacher_ID,Teacher_Name,Teacher_Password,Teacher_State,Teacher_Sex, TelNO,E-mail)Primary KeyTeacher_IDAlternate KeysTelNoAttributes字段中文 名称字段英文名称数据类型是否为 空缺省 值说明教师
3、编号Teacher idIntnnPK姓名Teacher namevarchar(30)nn性别Teacher sexchar(l)nn从标准代码表中取密码Teacher passwordvarchar(255)nn单向加密电话TelNovarchar(30)yn电子信箱E-mailvarchar(50)yn状态Teacher_Statechar(l)nn教师状态为上课教师（y）和非 上课教师（n）CourseRelation schemaCourse(Course_ID,Course_Name,Major_ID,Course_Type,Course_Created, Course_Start
4、,Course_End,Course_Time,Course_Score,Course_State, Course_Intro)Primary KeyCourse_IDAttributes字段中文名称字段英文名称物上 IE MA -Tprl 数据类型能否为空缺省值说明课程编号Course IDIntNPK课程名称Course NameVarchar(lOO)N课程专业Major IDInt课程类别Course TypeInt创建日期Course CreatedDatetimeN开课日期Course StartDatetimeN结课日期Course EndDatetimeN课程学时Course
5、TimeIntN0课程学分Course ScoreIntN课程状态Course StateChar(l)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字段中文 名称字段英文名称数据类型是否为 空缺省 值说明
6、在线流水serial nointno主关键字(PK)课堂开始 时间class_begindatetimey如果课堂是要求固定日期时间 上课的话，记录上课的时间课堂结束 时间class_enddatetimey如果课堂是要求固定日期时间 上课的话，记录下课的时间开始时间online begindatetimen记录学员登录进入系统的时间结束时间online enddatetimey记录学贝离开系统的时间ApplicationRelation schemaApplication (Apply_ID, Student_ID, Apply_Date, Course_ID, Apply_Type, Ap
7、ply_State, Apply_Content)Primary KeyApply_IDForeign KeyStudent_ID references Student (Student_ID)Attributes字段中文名称字段英文名称数据类型能否为空缺省值说明申请编号Apply IDIntNNPK学员编号Student IDIntNN课程编号Course IDIntNN申请日期Apply DateDatetimeN申请类型Apply TypeChar(l)N申请状态Apply StateChar(l)N申请内容Apply ContentVarchar(200)YCourseStudentR
8、elation schemaCourseStudent (Course_ID, Student_ID, Serial_no, Student_type, Student_Score, Student_Grade, Student_Result)Primary KeyCourse_ID, Student_ID,Serial_noForeign KeyCourse_ID references Course (Course_ID) Student_ID references Student (Student_ID)Serial_no references Classroom (Serial_no)A
9、ttributes字段中文名称字段英文名称数据类型能否为空缺省值说明课程编号Course IDIntNNPK,FK学员编号Student IDIntNNPK,FK在线流水serial nointn0PK,FK学员类型Student typeChar(l)NN考试成绩Student ScoreNumber(3)学分成绩Student GradeNumber(3)综合成绩Student ResultNumber(3)CourseTeacherRelation schemaCourseTeacher (Course_ID, Teacher_ID, Serial_no, Start_Date, End
10、_Date, Teacher_State)Primary KeyCourse_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
11、,FK开始日期start datedatetime结束日期end datedatetime教师状态teacher statechar(l)nTestRelation schemaTest(Test_ID,Test_Name,Course_ID ,Test_Date,Test_Time,Test_Type,Teacher_ID,Test_State, Paper_ID)Primary KeyTest_IDForeign KeyCourse_ID references Course (Course_ID)Attributes字段中文名称字段英文名称数据类型能否为空缺省值说明考试编号Test IDI
12、ntNNPK考试名称Test NameVarchar(lOO)NN课程编号Course IDIntNNFK考试日期Test DateDatetimeNN考试时间Test TimeIntN0分钟考试类型Test TypeIntN试卷编号Paper IDIntN监控教师Teacher IDInt考试状态Test StateChar(l)N已安排TestPaperRelation schemaTestPaper (Paper_ID, Test_ID, Subject_ID, Object_Anwser ,Subject_Answer, Subject_Score)Primary KeyPaper_I
13、DForeign KeyTest_ID references 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 KeyStudent_ID, P
