 |
|
/*
SQLyog Enterprise - MySQL GUI v7.15
MySQL - 5.0.51b-community-nt-log : Database - bus_db
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!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' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`bus_db` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `bus_db`;
/*Table structure for table `tb_bus` */
DROP TABLE IF EXISTS `tb_bus`;
CREATE TABLE `tb_bus` (
`bus_id` int(5) NOT NULL auto_increment,
`bustype_id` int(5) default NULL,
PRIMARY KEY (`bus_id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `tb_bus` */
insert into `tb_bus`(`bus_id`,`bustype_id`) values (1,1),(2,1),(3,1),(4,2),(5,2),(6,2),(7,1),(8,1),(9,2),(10,2),(11,2),(12,2);
/*Table structure for table `tb_busprice` */
DROP TABLE IF EXISTS `tb_busprice`;
CREATE TABLE `tb_busprice` (
`price_id` int(5) NOT NULL auto_increment,
`bustype_id` int(5) default NULL,
`busstop_id` int(5) default NULL,
`price` int(5) default NULL,
PRIMARY KEY (`price_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `tb_busprice` */
insert into `tb_busprice`(`price_id`,`bustype_id`,`busstop_id`,`price`) values (1,1,1,20),(2,1,2,25),(3,1,3,30),(4,2,1,10),(5,2,2,15),(6,2,3,12),(7,3,1,5),(8,3,2,12),(9,3,3,7);
/*Table structure for table `tb_busstop` */
DROP TABLE IF EXISTS `tb_busstop`;
CREATE TABLE `tb_busstop` (
`busstop_id` int(5) NOT NULL auto_increment,
`route_id` int(5) default NULL,
`station_id` int(5) default NULL,
`station_id2` int(5) default NULL,
`point_no` int(3) default NULL,
`distance` int(5) default NULL,
PRIMARY KEY (`busstop_id`),
KEY `FK_tb_busstop` (`route_id`),
KEY `FK_tb_busstop1` (`station_id`),
CONSTRAINT `FK_tb_busstop` FOREIGN KEY (`route_id`) REFERENCES `tb_route` (`route_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_tb_busstop1` FOREIGN KEY (`station_id`) REFERENCES `tb_station` (`station_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `tb_busstop` */
insert into `tb_busstop`(`busstop_id`,`route_id`,`station_id`,`station_id2`,`point_no`,`distance`) values (1,1,1,2,0,5),(2,1,2,8,1,10),(3,1,8,5,2,20),(4,1,5,NULL,3,0),(5,2,1,4,0,10),(6,2,4,5,1,10),(7,2,5,NULL,2,0),(8,3,1,4,0,5),(9,3,4,5,1,15),(10,3,5,3,2,30),(11,3,3,NULL,3,0),(12,4,6,8,0,5),(13,4,8,5,1,10),(14,4,5,NULL,2,0),(15,5,6,10,0,15),(16,5,10,11,1,10),(17,5,11,NULL,2,0);
/*Table structure for table `tb_bustime` */
DROP TABLE IF EXISTS `tb_bustime`;
CREATE TABLE `tb_bustime` (
`time_id` int(5) NOT NULL auto_increment,
`bus_id` int(5) default NULL,
`busstop_id` int(5) default NULL,
`time_travel` time default NULL,
`time_stop` time default NULL,
PRIMARY KEY (`time_id`),
KEY `FK_tb_bus` (`busstop_id`),
CONSTRAINT `FK_tb_bus` FOREIGN KEY (`busstop_id`) REFERENCES `tb_busstop` (`busstop_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `tb_bustime` */
insert into `tb_bustime`(`time_id`,`bus_id`,`busstop_id`,`time_travel`,`time_stop`) values (1,1,1,'05:00:00','06:00:00'),(2,1,2,'06:05:00','08:00:00'),(3,1,3,'08:05:00','09:00:00'),(5,2,1,'10:00:00','11:00:00'),(6,2,2,'11:05:00','12:00:00'),(7,2,3,'12:05:00','13:00:00'),(9,3,1,'14:00:00','15:00:00'),(10,3,2,'15:05:00','16:00:00'),(11,3,3,'16:05:00','17:00:00'),(13,4,5,'04:00:00','05:00:00'),(14,4,6,'05:05:00','06:00:00'),(16,5,5,'05:00:00','06:00:00'),(17,5,6,'06:05:00','07:00:00');
/*Table structure for table `tb_bustype` */
DROP TABLE IF EXISTS `tb_bustype`;
CREATE TABLE `tb_bustype` (
`bustype_id` int(5) NOT NULL auto_increment,
`bustype_name` varchar(50) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`bustype_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `tb_bustype` */
insert into `tb_bustype`(`bustype_id`,`bustype_name`) values (1,'ปรับอากาศชั้น 1'),(2,'ปรับอากาศชั้น 2'),(3,'พัดลมธรรมดา');
/*Table structure for table `tb_provider` */
DROP TABLE IF EXISTS `tb_provider`;
CREATE TABLE `tb_provider` (
`provider_id` int(5) NOT NULL auto_increment,
`provider_name` varchar(50) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`provider_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `tb_provider` */
insert into `tb_provider`(`provider_id`,`provider_name`) values (1,'วาปีเดินรถ'),(2,'ขอนแก่นชัยชนะ'),(3,'โกสุมขนส่ง'),(4,'แสงประทีบ');
/*Table structure for table `tb_route` */
DROP TABLE IF EXISTS `tb_route`;
CREATE TABLE `tb_route` (
`route_id` int(5) NOT NULL auto_increment,
`route_name` varchar(100) collate utf8_unicode_ci default NULL,
`provider_id` int(5) default NULL,
`bustype_id` int(5) default NULL,
PRIMARY KEY (`route_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `tb_route` */
insert into `tb_route`(`route_id`,`route_name`,`provider_id`,`bustype_id`) values (1,'(534)ขอนแก่น-สารคาม (กันทรฯ)',1,1),(2,'(219)ขอนแก่น-สารคาม (โกสุมฯ)',2,2),(3,'(200)ขอนแก่น-ร้อยเอ็ด',3,1),(4,'(242)กาฬสินธุ์-สารคาม',1,2),(5,'(555)กาฬสินธ์ - นครพนม',4,3);
/*Table structure for table `tb_station` */
DROP TABLE IF EXISTS `tb_station`;
CREATE TABLE `tb_station` (
`station_id` int(5) NOT NULL auto_increment,
`station_name` varchar(100) collate utf8_unicode_ci default NULL,
`lat` float(10,6) default NULL,
`lng` float(10,6) default NULL,
PRIMARY KEY (`station_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `tb_station` */
insert into `tb_station`(`station_id`,`station_name`,`lat`,`lng`) values (1,'ขอนแก่น',16.496666,102.785339),(2,'เชียงยืน',16.407846,103.105919),(3,'ร้อยเอ็ด',16.016775,103.647766),(4,'โกสุมพิสัย',16.248545,103.066818),(5,'มหาสารคาม',16.127625,103.314056),(6,'กาฬสินธุ์',16.434027,103.505974),(7,'อุดรธานี',13.974925,100.590607),(8,'กันทรวิชัย',16.259504,103.257751),(9,'กมลาไสย',16.338797,103.575752),(10,'สกลนคร',17.125124,104.053871),(11,'นครพนม',17.379515,104.682152);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
 |
 |
 |
 |
| Date :
2010-07-28 09:54:41 |
By :
wisan191 |
|
 |
 |
 |
 |
|
|
 |
