SQL SERVER学校作业的一个文档

考试事务子系统    杜文锋负责部分模块

1．模块部分

2．数据表定义

/*---------------试卷库管理-------------------------------------*/

create database KSSW

go

use KSSW

create table DW /*部门信息*/

(

DW_ID int identity primary key,

dwName varchar(16) not null, /*单位名称*/

Tel char(12) not null, /*单位电话*/

AD varchar(20) null, /*单位地址*/

)

go

use KSSW

create table JS /*教师信息*/

(

JS_ID int identity primary key,

JsName char(4) not null, /*教师名*/

DW_ID int foreign key references DW(DW_ID), /*所属部门*/

Sex char(2) check(sex in ('男','女')) default('男'),

MbTel char(11) default('无') null, /*移动电话*/

)

go

use KSSW

create table KC /*课程信息*/

(

KC_ID int identity primary key,

KcName char(16) not null, /*课程名*/

Type char(10) default('计算机'), /*课程类别*/

Major char(10) not null, /*专业，选定试卷时用到*/

Credit tinyint default(3), /*学分*/

)

use KSSW

create table SJ  /*试卷信息*/

(

SJ_ID int IDENTITY primary key, /*试卷ID, 自增长*/

SjName CHAR(26) NOT NULL, /*试卷名称*/

fPath char(22) not null, /*存放路径*/

Type char (1) default 'A', /*试卷套别*/

KJKS char (1) default '0', /*是否开卷*/

SJYS tinyint not null,   /*试卷页数*/

CJRQ smalldatetime NOT NULL DEFAULT(GETDATE()), /*出卷日期*/

DW_ID int foreign key references DW(DW_ID)NOT NULL, /*承担单位ID*/

JS_ID int foreign key references JS(JS_ID)NOT NULL, /*出卷教师ID*/

KC_ID int foreign key references KC(KC_ID) NOT NULL,/*课程ID*/

XYZT char(1) check(XYZT in ('T','F')) default('F'), /*选用状态*/

LQBZ char(1) check(LQBZ in ('T','F')) default('F'), /*领取标志*/

SJSL tinyint default (0), /*试卷已有份数*/

SJFS tinyint default(100), /*试卷所需数量*/

)

use KSSW

create table SY /*速印信息*/

(

JS_ID int foreign key references JS(JS_ID) NOT NULL, /*速印教师*/

SJ_ID int foreign key references SJ(SJ_ID) NOT NULL, /*速印试卷*/

Time smalldatetime NOT NULL /*速印时间*/

)

use KSSW

create table LQ /*领取信息*/

(

JS_ID int foreign key references JS(JS_ID) NOT NULL, /*领取教师*/

SJ_ID int foreign key references SJ(SJ_ID) NOT NULL, /*领取试卷*/

Time smalldatetime NOT NULL /*速印时间*/

)

 go

3．数据表查询

USE KSSW

SELECT JS.JsName as 速印人,SY.Time as 速印时间,SJ.pName as 试卷名称,

       SJ.LQBZ as 领取情况

FROM SY, SJ, JS

WHERE SY.JS_ID = JS.JS_ID and SY.SJ_ID = SJ.SJ_ID

go

USE KSSW

SELECT KC.KcName as 课程名,SJ.SJFS as 所需数量,SJ.SJFS+20 as 速印数量

FROM KC,SJ

WHERE KC.KC_ID = SJ.KC_ID

go

USE KSSW

SELECT KC.KcName as 课程名,SJ.SJSL as 试卷份数,

SJ.SJYS as 试卷页数,SJ.SJYS/2 as 答题纸页数,SJ.SJYS/2 as 草稿纸页数

FROM KC,SJ

WHERE KC.KC_ID = SJ.KC_ID

Go

USE KSSW

SELECT KC.KcName as 课程名,SJ.SjName as 试卷名称,SJ.Type as 试卷套别

FROM KC,SJ

WHERE KC.KC_ID = SJ.KC_ID

Go