-- MySQL dump 10.9
--
-- Host: localhost    Database: hotel
-- ------------------------------------------------------
-- Server version	5.0.3-beta-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `city`
--

DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
  `zip` char(5) NOT NULL,
  `name` char(20) NOT NULL,
  `state` char(2) NOT NULL,
  PRIMARY KEY  (`zip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `city`
--


/*!40000 ALTER TABLE `city` DISABLE KEYS */;
LOCK TABLES `city` WRITE;
INSERT INTO `city` VALUES ('10019','New York','NY'),('10580','New York','NY'),('11788','Long Island','NY'),('12203','Albany','NY'),('20005','Washington','DC'),('20019','Washington','DC'),('20037','Washington','DC'),('20903','Silver Spring','MD'),('32018','Daytona Beach','FL'),('33441','Deerfield Beach','FL'),('33575','Clearwater','FL'),('45211','Cincinnati','OH'),('48226','Detroit','MI'),('60018','Rosemont','IL'),('60601','Chicago','IL'),('60615','Chicago','IL'),('70112','New Orleans','LA'),('75243','Dallas','TX'),('90018','Los Angeles','CA'),('90029','Hollywood','CA'),('90804','Long Beach','CA'),('92262','Palm Springs','CA'),('92714','Irvine','CA'),('95054','Santa Clara','CA'),('97213','Portland','OR');
UNLOCK TABLES;
/*!40000 ALTER TABLE `city` ENABLE KEYS */;

--
-- Table structure for table `custom_hotel`
--

DROP TABLE IF EXISTS `custom_hotel`;

--
-- Table structure for table `customer`
--

DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `cno` decimal(4,0) NOT NULL,
  `title` enum('Mr','Mrs','Company') default NULL,
  `firstname` char(10) default NULL,
  `name` char(10) NOT NULL,
  `zip` char(5) default NULL,
  `address` char(25) NOT NULL,
  PRIMARY KEY  (`cno`),
  KEY `customer_zip_in_city` (`zip`),
  CONSTRAINT `customer_zip_in_city` FOREIGN KEY (`zip`) REFERENCES `city` (`zip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `customer`
--


/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
LOCK TABLES `customer` WRITE;
INSERT INTO `customer` VALUES ('3000','Mrs','Jenny','Porter','10580','1340 N.Ash Street, #3'),('3100','Mr','Peter','Brown','48226','1001 34th Str., APT.3'),('3200','Company',NULL,'Datasoft','90018','486 Maple Str.'),('3300','Mrs','Rose','Brian','75243','500 Yellowstone Drive, #2'),('3400','Mrs','Mary','Griffith','20005','3401 Elder Lane'),('3500','Mr','Martin','Randolph','60615','340 MAIN STREET, #7'),('3600','Mrs','Sally','Smith','75243','250 Curtis Street'),('3700','Mr','Mike','Jackson','45211','133 BROADWAY APT. 1'),('3800','Mrs','Rita','Doe','97213','2000 Humboldt Str., #6'),('3900','Mr','George','Howe','75243','111 B Parkway, #23'),('4000','Mr','Frank','Miller','95054','27 5th Str., 76'),('4100','Mrs','Susan','Baker','90018','200 MAIN STREET, #94'),('4200','Mr','Joseph','Peters','92714','700 S. Ash Str., APT.12'),('4300','Company',NULL,'TOOLware','20019','410 Mariposa Str., #10'),('4400','Mr','Antony','Jenkins','20903','55 A Parkway, #15');
UNLOCK TABLES;
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;

--
-- Table structure for table `customer_addr`
--

DROP TABLE IF EXISTS `customer_addr`;

--
-- Table structure for table `employee`
--

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `hno` decimal(4,0) NOT NULL default '0',
  `eno` decimal(4,0) NOT NULL default '0',
  `title` enum('Mr','Mrs') default NULL,
  `firstname` char(10) default NULL,
  `name` char(10) NOT NULL,
  `manager_eno` decimal(4,0) default NULL,
  PRIMARY KEY  (`hno`,`eno`),
  CONSTRAINT `employee_hno_in_hotel` FOREIGN KEY (`hno`) REFERENCES `hotel` (`hno`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employee`
--


/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
LOCK TABLES `employee` WRITE;
INSERT INTO `employee` VALUES ('10','1','Mrs','Martina','Bender',NULL),('10','2','Mr','Martin','Bender',NULL),('10','3','Mrs','Claudia','Randolph','1'),('10','4','Mr','Mark','Nober','1'),('10','5','Mrs','Phyllis','Manger','3'),('10','6','Mrs','Francis','Nick','2'),('10','7','Mr','Peter','Carley','2'),('10','8','Mr','Ian','Wolf',NULL),('10','9','Mr','Bill','Tucker','8'),('10','10','Mrs','Diana','Corner','1');
UNLOCK TABLES;
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;

--
-- Table structure for table `hotel`
--

DROP TABLE IF EXISTS `hotel`;
CREATE TABLE `hotel` (
  `hno` decimal(4,0) NOT NULL,
  `name` char(15) NOT NULL,
  `zip` char(5) default NULL,
  `address` char(25) NOT NULL,
  `info` mediumtext,
  PRIMARY KEY  (`hno`),
  KEY `hotel_zip_in_city` (`zip`),
  CONSTRAINT `hotel_zip_in_city` FOREIGN KEY (`zip`) REFERENCES `city` (`zip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `hotel`
--


/*!40000 ALTER TABLE `hotel` DISABLE KEYS */;
LOCK TABLES `hotel` WRITE;
INSERT INTO `hotel` VALUES ('10','Congress','20005','155 Beechwood Str.','Sports and Games\n\n- - - - - - - - - - - -\n\n \n\nsolaria in separate relaxation rooms\n\nsauna, free of charge, individual booking if you\n\nprefer to sweat alone\n\na playroom for our small guests\n\nbicycles for our health-conscious guests\n\nstoreroom for bicycles in the house\n\nvarious activities and events every night\n\nbroad selection of board games available\n\nindoor swimming pool and fitness center\n\n \n\n \n\nTeaching and Learning\n\n- - - - - - - - - - - -\n\n \n\nEnjoy it. In an absolutely quiet place. The person in charge is available\n\nduring the whole meeting. Consistently high standards providing the ideal\n\nworking environment for you to have a successful meeting.\n\nTelefax and copy machines, even for transparencies.\n\nDaylit rooms creatively arranged.\n\nAroma lamps with blends of fragrances to improve\n\nconcentration and learning capabilities.\n\nMusic in the meeting rooms whenever you like.\n\n \n\nAre you curious?  We are waiting for you!\n\n'),('20','Long Island','11788','1499 Grove Street',NULL),('30','Regency','20037','477 17th Avenue','Our hotel is situated in the city centre in the direct vicinity of the shopping mall.\n\nOur rooms offer you the maximal facilities of a modern middleclass hotel.'),('40','Eight Avenue','10019','112 8th Avenue',NULL),('50','Lake Michigan','60601','354 OAK Terrace',NULL),('60','Airport','60018','650 C Parkway','Welcome in the Airport Hotel.\n\n- - - - - - - - - - - - - - -\n\n \n\nThe Airport Hotel is situated right in the city centre.\n\nOur hotel has spacious, comfortable rooms at attractive prices.\n\nAll rooms are with bath/shower, toilet, radio, colour TV,\n\nvideo, telephone, minibar and desk.'),('70','Empire State','12203','65 Yellowstone Dr.',NULL),('80','Midtown','10019','12 Barnard Str.',NULL),('90','Sunshine','33575','200 Yellowstone Dr.','The Sunshine is located in the centre of the city.\n\n489 rooms, suites and facilities for the disabled, with\n\nsuperb furnishings and genuine elegance are only some of the\n\nfeatures which make this hotel something special.\n\nIndividual service ensures personal comfort for all our guests 24 hours a day -\n\nmore than just mere luxury.\n\n \n\n11 function rooms accommodating up to 300 persons are\n\navailable for all types of events.\n\n \n\nFor relaxation and recreation there are a swimming pool with\n\nwhirlpool, a sauna, steam bath and solarium.\n\n500 parking spaces are available in the hotel garage.\n\n'),('100','Beach','32018','1980 34th Str.',NULL),('110','Atlantic','33441','111 78th Str.',NULL),('120','Long Beach','90804','35 Broadway',NULL),('130','Star','90029','13 Beechwood Place',NULL),('140','River Boat','70112','788 MAIN STREET',NULL),('150','Indian Horse','92262','16 MAIN STREET',NULL);
UNLOCK TABLES;
/*!40000 ALTER TABLE `hotel` ENABLE KEYS */;

--
-- Table structure for table `hotel_addr`
--

DROP TABLE IF EXISTS `hotel_addr`;

--
-- Table structure for table `reservation`
--

DROP TABLE IF EXISTS `reservation`;
CREATE TABLE `reservation` (
  `rno` decimal(4,0) NOT NULL,
  `cno` decimal(4,0) default NULL,
  `hno` decimal(4,0) default NULL,
  `type` enum('single','double','suite') default NULL,
  `arrival` date NOT NULL,
  `departure` date NOT NULL,
  PRIMARY KEY  (`rno`),
  KEY `reservation_cno_in_customer` (`cno`),
  KEY `reservation_info_in_room` (`hno`,`type`),
  CONSTRAINT `reservation_cno_in_customer` FOREIGN KEY (`cno`) REFERENCES `customer` (`cno`) ON DELETE CASCADE,
  CONSTRAINT `reservation_info_in_room` FOREIGN KEY (`hno`, `type`) REFERENCES `room` (`hno`, `type`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `reservation`
--


/*!40000 ALTER TABLE `reservation` DISABLE KEYS */;
LOCK TABLES `reservation` WRITE;
INSERT INTO `reservation` VALUES ('100','3000','80','single','2004-11-13','2004-11-15'),('110','3000','100','double','2004-12-24','2005-01-06'),('120','3200','50','suite','2004-11-14','2004-11-18'),('130','3900','110','single','2005-02-01','2005-02-03'),('140','4300','80','double','2004-04-12','2004-04-30'),('150','3600','70','double','2005-03-14','2005-03-24'),('160','4100','70','single','2004-04-12','2004-04-15'),('170','4400','150','suite','2004-09-01','2004-09-03'),('180','3100','120','double','2004-12-23','2005-01-08'),('190','4300','140','double','2004-11-14','2004-11-17');
UNLOCK TABLES;
/*!40000 ALTER TABLE `reservation` ENABLE KEYS */;

--
-- Table structure for table `room`
--

DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
  `hno` decimal(4,0) NOT NULL default '0',
  `type` enum('single','double','suite') NOT NULL default 'single',
  `free` int(3) unsigned default NULL,
  `price` decimal(6,2) default NULL,
  PRIMARY KEY  (`hno`,`type`),
  CONSTRAINT `room_hno_in_hotel` FOREIGN KEY (`hno`) REFERENCES `hotel` (`hno`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `room`
--


/*!40000 ALTER TABLE `room` DISABLE KEYS */;
LOCK TABLES `room` WRITE;
INSERT INTO `room` VALUES ('10','single',20,'135.00'),('10','double',45,'200.00'),('20','single',10,'70.00'),('20','double',13,'100.00'),('30','single',12,'45.00'),('30','double',15,'80.00'),('40','single',20,'85.00'),('40','double',35,'140.00'),('50','single',50,'105.00'),('50','double',230,'180.00'),('50','suite',12,'500.00'),('60','single',10,'120.00'),('60','double',39,'200.00'),('60','suite',20,'500.00'),('70','single',4,'115.00'),('70','double',11,'180.00'),('80','single',15,'90.00'),('80','double',19,'150.00'),('80','suite',5,'400.00'),('90','single',45,'90.00'),('90','double',145,'150.00'),('90','suite',60,'300.00'),('100','single',11,'60.00'),('100','double',24,'100.00'),('110','single',2,'70.00'),('110','double',10,'130.00'),('120','single',34,'80.00'),('120','double',78,'140.00'),('120','suite',55,'350.00'),('130','single',89,'160.00'),('130','double',300,'270.00'),('130','suite',100,'700.00'),('140','single',10,'125.00'),('140','double',9,'200.00'),('140','suite',78,'600.00'),('150','single',44,'100.00'),('150','double',115,'190.00'),('150','suite',6,'450.00');
UNLOCK TABLES;
/*!40000 ALTER TABLE `room` ENABLE KEYS */;

--
-- View structure for view `custom_hotel`
--

DROP VIEW IF EXISTS `custom_hotel`;
CREATE ALGORITHM=UNDEFINED VIEW `hotel`.`custom_hotel` AS select `customer_addr`.`name` AS `customname`,`customer_addr`.`city` AS `customcity`,`hotel_addr`.`name` AS `hotelname`,`hotel_addr`.`city` AS `hotelcity` from `hotel`.`customer_addr` join `hotel`.`hotel_addr` join `hotel`.`reservation` where ((`customer_addr`.`cno` = `hotel`.`reservation`.`cno`) and (`hotel_addr`.`hno` = `hotel`.`reservation`.`hno`));

--
-- View structure for view `customer_addr`
--

DROP VIEW IF EXISTS `customer_addr`;
CREATE ALGORITHM=UNDEFINED VIEW `hotel`.`customer_addr` AS select `hotel`.`customer`.`cno` AS `cno`,`hotel`.`customer`.`title` AS `title`,`hotel`.`customer`.`name` AS `name`,`hotel`.`customer`.`zip` AS `zip`,`hotel`.`city`.`name` AS `city`,`hotel`.`city`.`state` AS `state`,`hotel`.`customer`.`address` AS `address` from `hotel`.`customer` join `hotel`.`city` where (`hotel`.`customer`.`zip` = `hotel`.`city`.`zip`) WITH CASCADED CHECK OPTION;

--
-- View structure for view `hotel_addr`
--

DROP VIEW IF EXISTS `hotel_addr`;
CREATE ALGORITHM=UNDEFINED VIEW `hotel`.`hotel_addr` AS select `hotel`.`hotel`.`hno` AS `hno`,`hotel`.`hotel`.`name` AS `name`,`hotel`.`hotel`.`zip` AS `zip`,`hotel`.`city`.`name` AS `city`,`hotel`.`city`.`state` AS `state`,`hotel`.`hotel`.`address` AS `address` from `hotel`.`hotel` join `hotel`.`city` where (`hotel`.`hotel`.`zip` = `hotel`.`city`.`zip`) WITH CASCADED CHECK OPTION;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;