createtables.sql

这是1个数据库

/****** Object:  Database park    Script Date: 2003-5-21 15:04:59 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'park')
	DROP DATABASE [park]
GO

CREATE DATABASE [park]
 COLLATE Chinese_PRC_CS_AS
GO

exec sp_dboption N'park', N'autoclose', N'true'
GO

exec sp_dboption N'park', N'bulkcopy', N'false'
GO

exec sp_dboption N'park', N'trunc. log', N'true'
GO

exec sp_dboption N'park', N'torn page detection', N'true'
GO

exec sp_dboption N'park', N'read only', N'false'
GO

exec sp_dboption N'park', N'dbo use', N'false'
GO

exec sp_dboption N'park', N'single', N'false'
GO

exec sp_dboption N'park', N'autoshrink', N'true'
GO

exec sp_dboption N'park', N'ANSI null default', N'false'
GO

exec sp_dboption N'park', N'recursive triggers', N'false'
GO

exec sp_dboption N'park', N'ANSI nulls', N'false'
GO

exec sp_dboption N'park', N'concat null yields null', N'false'
GO

exec sp_dboption N'park', N'cursor close on commit', N'false'
GO

exec sp_dboption N'park', N'default to local cursor', N'false'
GO

exec sp_dboption N'park', N'quoted identifier', N'false'
GO

exec sp_dboption N'park', N'ANSI warnings', N'false'
GO

exec sp_dboption N'park', N'auto create statistics', N'true'
GO

exec sp_dboption N'park', N'auto update statistics', N'true'
GO

use [park]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Selected_Class_Class]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Selected_Class] DROP CONSTRAINT FK_Selected_Class_Class
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Login_Student]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Login] DROP CONSTRAINT FK_Login_Student
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Selected_Class_Student]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Selected_Class] DROP CONSTRAINT FK_Selected_Class_Student
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Class_Teacher]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Class] DROP CONSTRAINT FK_Class_Teacher
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Login_Teacher]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Login] DROP CONSTRAINT FK_Login_Teacher
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Login_UserView]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Login] DROP CONSTRAINT FK_Login_UserView
GO

/****** Object:  Table [dbo].[Class]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Class]
GO

/****** Object:  Table [dbo].[Classes]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Classes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Classes]
GO

/****** Object:  Table [dbo].[Content]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Content]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Content]
GO

/****** Object:  Table [dbo].[Course]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Course]
GO

/****** Object:  Table [dbo].[Enum]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Enum]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Enum]
GO

/****** Object:  Table [dbo].[Login]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Login]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Login]
GO

/****** Object:  Table [dbo].[Navigator]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Navigator]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Navigator]
GO

/****** Object:  Table [dbo].[Selected_Class]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Selected_Class]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Selected_Class]
GO

/****** Object:  Table [dbo].[Student]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Student]
GO

/****** Object:  Table [dbo].[Teacher]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Teacher]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Teacher]
GO

/****** Object:  Table [dbo].[UserView]    Script Date: 2003-5-21 15:04:59 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserView]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UserView]
GO

/****** Object:  Login park    Script Date: 2003-5-13 8:41:34 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'park')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'park', @loginlang = N'简体中文'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'park', null, @logindb, @loginlang
END
GO

/****** Object:  User park    Script Date: 2003-5-21 15:04:59 ******/
if not exists (select * from dbo.sysusers where name = N'park' and uid < 16382)
	EXEC sp_grantdbaccess N'park', N'park'
GO

/****** Object:  User park    Script Date: 2003-5-21 15:04:59 ******/
exec sp_addrolemember N'db_owner', N'park'
GO

/****** Object:  Table [dbo].[Class]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Class] (
	[ID] [int] NOT NULL ,
	[Name] [varchar] (10) NULL ,
	[CourseID] [int] NULL ,
	[TeacherID] [nvarchar] (20) NULL ,
	[Capicity] [int] NULL ,
	[EndTime] [smalldatetime] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Classes]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Classes] (
	[ID] [int] NULL ,
	[ClassName] [varchar] (30) NOT NULL ,
	[Type] [int] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Content]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Content] (
	[Content] [varchar] (50) NOT NULL ,
	[Help] [varchar] (50) NULL ,
	[Role] [varchar] (50) NOT NULL ,
	[NextContent] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Course]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Course] (
	[CourseID] [int] NOT NULL ,
	[AvailableTeacher] [char] (10) NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Enum]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Enum] (
	[Type] [int] NOT NULL ,
	[Info] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Login]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Login] (
	[UserID] [nvarchar] (20) NOT NULL ,
	[Password] [varchar] (50) NULL ,
	[Role] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Navigator]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Navigator] (
	[IndexName] [varchar] (50) NOT NULL ,
	[Content] [varchar] (50) NULL ,
	[ChildIndexName] [varchar] (50) NULL ,
	[Icon] [varchar] (50) NULL ,
	[Title] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Selected_Class]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Selected_Class] (
	[StudentID] [nvarchar] (20) NULL ,
	[ClassID] [int] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Student]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Student] (
	[ID] [nvarchar] (20) NOT NULL ,
	[Name] [char] (10) NULL ,
	[BelongingClass] [varchar] (30) NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Teacher]    Script Date: 2003-5-21 15:05:04 ******/
CREATE TABLE [dbo].[Teacher] (
	[ID] [nvarchar] (20) NOT NULL ,
	[Name] [char] (10) NULL ,
	[Email] [char] (20) NULL ,
	[Prof] [varchar] (10) NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[UserView]    Script Date: 2003-5-21 15:05:05 ******/
CREATE TABLE [dbo].[UserView] (
	[Role] [varchar] (50) NOT NULL ,
	[FirstIndex] [varchar] (50) NULL ,
	[SecondIndex] [varchar] (50) NULL ,
	[ThirdIndex] [varchar] (50) NULL ,
	[Content] [varchar] (50) NULL ,
	[Title] [varchar] (50) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Class] WITH NOCHECK ADD 
	CONSTRAINT [PK_Class] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Classes] WITH NOCHECK ADD 
	CONSTRAINT [PK_Classes] PRIMARY KEY  CLUSTERED 
	(
		[ClassName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Enum] WITH NOCHECK ADD 
	CONSTRAINT [PK_Enum] PRIMARY KEY  CLUSTERED 
	(
		[Type]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Login] WITH NOCHECK ADD 
	CONSTRAINT [PK_Login] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Student] WITH NOCHECK ADD 
	CONSTRAINT [PK_Student] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Teacher] WITH NOCHECK ADD 
	CONSTRAINT [PK_Teacher] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserView] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserView] PRIMARY KEY  CLUSTERED 
	(
		[Role]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Class] ADD 
	CONSTRAINT [FK_Class_Teacher] FOREIGN KEY 
	(
		[TeacherID]
	) REFERENCES [dbo].[Teacher] (
		[ID]
	)
GO

ALTER TABLE [dbo].[Login] ADD 
	CONSTRAINT [FK_Login_Student] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[Student] (
		[ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_Login_Teacher] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[Teacher] (
		[ID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_Login_UserView] FOREIGN KEY 
	(
		[Role]
	) REFERENCES [dbo].[UserView] (
		[Role]
	)
GO

alter table [dbo].[Login] nocheck constraint [FK_Login_Student]
GO

alter table [dbo].[Login] nocheck constraint [FK_Login_Teacher]
GO

ALTER TABLE [dbo].[Selected_Class] ADD 
	CONSTRAINT [FK_Selected_Class_Class] FOREIGN KEY 
	(
		[ClassID]
	) REFERENCES [dbo].[Class] (
		[ID]
	),
	CONSTRAINT [FK_Selected_Class_Student] FOREIGN KEY 
	(
		[StudentID]
	) REFERENCES [dbo].[Student] (
		[ID]
	)
GO