邏輯數(shù)據(jù)庫設(shè)計示例

1、StudentRelation schemaStudent(Student_ID,Student_Name,Student_Password,Student_State,Student_Sex,Enrolling_Date)Primary Key Student_IDAttributes字段中文名稱字段英文名稱數(shù)據(jù)類型是否為空缺省值說明學(xué)員編號Student_IDIntN0主關(guān)鍵字(PK) 學(xué)員名稱Student_NameVarchar(20)N無默認(rèn)情況下與人員名稱保持一致學(xué)員口令Student_passwordVarchar(10)N無采用des加密學(xué)員狀態(tài)Student_StatusCh

2、ar(1)N無Y ：正常 N：禁用學(xué)員性別Student_SexChar(1) MorF N無入學(xué)日期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字段中文名稱字段英文名稱數(shù)據(jù)類型是否為空缺省值說明教師編號Teacher_idIn

3、tnnPK姓名Teacher_namevarchar(30)nn性別Teacher_sexchar(1)nn從標(biāo)準(zhǔn)代碼表中取密碼Teacher_passwordvarchar(255)nn單向加密電話TelNovarchar(30)yn電子信箱E-mailvarchar(50)yn狀態(tài)Teacher_Statechar(1)nn教師狀態(tài)為上課教師（y）和非上課教師（n）CourseRelation schemaCourse(Course_ID,Course_Name,Major_ID,Course_Type,Course_Created,Course_Start,Course_End,Cour

4、se_Time,Course_Score,Course_State,Course_Intro)Primary KeyCourse_IDAttributes字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明課程編號Course_IDIntNPK課程名稱Course_NameVarchar(100)N課程專業(yè)Major_IDInt課程類別Course_TypeInt創(chuàng)建日期Course_CreatedDatetimeN開課日期Course_StartDatetimeN結(jié)課日期Course_EndDatetimeN課程學(xué)時Course_TimeIntN0課程學(xué)分Course_ScoreIntN課程狀

5、態(tài)Course_StateChar(1)N課程簡介Course_IntroText說明：課程類別分為限定上課時間（以0表示）和不限定上課時間（以1表示）課程狀態(tài)為未上課（N）、正上課（Y）、已結(jié)束（O）、已撤銷（C），已備份（B）ClassroomRelation schemaClassroom (Serial_no,Class_begin,Class_end, online_begin, online_end)Primary KeySerial_noAttributes字段中文名稱字段英文名稱數(shù)據(jù)類型是否為空缺省值說明在線流水serial_nointn0主關(guān)鍵字(PK)課堂開始時間class

6、_begindatetimey如果課堂是要求固定日期時間上課的話，記錄上課的時間課堂結(jié)束時間class_enddatetimey如果課堂是要求固定日期時間上課的話，記錄下課的時間開始時間online_begindatetimen記錄學(xué)員登錄進(jìn)入系統(tǒng)的時間結(jié)束時間online_enddatetimey記錄學(xué)員離開系統(tǒng)的時間ApplicationRelation schemaApplication (Apply_ID, Student_ID, Apply_Date, Course_ID, Apply_Type, Apply_State, Apply_Content)Primary Key Appl

7、y_IDForeign KeyStudent_ID references Student (Student_ID)Attributes字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明申請編號Apply_IDIntNNPK學(xué)員編號Student_IDIntNN課程編號Course_IDIntNN申請日期Apply_DateDatetimeN申請類型Apply_TypeChar(1)N申請狀態(tài)Apply_StateChar(1)N申請內(nèi)容Apply_ContentVarchar(200)YCourseStudentRelation schemaCourseStudent (Course_ID,

8、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字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明課程編號Co

9、urse_IDIntNNPK,FK學(xué)員編號Student_IDIntNNPK,FK在線流水serial_nointn0PK,FK學(xué)員類型Student_typeChar(1)NN考試成績Student_ScoreNumber(3)學(xué)分成績Student_GradeNumber(3)綜合成績Student_ResultNumber(3)CourseTeacherRelation schemaCourseTeacher (Course_ID, Teacher_ID, Serial_no, Start_Date, End_Date, Teacher_State)Primary Key Course_

10、ID, Teacher_ID,Serial_noForeign KeyCourse_ID references Course (Course_ID)Teacher_ID references Teacher (Teacher_ID)Serial_no references Classroom (Serial_no)Attributes字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明課程編號course_idintnnPK,FK教師編號teacher_idintnnPK,FK在線流水serial_nointn0PK,FK開始日期start_datedatetime結(jié)束日期end_datedat

11、etime教師狀態(tài)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字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明考試編號Test_IDIntNNPK考試名稱Test_NameVarchar(100)NN課程編號C

12、ourse_IDIntNNFK考試日期Test_DateDatetimeNN考試時間Test_TimeIntN0分鐘考試類型Test_TypeIntN試卷編號Paper_IDIntN監(jiān)控教師Teacher_IDInt考試狀態(tài)Test_StateChar(1)N已安排TestPaperRelation schemaTestPaper (Paper_ID, Test_ID, Subject_ID, Object_Anwser ,Subject_Answer, Subject_Score)Primary KeyPaper_IDForeign KeyTest_ID references Test (T

13、est_ID)Subject_ID references Subject (Subject_ID)Attributes字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明試卷編號paper_idintPK考試編號test_idintnnFK試題編號Subject_idintnnFK客觀題答案object_anwservarchar(255)nn主觀題答案subject_anwsertext試題成績subject_scorenumber(3)n0TestStudentRelation schemaTestStudent (Student_ID, Paper_ID)Primary KeyStuden

14、t_ID, Paper_ID Foreign KeyStudent_ID references Student (Student_ID)Paper_ID references TestPaper (Paper_ID)Attributes字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明學(xué)員編號Student_IDIntN0PK,FK 試卷編號Paper_IDIntNPK,FKTestResultRelation schemaTestResult(Test_ID,Student_ID,Course_IDTest_Done,Object_Score,Subject_Score,Test_Score

15、 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字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明考試編號Test_IDIntNNPK,FK學(xué)員編號Student_IDIntNNPK,FK課程編號Course_IDIntNNPK,FK是否考試Test_DoneChar(1)

16、N客觀題成績Object_ScoreNumber(4)NN主觀題成績Subject_ScoreNumber(4)N0PK總成績Test_ScoreNumber(4)N0是否補(bǔ)考Test_MemoChar(1)N正常SubjectRelation schemaSubject(Subject_ID,Subject_Type,Subject_Lever,Subject_Contents, Object_Anwser，Subject_Answer)Primary KeySubject_IDAttributes字段中文名稱字段英文名稱數(shù)據(jù)類型能否為空缺省值說明試題編號Subject_IDIntNPK試題

17、類型Subject_TypeChar(1)O（客觀），S（主觀）試題難度Subject_LeverIntN0（易），1（中），2（難）試題內(nèi)容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_Answer)Primary KeyExercise_IDForeign KeyStudent_ID references Student (Student_ID)Subject_ID references Subject (Subject_ID)Teacher_ID reference