CREATE  DATABASE  STUDENT
ON  PRIMARY
(NAME=STUDENT_DAT , FILENAME='D:\STUDENT_DAT.MDF',
SIZE=1 , MAXSIZE=2 , FILEGROWTH=15%)
LOG  ON
(NAME=STUDENT_LOG , FILENAME='D:\STUDENT_LOG.LDF',
SIZE=1 , MAXSIZE=2 , FILEGROWTH=15%)


CREATE  TABLE  DEPARTMENT
(
DNO        CHAR(2) PRIMARY  KEY,
DN         CHAR(20) NOT NULL,
D_DATE     CHAR(7) NOT NULL,
D_HEAD     CHAR(10) NOT NULL,
D_ADDRESS  CHAR(20) NULL,
D_PHONE    CHAR(12) NULL
)

CREATE  TABLE  TEACHER
(
TNO        CHAR(10) PRIMARY  KEY,
TN         CHAR(20) NOT NULL,
T_SEX      CHAR(2)  NOT NULL DEFAULT('男') CHECK(T_SEX='男' OR T_SEX='女'),
T_PROF     CHAR(10) NULL,
T_BIRTHDAY CHAR(10),
T_DATE     CHAR(7) NOT NULL,
T_SALARY   FLOAT NULL,
DNO        CHAR(2)  NULL REFERENCES DEPARTMENT(DNO)
)


CREATE  TABLE  CLASS
(
CNO         CHAR(10) PRIMARY  KEY,
CN          CHAR(10) NOT NULL,
C_DATE      CHAR(7) NOT NULL,
C_CLASSROOM CHAR(14) NULL,
C_NUM       INT NULL,
TNO         CHAR(10) NULL REFERENCES TEACHER(TNO),
DNO         CHAR(2)  NULL REFERENCES DEPARTMENT(DNO)
)


CREATE  TABLE  SPECIALITY
(
SP_NO        CHAR(2) PRIMARY  KEY,
SP_N         CHAR(20) NOT NULL,
SP_DATE      CHAR (7) NOT NULL,
SP_NUM       INT NULL,
DNO          CHAR(2) NULL REFERENCES DEPARTMENT(DNO)
)

CREATE  TABLE  CHAMBER
(
CH_NO         CHAR(5) PRIMARY  KEY,
CH_ADDRESS    CHAR(30) NOT NULL,
CH_NUM        INT NULL
)

CREATE  TABLE  STUDENT
(
SNO        CHAR(10) PRIMARY  KEY,
SN         CHAR(20) NOT NULL,
S_SEX      CHAR(2)  NOT NULL DEFAULT('男') CHECK(S_SEX='男' OR S_SEX='女'),
S_BIRTHDAY CHAR(10) NOT NULL,
S_DATE     CHAR(7) NOT NULL,
S_SCHOOL   CHAR(30) NULL,
S_GRADE    FLOAT NULL,
S_PHONE    CHAR(12) NULL,
S_ADDRESS  CHAR(30) NULL,
SP_NO      CHAR(2) NULL REFERENCES SPECIALITY(SP_NO),
CNO        CHAR(10) NULL,
CH_NO      CHAR(5)  NULL REFERENCES CHAMBER(CH_NO),
CONSTRAINT  FK_S_C  FOREIGN  KEY(CNO)  REFERENCES  CLASS(CNO)
)

CREATE  TABLE  COURSE
(
CO_NO        CHAR(10) PRIMARY  KEY,
CO_N         CHAR(20) NOT NULL,
CO_T_TIME    INT NULL,
CO_E_TIME    INT NULL,
CO_S         INT NULL,
CO_BOOK      CHAR(24) NULL,
DNO          CHAR(2) NULL REFERENCES DEPARTMENT(DNO)
)



CREATE  TABLE  ACTIVITY
(
ANO         CHAR(3) PRIMARY  KEY,
AN          CHAR(30) NOT NULL,
A_DATE      CHAR(7) NOT NULL
)


CREATE  TABLE  REGISTER
(
RNO         CHAR(5) PRIMARY  KEY,
DNO         CHAR(2) NULL REFERENCES DEPARTMENT(DNO),
R_COST      INT NULL
)

CREATE  TABLE  SC
(
SNO         CHAR(10) NOT NULL REFERENCES STUDENT(SNO),
CO_NO       CHAR(10)  NOT NULL REFERENCES COURSE(CO_NO),
SC_CLASS    CHAR(6)  NULL,
SC_GRADE    FLOAT    NULL,
TNO         CHAR(10) REFERENCES TEACHER(TNO),
SC_DATE     CHAR(7)  NULL
PRIMARY KEY(SNO,CO_NO)
)


CREATE  TABLE  PROJECT
(
SP_NO         CHAR(2) NOT NULL REFERENCES SPECIALITY(SP_NO),
CO_NO         CHAR(10)  NOT NULL REFERENCES COURSE(CO_NO),
CLASS         CHAR(10) NULL,
PRIMARY KEY(SP_NO,CO_NO)
)

CREATE  TABLE  TC
(
TNO             CHAR(10) NOT NULL REFERENCES TEACHER(TNO),
CO_NO           CHAR(10)  NOT NULL REFERENCES COURSE(CO_NO),
TC_T_DATETIME   CHAR(20) NULL,
TC_T_ADDRESS    CHAR(10) NULL,
TC_E_DATETIME   CHAR(20) NULL,
TC_E_ADDRESS    CHAR(10) NULL,
PRIMARY KEY(TNO,CO_NO)
)

CREATE  TABLE  JOINACTIVITY
(
ANO             CHAR(3) NOT NULL REFERENCES ACTIVITY(ANO),
J_GRADE         FLOAT NULL,
J_OTHER         CHAR(20) NULL,
SNO             CHAR(10) NOT NULL REFERENCES STUDENT(SNO),
PRIMARY KEY(ANO,SNO)
)

CREATE  TABLE  LOGON
(
RNO            CHAR(5) NOT NULL REFERENCES REGISTER(RNO),
SNO            CHAR(10) NOT NULL REFERENCES STUDENT(SNO),
L_DATE         CHAR(7) NOT NULL ,
PRIMARY KEY(RNO,SNO)
)

INSERT INTO DEPARTMENT VALUES('01','計算機技術(shù)與科學系','2000-01','張三','科技樓','111111')
INSERT INTO TEACHER VALUES('0000000001','李四','男','副教授','1964-01-01','1987-02',4000.0,'01')
INSERT INTO CLASS VALUES('0000000001','信科2班','2006-09','寧-1-4',33,NULL,'01')
INSERT INTO SPECIALITY VALUES('01','軟件專業(yè)','2006-09',33,'01')
INSERT INTO STUDENT VALUES('2006012201','李六','男','1987-01-01','2006-09','長沙市第一中學',521,'222222','開福區(qū)198號','01','0000000001')
INSERT INTO COURSE VALUES('0000000001','數(shù)據(jù)庫',80,10,5,'數(shù)據(jù)庫原理與應(yīng)用','01')
INSERT INTO ACTIVITY VALUES('001','運動會','2007-10')
INSERT INTO CHAMBER VALUES('A1113','洪山宿舍一棟',12)
INSERT INTO REGISTER VALUES('20072','01',6000)
INSERT INTO SC VALUES('2006012201','0000000001','考試科',90,'0000000001','2007-09')
INSERT INTO PROJECT VALUES('01','0000000001','選修')


INSERT INTO TC VALUES('0000000001','0000000001','星期一第一大節(jié)','寧-1-4','星期三第二大節(jié)','科技樓1605')


INSERT INTO JOINACTIVITY VALUES('001',11,'一百米短跑','2006012201')
INSERT INTO LOGON VALUES('20072','2006012201','2007-09')