/*
用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