1
\$\begingroup\$

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:

database relation

This is a database image I created myself in phpmyadmin:

database designer, phpmyadmin

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 have also made relationships between tables.. 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?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.