sql.txt

一个KTV管理系统

消费类型信息表创建
KTV_CONSUMED_TYPE_INFO
create table KTV_CONSUMED_TYPE_INFO
(
  CONSUMED_TYPE_NAME VARCHAR2(16) not null,

  REBATE             NUMBER(3,2)
)
alter table KTV_CONSUMED_TYPE_INFO
  add constraint KTV_CONSUMED_TYPE_INFO primary key (CONSUMED_TYPE_NAME)
alter table KTV_GOODS_CONSUMED_INFO
  add constraint KTV_FOR_GOODS_CONSUMED_INFO foreign key (CONSUMED_ID)
  references KTV_GUEST_CONSUMED_INFO (CONSUMED_ID);

商品消费信息表创建 
KTV_GOODS_CONSUMED_INFO
create table KTV_GOODS_CONSUMED_INFO
(
  CONSUMED_ID     NUMBER(10),
  GOODS_NAME      VARCHAR2(1) not null,
  CONSUMED_NUMBER NUMBER(2),
  CONSUMED_MONEY  NUMBER(6,2),
  CONSUMED_TIME   DATE not null
)
alter table KTV_GOODS_CONSUMED_INFO
  add constraint KTV_PRI_GOODS_CONSUMED_INFO primary key (GOODS_NAME,CONSUMED_TIME)


KTV_GUEST_CONSUMED_INFO
宾客消费信息表创建
create table KTV_GUEST_CONSUMED_INFO
(
  CONSUMED_ID          NUMBER(10) ,
  GUEST_NAME           VARCHAR2(8),
  ROOM_NUMBER          CHAR(5),
  GUEST_ID             NUMBER(18),
  GUEST_CHECK_IN_TIME  DATE,
  GUEST_CHECK_OUT_TIME DATE,
  CONSUMED_TIME        DATE,
  GOODS_CONSUMED_MONEY NUMBER(7,2),
  ROOM_MONEY           NUMBER(5),
  CONSUMED_TYPE_NAME   VARCHAR2(16),
  REBATE_DAY           VARCHAR2(10),
  DEPOSIT              NUMBER(5),
  FEE                  NUMBER(7,2),
  REBATE	       NUMBER(3,2)
)
alter table KTV_GUEST_CONSUMED_INFO
  add constraint KTV_PRI_GUEST_CONSUMED_INFO primary key (CONSUMED_ID)

alter table KTV_GUEST_CONSUMED_INFO
  add constraint KTV_FOR_GUEST_CONSUMED_INFO foreign key (ROOM_NUMBER)
  references KTV_ROOM_INFO (ROOM_NUMBER);

KTV_RESERVATION_INFO
预定信息表创建
create table KTV_RESERVATION_INFO
(
  GUEST_NAME       VARCHAR2(8),
  GUEST_TELEPHONE  NUMBER(12),
  ROOM_NUMBER      CHAR(5) not null,
  ROOM_TYPE        CHAR(8),
  RESERVATION_TIME DATE,
  RESERVATION_TYPE CHAR(2)
)

alter table KTV_RESERVATION_INFO
  add constraint KTV_PRI_RESERVATION_INFO primary key (RESERVATION_TIME)

KTV_OPERATOR_INFO
管理员基本信息表创建
create table KTV_OPERATOR_INFO
(
  OPERATOR_ID    NUMBER(2) not null,
  OPERATOR_NAME  VARCHAR2(8),
  OPERATOR_LEVEL VARCHAR2(10),
  PASSWORD       VARCHAR2(10)
)

alter table KTV_OPERATOR_INFO
  add constraint KTV_PRI_OPERATOR_INFO primary key (OPERATOR_ID)

KTV_GOODS_INFO
商品基本信息表创建
create table KTV_GOODS_INFO
(
  GOODS_NAME         VARCHAR2(12) not null,
  GOODS_UNIT         CHAR(2),
  GOODS_COST_PRICE   NUMBER(6,2),
  GOODS_SELL_PRICE   NUMBER(6,2),
  GOODS_STOCK        NUMBER(3),
  GOODS_ALERT        NUMBER(3),
  GOODS_ABBREVIATION VARCHAR2(6)
)

alter table KTV_GOODS_INFO
  add constraint KTV_PRI_GOODS_INFO primary key (GOODS_NAME)

KTV_ROOM_INFO
包间基本信息表创建
create table KTV_ROOM_INFO
(
  ROOM_NUMBER    CHAR(5) not null,
  ROOM_TYPE      CHAR(8),
  ROOM_PRICE     NUMBER(4),
  ROOM_NAME      VARCHAR2(10),
  ROOM_STATEMENT CHAR(4),
  ROOM_CAPACITY  NUMBER(3),
  CONSUMED_ID    NUMBER(10)
)

alter table KTV_ROOM_INFO
  add constraint KTV_PRI_ROOM_INFO primary key (ROOM_NUMBER)


服务生信息表
create table KTV_waiter(waiter_id number(5),waiter_name varchar(15),waiter_sex varchar(6),photo varchar(15),area varchar(15),level varchar(15));