chinagate2.sql

oracle的实例

DROP TABLE hotel CASCADE CONSTRAINTS;
DROP TABLE customer CASCADE CONSTRAINTS;
DROP TABLE reservation CASCADE CONSTRAINTS;
DROP TABLE registration CASCADE CONSTRAINTS;
DROP TABLE room CASCADE CONSTRAINTS;
DROP TABLE room_type CASCADE CONSTRAINTS;
DROP TABLE payroll CASCADE CONSTRAINTS;
DROP TABLE china_user CASCADE CONSTRAINTS;
--DROP TABLE china_group CASCADE CONSTRAINTS;


DROP SEQUENCE payroll_p_id_seq;
DROP SEQUENCE customer_c_id_seq;
DROP SEQUENCE reservation_res_id_seq;
DROP SEQUENCE registration_reg_id_seq;



CREATE TABLE hotel
(h_id NUMBER(4),
 h_name VARCHAR2(40),
 h_address VARCHAR2(40),
 h_city VARCHAR2(20),
 h_state CHAR(2),
 h_zip varchar2(10),
 h_phone VARCHAR2(10),
 CONSTRAINT hotel_h_id_pk PRIMARY KEY(h_id));

CREATE TABLE customer
( c_id NUMBER(6)
  CONSTRAINT customer_c_id_pk PRIMARY KEY,
  c_last VARCHAR2(30),
  c_first VARCHAR2(30),
  c_mi VARCHAR2(3),
  c_gender CHAR(1),
  c_phone VARCHAR2(10),
  c_arrival_date VARCHAR2(40),
  c_depart_date VARCHAR2(40),
  CONSTRAINT customer_c_gender_cc CHECK ((c_gender = 'M') OR (c_gender = 'F')));

CREATE TABLE room_type
(r_type_id NUMBER(2),
 r_type_description VARCHAR2(40),
 r_price NUMBER(7,2),
 CONSTRAINT room_type_type_id_pk PRIMARY KEY(r_type_id));


CREATE TABLE room
(h_id NUMBER(4),
 r_no VARCHAR2(4),
 r_type_id NUMBER(2),
 r_status NVARCHAR2(15),
 r_phone VARCHAR2(10),
 CONSTRAINT room_h_id_r_no_pk PRIMARY KEY (h_id, r_no),
 CONSTRAINT room_m_id_fk FOREIGN KEY (h_id) REFERENCES hotel (h_id),
 CONSTRAINT room_r_type_id_fk FOREIGN KEY (r_type_id) REFERENCES room_type(r_type_id),
 CONSTRAINT room_r_status_cc CHECK ((r_status ='empty') OR (r_status ='occupied') OR (r_status='reserved')),
 CONSTRAINT room_r_phone_uk UNIQUE (r_phone));

CREATE TABLE reservation
( res_id NUMBER(6),
  c_id NUMBER(6),
  h_id NUMBER(4),
  r_type_id NUMBER(2),
  r_no VARCHAR2(4),
--NULL, cancelled, registered
  res_status VARCHAR2(10),
  CONSTRAINT reservation_res_id_pk PRIMARY KEY (res_id),
  CONSTRAINT reservation_c_id_fk FOREIGN KEY (c_id) REFERENCES customer(c_id),  
  CONSTRAINT reservation_h_id_r_no_fk FOREIGN KEY (h_id,r_no) REFERENCES room(h_id,r_no),
  CONSTRAINT reservation_r_type_id_fk FOREIGN KEY (r_type_id) REFERENCES room_type(r_type_id),
  CONSTRAINT reservation_res_status_cc CHECK ((res_status = NULL) OR (res_status = 'cancelled') OR (res_status ='registered')));
 

CREATE TABLE registration
(reg_id NUMBER(6),
 c_id NUMBER(6),
 h_id NUMBER(4),
 r_type_id NUMBER(2),
 r_no VARCHAR2(4),
 reg_description VARCHAR2(40),
 CONSTRAINT registration_reg_id_pk PRIMARY KEY (reg_id),
 CONSTRAINT registration_c_id_fk FOREIGN KEY (c_id) REFERENCES customer(c_id),
 CONSTRAINT registration_r_type_id_fk FOREIGN KEY (r_type_id) REFERENCES room_type(r_type_id),  
 CONSTRAINT registration_h_id_r_no_fk FOREIGN KEY (h_id,r_no) REFERENCES room(h_id,r_no));

CREATE TABLE payroll
(p_id NUMBER(6),
 c_id NUMBER(6),
 h_id NUMBER(4),
 r_no VARCHAR2(4),
 --cc or CHECK or cash
 pay_meth VARCHAR(5),
 total_amount NUMBER(8,2),
 CONSTRAINT payroll_p_id_pk PRIMARY KEY (p_id),
 CONSTRAINT payroll_pay_meth_cc CHECK ((pay_meth = 'CC') OR (pay_meth = 'CHECK') OR (pay_meth = 'CASH')),
 CONSTRAINT payroll_c_id_fk FOREIGN KEY (c_id) REFERENCES customer(c_id),  
 CONSTRAINT payroll_h_id_r_no_fk FOREIGN KEY (h_id,r_no) REFERENCES room(h_id,r_no));

--CREATE TABLE china_group
--(g_id NUMBER(4),
-- g_name VARCHAR2(15),
-- CONSTRAINT china_group_g_id_pk PRIMARY KEY(g_id));


CREATE TABLE china_user
(u_id NUMBER(4),
 u_name VARCHAR2(15),
 u_password CHAR(6),
-- g_id NUMBER(4),
 CONSTRAINT china_user_u_name_uk UNIQUE (u_name),
 CONSTRAINT china_user_u_id_pk PRIMARY KEY(u_id));
-- CONSTRAINT chian_user_g_id_fk FOREIGN KEY(g_id) REFERENCES china_group(g_id));
 

--insert value
INSERT INTO hotel VALUES (1,'George Town', '1034 ELM AVE ', 'Americus', 'GA', '31709','2299314212');


INSERT INTO room_type VALUES (1,'studio',50.00);
INSERT INTO room_type VALUES (2,'twin',99.00);
INSERT INTO room_type VALUES (3,'triple',130.00);
INSERT INTO room_type VALUES (4,'double twin',175.00);



INSERT INTO room VALUES (1,'A01',1,'occupied','2299311501');
INSERT INTO room VALUES (1,'A02',1,'empty','2299311502');
INSERT INTO room VALUES (1,'A03',1,'empty','2299311503');
INSERT INTO room VALUES (1,'A04',1,'empty','2299311504');
INSERT INTO room VALUES (1,'A05',1,'empty','2299311505');
INSERT INTO room VALUES (1,'A06',2,'empty','2299311506');
INSERT INTO room VALUES (1,'A07',2,'empty','2299311507');
INSERT INTO room VALUES (1,'A08',2,'empty','2299311508');
INSERT INTO room VALUES (1,'A09',2,'empty','2299311509');
INSERT INTO room VALUES (1,'A10',2,'empty','2299311510');
INSERT INTO room VALUES (1,'B01',3,'empty','2299311511');
INSERT INTO room VALUES (1,'B02',3,'empty','2299311512');
INSERT INTO room VALUES (1,'B03',3,'occupied','2299311513');
INSERT INTO room VALUES (1,'B04',3,'empty','2299311514');
INSERT INTO room VALUES (1,'B05',3,'empty','2299311515');
INSERT INTO room VALUES (1,'B06',4,'empty','2299311516');
INSERT INTO room VALUES (1,'B07',4,'empty','2299311517');
INSERT INTO room VALUES (1,'B08',4,'empty','2299311518');
INSERT INTO room VALUES (1,'B09',4,'empty','2299311519');
INSERT INTO room VALUES (1,'B10',4,'empty','2299311520');
INSERT INTO room VALUES (1,'B11',4,'empty','2299311521');



INSERT INTO customer VALUES (1,'Jone','Tammy','R','F','2298153345','11-09-07 20:44:01','15-09-07 11:50:45');
INSERT INTO customer VALUES (2, 'Perez','Jorge','C','M','2298157789','13-09-07 13:08:34',NULL);
INSERT INTO customer VALUES (3,'Lewis','Sheila','A','F','2298152787','14-09-07 14:12:31',NULL);
INSERT INTO customer VALUES (4,'Johnnson','Lisa','M','F','2299313437',NULL,NULL);
INSERT INTO customer VALUES (5,'Brown','Jonnel','J','M','2298156767','14-09-07 16:11:01','15-09-07 9:30:45');




INSERT INTO reservation VALUES (1,2,1,1,'A01','registered');
INSERT INTO reservation VALUES (2,4,1,4,'B11','cancelled');



INSERT INTO registration VALUES (1,1,1,2,'A10','two adults');
INSERT INTO registration VALUES (2,2,1,1,'A01','one adult');
INSERT INTO registration VALUES (3,3,1,3,'B03','three adults');
INSERT INTO registration VALUES (4,5,1,1,'A04','one adult');




INSERT INTO payroll VALUES (1,1,1,'A10','CHECK',396.00);
INSERT INTO payroll VALUES (2,5,1,'A04','CC',50.00);



--INSERT INTO china_group VALUES (1,'manager');
--INSERT INTO china_group VALUES (2, 'accountant');
--INSERT INTO china_group VALUES (3, 'clerk');


INSERT INTO china_user VALUES (1,'clerk','298437');
INSERT INTO china_user VALUES (2,'accountant','987783');
INSERT INTO china_user VALUES (3,'manager','435540');


CREATE SEQUENCE payroll_p_id_seq
START WITH 3;

CREATE SEQUENCE customer_c_id_seq
START WITH 6;

CREATE SEQUENCE reservation_res_id_seq
START WITH 3;


CREATE SEQUENCE registration_reg_id_seq
START WITH 5;

COMMIT;