Please help me to create the tables from these database fields, denote the prima

Question

Please help me to create the tables from these database fields, denote the primary key and foreign key in each table. I was also asked to make sure each table had 5 rows of data, it can be fictional but it has to make sense with the table. This is all in SQL

TRAIN (table) TRAIN_ID TRAIN_NAME TRAIN_SCHEDULE (table) TRAIN_ID STATION_ID ARRIVAL_TIME DEPARTURE_TIME TRAIN_SEATS (table) SEAT_ID SEAT_NUMBER TRAIN_ID STATION (table) STATION_ID STATION_NAME STATION_LOCATION PASSENGER (table) PASSENGER_ID PASSENGER_FIRSTNAME PASSENGER_LASTNAME ADDRESS PHONE_NUMBER RESERVATION (table) RESERVATION_ID PASSENGER_ID DATE_OF_TRAVEL STATION_ID SEAT_RESERVATION (table) SEAT_ID RESERVATION_ID

Explanation / Answer

CREATE TABLE TRAIN

(

TRAIN_ID int NOT NULL PRIMARY KEY,
TRAIN_Name varchar(255) NOT NULL
);

CREATE TABLE TRAIN_SCHEDULE
(
TRAIN_ID int NOT NULL PRIMARY KEY,
STATION_ID varchar(255) NOT NULL FOREIGN KEY,
ARRIVAL_TIME time NOT NULL,
DEPARTURE_TIME time NOT NULL
);

CREATE TABLE TRAIN_SEATS
(
SEAT_ID int NOT NULL PRIMARY KEY,
SEAT_NUMBER int NOT NULL,
TRAIN_ID int NOT NULL FOREIGN KEY,
);

CREATE TABLE STATION
(
STATION_ID varchar(255) NOT NULL PRIMARY KEY,
STATION_NAME varchar(255) NOT NULL,
STATION_LOCATION varchar(255) NOT NULL,
);

CREATE TABLE PASSENGER
(
PASSENGER_ID int NOT NULL PRIMARY KEY,
PASSENGER_FIRSTNAME varchar(255) NOT NULL,
PASSENGER_LASTNAME varchar(255),
ADDRESS varchar(255) ,
PHONE_NUMBER int
);

CREATE TABLE RESERVATION
(
RESERVATION_ID int NOT NULL PRIMARY KEY
PASSENGER_ID int NOT NULL FOREIGN KEY,
DATE_OF_TRAVEL date NOT NULL,
STATION_ID varchar(255) NOT NULL,
);

CREATE TABLE SEAT_RESERVATION
(
SEAT_ID int NOT NULL PRIMARY KEY
RESERVATION_ID int NOT NULL FOREIGN KEY
);

INSERT INTO TRAIN (TRAIN_ID, TRAIN_NAME) VALUES (12345, 'JAIPUR EXPRESS');

INSERT INTO TRAIN (TRAIN_ID, TRAIN_NAME) VALUES (12346, 'DELHI EXPRESS');

INSERT INTO TRAIN (TRAIN_ID, TRAIN_NAME) VALUES (12347, 'MUMBAI EXPRESS');

INSERT INTO TRAIN (TRAIN_ID, TRAIN_NAME) VALUES (12348, 'CHENNAI EXPRESS');

INSERT INTO TRAIN (TRAIN_ID, TRAIN_NAME) VALUES (12349, 'KOLKUTTA EXPRESS');

INSERT INTO TRAIN_SCHEDULE (TRAIN_ID, STATION_ID, ARRIVAL_TIME, DEPARTURE_TIME) VALUES (12345, 'JP', '15:10','15:25')

INSERT INTO TRAIN_SCHEDULE (TRAIN_ID, STATION_ID, ARRIVAL_TIME, DEPARTURE_TIME) VALUES (12345, 'GUR', '15:40','15:55')

INSERT INTO TRAIN_SCHEDULE (TRAIN_ID, STATION_ID, ARRIVAL_TIME, DEPARTURE_TIME) VALUES (12345, 'API', '16:10','16:25')

INSERT INTO TRAIN_SCHEDULE (TRAIN_ID, STATION_ID, ARRIVAL_TIME, DEPARTURE_TIME) VALUES (12345, 'NDI', '17:10','17:25')

INSERT INTO TRAIN_SCHEDULE (TRAIN_ID, STATION_ID, ARRIVAL_TIME, DEPARTURE_TIME) VALUES (12345, 'NDLS', '18:10','19:25')

INSERT INTO TRAIN_SEATS (SEAT_ID, SEAT_NUMBER, TRAIN_ID) VALUES (1001,23,12345)

INSERT INTO TRAIN_SEATS (SEAT_ID, SEAT_NUMBER, TRAIN_ID) VALUES (1002,24,12345)

INSERT INTO TRAIN_SEATS (SEAT_ID, SEAT_NUMBER, TRAIN_ID) VALUES (1003,25,12345)

INSERT INTO TRAIN_SEATS (SEAT_ID, SEAT_NUMBER, TRAIN_ID) VALUES (1004,26,12345)

INSERT INTO TRAIN_SEATS (SEAT_ID, SEAT_NUMBER, TRAIN_ID) VALUES (1005,27,12345)

INSERT INTO STATION (STATION_ID, STATION_NAME, STATION_LOCATION) VALUES ('JP','JAIPUR',JAIPUR')

INSERT INTO STATION (STATION_ID, STATION_NAME, STATION_LOCATION) VALUES ('NDLS','NEW DELHI','NEW DELHI')

INSERT INTO STATION (STATION_ID, STATION_NAME, STATION_LOCATION) VALUES ('BCT','MUMBAI CENTRAL,'MUMBAI')

INSERT INTO STATION (STATION_ID, STATION_NAME, STATION_LOCATION) VALUES ('CUR','CHURU','CHURU')

INSERT INTO STATION (STATION_ID, STATION_NAME, STATION_LOCATION) VALUES ('BUB','BISSAU','BISSAU')

INSERT INTO PASSENGER (PASSENGER_ID, PASSENGER_FIRSTNAME) VALUES (56456, 'SOMPRAKASH')

INSERT INTO PASSENGER (PASSENGER_ID, PASSENGER_FIRSTNAME) VALUES (54545, 'SOM')

INSERT INTO PASSENGER (PASSENGER_ID, PASSENGER_FIRSTNAME) VALUES (45545, 'SOMPRAKASHGUPTA')

INSERT INTO PASSENGER (PASSENGER_ID, PASSENGER_FIRSTNAME) VALUES (54454, 'GUNJAN')

INSERT INTO PASSENGER (PASSENGER_ID, PASSENGER_FIRSTNAME) VALUES (54699, 'GG')

INSERT INTO RESERVATION (RESERVATION_ID, PASSENGER_ID, DATE_OF_TRAVEL, STATION_ID) VALUES (1004,54699,'12-03-2015','JP')

INSERT INTO RESERVATION (RESERVATION_ID, PASSENGER_ID, DATE_OF_TRAVEL, STATION_ID) VALUES (1003,54699,'23-06-2015','JP')

INSERT INTO RESERVATION (RESERVATION_ID, PASSENGER_ID, DATE_OF_TRAVEL, STATION_ID) VALUES (1002,54699,'25-09-2015','ADI')

INSERT INTO RESERVATION (RESERVATION_ID, PASSENGER_ID, DATE_OF_TRAVEL, STATION_ID) VALUES (1001,54699,'24-12-2015','ADI')

INSERT INTO RESERVATION (RESERVATION_ID, PASSENGER_ID, DATE_OF_TRAVEL, STATION_ID) VALUES (1005,54699,'12-03-2016','JP')

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---