mobile.sql

开发的一个java web的任务管理系统。

set names gbk;/*从控制台输入则需要设置字符集*/

create database unicom;/*建立数据库mobile*/

use unicom;/*选择数据库*/

/*建立TOperator表*/
create table TOperator
(
	id int(5) AUTO_INCREMENT primary key ,/*操作员id*/
  	name varchar(20),/*操作员名字*/
  	pwd varchar(20),
  	isAdmin char(1)  /*Y:具有管理其他管理员权限的管理员 N:一般管理员*/
);
/*如果在控制台插入或取中文字符需要执行:set names GBK;如果在Navicat中插入则不需要*/
/*插入TOperator表样本数据*/
insert into TOperator values(null,'aa','aa','Y');
insert into TOperator values(null,'bb','bb','N');

/*建立TMobiles表*/
create table TMobile
(
  	number varchar(12) primary key,
  	type char(3) not null,
  	cardNumber varchar(13),
  	isAvailable char(1) default 'Y' 
);
/*插入TMobiles表样本数据*/
insert into tmobile(number,type,isAvailable) values('13300000003','SIM','Y'); 

/*建立TCharge表:费用细项*/
create table TCharge
(
	code char(1) primary key,/*A:开户费,B:漫游费,C:押金,D:入网费,E:选号费*/
	name varchar(10),/*费用名称*/
	charge double /*业务费用*/
);

/*插入TCharge表样本数据*/
insert into TCharge(code,name,charge) values('A','开户费',10.00);
insert into TCharge(code,name,charge) values('B','漫游费',5.00);
insert into TCharge(code,name,charge) values('C','押金',100.00);
insert into TCharge(code,name,charge) values('D','入网费',50.00);
insert into TCharge(code,name,charge) values('E','选号费',10.00);

/*建立TBusinesse表(业务表)*/
create table TBusiness
(
	id char(1), /*用于功能的唯一标识，目前只需要有表示开户功能的'O'*/
	name varchar(20),/*功能名称*/
	primary key(id)
);

/*插入TBusiness的样本数据*/
insert into TBusiness(id,name) values('O','开户业务');


/*建立TBusinessRule表*/
create table TBusinessRule
(
  id char(1),
  code char(1),
  primary key(id,code),
  foreign key(id) references TBusiness(id) on delete cascade on update cascade,
  foreign key(code) references TCharge(code) on delete cascade on update cascade
);

/*插入样本数据*/
insert into TBusinessRule(id,code) values('O','A');
insert into TBusinessRule(id,code) values('O','E');
insert into TBusinessRule(id,code) values('O','C');


/*新建Tcustomer表,对应实体的人*/
create table TCustomer
(
  id int(5) auto_increment primary key,
  type char(1),/*客户的证件类型，目前只考虑身份证(D)、军官证(A)、护照(P)三种类型*/
  number varchar(20) unique,/*身份证件号码:唯一的*/
  name varchar(20),/*客户名称*/
  birthday Date,/*客户生日*/
  sex char(2),/*客户性别*/
  address varchar(50)/*客户联系地址*/
);

/*插入TCustomer表的样本数据*/
insert into TCustomer values(null,'D','0000','xiaogang','1984-10-22','M','四川万林射红');
/*新建TAccount表*/
create table TAccount
(
  id int(5) primary key,
  person varchar(20),/*帐号对应的联系人姓名*/
  address varchar(50),/*帐号对应的联系人地址*/
  balance double /*帐户中的余额*/
);

/*新建TUser表*/
create table TUser
(
  id int(5) auto_increment primary key,
  number varchar(12),
  status char(1),/*漫游状态，分为省内漫游('P')、国内漫游('D')和国际漫游('T')三种*/
  level char(1),/*通话级别,本地通话('L'),国内长途('D'),国际长途('I')*/
  customer_Id int(5),
  account_Id int(5),
  foreign key(customer_Id) references TCustomer(id) on delete cascade on update cascade,
  foreign key(account_Id) references TAccount(id) on delete cascade on update cascade
);

/*新建视图v_TMobile_TUser*/
create view v_TMobile_TUser
as
select TMobile.number as number 
from TMobile,TUser
where TMobile.number = TUser.number and TUser.account_Id is null;

/*创建视图v_TCharge_TBusiness*/
create view v_TCharge_TBusinessRule
as
select TBusinessRule.id,TCharge.charge
from TBusinessRule,TCharge
where TCharge.code = TBusinessRule.code;