How to insert dummy data into MySQL?

Question

I have been given a college project where I have to create a database with some choices. I have selected to create a database for a flight booking system. It's very basic.

However, I am inserting dummy data and for one of the columns, FlightNumber, I will need it to be the same for more than one input. Is there any way of doing this. I will also need to have the values in other tables.

For example, FLNum is a primary key in flights and is a foreign key in passengers. Are there any websites that can do this?

Update: Needs to be 100000 records

Table: passengers

BookRef (PK)
Fname
Lname
Gender
Date_Depart
Date_Return
FLNum (FK)

Table: flights

FLNum (PK)
Date_Depart
Tail_No
CID (FK)
FID (FK)

Table: Captains

CID (PK)
Fname
Lname

Table: First_Officers

FID (PK)
Fname
Lname

Sample Value for passengers:

BookRef (PK) 4MTH2R
Fname  Hortense
Lname Jacqueminot
Gender Female
Date_Depart 2017-12-26
Date_Return 2017-12-31
FLNum (FK) KQU59GS

Answer 1

Overview

We have a problem here in that we do not want the records to be completely random (e.g. Captain Etaoin Asdfsdasdf departing on 13-09-1045). We will therefore declare FLNum as integer, primary key, auto_increment in MySQL, and maybe alter the table definition to start at 1000000 instead of 1 (just to have all numbers with more or less the same number of digits). But this is small potatoes.

Then we want the departure date in a given range, and the same thing for the tail number and the captains and first officers.

So we start estimating the cardinality of our tables. We want one hundred thousand flights, it seems reasonable to do this with one thousand planes, which means let's say two hundred Captains and as many First Officers.

We then need an auxiliary ("seed") table with names and surnames, and we can find it on GitHub. We will build a XName and a XSurname table.

Captains and First Officers

We want 200 Captains, so more or less:

INSERT INTO Captains (`name`,`surname`)
    SELECT `name`, `surname` FROM XNames JOIN XSurnames 
    ORDER BY RAND() LIMIT 200;

The above will probably require a long time to run if ran against a Names/Surnames table in the tens of thousands, so you might want to first create two temporary tables with, say, two hundred names and two hundred surnames by running

CREATE TEMPORARY TABLE tbl1 AS 
    SELECT * FROM XSurnames 
    ORDER BY RAND() LIMIT 200;

and then join the two temporary tables (200*200 gives 40000, which helps keeping fully duplicate names low. You will then SELECT 200 entries from these 40k).

Departure dates

We create a new table DepartureDates with only one column, DepartureDate. Then we insert a single date.

INSERT INTO DepartureDates (DepartureDate) VALUES ('2017-12-01');

Then we insert the day after:

INSERT INTO DepartureDates 
    SELECT DATE_ADD(DepartureDate, INTERVAL 1 DAY) FROM DepartureDates;

There are now 2 dates in the table (let's call them 0 and 1). The command below will then select 0 and 1 and add 2 and 3:

INSERT INTO DepartureDates 
    SELECT DATE_ADD(DepartureDate, INTERVAL 2 DAY) FROM DepartureDates;

Running the command again with INTERVAL 4 DAY will enter other four dates. With the fourth command and 8 DAY, we enter other 8 dates and have now sixteen days. The fifth command brings the total to thirty-two days. With just eight commands you can enter as many months' worth of dates.

Flights

Now that you have the dates and the flights (other data such as airports is similar) you run the input:

CREATE PROCEDURE populate()
BEGIN
    DECLARE i int DEFAULT 1;
    WHILE i <= 100000 DO
        INSERT INTO flights (`date`, `cid`, `fid`, whatever)
             VALUES (
            (SELECT DepartureDate FROM DepartureDates ORDER BY RAND() LIMIT 1) AS `date`,
            (SELECT cid FROM Captains ORDER BY RAND() LIMIT 1) AS cid,                
            (SELECT cid FROM FirstOfficers ORDER BY RAND() LIMIT 1) AS fid,
            ... et cetera ...
        );
        SET i = i + 1;
    END WHILE;
END 

Then, CALL populate(); will (after a good long while) get you the required data. The auto-increment will take care of FLNum.

===

(Or if you can run a script in bash, PHP, Perl or python, I can write you a simpler generator)

Answer 2

You can use this PHP library.This PHP library can generate fake data for you.