create table GUEST(
CPR  Integer  PRIMARY KEY,
NAME  Varchar(20)  NOT NULL,
EMAIL  Varchar(25)  NOT NULL,
ADDRESS  Varchar(25) NOT NULL,
TYPE  Varchar(15),
DATE_OF_PAYMENT  DATE,
GBILL_NO  Number(5),
FOREIGN KEY (GBILL_NO) REFERENCES BILL(BILL_NO)
  );

INSERT INTO GUEST VALUES(95673812, 'Kris Smith', 'ksmith@gmail.com', 'Miami, Florida, USA', 'cash', 16-09-2018,  02345);
INSERT INTO GUEST VALUES(87364713, 'Leroy Tyson', 'ltyson@gmail.com', 'London, Uk',  'credit', 21-03-2020, 28945);
INSERT INTO GUEST VALUES(96092478, 'Eduardo Ferreira', 'eferreira@gmail.com', 'Venice, Italy', 'credit', 3-02-2022,  32791);
INSERT INTO GUEST VALUES(01083129, 'Vikram Clay', 'vclay@gmail.com', 'Berlin, Germany', 'credit', 3-02-2022,  19028);
INSERT INTO GUEST VALUES(95948589, 'Mark Boone', 'mboone@gmail.com', 'Manama, Bahrain',  'credit', 24-03-2022,  00386);
INSERT INTO GUEST VALUES(89357592, 'Ali Mohammed', 'amohammed@gmail.com', 'Dubai, UAE',  'cash', 16-12-2021, 90279);
INSERT INTO GUEST VALUES(83747923, 'Ariadne Johns', 'ajohns@gmail.com', 'Chicago, USA', 'cash', 22-05-2020, 18902);
INSERT INTO GUEST VALUES(79348270, 'Minnie Stone', 'mstone@gmail.com', 'Barcelona, Spain',  'credit', 24-11-2021,  16738);
INSERT INTO GUEST VALUES(81028471, 'Simon Amos', 'samos@gmail.com', 'Mumbai, India', 'credit', 24-03-2022,  48302);
INSERT INTO GUEST VALUES(88190038, 'Issa Kim', 'inichols@gmail.com', 'Incheon, South Korea', 'credit', 19-08-2021, 70037);
INSERT INTO GUEST VALUES(91230085, 'Esther Sykes', 'esykes@gmail.com', 'Riyadh, Saudi Arabia', 'credit', 12-01-2022, 17429);
INSERT INTO GUEST VALUES(99038920, 'Tiana Barlow', 'tbarlow@gmail.com', 'Cannes, France', 'cash', 12-01-2022, 36820);




create table EMPLOYEE(
CPR  Integer  PRIMARY KEY,
ID   Number(5) UNIQUE,
NAME  Varchar(20)  NOT NULL,
EMAIL  Varchar(25)  NOT NULL,
ADDRESS  Varchar(25) NOT NULL
);

insert into EMPLOYEE values(19034782, 02354, 'Reem Patel', 'rpatel@gmail.com', 'Manama, Bahrain' );
insert into EMPLOYEE values(89018234, 06129, 'Yosef Compton', 'ycompton@gmail.com', 'Hamad Town, Bahrain' );
insert into EMPLOYEE values(67090812, 00435, 'Lennie Kline', 'lkline@gmail.com', 'Muharraq, Bahrain' );
insert into EMPLOYEE values(92333376, 10358, 'Nansi Amin', 'namin@gmail.com', 'Manama, Bahrain' );
insert into EMPLOYEE values(34984601, 21093, 'Safiyyah Mcdonnell', 'smcdonnel@gmail.com', 'Manama, Bahrain' );
insert into EMPLOYEE values(78209311, 92012, 'Hassan Ramsay', 'hramsay@gmail.com', 'Salman Town, Bahrain' );
insert into EMPLOYEE values(01928336, 00492, 'Rahim Haines', 'rhaines@gmail.com', 'Isa Town, Bahrain' );
insert into EMPLOYEE values(11093899, 02902, 'Anayah Villegas', 'avillegas@gmail.com', 'Muharraq, Bahrain' );
insert into EMPLOYEE values(44983337, 30928, 'Safia Wael', 'swael@gmail.com', 'Sitra, Bahrain');
insert into EMPLOYEE values(01234958, 75902, 'Saeed Noor', 'snoor@gmail.com', 'Manama, Bahrain');
insert into EMPLOYEE values(18090034, 11083, 'Hussain Mustafa', 'hmustafa@gmail.com', 'Isa Town, Bahrain' );
insert into EMPLOYEE values(55623348, 48920, 'Sanjeev Patel', 'spatel@gmail.com', 'Muharraq, Bahrain' );
insert into EMPLOYEE values(77223456, 19200, 'Hana Kouma', 'hkouma@gmail.com', 'Riffa, Bahrain');
insert into EMPLOYEE values(00921278, 38405, 'Jamaal Kadhim', 'jkadhim@gmail.com', 'Riffa, Bahrain' );
insert into EMPLOYEE values(88946839, 83927, 'Taylor Garza', 'tgarza@gmail.com', 'Sitra, Bahrain');

create table BILL(
BILL_NO  Number(5)  PRIMARY KEY,
TOTAL CHARGES  DOUBLE,
ECPR Integer,
FOREIGN KEY (ECPR) REFERENCES EMPLOYEE(CPR)

);

insert into BILL values (02345, 380.4, 19034782);
insert into BILL values (28945, 284.3, 67090812);
insert into BILL values( 32791, 219.3, 01928336);
insert into BILL values( 19028, 273.9, 67090812);
insert into BILL values ( 00386,  189.6, 34984601);
insert into BILL values(90279, 242.4, 19034782);
insert into BILL values(18902, 189.4, 89018234);
insert into BILL values(16738, 169.7, 19034782);
insert into BILL values(48302, 189.3, 92333376);
insert into BILL values(70037, 286.3, 89018234);
insert into BILL values(17429, 234.6, 19034782);
insert into BILL values(36820, 155.5, 67090812);



create table ROOMS (  
FLOOR_ROOM_NO  Number(3)  PRIMARY KEY,
ROOM_TYPE    Varchar(20),
ROOM_CHARGE_PNIGHT  Double,
ROOM_STATUS    Varchar(11),
CHECKIN_D  Date,
CHECKOUT_D  Date,
RESERVATION_NO  Integer,
GCPR Integer ,
 foreign key (GCPR) REFERENCES GUEST(CPR) 
); 



insert into ROOMS values (212, 'Single', 25, 'Available', Null, Null, Null, Null);
insert into ROOMS values (123, 'Double', 40, 'Booked', 9-01-2022, 12-01-2022, Null, 99038920);
insert into ROOMS values (046, 'Single', 25, 'Booked', 18-03-2022, 24-03-2022, Null, 81028471);
insert into ROOMS values (327, 'Suite', 70, 'Available', Null, Null, Null, Null);
insert into ROOMS values (436, 'Double', 40, 'Booked', 1-12-2021, 16-12-2021, Null, 89357592);
insert into ROOMS values (229, 'Double', 40, 'Booked', 28-01-2022, 3-02-2022, Null, 01083129);
insert into ROOMS values (423, 'Double', 40, 'Available', Null, Null, Null, Null);
insert into ROOMS values (513, 'Single', 25, 'Booked', 18-03-2022, 24-03-2022, Null, 95948589);
insert into ROOMS values (312, 'Suite', 70, 'Booked', 9-01-2022, 12-01-2022, Null, 91230085);
insert into ROOMS values (025, 'Single', 25, 'Available', Null, Null, Null, Null);
insert into ROOMS values (143, 'Single', 25, 'Available', Null, Null, Null, Null);
insert into ROOMS values (434, 'Single', 25, 'Booked', 14-11-2021, 24-11-2021, Null, 79348270);


create table MISCELLANOUS( 
ID Integer PRIMARY KEY,
NAME   Varchar(25),
CHARGE_PNIGHT   Double,
MBILL_NO  Number(5),
FOREIGN KEY (MBILL_NO) REFERENCES BILL(BILL_NO)
);

insert into MISCELLANOUS values (1,'Breakfast Buffet', 15, 90279);
insert into MISCELLANOUS values (2,'Extra mattress', 10.5,  90279);
insert into MISCELLANOUS values (3,'Laundry', 10,  32791);
insert into MISCELLANOUS values (4,'Gym', 20,32791);
insert into MISCELLANOUS values (5,'Vallet services', 14.5, 70037);
insert into MISCELLANOUS values (6,'Extra mattress', 10.5, 90279);
insert into MISCELLANOUS values (7,'Extra mattress', 10.5, 18902);
insert into MISCELLANOUS values (8,'Breakfast Buffet', 15, 18902);
insert into MISCELLANOUS values (9,'Vallet services', 14.5, 18902);
insert into MISCELLANOUS values (10,'Gym', 20, 70037);
insert into MISCELLANOUS values (11,'Breakfast Buffet', 15, 48302);
insert into MISCELLANOUS values (12,'Vallet services', 14.5, 21093);
insert into MISCELLANOUS values (13,'Breakfast Buffet', 15, 16738);

create table GUESTBILL_PHONE(
GCPR  Integer,
PHONE  Integer  PRIMARY KEY,
FOREIGN KEY (GCPR) REFERENCES GUEST(CPR)
);

insert into GUESTBILL_PHONE values(95673812, 38929384);
insert into GUESTBILL_PHONE values(95673812, 35682384);
insert into GUESTBILL_PHONE values(87364713, 36679203);
insert into GUESTBILL_PHONE values(96092478, 33573985);
insert into GUESTBILL_PHONE values(01083129, 31938483);
insert into GUESTBILL_PHONE values(95948589, 39468923);
insert into GUESTBILL_PHONE values(89357592, 30948234);
insert into GUESTBILL_PHONE values(89357592, 32347284);
insert into GUESTBILL_PHONE values(83747923, 39454873);
insert into GUESTBILL_PHONE values(81028471, 37738399);
insert into GUESTBILL_PHONE values(88190038, 34448749);
insert into GUESTBILL_PHONE values(91230085, 32224834);
insert into GUESTBILL_PHONE values(99038920, 31184330);


create table EMPLOYEE_PHONE(
ECPR  Integer,
PHONE  Integer PRIMARY KEY,
FOREIGN KEY (ECPR) REFERENCES EMPLOYEE(CPR)
);

insert into EMPLOYEE_PHONE values (19034782, 39983920);
insert into EMPLOYEE_PHONE values (89018234, 35579380);
insert into EMPLOYEE_PHONE values (67090812, 30274932);
insert into EMPLOYEE_PHONE values (67090812, 33345672);
insert into EMPLOYEE_PHONE values (92333376, 31183923);
insert into EMPLOYEE_PHONE values (34984601, 35849230);
insert into EMPLOYEE_PHONE values (78209311, 34774839);
insert into EMPLOYEE_PHONE values (01928336, 39556378);
insert into EMPLOYEE_PHONE values (11093899, 34468399);
insert into EMPLOYEE_PHONE values (44983337, 32226493);
insert into EMPLOYEE_PHONE values (01234958, 34467773);
insert into EMPLOYEE_PHONE values (18090034, 38291104);
insert into EMPLOYEE_PHONE values (55623348, 35282383);
insert into EMPLOYEE_PHONE values (77223456, 33457933);
insert into EMPLOYEE_PHONE values (00921278, 32348749);
insert into EMPLOYEE_PHONE values (88946839, 32383994);