Question

Using MySQL, you will create data base for given conditions and examine the mysqldump.

•Create the table for the given data structure

PASSENGERS: passengerid INTEGER, name VARCHAR(30), surname VARCHAR(30), email VARCHAR(50), address VARCHAR(100), city VARCHAR(30)

FLT-SCHEDULE: fltno INTEGER, airlinename VARCHAR(50), dtime TIME, from-airportcode VARCHAR(5), atime TIME, to-airportcode VARCHAR(5), miles INTEGER, price INTEGER

FLT-HISTORY: passengerid INTEGER, airlineid INTEGER, fltdate DATE

AIRLINE: airlineid INTEGER, airlinename VARCHAR(50)

-Conditions-

1. For each passengers passengerid is neccessary (primary key). Name, surname, address information, city information, e-mail (must be unique).
2. For each planned flight, there must be unique flight number which is given as fltno (primary key). Airline name, departure time (dtime), name of the airport (from-airportcode), arrival time (atime), name of the airport destination (to-airportcode), journey time in mile and the price of ticket.
3. For the history of flight, for each line, there should be passenger id (passengerid), airline number (airlineid) and flight date (fltdate), passengerid, airlineid and tdate is primary key.
4. Each airline must be different id number and name (airlineid is primary key).

•You can use WAMP server and by using mysqldump command to dump the data base.

•It must be dump document with a .sql extension.

Answer 1

CREATE TABLE PASSENGERS (

passengerid INTEGER PRIMARY KEY,

name VARCHAR(30) UNIQUE,

surname VARCHAR(30) UNIQUE,

email VARCHAR(50) UNIQUE,

address VARCHAR(100) UNIQUE,

city VARCHAR(30) UNIQUE

);

CREATE TABLE FLT_SCHEDULE (

fltno INTEGER PRIMARY KEY,

airlinename VARCHAR(50),

dtime TIME,

from_airportcode VARCHAR(5),

atime TIME,

to_airportcode VARCHAR(5),

miles INTEGER,

price INTEGER

);

CREATE TABLE FLT_HISTORY (

passengerid INTEGER,

airlieneid INTEGER,

fltdate DATE PRIMARY KEY

);

CREATE TABLE AIRLINE (

airlineid INTEGER PRIMARY KEY,

airlinename VARCHAR(50)

);