会议室预定系统数据库文件BookRoom

来源：互联网发布：php开源后台编辑：程序博客网时间：2024/04/26 08:13

本SQL脚本是本人毕业设计“会议室预定系统”的数据库文件，名为BookRoom.sql

DBMS：MySQL

/*==============================================================*/

/* DBMS name: MySQL 5.0 */

/* Created bookRoomon: 2009/03/15 19:55:29 */

/*==============================================================*/

/* Database: BookRoom */

/*==============================================================*/

create database if not exists BookRoom;

Use BookRoom;

drop table if exists tblEmployee;

drop table if exists tblRoom;

drop table if exists tblReserve;

/*==============================================================*/

/* Table: tblEmployee */

/*==============================================================*/

create table if not exists tblEmployee

(

empId int not null AUTO_INCREMENT comment 'primary key can not have a default value',

empCardNo varchar(20) default '' comment 'Employee ID can be leaded by 0, so we set it''s type varchar.',

empName varchar(100) default '',

empSex varchar(20) default '',

empWorkDateBegin date default NULL comment 'YYYY-MM-DD',

empTel varchar(100) default '' comment 'It can either be a mobile telephone number or a office telephone number or both.',

empEMail varchar(100) not null unique default '' comment 'this empty string equals the dbms own default value',

empPwd varchar(100) not null default '',

empRolevarchar(50) not null default '',

constraint empId primary key (empId)

)engine=InnoDB, charset=utf8, comment='Every person including the boss in the company is defined as employee';

create index idxEmpId on tblemployee(empId);

create index idxEmpEMail on tblemployee(empEMail);

/*==============================================================*/

/* Table: tblRoom */

/*==============================================================*/

create table if not exists tblRoom

(

roomId int not null AUTO_INCREMENT comment 'Generated by the database automatically.',

roomBuilding varchar(100) not null default '' comment 'When you reserve a meeting room, you may firstly choose a building.

And then choose one meeting room in the building.

Of course all the buildings and meeting rooms are listed for you.',

roomNo varchar(50) default '' comment 'Meeting rooms belong to every building are listed out for choosing.',

roomName varchar(100) not null default '',

roomAddr varchar(300) default '',

constraint roomId primary key (roomId)

)engine=InnoDB, charset=utf8, comment='There are meeting rooms, which names may be the same(eg:"The First Meeting Room';

/*create index idxRoomId on tblRoom(roomId);*/

/*==============================================================*/

/* Table: tblReserve */

/*==============================================================*/

create table if not exists tblReserve

(

rsrvId int not null AUTO_INCREMENT comment 'Generated by the database automatically.',

empId int not null default 0 comment 'the default value of foreign key is better',

roomId int not null default 0,

rsrvDateBegin date not null comment 'if it is not null, its default value should be null',

rsrvDateEnd date not null,

rsrvTimeBegin time not null,

rsrvTimeEnd time not null,

rsrvMtTitle varchar(100) default '',

rsrvDateTime datetime default NULL,

constraint rsrvId primary key (rsrvId),

constraint empId foreign key (empId) REFERENCES tblEmployee(empId) on delete cascade on update cascade,

constraint roomId foreign key (roomId) REFERENCES tblRoom(roomId) on delete cascade on update cascade

)engine=InnoDB, charset=utf8;

create index idxRsrvId on tblReserve(rsrvId);

create index idxEmpId on tblReserve(empId);

create index idxRoomId on tblReserve(roomId);

/*向员工表tblEmployee插入测试数据*/

insert into tblEmployee(empCardNo, empName, empSex, empWorkDateBegin,

empTel, empEMail, empPwd, empRole)

values('S200707M00001', 'admin', 'female', '2000-01-01',

'024001', 'admin@neusoft.com', '21232f297a57a5a743894a0e4a801fc3', 'admin');

insert into tblEmployee(empCardNo, empName, empSex, empWorkDateBegin,

empTel, empEMail, empPwd, empRole)

values('S200707M01395', 'xielj', 'male', '2009-07-01',

'13073548356', 'xielj@neusoft.com', 'eb530b803b2c31fea96cda98a8eb377d', 'user');

insert into tblEmployee(empCardNo, empName, empSex, empWorkDateBegin,

empTel, empEMail, empPwd, empRole)

values('S200707M00002', 'linice', 'male', '2009-07-01',

'13080719544', 'linice@neusoft.com', '4937ab52f35373e3391d5b98a4817258', 'user');

insert into tblEmployee(empCardNo, empName, empSex, empWorkDateBegin,

empTel, empEMail, empPwd, empRole)

values('S200707M01395', 'neusoft', 'female', '2000-07-01',

'024002', 'neusoft@neusoft.com', 'f7377865580a02f9a89533b31e4ca7b7', 'user');

/*向会议室表tblRoom插入测试数据*/

insert into tblRoom(roomBuilding, roomNo, roomName, roomAddr)

values('conference center', '101', 'linux', 'conference center');

insert into tblRoom(roomBuilding, roomNo, roomName, roomAddr)

values('conference center', '102', 'C#', 'conference center');

insert into tblRoom(roomBuilding, roomNo, roomName, roomAddr)

values('conference center', '201', 'sql', 'conference center');

insert into tblRoom(roomBuilding, roomNo, roomName, roomAddr)

values('A1', '101', 'java', 'A1');

insert into tblRoom(roomBuilding, roomNo, roomName, roomAddr)

values('A1', '102', 'C', 'A1');

insert into tblRoom(roomBuilding, roomNo, roomName, roomAddr)

values('A1', '103', 'C++', 'A1');

/*向预定表 tblReserve 插入测试数据*/

insert into tblReserve(empId, roomId, rsrvDateBegin, rsrvDateEnd,