/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2008-3-20 22:27:48                           */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('affiche') and o.name = 'FK_AFFICHE_REFERENCE_DEPARTME')
alter table affiche
   drop constraint FK_AFFICHE_REFERENCE_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('choose_course') and o.name = 'FK_CHOOSE_C_REFERENCE_CLASSROO')
alter table choose_course
   drop constraint FK_CHOOSE_C_REFERENCE_CLASSROO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('choose_course') and o.name = 'FK_CHOOSE_C_REFERENCE_PLAN_COU')
alter table choose_course
   drop constraint FK_CHOOSE_C_REFERENCE_PLAN_COU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('choose_course') and o.name = 'FK_CHOOSE_C_REFERENCE_TEACHER')
alter table choose_course
   drop constraint FK_CHOOSE_C_REFERENCE_TEACHER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('class') and o.name = 'FK_CLASS_REFERENCE_SPECIALI')
alter table class
   drop constraint FK_CLASS_REFERENCE_SPECIALI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('course') and o.name = 'FK_COURSE_REFERENCE_STUDENT')
alter table course
   drop constraint FK_COURSE_REFERENCE_STUDENT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('course') and o.name = 'FK_COURSE_REFERENCE_PLAN_COU')
alter table course
   drop constraint FK_COURSE_REFERENCE_PLAN_COU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('grade') and o.name = 'FK_GRADE_REFERENCE_PLAN_COU')
alter table grade
   drop constraint FK_GRADE_REFERENCE_PLAN_COU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('grade') and o.name = 'FK_GRADE_REFERENCE_STUDENT')
alter table grade
   drop constraint FK_GRADE_REFERENCE_STUDENT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('plan_course') and o.name = 'FK_PLAN_COU_REFERENCE_DEPARTME')
alter table plan_course
   drop constraint FK_PLAN_COU_REFERENCE_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('speciality') and o.name = 'FK_SPECIALI_REFERENCE_DEPARTME')
alter table speciality
   drop constraint FK_SPECIALI_REFERENCE_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('student') and o.name = 'FK_STUDENT_REFERENCE_CLASS')
alter table student
   drop constraint FK_STUDENT_REFERENCE_CLASS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('teacher') and o.name = 'FK_TEACHER_REFERENCE_DEPARTME')
alter table teacher
   drop constraint FK_TEACHER_REFERENCE_DEPARTME
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('affiche')
            and   name  = 'affiche_aid_indx'
            and   indid > 0
            and   indid < 255)
   drop index affiche.affiche_aid_indx
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('choose_course')
            and   name  = 'choose_course_id_indx'
            and   indid > 0
            and   indid < 255)
   drop index choose_course.choose_course_id_indx
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('choose_course')
            and   name  = 'choose_course_cid_indx'
            and   indid > 0
            and   indid < 255)
   drop index choose_course.choose_course_cid_indx
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('class')
            and   name  = 'class_clid_index'
            and   indid > 0
            and   indid < 255)
   drop index class.class_clid_index
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('classroom')
            and   name  = 'classroom_crid_indx'
            and   indid > 0
            and   indid < 255)
   drop index classroom.classroom_crid_indx
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('course')
            and   name  = 'course_stid_indx'
            and   indid > 0
            and   indid < 255)
   drop index course.course_stid_indx
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('department')
            and   name  = 'department_did_index'
            and   indid > 0
            and   indid < 255)
   drop index department.department_did_index
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('grade')
            and   name  = 'grade_stid_indx'
            and   indid > 0
            and   indid < 255)
   drop index grade.grade_stid_indx
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('grade')
            and   name  = 'grade_gid_indx'
            and   indid > 0
            and   indid < 255)
   drop index grade.grade_gid_indx
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('plan_course')
            and   name  = 'plan_course_did_indx'
            and   indid > 0
            and   indid < 255)
   drop index plan_course.plan_course_did_indx
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('plan_course')
            and   name  = 'plan_course_cid_indx'
            and   indid > 0
            and   indid < 255)
   drop index plan_course.plan_course_cid_indx
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('speciality')
            and   name  = 'speciality_did_index'
            and   indid > 0
            and   indid < 255)
   drop index speciality.speciality_did_index
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('speciality')
            and   name  = 'speciality_spid_index'
            and   indid > 0
            and   indid < 255)
   drop index speciality.speciality_spid_index
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('student')
            and   name  = 'student_clid_indx'
            and   indid > 0
            and   indid < 255)
   drop index student.student_clid_indx
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('student')
            and   name  = 'student_stid_indx'
            and   indid > 0
            and   indid < 255)
   drop index student.student_stid_indx
go

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

if exists (select 1
            from  sysindexes
           where  id    = object_id('teacher')
            and   name  = 'teacher_did_index'
            and   indid > 0
            and   indid < 255)
   drop index teacher.teacher_did_index
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('teacher')
            and   name  = 'teacher_tid_index'
            and   indid > 0
            and   indid < 255)
   drop index teacher.teacher_tid_index
go

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

/*==============================================================*/
/* Table: affiche                                               */
/*==============================================================*/
create table affiche (
   a_id                 int                  not null,
   d_id                 nvarchar(4)          not null,
   a_content            text                 null,
   a_time               datetime             null,
   a_enable_time        datetime             null,
   a_accessories        nvarchar(30)         null,
   constraint PK_AFFICHE primary key (a_id)
)
go

/*==============================================================*/
/* Index: affiche_aid_indx                                      */