SQL 贷款毕业生信息管理系统数据库设计

USE MASTER
GO
--创建数据库文件存放目录
EXEC XP_CMDSHELL 'MKDIR D:\LOANSTUMIS'
IF EXISTS(SELECT *
from SYSDATABASES
WHERE NAME = 'LOANSTU')
DROP DATABASE LOANSTU
GO
--创建数据库
CREATE DATABASE LOANSTU
ON
(
NAME = 'LOANSTU_DATA',
FILENAME = 'D:\LOANSTUMIS\LOANSTU_DATA.MDF',
SIZE = 10MB,
FILEGROWTH = 1MB,
MAXSIZE = 100MB
)
LOG ON
(
NAME = 'LOANSTU_LOG',
FILENAME = 'D:\LOANSTUMIS\LOANSTU_LOG.LDF',
SIZE = 10MB,
FILEGROWTH = 1MB,
MAXSIZE = 100MB
)
GO
USE LOANSTU
GO
--返回影响的行数提示
SET NOCOUNT ON
--建表
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_ADMIN')
DROP DATABASE TB_ADMIN
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_DLRZ')
DROP DATABASE TB_DLRZ
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_ZZJG')
DROP DATABASE TB_ZZJG
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_ZXXX')
DROP DATABASE TB_ZXXX
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_JTXX')
DROP DATABASE TB_JTXX
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_BYQX')
DROP DATABASE TB_BYQX
IF EXISTS(SELECT *
from SYSOBJECTS
WHERE NAME = 'TB_HTXX')
DROP DATABASE TB_HTXX
GO
--创建管理员信息表
CREATE TABLE TB_ADMIN
(
ADMINID VARCHAR(15) PRIMARY KEY NOT NULL,
ADMINNAME VARCHAR(10) NOT NULL,
ADMINPASSWORD VARCHAR(20) DEFAULT('abc123') NOT NULL
)
--创建登陆日志表
CREATE TABLE TB_DLRZ
(
LOGID INT IDENTITY(1000000,1) PRIMARY KEY NOT NULL,
USERID VARCHAR(15) NOT NULL,
LOGTIME SMALLDATETIME DEFAULT(GETDATE()) NOT NULL,
IP VARCHAR(16)
)
--创建组织结构表（院系、专业、班级）
CREATE TABLE TB_ZZJG
(
ORGID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ORGNAME VARCHAR(30),
ORGPID INT
)
--创建学生信息表
CREATE TABLE TB_ZXXX
(
STUID VARCHAR(15) PRIMARY KEY NOT NULL,
STUNAME VARCHAR(10),
CLASSID INT NOT NULL,
STUPASSWORD VARCHAR(20) DEFAULT('123456') NOT NULL,
STUKH VARCHAR(20),
STUSEX CHAR(2) CHECK(STUSEX IN('男','女')),