/*
SQLyog Community Edition- MySQL GUI v6.14
MySQL - 5.1.30-community : Database - hotel
*********************************************************************
*/
CREATE DATABASE IF NOT EXISTS hotel;

USE hotel;

/*Table structure for table `hotels` */

DROP TABLE IF EXISTS `hotels`;

CREATE TABLE `hotels` (
  `Hotel` int(11) NOT NULL,
  `Name` varchar(50) DEFAULT NULL,
   Stars varchar(10) DEFAULT NULL
)  ;

/*Data for the table `hotels` */

insert  into `hotels`(`Hotel`,`Name`,Stars) values (1,'Grand View Hotel','*'),(2,'Mountain Side Resort','**'),(3,'Hard Days Inn','***'),(4,'Gunners Lodge','****');

/*Table structure for table `paymenttypes` */

DROP TABLE IF EXISTS `paymenttypes`;

CREATE TABLE `paymenttypes` (
  `Payment` int(11) NOT NULL,
  `Description` varchar(50) DEFAULT NULL
)  ;

/*Data for the table `paymenttypes` */

insert  into `paymenttypes`(`Payment`,`Description`) values (1,'Check'),(2,'MasterCard'),(3,'Visa'),(4,'American Express'),(5,'Cash');

/*Table structure for table `tbl_reservations` */

DROP TABLE IF EXISTS `reservations`;

CREATE TABLE `reservations` (
  `ResNo` int(11) NOT NULL,
  `LastName` varchar(25) DEFAULT NULL,
  `FirstName` varchar(20) DEFAULT NULL,
  `Address` varchar(50) DEFAULT NULL,
  `City` varchar(30) DEFAULT NULL,
  `State` varchar(2) DEFAULT NULL,
  `Postal` varchar(10) DEFAULT NULL,
  `Phone` varchar(15) DEFAULT NULL,
  `Payment` int(11) DEFAULT NULL,
  `Amount` decimal(10,2) DEFAULT NULL,
  `Hotel` int(11) NOT NULL,
  `Room` smallint(6) DEFAULT NULL,
  `DateIn` date DEFAULT NULL,
  `DateOut` date DEFAULT NULL,
  `DateNow` datetime DEFAULT NULL
)  ;

/*Data for the table `reservations` */

insert  into `reservations`(`ResNo`,`LastName`,`FirstName`,`Address`,`City`,`State`,`Postal`,`Phone`,`Payment`,`Amount`,`Hotel`,`Room`,`DateIn`,`DateOut`,`DateNow`) values (5001,'Hedge     ','Helen   ','1125 Garden Way    ','Broadstill   ','WY','66431     ','(767)346-8725',2,'75.00',1,2650,'2006-10-25','2006-10-28','2006-10-14 09:09:23'),(5002,'Stern     ','Leo     ','Route 5            ','Pay          ','MY','01798     ','(174)553-3475',3,'85.00',3,3015,'2006-10-25','2006-10-29','2006-10-14 10:08:00'),(5003,'Alexander ','Paul    ','200 4th Ave #9     ','Alexandria   ','OH','34287     ','(901)677-4452',3,'100.00',1,7930,'2006-10-26','2006-11-06','2006-10-14 16:33:23'),(5004,'Peterson  ','Lena    ','Route 4 Box 79     ','Spring Meadow','IA','55920     ','(605)332-3274',2,'90.00',3,2388,'2006-11-01','2006-11-13','2006-10-15 19:35:23'),(5005,'Nista     ','Nina    ','6432 Maple Grove St','Cannon       ','TX','85492     ','(662)784-4434',4,'65.00',3,2597,'2006-11-01','2006-11-11','2006-10-15 19:44:23'),(5006,'Englewood ','Maggie  ','85 Parsley Dr      ','Janestown    ','GA','77693     ','(408)554-8691',1,'66.00',2,5630,'2006-11-02','2006-11-04','2006-10-15 19:45:23'),(5007,'Kelley    ','Teresa  ','40 Willow Dr       ','Townsend     ','CA','33297     ','(543)287-0101',4,'55.00',4,1774,'2006-11-03','2006-11-18','2006-10-15 19:47:23'),(5008,'Ivey      ','Stewart ','3 Twilight Ave     ','Townsend     ','IL','77876     ','(705)441-6673',2,'45.00',2,4750,'2006-11-01','2006-12-13','2006-10-15 19:49:23'),(5009,'Jessipe   ','Jennifer','55 7th St #7       ','Highland     ','FL','55491     ','(334)971-4454',3,'70.00',3,2960,'2006-11-05','2006-11-28','2006-10-15 19:50:23'),(5010,'Borderland','Jess    ','745 Hill St        ','Stevenson    ','KS','44529','(334)566-5732',4,'90.00',3,2674,'2006-11-08','2006-11-10','2006-10-15 19:53:23'),(5011,'McGerney  ','Walter  ','77 Mayberry Lane   ','Storm        ','ID','44542     ','(776)078-4413',1,'80.00',4,1895,'2006-11-09','2006-11-18','2006-10-15 20:09:23'),(5012,'Yellar    ','Olson   ','1 Salsberry Lane   ','Steak        ','OR','88789     ','(867)459-1030',4,'125.00',3,2443,'2006-11-11','2006-12-28','2006-10-15 21:22:23'),(5013,'Dickenson ','Martha  ','Route 66 Box 5     ','Oak          ','CA','77678     ','(776)321-6578',1,'98.00',4,2148,'2006-11-25','2007-01-28','2006-10-15 23:11:23');

/*Table structure for table `rooms` */

DROP TABLE IF EXISTS `tbl_rooms`;

CREATE TABLE `rooms` (
  `Hotel` int(11) NOT NULL,
  `Room` smallint(6) NOT NULL,
  `RoomType` int(11) DEFAULT NULL,
  `Price` decimal(10,2) DEFAULT NULL,
  `Comments` blob,
  `RoomStatus` varchar(1) DEFAULT NULL
)  ;

/*Data for the table `tbl_rooms` */

insert  into `rooms`(`Hotel`,`Room`,`RoomType`,`Price`,`Comments`,`RoomStatus`) values (1,1110,10,'75.00','','0'),(1,1220,10,'75.00','','0'),(1,1330,10,'75.00','','0'),(1,1440,10,'75.00','','0'),(1,1550,11,'90.00','','0'),(1,1660,11,'90.00','','0'),(1,1770,11,'90.00','','0'),(1,1880,12,'85.00','','0'),(1,1990,12,'85.00','','0'),(1,2100,12,'85.00','','0'),(1,2210,13,'100.00','','0'),(1,2320,13,'100.00','','0'),(1,2430,13,'100.00','','0'),(1,2540,14,'125.00','','0'),(1,2650,14,'125.00','','0'),(1,2760,15,'75.00','','0'),(1,2870,15,'75.00','','0'),(1,3090,16,'90.00','','0'),(1,3200,16,'90.00','','0'),(1,3310,17,'85.00','','0'),(1,3420,17,'85.00','','0'),(1,3530,18,'100.00','','0'),(1,3640,18,'100.00','','0'),(1,3750,19,'125.00','','0'),(1,3860,19,'125.00','','0'),(1,3970,20,'80.00','','0'),(1,4080,20,'80.00','','0'),(1,4190,20,'80.00','','0'),(1,4300,20,'80.00','','0'),(1,4410,20,'80.00','','0'),(1,4520,21,'95.00','','0'),(1,4630,21,'95.00','','0'),(1,4740,21,'95.00','','0'),(1,4850,21,'95.00','','0'),(1,4960,22,'90.00','','0'),(1,5070,22,'90.00','','0'),(1,5180,22,'90.00','','0'),(1,5290,22,'90.00','','0'),(1,5400,23,'105.00','','0'),(1,5510,23,'105.00','','0'),(1,5620,23,'105.00','','0'),(1,5730,24,'130.00','','0'),(1,5840,24,'130.00','','0'),(1,5950,25,'80.00','','0'),(1,6060,25,'80.00','','0'),(1,6170,25,'80.00','','0'),(1,6280,26,'95.00','','0'),(1,6390,26,'95.00','','0'),(1,6500,27,'90.00','','0'),(1,6610,27,'90.00','','0'),(1,6720,28,'105.00','','0'),(1,6830,28,'105.00','','0'),(1,6940,29,'130.00','','0'),(1,7050,29,'130.00','','0'),(1,7160,30,'85.00','','0'),(1,7270,30,'85.00','','0'),(1,7380,30,'85.00','','0'),(1,7490,30,'85.00','','0'),(1,7600,30,'85.00','','0'),(1,7710,31,'100.00','','0'),(1,7820,31,'100.00','','0'),(1,7930,32,'95.00','','0'),(1,8040,32,'95.00','','0'),(1,8150,33,'110.00','','0'),(1,8260,33,'110.00','','0'),(1,8370,34,'135.00','','0'),(1,8480,34,'135.00','','0'),(1,8590,35,'85.00','','0'),(1,8700,35,'85.00','','0'),(1,8810,36,'100.00','','0'),(1,8920,36,'100.00','','0'),(1,9030,37,'95.00','','0'),(1,9140,37,'95.00','','0'),(1,9250,38,'110.00','','0'),(1,9360,38,'110.00','','0'),(1,9470,39,'135.00','','0'),(1,9580,39,'135.00','','0'),(1,9690,40,'150.00','','0'),(1,9800,40,'150.00','','0'),(1,9910,41,'160.00','','0'),(1,9920,42,'160.00','','0'),(2,2110,10,'75.00','','0'),(2,2220,10,'75.00','','0'),(2,2330,10,'75.00','','0'),(2,2440,10,'75.00','','0'),(2,2550,11,'90.00','','0'),(2,2660,11,'90.00','','0'),(2,2770,11,'90.00','','0'),(2,2880,12,'85.00','','0'),(2,2990,12,'85.00','','0'),(2,3100,12,'85.00','','0'),(2,3210,13,'100.00','','0'),(2,3320,13,'100.00','','0'),(2,3430,13,'100.00','','0'),(2,3540,14,'125.00','','0'),(2,3650,14,'125.00','','0'),(2,3760,15,'75.00','','0'),(2,3870,15,'75.00','','0'),(2,3980,15,'75.00','','0'),(2,4090,16,'90.00','','0'),(2,4200,16,'90.00','','0'),(2,4310,17,'85.00','','0'),(2,4420,17,'85.00','','0'),(2,4530,18,'100.00','','0'),(2,4640,18,'100.00','','0'),(2,4750,19,'125.00','','0'),(2,4860,19,'125.00','','0'),(2,4970,20,'80.00','','0'),(2,5080,20,'80.00','','0'),(2,5190,20,'80.00','','0'),(2,5300,20,'80.00','','0'),(2,5410,20,'80.00','','0'),(2,5520,21,'95.00','','0'),(2,5630,21,'95.00','','0'),(2,5740,21,'95.00','','0'),(2,5850,21,'95.00','','0'),(2,5960,22,'90.00','','0'),(2,6070,22,'90.00','','0'),(2,6180,22,'90.00','','0'),(2,6290,22,'90.00','','0'),(2,6400,23,'105.00','','0'),(2,6510,23,'105.00','','0'),(2,6620,23,'105.00','','0'),(2,6730,24,'130.00','','0'),(2,6840,24,'130.00','','0'),(2,6950,25,'80.00','','0'),(2,7060,25,'80.00','','0'),(2,7170,25,'80.00','','0'),(2,7280,26,'95.00','','0'),(2,7390,26,'95.00','','0'),(2,7500,27,'90.00','','0'),(2,7610,27,'90.00','','0'),(2,7720,28,'105.00','','0'),(2,7830,28,'105.00','','0'),(2,7940,29,'130.00','','0'),(2,8050,29,'130.00','','0'),(2,8160,30,'85.00','','0'),(2,8270,30,'85.00','','0'),(2,8380,30,'85.00','','0'),(2,8490,30,'85.00','','0'),(2,8600,30,'85.00','','0'),(2,8710,31,'100.00','','0'),(2,8820,31,'100.00','','0'),(2,8930,32,'95.00','','0'),(2,9040,32,'95.00','','0'),(2,9150,33,'110.00','','0'),(2,9260,33,'110.00','','0'),(2,9370,34,'135.00','','0'),(2,9480,34,'135.00','','0'),(2,9590,35,'85.00','','0'),(2,9700,35,'85.00','','0'),(2,9810,36,'100.00','','0'),(2,9920,36,'100.00','','0'),(2,9930,37,'95.00','','0'),(2,9940,37,'95.00','','0'),(2,9950,38,'110.00','','0'),(2,9960,38,'110.00','','0'),(2,9970,39,'135.00','','0'),(2,9980,39,'135.00','','0'),(2,9990,40,'150.00','','0'),(2,9992,40,'150.00','','0'),(2,9994,41,'160.00','','0'),(2,9996,42,'160.00','','0'),(3,2311,10,'75.00','','0'),(3,2322,10,'75.00','','0'),(3,2333,10,'75.00','','0'),(3,2344,10,'75.00','','0'),(3,2355,11,'90.00','','0'),(3,2366,11,'90.00','','0'),(3,2377,11,'90.00','','0'),(3,2388,12,'85.00','','0'),(3,2399,12,'85.00','','0'),(3,2410,12,'85.00','','0'),(3,2421,13,'100.00','','0'),(3,2432,13,'100.00','','0'),(3,2443,13,'100.00','','0'),(3,2454,14,'125.00','','0'),(3,2465,14,'125.00','','0'),(3,2476,15,'75.00','','0'),(3,2487,15,'75.00','','0'),(3,2498,15,'75.00','','0'),(3,2509,16,'90.00','','0'),(3,2520,16,'90.00','','0'),(3,2531,17,'85.00','','0'),(3,2542,17,'85.00','','0'),(3,2553,18,'100.00','','0'),(3,2564,18,'100.00','','0'),(3,2575,19,'125.00','','0'),(3,2586,19,'125.00','','0'),(3,2597,20,'80.00','','0'),(3,2608,20,'80.00','','0'),(3,2619,20,'80.00','','0'),(3,2630,20,'80.00','','0'),(3,2641,20,'80.00','','0'),(3,2652,21,'95.00','','0'),(3,2663,21,'95.00','','0'),(3,2674,21,'95.00','','0'),(3,2685,21,'95.00','','0'),(3,2696,22,'90.00','','0'),(3,2707,22,'90.00','','0'),(3,2718,22,'90.00','','0'),(3,2729,22,'90.00','','0'),(3,2740,23,'105.00','','0'),(3,2751,23,'105.00','','0'),(3,2762,23,'105.00','','0'),(3,2773,24,'130.00','','0'),(3,2784,24,'130.00','','0'),(3,2795,25,'80.00','','0'),(3,2806,25,'80.00','','0'),(3,2817,25,'80.00','','0'),(3,2828,26,'95.00','','0'),(3,2839,26,'95.00','','0'),(3,2850,27,'90.00','','0'),(3,2861,27,'90.00','','0'),(3,2872,28,'105.00','','0'),(3,2883,28,'105.00','','0'),(3,2894,29,'130.00','','0'),(3,2905,29,'130.00','','0'),(3,2916,30,'85.00','','0'),(3,2927,30,'85.00','','0'),(3,2938,30,'85.00','','0'),(3,2949,30,'85.00','','0'),(3,2960,30,'85.00','','0'),(3,2971,31,'100.00','','0'),(3,2982,31,'100.00','','0'),(3,2993,32,'95.00','','0'),(3,3004,32,'95.00','','0'),(3,3015,33,'110.00','','0'),(3,3026,33,'110.00','','0'),(3,3037,34,'135.00','','0'),(3,3048,34,'135.00','','0'),(3,3059,35,'85.00','','0'),(3,3070,35,'85.00','','0'),(3,3081,36,'100.00','','0'),(3,3092,36,'100.00','','0'),(3,3103,37,'95.00','','0'),(3,3114,37,'95.00','','0'),(3,3125,38,'110.00','','0'),(3,3136,38,'110.00','','0'),(3,3147,39,'135.00','','0'),(3,3158,39,'135.00','','0'),(3,3169,40,'150.00','','0'),(3,3180,40,'150.00','','0'),(3,3191,41,'160.00','','0'),(3,3202,42,'160.00','','0'),(4,1411,10,'75.00','','0'),(4,1422,10,'75.00','','0'),(4,1433,10,'75.00','','0'),(4,1444,10,'75.00','','0'),(4,1455,11,'90.00','','0'),(4,1466,11,'90.00','','0'),(4,1477,11,'90.00','','0'),(4,1488,12,'85.00','','0'),(4,1499,12,'85.00','','0'),(4,1510,12,'85.00','','0'),(4,1521,13,'100.00','','0'),(4,1532,13,'100.00','','0'),(4,1543,13,'100.00','','0'),(4,1554,14,'125.00','','0'),(4,1565,14,'125.00','','0'),(4,1576,15,'75.00','','0'),(4,1587,15,'75.00','','0'),(4,1598,15,'75.00','','0'),(4,1609,16,'90.00','','0'),(4,1620,16,'90.00','','0'),(4,1631,17,'85.00','','0'),(4,1642,17,'85.00','','0'),(4,1653,18,'100.00','','0'),(4,1664,18,'100.00','','0'),(4,1675,19,'125.00','','0'),(4,1686,19,'125.00','','0'),(4,1697,20,'80.00','','0'),(4,1708,20,'80.00','','0'),(4,1719,20,'80.00','','0'),(4,1730,20,'80.00','','0'),(4,1741,20,'80.00','','0'),(4,1752,21,'95.00','','0'),(4,1763,21,'95.00','','0'),(4,1774,21,'95.00','','0'),(4,1785,21,'95.00','','0'),(4,1796,22,'90.00','','0'),(4,1807,22,'90.00','','0'),(4,1818,22,'90.00','','0'),(4,1829,22,'90.00','','0'),(4,1840,23,'105.00','','0'),(4,1851,23,'105.00','','0'),(4,1862,23,'105.00','','0'),(4,1873,24,'130.00','','0'),(4,1884,24,'130.00','','0'),(4,1895,25,'80.00','','0'),(4,1906,25,'80.00','','0'),(4,1917,25,'80.00','','0'),(4,1928,26,'95.00','','0'),(4,1939,26,'95.00','','0'),(4,1950,27,'90.00','','0'),(4,1961,27,'90.00','','0'),(4,1972,28,'105.00','','0'),(4,1983,28,'105.00','','0'),(4,1994,29,'130.00','','0'),(4,2005,29,'130.00','','0'),(4,2016,30,'85.00','','0'),(4,2027,30,'85.00','','0'),(4,2038,30,'85.00','','0'),(4,2049,30,'85.00','','0'),(4,2060,30,'85.00','','0'),(4,2071,31,'100.00','','0'),(4,2082,31,'100.00','','0'),(4,2093,32,'95.00','','0'),(4,2104,32,'95.00','','0'),(4,2115,33,'110.00','','0'),(4,2126,33,'110.00','','0'),(4,2137,34,'135.00','','0'),(4,2148,34,'135.00','','0'),(4,2159,35,'85.00','','0'),(4,2170,35,'85.00','','0'),(4,2181,36,'100.00','','0'),(4,2192,36,'100.00','','0'),(4,2203,37,'95.00','','0'),(4,2214,37,'95.00','','0'),(4,2225,38,'110.00','','0'),(4,2236,38,'110.00','','0'),(4,2247,39,'135.00','','0'),(4,2258,39,'135.00','','0'),(4,2269,40,'150.00','','0'),(4,2280,40,'150.00','','0'),(4,2291,41,'160.00','','0'),(4,2302,42,'160.00','','0'),(1,2780,15,'75.00','','0');

/*Table structure for table `roomtypes` */

DROP TABLE IF EXISTS `tbl_roomtypes`;

CREATE TABLE `roomtypes` (
  `RoomType` int(11) NOT NULL,
  `Beds` smallint(6) DEFAULT NULL,
  `Description` varchar(254) DEFAULT NULL
)  ;

/*Data for the table `roomtypes` */

insert  into `roomtypes`(`RoomType`,`Beds`,`Description`) values (10,1,'Double; Non-smoking'),(12,1,'Double; Non-smoking; Kitchenette'),(13,2,'Double; Non-smoking; Kitchenette'),(15,1,'Double; Smoking'),(16,2,'Double; Smoking'),(17,1,'Double; Smoking; Kitchenette'),(18,2,'Double; Smoking; Kitchenette'),(14,1,'Double; Non-smoking; Whirlpool'),(19,1,'Double; Smoking; Whirlpool'),(20,1,'Queen; Non-smoking'),(21,2,'Queen; Non-smoking'),(22,1,'Queen; Non-smoking; Kitchenette'),(23,2,'Queen; Non-smoking; Kitchenette'),(24,1,'Queen; Non-smoking; Whirlpool'),(25,1,'Queen; Smoking'),(26,2,'Queen; Smoking'),(27,1,'Queen; Smoking; Kitchenette'),(28,2,'Queen; Smoking; Kitchenette'),(29,1,'Queen; Smoking; Whirlpool'),(30,1,'King; Non-smoking'),(31,2,'King; Non-smoking'),(32,1,'King; Non-smoking; Kitchenette'),(33,2,'King; Non-smoking; Kitchenette'),(34,1,'King; Non-smoking; Whirlpool'),(35,1,'King; Smoking'),(36,2,'King; Smoking'),(37,1,'King; Smoking; Kitchenette'),(38,2,'King; Smoking; Kitchenette'),(39,1,'King; Smoking; Whirlpool'),(40,1,'Suite w/ King'),(41,1,'Suite w/ King & Whirlpool'),(42,1,'Penhouse w/ King'),(11,2,'Double; Non-smoking');