I’ve sorted out an image relational database, and created its original database in phpmyadmin (I use XAMPP).
here is a picture of the database relation
and this is a database image I created myself in phpmyadmin.
this for its sql file
-- phpMyAdmin SQL Dump -- version 4.7.4 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Jan 25, 2018 at 10:01 AM -- Server version: 10.1.28-MariaDB -- PHP Version: 5.6.32 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; /*!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 utf8mb4 */; -- -- Database: `ticketing` -- -- -------------------------------------------------------- -- -- Table structure for table `airplane` -- CREATE TABLE `airplane` ( `id` int(11) NOT NULL, `aircraft_type` varchar(30) NOT NULL, `eco_seat_num` int(3) NOT NULL, `business_seat_num` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `airport` -- CREATE TABLE `airport` ( `id` int(11) NOT NULL, `code` varchar(20) NOT NULL, `city` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `booking` -- CREATE TABLE `booking` ( `id` int(11) NOT NULL, `booking_code` int(11) NOT NULL, `customer` int(11) NOT NULL, `booking_date` date NOT NULL, `status` varchar(20) NOT NULL, `payment_status` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `customer` -- CREATE TABLE `customer` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL, `email` varchar(20) NOT NULL, `city` varchar(20) NOT NULL, `country` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `det_booking` -- CREATE TABLE `det_booking` ( `id` int(11) NOT NULL, `travel_no` int(11) NOT NULL, `status` varchar(20) NOT NULL, `fare_id` int(11) NOT NULL, `booking_code` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `flight` -- CREATE TABLE `flight` ( `id` int(11) NOT NULL, `flight_number` varchar(20) NOT NULL, `from` varchar(20) NOT NULL, `dest` varchar(20) NOT NULL, `depart_time` date NOT NULL, `arriv_time` date NOT NULL, `airplane_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `flight_fare` -- CREATE TABLE `flight_fare` ( `id` int(11) NOT NULL, `class` varchar(20) NOT NULL, `flight_number` varchar(20) NOT NULL, `fare` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `passenger` -- CREATE TABLE `passenger` ( `id` int(11) NOT NULL, `book_det` int(11) NOT NULL, `name` varchar(20) NOT NULL, `id_no` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `train` -- CREATE TABLE `train` ( `id` int(11) NOT NULL, `train_name` varchar(20) NOT NULL, `eco_seat_num` varchar(20) NOT NULL, `busines_seat_num` varchar(20) NOT NULL, `exec_seat_num` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `train_fare` -- CREATE TABLE `train_fare` ( `id` int(11) NOT NULL, `class` varchar(20) NOT NULL, `train_no` varchar(20) NOT NULL, `fare` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `train_journey` -- CREATE TABLE `train_journey` ( `id` int(11) NOT NULL, `depart_station` varchar(20) NOT NULL, `arival_station` varchar(20) NOT NULL, `train_no` varchar(20) NOT NULL, `depart_time` date NOT NULL, `arrival_time` date NOT NULL, `train_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `train_route` -- CREATE TABLE `train_route` ( `id` int(11) NOT NULL, `train_no` varchar(20) NOT NULL, `start_route` varchar(20) NOT NULL, `end_route` varchar(20) NOT NULL, `route_seq` int(11) NOT NULL, `depart_time` date NOT NULL, `arrival_time` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `train_station` -- CREATE TABLE `train_station` ( `id` int(11) NOT NULL, `code` varchar(20) NOT NULL, `city` varchar(20) NOT NULL, `name` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for dumped tables -- -- -- Indexes for table `airplane` -- ALTER TABLE `airplane` ADD PRIMARY KEY (`id`); -- -- Indexes for table `airport` -- ALTER TABLE `airport` ADD PRIMARY KEY (`id`), ADD KEY `code` (`code`); -- -- Indexes for table `booking` -- ALTER TABLE `booking` ADD PRIMARY KEY (`id`), ADD KEY `customer` (`customer`), ADD KEY `booking_code` (`booking_code`); -- -- Indexes for table `customer` -- ALTER TABLE `customer` ADD PRIMARY KEY (`id`), ADD KEY `id` (`id`); -- -- Indexes for table `det_booking` -- ALTER TABLE `det_booking` ADD PRIMARY KEY (`id`), ADD KEY `booking_code` (`booking_code`), ADD KEY `fare_id` (`fare_id`), ADD KEY `travel_no` (`travel_no`); -- -- Indexes for table `flight` -- ALTER TABLE `flight` ADD PRIMARY KEY (`id`), ADD KEY `flight_number` (`flight_number`), ADD KEY `airplane_id` (`airplane_id`), ADD KEY `from` (`from`), ADD KEY `dest` (`dest`); -- -- Indexes for table `flight_fare` -- ALTER TABLE `flight_fare` ADD PRIMARY KEY (`id`), ADD KEY `flight_number` (`flight_number`); -- -- Indexes for table `passenger` -- ALTER TABLE `passenger` ADD PRIMARY KEY (`id`), ADD KEY `book_det` (`book_det`); -- -- Indexes for table `train` -- ALTER TABLE `train` ADD PRIMARY KEY (`id`); -- -- Indexes for table `train_fare` -- ALTER TABLE `train_fare` ADD KEY `id` (`id`), ADD KEY `train_no` (`train_no`); -- -- Indexes for table `train_journey` -- ALTER TABLE `train_journey` ADD PRIMARY KEY (`id`), ADD KEY `train_id` (`train_id`), ADD KEY `train_id_2` (`train_id`), ADD KEY `train_no` (`train_no`), ADD KEY `depart_station` (`depart_station`), ADD KEY `arival_station` (`arival_station`), ADD KEY `train_no_2` (`train_no`); -- -- Indexes for table `train_route` -- ALTER TABLE `train_route` ADD PRIMARY KEY (`id`), ADD KEY `train_no` (`train_no`); -- -- Indexes for table `train_station` -- ALTER TABLE `train_station` ADD PRIMARY KEY (`id`), ADD KEY `code` (`code`); -- -- Constraints for dumped tables -- -- -- Constraints for table `booking` -- ALTER TABLE `booking` ADD CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`customer`) REFERENCES `customer` (`id`); -- -- Constraints for table `det_booking` -- ALTER TABLE `det_booking` ADD CONSTRAINT `det_booking_ibfk_1` FOREIGN KEY (`booking_code`) REFERENCES `booking` (`booking_code`), ADD CONSTRAINT `det_booking_ibfk_2` FOREIGN KEY (`id`) REFERENCES `passenger` (`book_det`), ADD CONSTRAINT `det_booking_ibfk_3` FOREIGN KEY (`fare_id`) REFERENCES `flight_fare` (`id`), ADD CONSTRAINT `det_booking_ibfk_4` FOREIGN KEY (`travel_no`) REFERENCES `train_route` (`id`); -- -- Constraints for table `flight` -- ALTER TABLE `flight` ADD CONSTRAINT `flight_ibfk_1` FOREIGN KEY (`from`) REFERENCES `airport` (`code`), ADD CONSTRAINT `flight_ibfk_2` FOREIGN KEY (`dest`) REFERENCES `airport` (`code`), ADD CONSTRAINT `flight_ibfk_3` FOREIGN KEY (`airplane_id`) REFERENCES `airplane` (`id`); -- -- Constraints for table `flight_fare` -- ALTER TABLE `flight_fare` ADD CONSTRAINT `flight_fare_ibfk_1` FOREIGN KEY (`flight_number`) REFERENCES `flight` (`flight_number`); -- -- Constraints for table `train_fare` -- ALTER TABLE `train_fare` ADD CONSTRAINT `train_fare_ibfk_1` FOREIGN KEY (`train_no`) REFERENCES `train_journey` (`train_no`); -- -- Constraints for table `train_journey` -- ALTER TABLE `train_journey` ADD CONSTRAINT `train_journey_ibfk_1` FOREIGN KEY (`train_id`) REFERENCES `train` (`id`), ADD CONSTRAINT `train_journey_ibfk_2` FOREIGN KEY (`depart_station`) REFERENCES `train_station` (`code`), ADD CONSTRAINT `train_journey_ibfk_3` FOREIGN KEY (`arival_station`) REFERENCES `train_station` (`code`); -- -- Constraints for table `train_route` -- ALTER TABLE `train_route` ADD CONSTRAINT `train_route_ibfk_1` FOREIGN KEY (`train_no`) REFERENCES `train_journey` (`train_no`); COMMIT; /*!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 */;
I have set the foreign key and its primary key, I also have made relationships between tables. which I want to ask, is the relation of my database correct? are there tables that should not be related or otherwise? or is there a relation between the columns is less appropriate? Thank you in advance