LOANSYS数据库部分

现将系统分为四个模块，

1. user模块

    用户信息表user(ID，username，password，sex，userid，department，professional，testnumber，idnumber，telphone，nation，grade，accesstime，foundtime，islogging)  

 

2. home模块

    家庭信息表home（ID ，studentid ，homeaddress , homepost , fathername , fatherid, fatheraddr, fatherphone , mothername , motherid, motheraddr, motherphone ,foundtime）

 

3. offschool模块

    离校信息表offschool（ID ，studentid，companyname，companyaddr，companypost，companyphone，foundtime ，foundtime）

 

4. contract模块

    合同信息表contract（ID ，studentid ，contractid，bankname ， branchname， approvetime, hander ， begintime ，deadline ，schoolyear ，teachfee ，lifefee ，dormfee ）

 

DROP DATABASE IF EXISTS LOANSYS;

CREATE DATABASE LOANSYS DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 
USE LOANSYS;

CREATE TABLE user(
	ID varchar(20) NOT NULL,
	username varchar(20),
	password varchar(30) NOT NULL default 'dg123456',
	sex varchar(4),
	userid INT(1) NOT NULL DEFAULT '1',
	department varchar(20),
	professional varchar(50),
	testnumber varchar(20),
	idnumber varchar(18),
	telephone varchar(20),
	bankcard varchar(20),
	nation varchar(10) default '汉族',
	grade varchar(10),
	accesstime int(10),
	foundtime datetime default '0000-00-00 00:00:00',
	islogging char(5) NOT NULL DEFAULT 'FALSE',
	constraint CHK_USERID CHECK (userid in('0','1')),
	constraint CHK_ISLOGGING CHECK (islogging in('TRUE','FALSE')),
	constraint PK_USER PRIMARY KEY(ID)
	)ENGINE=InnoDB DEFAULT CHARSET=gb2312;
	
CREATE TABLE home(
	ID int(10) not null auto_increment,
	studentid varchar(20),
	homeaddress varchar(50),
	homepost char(6),
	fathername varchar(10),
	fatherid varchar(18),
	fatheraddr varchar(30),
	fatherphone varchar(20),
	mothername varchar(10),
	motherid varchar(18),
	motheraddr varchar(30),
	motherphone	varchar(20),
	foundtime datetime default '0000-00-00 00:00:00',
	CONSTRAINT PK_HOME PRIMARY KEY (ID),
	INDEX(studentid),
	constraint FK_USER_HOME FOREIGN KEY(studentid) REFERENCES user(ID)
	ON DELETE CASCADE
	ON UPDATE CASCADE
	)ENGINE=InnoDB DEFAULT CHARSET=gb2312;
	
CREATE TABLE offschool(
	ID int(10) not null auto_increment,
	studentid varchar(20),
	companyname varchar(30),
	companyaddr varchar(50),
	companypost char(6),
	companyphone varchar(20),
	foundtime datetime default '0000-00-00 00:00:00',
	CONSTRAINT PK_OFFSCHOOL PRIMARY KEY (ID),
	INDEX(studentid),
	constraint FK_USER_OFFSCHOOL FOREIGN KEY(studentid) REFERENCES user(ID)
	ON DELETE CASCADE
	ON UPDATE CASCADE
	)ENGINE=InnoDB DEFAULT CHARSET=gb2312;
	
CREATE TABLE contract(
	ID int(10) not null auto_increment,
	studentid varchar(20),
	contractid varchar(30),
	bankname varchar(50),
	branchname varchar(50),
	approvetime varchar(20),
	hander varchar(10),
	begintime varchar(20),
	deadline varchar(20),
	schoolyear varchar(20),
	teachfee decimal(6,2),
	lifefee decimal(6,2),
	dormfee decimal(6,2),
	CONSTRAINT PK_CONTRACT PRIMARY KEY (ID),
	INDEX(studentid),
	constraint FK_USER_CONTRACT FOREIGN KEY(studentid) REFERENCES user(ID)
	ON DELETE CASCADE
	ON UPDATE CASCADE
	)ENGINE=InnoDB DEFAULT CHARSET=gb2312;