11- 13515040 - Patrick Nugroho Hadiwinoto

database/pr_ojek.sql

@@ -3,7 +3,7 @@
 -- https://www.phpmyadmin.net/
 --
 -- Host: 127.0.0.1
--- Generation Time: Oct 07, 2017 at 05:56 AM
+-- Generation Time: Oct 07, 2017 at 08:46 AM
 -- Server version: 10.1.25-MariaDB
 -- PHP Version: 5.6.31
 
@@ -25,41 +25,45 @@ SET time_zone = "+00:00";
 -- --------------------------------------------------------
 
 --
--- Table structure for table `driver_prefloc`
+-- Table structure for table `driver_preferred_locations`
 --
 
-CREATE TABLE `driver_prefloc` (
-  `no` int(4) NOT NULL,
-  `driverID` int(4) NOT NULL,
-  `prefLoc` varchar(20) NOT NULL
+CREATE TABLE `driver_preferred_locations` (
+  `no` int(11) NOT NULL,
+  `driverID` int(11) NOT NULL,
+  `preferred_location` varchar(50) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 --
--- Dumping data for table `driver_prefloc`
+-- Dumping data for table `driver_preferred_locations`
 --
 
-INSERT INTO `driver_prefloc` (`no`, `driverID`, `prefLoc`) VALUES
-(2, 4, 'Bandung');
+INSERT INTO `driver_preferred_locations` (`no`, `driverID`, `preferred_location`) VALUES
+(1, 5, 'Bandung'),
+(2, 5, 'Jakarta');
 
 -- --------------------------------------------------------
 
 --
--- Table structure for table `driver_review`
+-- Table structure for table `driver_reviews`
 --
 
-CREATE TABLE `driver_review` (
-  `driverID` int(4) NOT NULL,
-  `rating` float NOT NULL,
-  `votes` int(4) NOT NULL
+CREATE TABLE `driver_reviews` (
+  `no` int(11) NOT NULL,
+  `driverID` int(11) NOT NULL,
+  `rating` int(11) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 --
--- Dumping data for table `driver_review`
+-- Dumping data for table `driver_reviews`
 --
 
-INSERT INTO `driver_review` (`driverID`, `rating`, `votes`) VALUES
-(4, 3.25, 4),
-(6, 4.2, 1234);
+INSERT INTO `driver_reviews` (`no`, `driverID`, `rating`) VALUES
+(1, 5, 4),
+(2, 3, 3),
+(3, 5, 2),
+(4, 5, 5),
+(5, 3, 3);
 
 -- --------------------------------------------------------
 
@@ -129,7 +133,7 @@ INSERT INTO `user_data` (`ID`, `username`, `password`, `full_name`, `email`, `ph
 (3, 'admin', 'admin', 'admin', 'admin@gmail.com', '085780058876', 'yes', ''),
 (4, 'martin', 'hwhrskxk', 'Martin Lutta Putra', 'asd@gmail.com', '085780058876', 'yes', ''),
 (5, 'lutta', 'hehe', 'Martin Lutta Putra', 'hehe@gmail.com', '085780058876', 'yes', ''),
-(6, 'vinjerdim', 'marvin', 'Marvin Jerremy Budiman', 'vinjerdim@gmail.com', '085780000001', 'yes', ''),
+(6, 'vinjerdim', 'marvin', 'Marvin Jerremy Budiman', 'vinjerdim@gmail.com', '085780000001', 'no', ''),
 (7, 'patricknugrohoh', 'patrick', 'Patrick Nugroho H.', 'patricnugrohoh@gmail', '085780000002', 'yes', '');
 
 --
@@ -137,23 +141,26 @@ INSERT INTO `user_data` (`ID`, `username`, `password`, `full_name`, `email`, `ph
 --
 
 --
--- Indexes for table `driver_prefloc`
+-- Indexes for table `driver_preferred_locations`
 --
-ALTER TABLE `driver_prefloc`
+ALTER TABLE `driver_preferred_locations`
   ADD PRIMARY KEY (`no`),
   ADD KEY `driverID` (`driverID`);
 
 --
--- Indexes for table `driver_review`
+-- Indexes for table `driver_reviews`
 --
-ALTER TABLE `driver_review`
-  ADD PRIMARY KEY (`driverID`);
+ALTER TABLE `driver_reviews`
+  ADD PRIMARY KEY (`no`),
+  ADD KEY `driverID` (`driverID`);
 
 --
 -- Indexes for table `transaction_history`
 --
 ALTER TABLE `transaction_history`
-  ADD PRIMARY KEY (`transID`);
+  ADD PRIMARY KEY (`transID`),
+  ADD KEY `fk_passenger_id` (`passengerID`),
+  ADD KEY `fk_driver_id` (`driverID`);
 
 --
 -- Indexes for table `user_data`
@@ -168,10 +175,15 @@ ALTER TABLE `user_data`
 --
 
 --
--- AUTO_INCREMENT for table `driver_prefloc`
+-- AUTO_INCREMENT for table `driver_preferred_locations`
+--
+ALTER TABLE `driver_preferred_locations`
+  MODIFY `no` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
 --
-ALTER TABLE `driver_prefloc`
-  MODIFY `no` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
+-- AUTO_INCREMENT for table `driver_reviews`
+--
+ALTER TABLE `driver_reviews`
+  MODIFY `no` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
 --
 -- AUTO_INCREMENT for table `transaction_history`
 --
@@ -187,16 +199,23 @@ ALTER TABLE `user_data`
 --
 
 --
--- Constraints for table `driver_prefloc`
+-- Constraints for table `driver_preferred_locations`
 --
-ALTER TABLE `driver_prefloc`
-  ADD CONSTRAINT `driver_prefloc_ibfk_1` FOREIGN KEY (`driverID`) REFERENCES `driver_review` (`driverID`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `driver_preferred_locations`
+  ADD CONSTRAINT `driver_preferred_locations_ibfk_1` FOREIGN KEY (`driverID`) REFERENCES `user_data` (`ID`);
 
 --
--- Constraints for table `driver_review`
+-- Constraints for table `driver_reviews`
+--
+ALTER TABLE `driver_reviews`
+  ADD CONSTRAINT `driver_reviews_ibfk_1` FOREIGN KEY (`driverID`) REFERENCES `user_data` (`ID`);
+
 --
-ALTER TABLE `driver_review`
-  ADD CONSTRAINT `driver_review_ibfk_1` FOREIGN KEY (`driverID`) REFERENCES `user_data` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+-- Constraints for table `transaction_history`
+--
+ALTER TABLE `transaction_history`
+  ADD CONSTRAINT `fk_driver_id` FOREIGN KEY (`driverID`) REFERENCES `user_data` (`ID`),
+  ADD CONSTRAINT `fk_passenger_id` FOREIGN KEY (`passengerID`) REFERENCES `user_data` (`ID`);
 COMMIT;
 
 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;