/*
用Transact-SQL代码实现创建数据库、数据库表、以及设置数据库表的约束
1、下面是一个学校要实现对学生、教师、课程以及学生选课、学生课程成绩、教师上课、教师上课报酬等信息进行统一管理,并开发一个教学信息系统
*/

CREATE DATABASE TeachingManageSYS
ON        PRIMARY
(        NAME = TeachingManageSys,
FILENAME = 'E:\TeachingManageSYS\TeachingManageSYS.mdf',
SIZE = 3072KB,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB        )

LOG ON
(        NAME = 'TeachingManageSYS_log',
FILENAME = 'E:\TeachingManageSYS\TeachingManageSYS_log.ldf',
SIZE = 1024KB,MAXSIZE = 2048GB,FILEGROWTH = 10%        )
GO

USE TeachingManageSYS
GO
CREATE TABLE CourseInfo                /*课程信息表*/
(        
Cno                        char(4) PRIMARY KEY                NOT NULL,
Cname                char(10)        NOT NULL,
Credit                real        NOT NULL,
CouresHour        int                NOT NULL
        )
GO

CREATE TABLE RankPayMent        /*职别课酬*/
(
Trank                char(6) PRIMARY KEY                NOT NULL,
Payment                real        NOT NULL
        )
GO

CREATE TABLE StuAchievement        /*成绩表*/
(
Sno                char(6) PRIMARY KEY                NOT NULL,
Sfundamental        real        NULL,
Sminor                real        NULL
        )
GO

CREATE TABLE StudentCourse                /*学生选课表*/
(
Sno                char(6)         NOT NULL,
Cno                char(4)                NOT NULL,
Score        real        NULL
        )
GO

CREATE TABLE StudentInfo        /*学生信息表*/
(
Sno                char(6) PRIMARY KEY                NOT NULL,
Sname        char(10)        NOT NULL,
Ssex        char(2)                NOT NULL,
Sage        int NOT                NULL,
Saddress         varchar(30)         NULL,
Sphoto        p_w_picpath                NULL,
Smemory         varbinary(100)                NULL
        )
GO

CREATE TABLE Teach        /*教学表*/
(
Tno                char(6)                PRIMARY KEY                NOT NULL,
Cno                char(4)                NOT NULL,
Ssatisfact        real                NULL
        )
GO

CREATE TABLE TeacherInfo        /*教师信息表*/
(        
Tno                char(6)                PRIMARY KEY NOT NULL,
Tname        char(10)        NOT NULL,
Tsex        char(2)                NOT NULL,
Tage        int                        NOT NULL,
Trank        char(6)                NOT NULL,
Taddress        varchar(30)        NOT NULL,
Tphoto        p_w_picpath                NULL,
Tmemory        varchar(100)        NULL
        )
GO

/**
设置约束
1、需要对TeacherInfo(教师信息表)的Tname字段进行约束,
     即根据学校实际情况规定教师姓名不能重复
*/

CREATE UNIQUE NONCLUSTERED INDEX UN_TeacherInfo
ON        dbo.TeachInfo
(
Tname
)
GO

/**设置外键*/
--USE TeachingManageSYS
--GO

/* StudentCourse(外键 Sno)--StudentInfo(主键 Sno);*/
ALTER TABLE StudentCourse WITH CHECK ADD
CONSTRAINT FK_StudentCourse_StudentInfo
FOREIGN KEY        (Sno)
REFERENCES StudentInfo (Sno)
GO

/* StudentCourse(外键 Cno)--CourseInfo(主键 Cno).*/
ALTER TABLE StudentCourse WITH CHECK ADD
CONSTRAINT FK_StudentCourse_CourseInfo
FOREIGN KEY        (Cno)
REFERENCES        CourseInfo        (Sno)
GO

/*TeacherInfo(外键 Trank)--RankPayment(主键 Trank)*/
--USE TeachingManageSYS
--GO
ALTER TABLE TeacherInfo        WITH CHECK ADD
CONSTRAINT FK_TeacherInfo_RankPayMent
FOREIGN        KEY (Trank)
REFERENCES        RankPayment        (Trank)
GO

/*设计CHECK值
规定StudentInfo表的Sage(年龄)必须在18岁到20岁之间。
*/

ALTER TABLE StudentInfo
ADD CONSTRAINT Sage_check
CHECK (Sage>=18 AND Sage<=20)
GO

/*设计默认值
教师大部分家庭住址都是在“四川成都”,
可以在TeacherInfo(教师信息表)的Taddress字段处定义默认值为“四川成都”
*/

ALTER TABLE dbo.TeacherInfo
ADD CONSTRAINT DF_TeacherInfo_Taddress
DEFAULT '四川成都' FOR Taddress
GO

/*输入数据
以StudentInfo表为例,采用INSERT命令输入三条记录数据,由于记录手机比较多,
我们不在列出全部的命令。
*/

INSERT INTO StudentInfo
                (Sno,Sname,Ssex,Sage,Saddress)
        VALUES
                ('010101','蔡依林','女','25','台湾台北')
INSERT INTO StudentInfo
                (Sno,Sname,Ssex,Sage,Saddress)
        VALUES
                ('010102','刘欢','男','45','北京海淀')
INSERT INTO StudentInfo
        VALUES
                ('010103','马化腾','男','39','深圳南山')
GO