create.sql

一个经典的东东

use master
go
exec xp_cmdshell 'md c:\StuDB'
go
if exists(select * from sysdatabases where name='学生信息管理系统')
	drop database 学生信息管理系统
go
create database 学生信息管理系统
on primary
(
name='StuDB',
filename='c:\StuDB.mdf'
)
log on
(
name='StuDB_log',
filename='c:\StuDB.ldf'
)
go
use StuDB
go
/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2006-9-17 16:42:05                           */
/*==============================================================*/


alter table Course
   drop constraint FK_COURSE_FK_COURSE_CLASS
go

alter table Course
   drop constraint FK_COURSE_FK_COURSE_SUBJECT
go

alter table Score
   drop constraint FK_SCORE_FK_SCORE__COURSE
go

alter table Score
   drop constraint FK_Score_Student
go

alter table Student
   drop constraint FK_STUDENT_FK_STUDEN_CLASS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Class')
            and   type = 'U')
   drop table Class
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Course')
            and   type = 'U')
   drop table Course
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Score')
            and   type = 'U')
   drop table Score
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Student')
            and   type = 'U')
   drop table Student
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Subject')
            and   type = 'U')
   drop table Subject
go

/*==============================================================*/
/* Table: Class                                                 */
/*==============================================================*/
create table Class (
   ClassId            int     IDENTITY(1,1)               not null,
   ClassName            varchar(50)          null,
   EntranceDate         datetime             null,
   Remark               varchar(50)          null,
   constraint PK_CLASS primary key  (ClassId)
)
go

execute sp_addextendedproperty 'MS_Description', 
   '班级表',
   'user', '', 'table', 'Class'
go

/*==============================================================*/
/* Table: Course                                                */
/*==============================================================*/
create table Course (
   CourseId             int       IDENTITY(1,1)           not null,
   SubjectId            int                  null,
   ClassId              int                  null,
   BeginDate            datetime             null,
   FinishDate           datetime             null,
   Remark               varchar(256)         null,
   constraint PK_COURSE primary key  (CourseId)
)
go

execute sp_addextendedproperty 'MS_Description', 
   '课程表',
   'user', '', 'table', 'Course'
go

/*==============================================================*/
/* Table: Score                                                 */
/*==============================================================*/
create table Score (
   ScoreId              int       IDENTITY(1,1)           not null,
   CourseId             int                  null,
   StudentId            int                  null,
   Score                float(8)             null,
   constraint PK_SCORE primary key  (ScoreId)
)
go

execute sp_addextendedproperty 'MS_Description', 
   '分数表',
   'user', '', 'table', 'Score'
go

/*==============================================================*/
/* Table: Student                                               */
/*==============================================================*/
create table Student (
   StudentId            int       IDENTITY(1,1)           not null,
   StudentNO            varchar(20)          null,
   StudentName          varchar(50)          null,
   Gender               int                  null,
   Birthday             datetime             null,
   ClassId              int                  null,
   Remark               varchar(256)         null,
   constraint PK_STUDENT primary key  (StudentId)
)
go

execute sp_addextendedproperty 'MS_Description', 
   '学生表',
   'user', '', 'table', 'Student'
go

/*==============================================================*/
/* Table: Subject                                               */
/*==============================================================*/
create table Subject (
   SubjectId            int         IDENTITY(1,1)         not null,
   SubjectName          varchar(50)          null,
   Remark               varchar(256)         null,
   constraint PK_SUBJECT primary key  (SubjectId)
)
go

execute sp_addextendedproperty 'MS_Description', 
   '科目表',
   'user', '', 'table', 'Subject'
go

alter table Course
   add constraint FK_COURSE_FK_COURSE_CLASS foreign key (ClassId)
      references Class (ClassId)
         on update cascade on delete cascade
go

alter table Course
   add constraint FK_COURSE_FK_COURSE_SUBJECT foreign key (SubjectId)
      references Subject (SubjectId)
         on update cascade on delete cascade
go

alter table Score
   add constraint FK_SCORE_FK_SCORE__COURSE foreign key (CourseId)
      references Course (CourseId)
         on update cascade on delete cascade
go

alter table Score
   add constraint FK_Score_Student foreign key (StudentId)
      references Student (StudentId)
         on update cascade on delete cascade
go

alter table Student
   add constraint FK_STUDENT_FK_STUDEN_CLASS foreign key (ClassId)
      references Class (ClassId)
         on update cascade on delete cascade
go