Database design of students ' achievement data base two

database table diagram

Database Script Build Table script

1 /*Student Table*/2 CREATE TABLEStudent3 (4StunoNVARCHAR( A) not  NULL PRIMARY KEY,/*School Number*/5Stunamenvarchar( -) not NULL,/*name*/6Stustateint  not NULL default(1),/*status of the student. 1: in Reading; 2: probation; 3: dropping out;*/7Stujoinyearint /*Year of entry*/8 )9 /*School year Table*/Ten CREATE TABLEschoolyear One ( ASyidint Primary Key  not NULL Identity(1,1), -Systartyearint,/*year beginning of year*/ -Syendyearint/*year end of year*/ the ) -  - /*Course (subject) Table*/ - CREATE TABLECourse + ( -CourseIDint Primary Key  not NULL Identity(1,1), +Coursenamenvarchar( -),/*Course Name*/ ACoursetotalscoredecimal  not NULL default( -),/*total score of the course*/ atCoursepassscoredecimal  not NULL default( -)/*Pass scores for this course*/ - )  -  - /*Class Grade Table*/ - CREATE TABLEGradeclass - ( inGcidint Primary Key  not NULL Identity(1,1),/*class Grade ID*/ -Gradenoint  not NULL,/*Grade number*/ toClassnoint  not NULL,/*class Number*/ +GCNamenvarchar(Ten)/*Grade class name*/ - ) the  * Go $ Panax Notoginseng /*class Grade Student chart*/ - CREATE TABLEGradeclassstu the ( +Gcsidint Primary Key  not NULL Identity(1,1),/*Table Number*/ ASyidint  not NULL,/*School year Table*/ theGcidint  not NULL,/*Class Grade Table*/ +StunoNVARCHAR( A) not  NULL,/*Students ' study number*/ -      $     constraintFk_gradeclassstu_stunoForeign Key(Stuno)ReferencesStudent (Stuno), $     constraintFk_gradeclassstu_gcidForeign Key(GCID)ReferencesGradeclass (gcid), -     constraintFk_gradeclassstu_syidForeign Key(Syid)Referencesschoolyear (Syid), - ) the  - /*Course Registration Form*/Wuyi CREATE TABLEcourseregist the ( -Cridint Primary Key  not NULL Identity(1,1),/*ID*/ WuStunoNVARCHAR( A),/*Students ' study number*/ -Syidint,/*Registered school year*/ AboutCourseIDint,/*Enroll Course*/ $      -     constraintFk_courseregist_stunoForeign Key(Stuno)ReferencesStudent (Stuno), -     constraintFk_courseregist_syidForeign Key(Syid)Referencesschoolyear (Syid), -     constraintFk_courseregist_courseidForeign Key(CourseID)ReferencesCourse (CourseID), A ) +  the  -  $ /*Score Table*/ the CREATE TABLEscore the ( theScoreidint Primary Key  not NULL Identity(1,1), theStunoNVARCHAR( A) not  NULL,/*School Number*/ -CourseIDint  not NULL,/*Course ID*/ inSyidint  not NULL,/*school year ID*/ theScorevaluedecimal  not NULL default(0),/*Course Score*/ theScoreextracountint  not NULL default(0),/*number of extra points*/ About      the     constraintFk_score_stunoForeign Key(Stuno)ReferencesStudent (Stuno), the     constraintFk_score_syidForeign Key(Syid)Referencesschoolyear (Syid), the     constraintFk_score_courseidForeign Key(CourseID)ReferencesCourse (CourseID), +)

Database initialization statements

1 /*1 Initializing the database: for deleting data*/2 DeleteGradeclassstu3 Deletescore4 Deletecourseregist5 DeleteCourse6 Deleteschoolyear7 DeleteStudent8 DeleteGradeclass9 /*2 Initializing the database: Resetting the identity column*/Ten DBCCCheckident (Schoolyear,reseed,1)  One DBCCCheckident (Course,reseed,1) A DBCCCheckident (Courseregist,reseed,1) - DBCCCheckident (Score,reseed,1) - DBCCCheckident (Gradeclassstu,reseed,1)

Database design of students ' achievement data base two