create_db_template.sql

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

	CONSTRAINT [IX_教师课程表] UNIQUE  NONCLUSTERED 
	(
		[教师编号],
		[课程号],
		[任课班级]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[题库表] ADD 
	CONSTRAINT [DF_题库表_Action_Time] DEFAULT (getdate()) FOR [Action_Time]
GO

ALTER TABLE [dbo].[题库附件表] ADD 
	CONSTRAINT [DF_题库附件表_试题正文] DEFAULT (1) FOR [是否回收],
	CONSTRAINT [DF_题库附件表_Action_Time] DEFAULT (getdate()) FOR [Action_Time]
GO

ALTER TABLE [dbo].[改卷程序表] ADD 
	CONSTRAINT [DF_改卷程序表_Action_Time] DEFAULT (getdate()) FOR [Action_Time]
GO

ALTER TABLE [dbo].[任课教师表] ADD 
	CONSTRAINT [FK_任课教师表_考点信息表] FOREIGN KEY 
	(
		[所属考点]
	) REFERENCES [dbo].[考点信息表] (
		[考点代码]
	) ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[知识点表] ADD 
	CONSTRAINT [FK_知识点表_课程代码表] FOREIGN KEY 
	(
		[课程号]
	) REFERENCES [dbo].[课程代码表] (
		[课程号]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[答案附件表] ADD 
	CONSTRAINT [FK_答案附件表_考生考试表] FOREIGN KEY 
	(
		[学号],
		[课程号]
	) REFERENCES [dbo].[考生考试表] (
		[学号],
		[课程号]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[考场信息表] ADD 
	CONSTRAINT [FK_考场信息表_考点信息表] FOREIGN KEY 
	(
		[考点代码]
	) REFERENCES [dbo].[考点信息表] (
		[考点代码]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[考生试卷表] ADD 
	CONSTRAINT [FK_考生试卷表_考生考试表] FOREIGN KEY 
	(
		[学号],
		[课程号]
	) REFERENCES [dbo].[考生考试表] (
		[学号],
		[课程号]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[题型表] ADD 
	CONSTRAINT [FK_题型表_课程代码表] FOREIGN KEY 
	(
		[课程号]
	) REFERENCES [dbo].[课程代码表] (
		[课程号]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[教师课程表] ADD 
	CONSTRAINT [FK_教师课程表_课程代码表] FOREIGN KEY 
	(
		[课程号]
	) REFERENCES [dbo].[课程代码表] (
		[课程号]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_教师课程表_任课教师表] FOREIGN KEY 
	(
		[教师编号]
	) REFERENCES [dbo].[任课教师表] (
		[教师编号]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[题库表] ADD 
	CONSTRAINT [FK_题库表_题库表] FOREIGN KEY 
	(
		[PID]
	) REFERENCES [dbo].[题库表] (
		[ID]
	),
	CONSTRAINT [FK_题库表_题型表] FOREIGN KEY 
	(
		[课程号],
		[题型]
	) REFERENCES [dbo].[题型表] (
		[课程号],
		[题型]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[备选答案表] ADD 
	CONSTRAINT [FK_备选答案表_题库表] FOREIGN KEY 
	(
		[PID]
	) REFERENCES [dbo].[题库表] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[题库附件表] ADD 
	CONSTRAINT [FK_题库附件表_题库表] FOREIGN KEY 
	(
		[PID]
	) REFERENCES [dbo].[题库表] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[改卷程序表] ADD 
	CONSTRAINT [FK_改卷程序表_题库附件表] FOREIGN KEY 
	(
		[PID]
	) REFERENCES [dbo].[题库附件表] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_已考完考生表    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_已考完考生表
AS
SELECT xs.学号, xs.姓名, ks.课程号, ks.考试状态, ks.考生机器名, ks.考生IP, ks.开考时间, 
      ks.交卷时间
FROM dbo.考生考试表 ks INNER JOIN
      dbo.考生信息表 xs ON ks.学号 = xs.学号
WHERE (ks.考试状态 IN ('考完交卷', '舞弊'))


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_未开考考生表    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_未开考考生表
AS
SELECT 学号, 姓名, 性别, 班级, 所属考点
FROM dbo.考生信息表 xs
WHERE (学号 NOT IN
          (SELECT 学号
         FROM 考生考试表))


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_正在考试考生表    脚本日期: 2008-12-24 19:42:45 ******/
CREATE VIEW dbo.VIEW_正在考试考生表
AS
SELECT xs.学号, xs.姓名, ks.课程号, ks.考试状态, ks.考生机器名, ks.考生IP, ks.开考时间, 
      ks.交卷时间
FROM dbo.考生信息表 xs INNER JOIN
      dbo.考生考试表 ks ON ks.学号 = xs.学号
WHERE (ks.考试状态 IN ('正在考试', '抽题重考', '换机继考', '补时继考'))

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_考生考试表    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_考生考试表
AS
SELECT dbo.考生考试表.ID, dbo.考生考试表.学号, dbo.考生信息表.姓名, 
      dbo.考生信息表.班级, dbo.考生考试表.课程号, dbo.考生考试表.开考时间, 
      dbo.考生考试表.交卷时间, dbo.考生考试表.考试时长, dbo.考生考试表.成绩, 
      dbo.考生考试表.考试状态, dbo.考生考试表.考生机器名, dbo.考生考试表.考生IP, 
      dbo.考生考试表.试卷生成时间, dbo.考生信息表.所属考点
FROM dbo.考生考试表 INNER JOIN
      dbo.考生信息表 ON dbo.考生考试表.学号 = dbo.考生信息表.学号

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_舞弊考生表    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_舞弊考生表
AS
SELECT xs.学号, xs.姓名, ks.课程号, ks.考试状态, ks.考生机器名, ks.考生IP, ks.开考时间, 
      ks.交卷时间
FROM dbo.考生信息表 xs INNER JOIN
      dbo.考生考试表 ks ON ks.学号 = xs.学号
WHERE (ks.考试状态 = '舞弊')


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_题型信息    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_题型信息
AS
SELECT dbo.题型表.ID, dbo.题型表.课程号, dbo.题型表.题型, dbo.题型表.题型类别, 
      dbo.题型类别表.有标准答案, dbo.题型类别表.id AS 题型类别ID
FROM dbo.题型表 INNER JOIN
      dbo.题型类别表 ON dbo.题型表.题型类别 = dbo.题型类别表.题型类别


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_题型知识点难易度题量表    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_题型知识点难易度题量表
AS
SELECT a.课程号, b.题型, a.知识点, a.难易度, COUNT(a.课程号) AS 现有题量
FROM dbo.题库表 a RIGHT OUTER JOIN
      dbo.题型表 b ON a.课程号 = b.课程号 AND b.题型 = a.题型
WHERE (a.pid IS NULL)
GROUP BY a.课程号, b.题型, a.知识点, a.难易度


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_主观题成绩表    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_主观题成绩表
AS
SELECT dbo.考生试卷表.课程号, dbo.考生试卷表.学号, SUM(dbo.考生试卷表.得分) 
      AS 成绩
FROM dbo.考生试卷表 INNER JOIN
      dbo.题库表 ON dbo.考生试卷表.题目ID = dbo.题库表.ID INNER JOIN
      dbo.[VIEW_题型信息] ON dbo.题库表.课程号 = dbo.[VIEW_题型信息].课程号 AND 
      dbo.题库表.题型 = dbo.[VIEW_题型信息].题型
WHERE (dbo.[VIEW_题型信息].题型类别 = '主观题')
GROUP BY dbo.考生试卷表.学号, dbo.考生试卷表.课程号


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_客观题成绩表    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_客观题成绩表
AS
SELECT dbo.考生试卷表.课程号, dbo.考生试卷表.学号, SUM(dbo.考生试卷表.得分) 
      AS 成绩
FROM dbo.考生试卷表 INNER JOIN
      dbo.题库表 ON dbo.考生试卷表.题目ID = dbo.题库表.ID INNER JOIN
      dbo.VIEW_题型信息 ON dbo.题库表.课程号 = dbo.VIEW_题型信息.课程号 AND 
      dbo.题库表.题型 = dbo.VIEW_题型信息.题型
WHERE (dbo.VIEW_题型信息.题型类别 <> '主观题')
GROUP BY dbo.考生试卷表.学号, dbo.考生试卷表.课程号


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象: 视图 dbo.VIEW_教师任课信息    脚本日期: 2008-12-24 19:42:45 ******/

CREATE VIEW dbo.VIEW_教师任课信息
AS
SELECT dbo.教师课程表.教师编号, dbo.任课教师表.教师姓名, dbo.教师课程表.任课班级, 
      dbo.任课教师表.密码, dbo.任课教师表.所属考点, dbo.课程代码表.课程号, 
      dbo.课程代码表.课程名, dbo.任课教师表.是否启用
FROM dbo.教师课程表 INNER JOIN
      dbo.任课教师表 ON dbo.教师课程表.教师编号 = dbo.任课教师表.教师编号 INNER JOIN
      dbo.课程代码表 ON dbo.教师课程表.课程号 = dbo.课程代码表.课程号


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/****** 对象: 存储过程 dbo.sp_update_ks_state    脚本日期: 2008-12-24 19:42:45 ******/

CREATE PROCEDURE [dbo].[sp_update_ks_state] AS
UPDATE 考生考试表 SET 考试状态='考完交卷', 交卷时间=DATEADD(minute, 考试时长, 开考时间) WHERE 考试状态 not in ('补时继考','考完交卷') AND (DATEDIFF(minute,开考时间, GETDATE())>=考试时长)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/****** 对象: 存储过程 dbo.sp_copy_tmp_sjszb    脚本日期: 2008-12-24 19:42:45 ******/


CREATE PROCEDURE dbo.sp_copy_tmp_sjszb AS

  delete from tmp_试卷配置表
  
  SELECT a.题型,a.知识点,(select count(*) from 题库表 where 题型=a.题型 and 知识点=a.知识点) as 现有题量,
  b.题量 as 题量,b.分数 as 分数,b.题号 as 题号
  FROM 知识点类别表 a left join 试卷配置表 b on b.题型=a.题型 and b.知识点=a.知识点
  inner join 题型类别表 c on c.题型=a.题型 order by c.ID,a.ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


exec sp_addextendedproperty N'MS_Description', N'关联考生试卷表中的ID', N'user', N'dbo', N'table', N'答案附件表', N'column', N'PID'


GO