Embedded Files
SQL Database Creation
Structured Query Language (SQL)
Summary
Based on a provided ERD, I created a database using data definition language code in SQL Developer. Then, I converted the code to match compability with SQL Server to upload the database to Microsoft Azure.
Code
--DROP ALL TABLES
DROP TABLE Pass_Resv_Flight_Linking;
DROP TABLE Reservation;
DROP TABLE Frequent_Flyer_Profile;
DROP TABLE Passenger_Payment;
DROP TABLE Passenger;
DROP TABLE Flight;
DROP TABLE Employee;
--DROP ALL SEQUENCES
DROP SEQUENCE employee_id_seq;
DROP SEQUENCE flight_id_seq;
DROP SEQUENCE passenger_id_seq;
DROP SEQUENCE reservation_id_seq;
DROP SEQUENCE payment_id_seq;
--CREATE ALL SEQUENCES
----Create employee_id Sequence
CREATE SEQUENCE employee_id_seq
START WITH 100001 INCREMENT BY 1;
-- Create flight_id Sequence
CREATE SEQUENCE flight_id_seq
START WITH 1 INCREMENT BY 1;
-- Create passenger_id Sequence
CREATE SEQUENCE passenger_id_seq
START WITH 1 INCREMENT BY 1;
-- Create reservation_id Sequence
CREATE SEQUENCE reservation_id_seq
START WITH 1 INCREMENT BY 1;
-- Create payment_id Sequence
CREATE SEQUENCE payment_id_seq
START WITH 1 INCREMENT BY 1;
--CREATE ALL TABLES
CREATE TABLE Passenger (
Passenger_ID BIGINT default NEXT VALUE FOR passenger_id_seq PRIMARY KEY,
First_Name VARCHAR(50) NOT NULL,
Middle_Name VARCHAR(50),
Last_Name VARCHAR(50) NOT NULL,
Email VARCHAR(50) UNIQUE NOT NULL,
Gender CHAR(1) NOT NULL,
Country_of_Residence VARCHAR(50) NOT NULL,
State_of_Residence CHAR(2) NOT NULL,
Mailing_Address_1 VARCHAR(50) NOT NULL,
Mailing_Address_2 VARCHAR(50),
Mailing_City VARCHAR(50) NOT NULL,
Mailing_State CHAR(2) NOT NULL,
Mailing_Zip CHAR(5) NOT NULL,
Primary_Phone CHAR(12) NOT NULL,
Secondary_Phone CHAR(12),
CONSTRAINT EMAIL_LENGTH_CHECK CHECK (LEN(Email) >= 7)
);
CREATE TABLE Frequent_Flyer_Profile (
Passenger_ID BIGINT PRIMARY KEY,
Frequent_Flyer_ID VARCHAR(8) UNIQUE NOT NULL,
FF_Password VARCHAR(30) NOT NULL,
FF_Level VARCHAR(2) NOT NULL,
Miles_Balance BIGINT DEFAULT 5000 NOT NULL,
CONSTRAINT frequent_flyer_fk FOREIGN KEY (Passenger_ID) REFERENCES Passenger (Passenger_ID),
CONSTRAINT FF_LEVEL_CHECK CHECK (FF_Level IN ('B','S','G','P'))
);
CREATE TABLE Reservation (
Reservation_ID BIGINT default NEXT VALUE FOR reservation_id_seq PRIMARY KEY,
Confirmation_Number VARCHAR(6) unique NOT NULL,
Date_Booked DATE default SYSDATETIME() NOT NULL,
Trip_Contact_Email VARCHAR(50) NOT NULL,
Trip_Contact_Phone VARCHAR(12) NOT NULL
);
CREATE TABLE Employee (
Employee_ID BIGINT default NEXT VALUE FOR employee_id_seq PRIMARY KEY,
First_Name VARCHAR(50) NOT NULL,
Last_Name VARCHAR(50) NOT NULL,
Birthday DATE NOT NULL,
Tax_ID_Number VARCHAR(9) UNIQUE NOT NULL,
Mailing_Address VARCHAR(50) NOT NULL,
Mailing_City VARCHAR(50) NOT NULL,
Mailing_State CHAR(2) NOT NULL,
Mailing_Zip CHAR(5) NOT NULL,
Emp_Level CHAR(1) NOT NULL,
constraint employee_level_check CHECK (Emp_Level in (1,2,3))
);
CREATE TABLE Flight (
Flight_ID BIGINT default NEXT VALUE FOR flight_id_seq PRIMARY KEY,
Flight_Number VARCHAR(4) NOT NULL,
Departure_DateTime DATETIME NOT NULL,
Departure_City CHAR(3) NOT NULL,
Arrival_City CHAR(3) NOT NULL,
Assigned_Employee BIGINT,
CONSTRAINT employee_id_fk FOREIGN KEY (Assigned_Employee) REFERENCES employee (employee_id)
);
CREATE TABLE Passenger_Payment (
Payment_ID BIGINT default NEXT VALUE FOR payment_id_seq PRIMARY KEY,
Passenger_ID BIGINT NOT NULL,
Cardholder_First_Name VARCHAR(40) NOT NULL,
Cardholder_Mid_Name VARCHAR(40),
Cardholder_Last_Name VARCHAR(40) NOT NULL,
CardType VARCHAR(20) NOT NULL,
CardNumber VARCHAR(16) NOT NULL,
Expiration_Date DATE NOT NULL,
CC_ID VARCHAR(4) NOT NULL,
Billing_Address VARCHAR(50) NOT NULL,
Billing_City VARCHAR(50) NOT NULL,
Billing_State CHAR(2) NOT NULL,
Billing_Zip CHAR(5) NOT NULL,
CONSTRAINT passenger_id_fk FOREIGN KEY (Passenger_ID) REFERENCES Passenger (Passenger_ID)
);
CREATE TABLE Pass_Resv_Flight_Linking (
Passenger_ID BIGINT,
Reservation_ID BIGINT,
Flight_ID BIGINT,
Seat_Assignment VARCHAR(4),
Ticket_Number VARCHAR(6),
Checked_In_Flag CHAR(1) default 'N' NOT NULL,
Boarded_Flag CHAR(1) default 'N' NOT NULL,
CONSTRAINT resv_flight_pk PRIMARY KEY (Passenger_ID,Reservation_ID, Flight_ID),
CONSTRAINT passenger_id_link_fk FOREIGN KEY (Passenger_ID) REFERENCES Passenger (Passenger_ID),
CONSTRAINT reservation_id_link_fk FOREIGN KEY (Reservation_ID) REFERENCES reservation (Reservation_ID),
CONSTRAINT flight_id_link_fk FOREIGN KEY (Flight_ID) REFERENCES flight (Flight_ID),
CONSTRAINT y_or_n_flag_check CHECK (Checked_In_Flag in ('N','Y'))
);
--INSERTING FAKE DATA INTO TABLES
--EMPLOYEE DATA
BEGIN TRAN;
INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, BIRTHDAY, TAX_ID_NUMBER, MAILING_ADDRESS, MAILING_CITY, MAILING_STATE, MAILING_ZIP, EMP_LEVEL)
VALUES (100001,'James','Baloney','01-Jan-1970','123456789', '10 Westoaks Street','San Antonio','TX','10021','2');
INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, BIRTHDAY, TAX_ID_NUMBER, MAILING_ADDRESS, MAILING_CITY, MAILING_STATE, MAILING_ZIP, EMP_LEVEL)
VALUES (100002,'Dawn','Carbana','20-Mar-1989','987654321', '22 Alaska Avenue','San Antonio','TX','10245','1');
INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, BIRTHDAY, TAX_ID_NUMBER, MAILING_ADDRESS, MAILING_CITY, MAILING_STATE, MAILING_ZIP, EMP_LEVEL)
VALUES (100003,'Ego','Fridge','25-May-1973','832173912', '25 Eros Lane','El Paso','TX','10932','2');
INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, BIRTHDAY, TAX_ID_NUMBER, MAILING_ADDRESS, MAILING_CITY, MAILING_STATE, MAILING_ZIP, EMP_LEVEL)
VALUES (100004,'Ice','Scream','30-Jun-1989','382913281', '30 Eggo Street','El Paso','TX','10238','1');
INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, BIRTHDAY, TAX_ID_NUMBER, MAILING_ADDRESS, MAILING_CITY, MAILING_STATE, MAILING_ZIP, EMP_LEVEL)
VALUES (100005,'Arnold','Onions','5-May-1978','213901321', '89 Brownie Lane','San Diego','CA','39012','2');
INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, BIRTHDAY, TAX_ID_NUMBER, MAILING_ADDRESS, MAILING_CITY, MAILING_STATE, MAILING_ZIP, EMP_LEVEL)
VALUES (100006,'Charlie','Door','29-Sep-1988','210932123', '90 Sundae Avenue','San Diego','CA','32109','1');
COMMIT TRAN;
--FLIGHT DATA
BEGIN TRAN;
INSERT INTO FLIGHT (FLIGHT_ID, FLIGHT_NUMBER, DEPARTURE_DATETIME, DEPARTURE_CITY, ARRIVAL_CITY, ASSIGNED_EMPLOYEE)
VALUES (1,'231',CAST('2024-06-01 6:30 AM' AS DATETIME),'SAT','ELP',100001);
INSERT INTO FLIGHT (FLIGHT_ID, FLIGHT_NUMBER, DEPARTURE_DATETIME, DEPARTURE_CITY, ARRIVAL_CITY, ASSIGNED_EMPLOYEE)
VALUES (2,'232',CAST('2024-06-05 7:45 AM' AS DATETIME),'ELP','SAN',100002);
INSERT INTO FLIGHT (FLIGHT_ID, FLIGHT_NUMBER, DEPARTURE_DATETIME, DEPARTURE_CITY, ARRIVAL_CITY, ASSIGNED_EMPLOYEE)
VALUES (3,'451',CAST('2024-06-10 8:30 AM' AS DATETIME),'SAN','ELP',100003);
INSERT INTO FLIGHT (FLIGHT_ID, FLIGHT_NUMBER, DEPARTURE_DATETIME, DEPARTURE_CITY, ARRIVAL_CITY, ASSIGNED_EMPLOYEE)
VALUES (4,'452',CAST('2024-06-15 10:35 AM' AS DATETIME),'ELP','SAT',100004);
INSERT INTO FLIGHT (FLIGHT_ID, FLIGHT_NUMBER, DEPARTURE_DATETIME, DEPARTURE_CITY, ARRIVAL_CITY, ASSIGNED_EMPLOYEE)
VALUES (5,'231',CAST('2024-06-02 6:30 AM' AS DATETIME),'SAT','ELP',100005);
INSERT INTO FLIGHT (FLIGHT_ID, FLIGHT_NUMBER, DEPARTURE_DATETIME, DEPARTURE_CITY, ARRIVAL_CITY, ASSIGNED_EMPLOYEE)
VALUES (6,'232',CAST('2024-06-02 8:00 AM' AS DATETIME),'ELP','SAN',100006);
INSERT INTO FLIGHT (FLIGHT_ID, FLIGHT_NUMBER, DEPARTURE_DATETIME, DEPARTURE_CITY, ARRIVAL_CITY, ASSIGNED_EMPLOYEE)
VALUES (7,'451',CAST('2024-06-02 7:00 AM' AS DATETIME),'SAN','ELP',100001);
INSERT INTO FLIGHT (FLIGHT_ID, FLIGHT_NUMBER, DEPARTURE_DATETIME, DEPARTURE_CITY, ARRIVAL_CITY, ASSIGNED_EMPLOYEE)
VALUES (8,'452',CAST('2024-06-02 6:30 AM' AS DATETIME),'ELP','SAT',100002);
COMMIT TRAN;
--PASSENGER DATA
BEGIN TRAN;
INSERT INTO PASSENGER (passenger_id, first_name, middle_name, last_name, email, gender, country_of_residence, state_of_residence, mailing_address_1, mailing_address_2, mailing_city, mailing_state, mailing_zip, primary_phone, secondary_phone)
VALUES (20,'Austin','','Nguyen','austin@nguyen.com','M','United States','TX','123 Nowhere Avenue','','Austin','TX','71239','123456789','');
INSERT INTO PASSENGER (passenger_id, first_name, middle_name, last_name, email, gender, country_of_residence, state_of_residence, mailing_address_1, mailing_address_2, mailing_city, mailing_state, mailing_zip, primary_phone, secondary_phone)
VALUES (40,'Howdy','','Richard','nobody@somebody.com','M','United States','TX','982 Somewhere Lane','','Austin','TX','98231','987654321','');
COMMIT TRAN;
--FREQUENT FLYER PROFILE DATA
BEGIN TRAN;
INSERT INTO frequent_flyer_profile(passenger_id,frequent_flyer_id,ff_password,ff_level,miles_balance)
VALUES (20,89123,'cocacola','B',20172);
INSERT INTO frequent_flyer_profile(passenger_id,frequent_flyer_id,ff_password,ff_level,miles_balance)
VALUES (40,90123,'pepsi','S',30192);
COMMIT TRAN;
--PASSENGER PAYMENT DATA
BEGIN TRAN;
INSERT INTO passenger_payment(payment_id,passenger_id,cardholder_first_name,cardholder_mid_name,cardholder_last_name,cardtype,cardnumber,expiration_date,cc_id,billing_address,billing_city,billing_state,billing_zip)
VALUES (092,20,'Austin','','Nguyen','AMEX','9182759384920192','01-Jan-2027',1928,'123 Nowhere Avenue','Austin','TX','71239');
INSERT INTO passenger_payment(payment_id,passenger_id,cardholder_first_name,cardholder_mid_name,cardholder_last_name,cardtype,cardnumber,expiration_date,cc_id,billing_address,billing_city,billing_state,billing_zip)
VALUES (095,20,'Austin','','Nguyen','Mastercard','9101928321920192','25-May-2027',1928,'821 Business Lane','Austin','TX','73291');
INSERT INTO passenger_payment(payment_id,passenger_id,cardholder_first_name,cardholder_mid_name,cardholder_last_name,cardtype,cardnumber,expiration_date,cc_id,billing_address,billing_city,billing_state,billing_zip)
VALUES (291,40,'Howdy','','Richard','Visa','7285938275928106','26-May-2028',2918,'982 Somewhere Lane','Austin','TX','98231');
COMMIT TRAN;
--RESERVATION DATA
BEGIN TRAN;
INSERT INTO reservation(reservation_id,confirmation_number,date_booked,trip_contact_email,trip_contact_phone)
VALUES (873,182938,'01-Jun-2024','austin@nguyen.com','123456789');
INSERT INTO reservation(reservation_id,confirmation_number,date_booked,trip_contact_email,trip_contact_phone)
VALUES (184,129381,'01-Jun-2024','nobody@somebody.com','987654321');
COMMIT TRAN;
--PASSENGER, RESERVATION, AND FLIGHT DATA
BEGIN TRAN;
INSERT INTO pass_resv_flight_linking(passenger_id,reservation_id,flight_id,seat_assignment,ticket_number,checked_in_flag,boarded_flag)
VALUES (20,873,1,'5A','029181','N','N');
INSERT INTO pass_resv_flight_linking(passenger_id,reservation_id,flight_id,seat_assignment,ticket_number,checked_in_flag,boarded_flag)
VALUES (20,873,2,'1B','029381','N','N');
INSERT INTO pass_resv_flight_linking(passenger_id,reservation_id,flight_id,seat_assignment,ticket_number,checked_in_flag,boarded_flag)
VALUES (20,873,3,'2C','958395','N','N');
INSERT INTO pass_resv_flight_linking(passenger_id,reservation_id,flight_id,seat_assignment,ticket_number,checked_in_flag,boarded_flag)
VALUES (20,873,4,'2D','492811','N','N');
INSERT INTO pass_resv_flight_linking(passenger_id,reservation_id,flight_id,seat_assignment,ticket_number,checked_in_flag,boarded_flag)
VALUES (40,184,5,'5B','293812','N','N');
INSERT INTO pass_resv_flight_linking(passenger_id,reservation_id,flight_id,seat_assignment,ticket_number,checked_in_flag,boarded_flag)
VALUES (40,184,6,'1C','884722','N','N');
INSERT INTO pass_resv_flight_linking(passenger_id,reservation_id,flight_id,seat_assignment,ticket_number,checked_in_flag,boarded_flag)
VALUES (40,184,7,'2A','298844','N','N');
INSERT INTO pass_resv_flight_linking(passenger_id,reservation_id,flight_id,seat_assignment,ticket_number,checked_in_flag,boarded_flag)
VALUES (40,184,8,'2C','944423','N','N');
COMMIT TRAN;
--CREATING ALL INDEXES
CREATE INDEX passenger_payment_passenger_id_ix
ON passenger_payment (passenger_id);
CREATE INDEX flight_assigned_employee_ix
ON flight (assigned_employee);
CREATE INDEX flight_departure_city_ix
ON flight (departure_city);
CREATE INDEX flight_arrival_city_ix
ON flight (arrival_city);
CREATE INDEX passenger_mailing_zip_ix
ON passenger (mailing_zip);
CREATE INDEX passenger_primary_phone_ix
ON passenger (primary_phone);
Entity Relationship Diagram (ERD)
Project Showcase
Creating the Database
Checking Employee Table
Checking Passenger Table
Checking Flight Table
Running a Simple Query
Work, projects, and text pieces displayed on this website are
intellectual property of Austin Nguyen. For commercial or academic use,
please contact austin.usbusiness@gmail.com.Established in 2024
Google Sites
Report abuse