Jalan Keluar

/*
SQLyog Ultimate v11.11 (32 bit)
MySQL - 5.0.21-community-nt : Database - ukom
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!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 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`ukom` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */;

/*Table structure for table `admin` */

DROP TABLE IF EXISTS `admin`;

CREATE TABLE `admin` (
  `username` varchar(50) collate latin1_general_ci NOT NULL,
  `password` varchar(50) collate latin1_general_ci NOT NULL,
  `name` varchar(50) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `admin` */

insert  into `admin`(`username`,`password`,`name`) values ('admin','master','Admin');

/*Table structure for table `booking` */

DROP TABLE IF EXISTS `booking`;

CREATE TABLE `booking` (
  `kode_booking` varchar(20) collate latin1_general_ci NOT NULL,
  `time_booking` datetime NOT NULL,
  `username` varchar(50) collate latin1_general_ci NOT NULL,
  `id_show` varchar(10) collate latin1_general_ci NOT NULL,
  `invoiceNumber` varchar(200) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`kode_booking`),
  KEY `username` (`username`),
  KEY `invoiceNumber` (`invoiceNumber`),
  KEY `time_booking` (`time_booking`),
  KEY `id_show` (`id_show`),
  CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `booking_ibfk_3` FOREIGN KEY (`invoiceNumber`) REFERENCES `payment` (`invoiceNumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `booking` */

/*Table structure for table `payment` */

DROP TABLE IF EXISTS `payment`;

CREATE TABLE `payment` (
  `invoiceNumber` varchar(200) collate latin1_general_ci NOT NULL,
  `paymentName` varchar(200) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`invoiceNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `payment` */

/*Table structure for table `plane` */

DROP TABLE IF EXISTS `plane`;

CREATE TABLE `plane` (
  `id_plane` varchar(10) collate latin1_general_ci NOT NULL,
  `plane` varchar(50) collate latin1_general_ci NOT NULL,
  `seat` int(10) NOT NULL,
  PRIMARY KEY  (`id_plane`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `plane` */

insert  into `plane`(`id_plane`,`plane`,`seat`) values ('HA0001','Sukhoi',30),('HA0002','Solomons Airbus',30);

/*Table structure for table `seat` */

DROP TABLE IF EXISTS `seat`;

CREATE TABLE `seat` (
  `seatName` varchar(10) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`seatName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `seat` */

insert  into `seat`(`seatName`) values ('A1'),('A10'),('A2'),('A3'),('A4'),('A5'),('A6'),('A7'),('A8'),('A9'),('B1'),('B10'),('B2'),('B3'),('B4'),('B5'),('B6'),('B7'),('B8'),('B9'),('C1'),('C10'),('C2'),('C3'),('C4'),('C5'),('C6'),('C7'),('C8'),('C9');

/*Table structure for table `show` */

DROP TABLE IF EXISTS `show`;

CREATE TABLE `show` (
  `id_show` int(10) NOT NULL auto_increment,
  `id_plane` varchar(10) collate latin1_general_ci NOT NULL default '',
  `id_venue` varchar(8) collate latin1_general_ci NOT NULL default '',
  `departure_time` datetime NOT NULL,
  `arrived_time` datetime NOT NULL,
  `seatName` varchar(10) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id_show`),
  KEY `seatName` (`seatName`),
  KEY `id_venue` (`id_venue`),
  KEY `id_plane` (`id_plane`),
  CONSTRAINT `show_ibfk_3` FOREIGN KEY (`seatName`) REFERENCES `seat` (`seatName`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `show_ibfk_4` FOREIGN KEY (`id_venue`) REFERENCES `venue` (`id_venue`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `show_ibfk_5` FOREIGN KEY (`id_plane`) REFERENCES `plane` (`id_plane`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `show` */

insert  into `show`(`id_show`,`id_plane`,`id_venue`,`departure_time`,`arrived_time`,`seatName`) values (7,'HA0001','JKTMKSAM','2016-02-03 05:01:00','2016-02-02 15:59:00','A1'),(8,'HA0001','JKTMKSAM','2016-02-20 04:02:00','2016-02-16 18:04:00','A1'),(9,'HA0001','JKTMKSAM','2016-02-20 04:02:00','2016-02-16 18:04:00','A1'),(10,'HA0001','JKTMKSAM','2016-02-20 04:02:00','2016-02-16 18:04:00','A1'),(11,'HA0001','JKTMKSAM','2016-03-11 04:02:00','2016-02-17 03:02:00','A1'),(12,'HA0001','JKTMKSAM','2016-02-01 13:03:00','2016-02-02 01:04:00','A1'),(13,'HA0001','JKTMKSAM','2016-06-01 05:01:00','2016-06-02 03:02:00','A1'),(14,'HA0002','JKTHA','2016-02-29 01:00:00','2016-02-29 02:00:00','B2');

/*Table structure for table `ticket` */

DROP TABLE IF EXISTS `ticket`;

CREATE TABLE `ticket` (
  `id_ticketName` int(10) NOT NULL,
  `ticketName` varchar(200) collate latin1_general_ci NOT NULL,
  `id_booking` varchar(20) collate latin1_general_ci NOT NULL,
  `id_ticketType` int(10) NOT NULL,
  PRIMARY KEY  (`id_ticketName`),
  KEY `id_booking` (`id_booking`),
  KEY `id_ticketType` (`id_ticketType`),
  CONSTRAINT `ticket_ibfk_1` FOREIGN KEY (`id_booking`) REFERENCES `booking` (`kode_booking`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ticket_ibfk_2` FOREIGN KEY (`id_ticketType`) REFERENCES `tickettype` (`id_ticketType`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `ticket` */

/*Table structure for table `tickettype` */

DROP TABLE IF EXISTS `tickettype`;

CREATE TABLE `tickettype` (
  `id_ticketType` int(10) NOT NULL,
  `ticketType` varchar(200) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id_ticketType`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `tickettype` */

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `username` varchar(50) collate latin1_general_ci NOT NULL default '',
  `password` varchar(50) collate latin1_general_ci NOT NULL default '',
  `firstname` varchar(50) collate latin1_general_ci NOT NULL default '',
  `lastname` varchar(50) collate latin1_general_ci NOT NULL,
  `address` varchar(200) collate latin1_general_ci NOT NULL default '',
  `town` varchar(50) collate latin1_general_ci NOT NULL,
  `country` varchar(50) collate latin1_general_ci NOT NULL,
  `postcode` varchar(50) collate latin1_general_ci NOT NULL,
  `email` varchar(200) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `user` */

insert  into `user`(`username`,`password`,`firstname`,`lastname`,`address`,`town`,`country`,`postcode`,`email`) values ('','','','','','','','',''),('solomon','sulaiman','Sulaiman','Abdul Karim','Komp Deppen','Depok','Indonesia','16954','bilasofyan@gmail.com');

/*Table structure for table `venue` */

DROP TABLE IF EXISTS `venue`;

CREATE TABLE `venue` (
  `id_venue` varchar(8) collate latin1_general_ci NOT NULL default '',
  `venueName` varchar(200) collate latin1_general_ci NOT NULL,
  `venueLocation` varchar(200) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id_venue`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `venue` */

insert  into `venue`(`id_venue`,`venueName`,`venueLocation`) values ('JKTHA','Jakarta','Thailand'),('JKTMKSAM','Jakarta','Makassar');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;