create_db_template.sql

MsSQL通用数据库创建程序源码 ,程序的功能主要是在Delphi中利用程序创建MsSQL通用数据库

) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[考试配置表]    脚本日期: 2008-12-24 19:42:43 ******/
CREATE TABLE [dbo].[考试配置表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[考试开始日期] [smalldatetime] NULL ,
	[考试结束日期] [smalldatetime] NULL ,
	[考试时长] [decimal](18, 0) NULL ,
	[补时密码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[重考密码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[现场给分] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[是否启用] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[开放模拟] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[是否验证学号] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[是否验证准考证] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[考号位数] [int] NOT NULL ,
	[题量比例] [smallint] NOT NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[试卷配置表]    脚本日期: 2008-12-24 19:42:43 ******/
CREATE TABLE [dbo].[试卷配置表] (
	[ID] [int] NOT NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[题型] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[知识点] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[难易度] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[题量] [int] NULL ,
	[分数] [decimal](5, 1) NULL ,
	[题号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[课程代码表]    脚本日期: 2008-12-24 19:42:43 ******/
CREATE TABLE [dbo].[课程代码表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[课程名] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[阅卷模式] [int] NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[题型类别表]    脚本日期: 2008-12-24 19:42:43 ******/
CREATE TABLE [dbo].[题型类别表] (
	[ID] [int] NOT NULL ,
	[题型类别] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[有标准答案] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[任课教师表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[任课教师表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[教师编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[教师姓名] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[密码] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[所属考点] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[是否启用] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[知识点表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[知识点表] (
	[ID] [decimal](18, 0) NOT NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[知识点] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[答案附件表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[答案附件表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[PID] [int] NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[学号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[答案附件文件名] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[答案附件] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[考场信息表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[考场信息表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[考点代码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[考场代码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[考场名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[考生试卷表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[考生试卷表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[学号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[题号] [int] NOT NULL ,
	[小题号] [int] NOT NULL ,
	[题型] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[知识点] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[难易度] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[题目ID] [decimal](18, 0) NOT NULL ,
	[标准答案] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[题目分值] [decimal](18, 1) NULL ,
	[答案] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[得分] [decimal](18, 1) NULL ,
	[Action_Time] [smalldatetime] NOT NULL ,
	[阅卷人] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[审核人] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[阅卷时间] [smalldatetime] NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[题型表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[题型表] (
	[ID] [int] NOT NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[题型] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[题型类别] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[教师课程表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[教师课程表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[教师编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[任课班级] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[是否上传] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[题库表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[题库表] (
	[ID] [int] NOT NULL ,
	[PID] [int] NULL ,
	[课程号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[题型] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[知识点] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[难易度] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[试题] [image] NOT NULL ,
	[标准答案] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[答案分析] [image] NULL ,
	[Action_Time] [smalldatetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[备选答案表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[备选答案表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[PID] [int] NOT NULL ,
	[备选项] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[备选内容] [image] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[题库附件表]    脚本日期: 2008-12-24 19:42:44 ******/
CREATE TABLE [dbo].[题库附件表] (
	[ID] [int] NOT NULL ,
	[PID] [int] NOT NULL ,
	[是否回收] [bit] NOT NULL ,
	[试题附件文件名] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[试题附件] [image] NULL ,
	[编辑软件] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[Action_Time] [smalldatetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** 对象: 表 [dbo].[改卷程序表]    脚本日期: 2008-12-24 19:42:45 ******/
CREATE TABLE [dbo].[改卷程序表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[PID] [int] NOT NULL ,
	[改卷程序名] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[改卷程序] [image] NOT NULL ,
	[Action_Time] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tmp_试卷配置表] WITH NOCHECK ADD 
	CONSTRAINT [PK_tmp_试卷配置表] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[准考证表] WITH NOCHECK ADD 
	CONSTRAINT [PK_准考证表] PRIMARY KEY  CLUSTERED 
	(
		[准考证号]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[操作员表] WITH NOCHECK ADD 
	CONSTRAINT [PK_操作员表] PRIMARY KEY  CLUSTERED 
	(
		[操作员ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[操作说明表] WITH NOCHECK ADD 
	CONSTRAINT [PK_操作说明表] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[教师改卷题型表] WITH NOCHECK ADD 
	CONSTRAINT [PK_教师改卷题型表] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[考点信息表] WITH NOCHECK ADD 
	CONSTRAINT [PK_考点信息表] PRIMARY KEY  CLUSTERED 
	(
		[考点代码]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[考生信息表] WITH NOCHECK ADD 
	CONSTRAINT [PK_考生信息表] PRIMARY KEY  CLUSTERED 
	(
		[学号]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[考生考试表] WITH NOCHECK ADD 
	CONSTRAINT [PK_考生考试表] PRIMARY KEY  CLUSTERED 
	(
		[学号],
		[课程号]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[考试配置表] WITH NOCHECK ADD 
	CONSTRAINT [PK_考试配置表] PRIMARY KEY  CLUSTERED 
	(
		[课程号]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[试卷配置表] WITH NOCHECK ADD 
	CONSTRAINT [PK_试卷配置表] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[课程代码表] WITH NOCHECK ADD 
	CONSTRAINT [PK_课程代码表] PRIMARY KEY  CLUSTERED 
	(
		[课程号]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[题型类别表] WITH NOCHECK ADD 
	CONSTRAINT [PK_题型类别表] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[任课教师表] WITH NOCHECK ADD 
	CONSTRAINT [PK_任课教师表] PRIMARY KEY  CLUSTERED 
	(
		[教师编号]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[知识点表] WITH NOCHECK ADD 
	CONSTRAINT [PK_知识点类别表] PRIMARY KEY  CLUSTERED 
	(
		[课程号],
		[知识点]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[答案附件表] WITH NOCHECK ADD 
	CONSTRAINT [PK_答案附件表] PRIMARY KEY  CLUSTERED 
	(
		[课程号],
		[学号],
		[答案附件文件名]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[考场信息表] WITH NOCHECK ADD 
	CONSTRAINT [PK_考场信息表] PRIMARY KEY  CLUSTERED 
	(
		[考点代码],
		[考场代码]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[考生试卷表] WITH NOCHECK ADD 
	CONSTRAINT [PK_考生试卷表] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[题型表] WITH NOCHECK ADD 
	CONSTRAINT [PK_题型表] PRIMARY KEY  CLUSTERED 
	(
		[课程号],
		[题型]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[题库表] WITH NOCHECK ADD 
	CONSTRAINT [PK_题库表] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[备选答案表] WITH NOCHECK ADD 
	CONSTRAINT [PK_备选答案] PRIMARY KEY  CLUSTERED 
	(
		[PID],
		[备选项]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[题库附件表] WITH NOCHECK ADD 
	CONSTRAINT [PK_题库附件表] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[改卷程序表] WITH NOCHECK ADD 
	CONSTRAINT [PK_改卷程序表] PRIMARY KEY  CLUSTERED 
	(
		[PID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[操作说明表] ADD 
	CONSTRAINT [DF_操作说明表_id] DEFAULT (1) FOR [ID]
GO

ALTER TABLE [dbo].[教师改卷题型表] ADD 
	CONSTRAINT [DF_教师改卷题号表_是否上传] DEFAULT (0) FOR [是否上传],
	CONSTRAINT [IX_教师改卷题型表] UNIQUE  NONCLUSTERED 
	(
		[教师编号],
		[课程号],
		[题型],
		[学号]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[考点信息表] ADD 
	CONSTRAINT [DF_考点信息表_考点代码] DEFAULT ('01') FOR [考点代码]
GO

ALTER TABLE [dbo].[考生信息表] ADD 
	CONSTRAINT [DF_考生信息表_班级] DEFAULT ('默认班级') FOR [班级],
	CONSTRAINT [DF_考生信息表_所属考点] DEFAULT (1) FOR [所属考点]
GO

ALTER TABLE [dbo].[考生考试表] ADD 
	CONSTRAINT [DF_考生考试表_考试状态] DEFAULT ('尚未开考') FOR [考试状态],
	CONSTRAINT [DF_考生考试表_试卷生成时间] DEFAULT (getdate()) FOR [试卷生成时间]
GO

ALTER TABLE [dbo].[考试配置表] ADD 
	CONSTRAINT [DF_考试配置表_现场给分] DEFAULT ('否') FOR [现场给分],
	CONSTRAINT [DF_考试配置表_是否启用] DEFAULT ('否') FOR [是否启用],
	CONSTRAINT [DF_考试配置表_开放模拟] DEFAULT ('否') FOR [开放模拟],
	CONSTRAINT [DF_考试配置表_是否验证考生] DEFAULT ('是') FOR [是否验证学号],
	CONSTRAINT [DF_考试配置表_是否验证] DEFAULT ('是') FOR [是否验证准考证],
	CONSTRAINT [DF_考试配置表_考号位数] DEFAULT (0) FOR [考号位数],
	CONSTRAINT [CK_考试配置表] CHECK ([是否启用] = '否' or [是否启用] = '是'),
	CONSTRAINT [CK_考试配置表_1] CHECK ([开放模拟] = '否' or [开放模拟] = '是'),
	CONSTRAINT [CK_考试配置表_2] CHECK ([现场给分] = '否' or [现场给分] = '是')
GO

ALTER TABLE [dbo].[试卷配置表] ADD 
	CONSTRAINT [DF_试卷配置表_题量] DEFAULT (0) FOR [题量],
	CONSTRAINT [DF_试卷配置表_分数] DEFAULT (0) FOR [分数],
	CONSTRAINT [IX_试卷配置表] UNIQUE  NONCLUSTERED 
	(
		[课程号],
		[题型],
		[知识点],
		[难易度]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[题型类别表] ADD 
	CONSTRAINT [DF_题型类别表2_是否有标准答案] DEFAULT ('是') FOR [有标准答案],
	CONSTRAINT [IX_题型类别表] UNIQUE  NONCLUSTERED 
	(
		[题型类别]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[任课教师表] ADD 
	CONSTRAINT [DF_任课教师表_是否启用] DEFAULT ('否') FOR [是否启用]
GO

ALTER TABLE [dbo].[考生试卷表] ADD 
	CONSTRAINT [DF_考生试卷表_小题号] DEFAULT (0) FOR [小题号],
	CONSTRAINT [DF_考生试卷表_Action_Time] DEFAULT (getdate()) FOR [Action_Time],
	CONSTRAINT [IX_考生试卷表] UNIQUE  NONCLUSTERED 
	(
		[学号],
		[题目ID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_考生试卷表_1] UNIQUE  NONCLUSTERED 
	(
		[学号],
		[课程号],
		[题号],
		[小题号]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[教师课程表] ADD 
	CONSTRAINT [DF_教师课程表_是否上传] DEFAULT ('否') FOR [是否上传],