1 /*学生表*/ 2 CREATE TABLE Student 3 ( 4     StuNO NVARCHAR(12) NOT  NULL PRIMARY KEY,/*学号*/ 5     StuName nvarchar(20) not null,/*姓名*/ 6     StuState int not null default(1),/*学籍状态。1:在读；2:试读；3:退学；*/ 7     StuJoinYear int /*入学年份*/ 8 ) 9 /*学年表*/10 CREATE TABLE SchoolYear11 (12     SyID int primary key not null identity(1,1),13     SyStartYear int,/*学年开始年份*/14     SyEndYear int/*学年结束年份*/15 )16 17 /*课程（科目）表*/18 CREATE TABLE Course19 (20     CourseID int primary key not null identity(1,1),21     CourseName nvarchar(30),/*课程名称*/22     CourseTotalScore decimal not null default(100),/*该课程总分数*/23     CoursePassScore decimal not null default(60)/*该课程及格分数*/24 ) 25 26 /*班级年级表*/27 CREATE TABLE GradeClass28 (29     GcID int primary key not null identity(1,1),/*班级年级ID*/30     GradeNo int not null,/*年级编号*/31     ClassNo int not null,/*班级编号*/32     GcName nvarchar(10)/*年级班级名称*/33 )34 35 go36 37 /*班级年级学生对照表*/38 CREATE TABLE GradeClassStu39 (40     GcsID int primary key not null identity(1,1),/*对照表编号*/41     SyID int not null,/*学年表*/42     GcID int not null,/*班级年级表*/43     StuNO NVARCHAR(12) NOT  NULL,/*学生学号*/44     45     constraint FK_GradeClassStu_StuNO foreign key (StuNO) references Student (StuNO),46     constraint FK_GradeClassStu_GcID foreign key (GcID) references GradeClass (GcID),47     constraint FK_GradeClassStu_SyID foreign key (SyID) references SchoolYear (SyID),48 )49 50 /*课程注册表*/51 CREATE TABLE CourseRegist52 (53     CRID int primary key not null identity(1,1),/*ID*/54     StuNO NVARCHAR(12),/*学生学号*/55     SyID int,/*注册学年*/56     CourseID int,/*注册课程*/57     58     constraint FK_CourseRegist_StuNO foreign key (StuNO) references Student (StuNO),59     constraint FK_CourseRegist_SyID foreign key (SyID) references SchoolYear (SyID),60     constraint FK_CourseRegist_CourseID foreign key (CourseID) references Course (CourseID),61 )62 63 64 65 /*成绩表*/66 CREATE TABLE Score67 (68     ScoreID int primary key not null identity(1,1),69     StuNO  NVARCHAR(12) NOT  NULL,/*学号*/70     CourseID int not null,/*课程ID*/71     SyID int not null,/*学年ID*/72     ScoreValue decimal not null default(0),/*课程得分*/73     ScoreExtraCount int not null default(0),/*加分次数*/74     75     constraint FK_Score_StuNO foreign key (StuNO) references Student (StuNO),76     constraint FK_Score_SyID foreign key (SyID) references SchoolYear (SyID),77     constraint FK_Score_CourseID foreign key (CourseID) references Course (CourseID),78 )

 1 /*1 初始化数据库：用于删除数据*/ 2 delete  GradeClassStu 3 delete  Score 4 delete  CourseRegist 5 delete  Course 6 delete  SchoolYear 7 delete  Student 8 delete  GradeClass 9 /*2 初始化数据库：重置标识列*/10 DBCC checkident (SchoolYear,reseed,1) 11 DBCC checkident (Course,reseed,1)12 DBCC checkident (CourseRegist,reseed,1)13 DBCC checkident (Score,reseed,1)14 DBCC checkident (GradeClassStu,reseed,1)