|
|
发表于 2014-5-30 12:55:43
|
显示全部楼层
if exists (select 1
from sysobjects
where id = object_id('v_Score')
and type = 'V')
drop view v_Score
go
if exists (select 1
from sysobjects
where id = object_id('v_Teaching')
and type = 'V')
drop view v_Teaching
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('Elective')
and type = 'U')
drop table Elective
go
if exists (select 1
from sysobjects
where id = object_id('Notice')
and type = 'U')
drop table Notice
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('Teacher')
and type = 'U')
drop table Teacher
go
if exists (select 1
from sysobjects
where id = object_id('Teaching')
and type = 'U')
drop table Teaching
go
if exists(select 1 from systypes where name='Domain_1')
drop type Domain_1
go
/*==============================================================*/
/* Domain: Domain_1 */
/*==============================================================*/
create type Domain_1
from char(10)
go
/*==============================================================*/
/* Table: Course */
/*==============================================================*/
create table Course (
CID int identity,
Description nvarchar(Max) null,
Grade nvarchar(10) null,
Term nvarchar(10) null,
Credit int null,
CName nvarchar(20) null,
constraint PK_COURSE primary key (CID)
)
go
/*==============================================================*/
/* Table: Elective */
/*==============================================================*/
create table Elective (
EID int identity,
SID int null,
CID int null,
Score float null,
constraint PK_ELECTIVE primary key (EID)
)
go
/*==============================================================*/
/* Table: Notice */
/*==============================================================*/
create table Notice (
NID int identity,
NTitle nvarchar(100) null,
NContent nvarchar(max) null,
NAuthor nvarchar(20) null,
NTime datetime null default getdate(),
constraint PK_NOTICE primary key (NID)
)
go
/*==============================================================*/
/* Table: Student */
/*==============================================================*/
create table Student (
SID int identity,
SNo char(9) null,
SName nvarchar(8) null,
SPwd varchar(20) null,
SSex nchar(1) null,
SClass varchar(10) null,
SType varchar(10) null,
SDepartment nvarchar(20) null,
SMajor nvarchar(20) null,
SMaxNum int null,
SActualNum int null,
SBirthday datetime null,
SInTime datetime null,
SGrade nvarchar(10) null,
SNote nvarchar(100) null,
constraint PK_STUDENT primary key (SID)
)
go
/*==============================================================*/
/* Table: Teacher */
/*==============================================================*/
create table Teacher (
TID int identity,
TNo char(4) null,
TName nvarchar(8) null,
TSex nchar(1) null,
TMajor nvarchar(20) null,
TPwd nvarchar(20) null,
TDepartment nvarchar(20) null,
TTitle nvarchar(10) null,
TIsAdmin int null default 0,
constraint PK_TEACHER primary key (TID)
)
go
/*==============================================================*/
/* Table: Teaching */
/*==============================================================*/
create table Teaching (
ID int identity,
TID int null,
CID int identity,
Week nvarchar(20) null,
Timeperiod nvarchar(20) null,
Place nvarchar(20) null,
MaxNum int null,
ActualNum int null default 0,
constraint PK_TEACHING primary key (ID)
)
go
/*==============================================================*/
/* View: v_Score */
/*==============================================================*/
create view v_Score as
SELECT dbo.Student.SID, dbo.Student.SNo, dbo.Student.SName, dbo.Course.CID, dbo.Course.Credit, dbo.Course.CName, dbo.Teaching.Week, dbo.Teaching.Timeperiod,
dbo.Teaching.Place, dbo.Teaching.MaxNum, dbo.Teaching.ActualNum, dbo.Elective.Score, dbo.Elective.EID
FROM dbo.Course INNER JOIN
dbo.Elective ON dbo.Course.CID = dbo.Elective.CID INNER JOIN
dbo.Student ON dbo.Elective.SID = dbo.Student.SID INNER JOIN
dbo.Teaching ON dbo.Course.CID = dbo.Teaching.CID INNER JOIN
dbo.Teacher ON dbo.Teaching.TID = dbo.Teacher.TID
go
/*==============================================================*/
/* View: v_Teaching */
/*==============================================================*/
create view v_Teaching as
SELECT dbo.Student.SID, dbo.Student.SNo, dbo.Student.SName, dbo.Course.CID, dbo.Course.Credit, dbo.Course.CName, dbo.Teaching.Week, dbo.Teaching.Timeperiod,
dbo.Teaching.Place, dbo.Teaching.MaxNum, dbo.Teaching.ActualNum, dbo.Elective.Score
FROM dbo.Course INNER JOIN
dbo.Elective ON dbo.Course.CID = dbo.Elective.CID INNER JOIN
dbo.Student ON dbo.Elective.SID = dbo.Student.SID INNER JOIN
dbo.Teaching ON dbo.Course.CID = dbo.Teaching.CID INNER JOIN
dbo.Teacher ON dbo.Teaching.TID = dbo.Teacher.TID
go
|
|
[复制链接]
