Skip to content

A project to design a fact and dimension star schema for optimizing queries on a flight booking database using PostgreSQL, a relational database management system. This schema is well-suited for a flight booking database, as it allows for efficient querying of data such as booking dates, flight routes, and passenger information.

License

Notifications You must be signed in to change notification settings

saboye/Data-Modeling-with-Postgres

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

30 Commits
 
 
 
 
 
 
 
 

Repository files navigation

GitHub contributors GitHub forks GitHub issues GitHub GitHub last commit


Logo

Data Modeling with Postgres

project description
Explore the docs »

View Demo · Report Bug · Request Feature

Table of contents

  1. About The Project
  2. List of DB Tables
  3. Metadata
  4. Fact and Dimensional tables
  5. Installation
  6. License
  7. Contact
  8. Acknowledgements

About The Project

In this project, I am creating a database schema using a Postgre Relational database. The project tasks need to define fact and dimension tables for a star schema for a particular analytic focus and answer the business questions using PostgreSQL.

To see the online version of the entity relationship diagram (ERD) Click here

List of Tables

Name Type Small Medium Big Description
aircrafts table 16 kB 16 kB 16 kB Aircraft
airports table 48 kB 48 kB 48 kB Airports
boarding_passes table 31 MB 102 MB 427 MB Boarding passes
bookings table 13 MB 30 MB 105 MB Bookings
flights table 3 MB 6 MB 19 MB Flights
flights_v view 0 kb 0 kB 0 kB Flights
routes mat. view 136 kB 136 kB 136 kB Routes
seats table 88 kB 88 kB 88 kB Seats
ticket_flights table 64 MB 145 MB 516 MB Flight segments
tickets table 47 MB 107 MB 381 MB Tickets

Metadata

Table bookings.aircrafts_data
Column Type Modifiers Description
aircraft_code char(3) NOT NULL Aircraft code, IATA
model text NOT NULL Aircraft model
range integer NOT NULL Maximal flying distance, km
Table bookings.airports
Column Type Modifiers Description
airport_code char(3) NOT NULL Airport code
airport_name text NOT NULL Airport name
city text NOT NULL City
longitude float NOT NULL Airport coordinates: longitude
latitude float NOT NULL Airport coordinates: latitude
timezone text NOT NULL Airport time zone

The coordinates of the longitude and latitude have been transformed to point data type in the table. Here is the function to convert the longitude and latitude to point.

--Return point with unknown SRID
SELECT ST_MakePoint(-71.1043443253471, 42.3150676015829);

--Return point marked as WGS 84 long lat
SELECT ST_SetSRID(ST_MakePoint(-71.1043443253471, 42.3150676015829),4326);

result
-------
1.5

For geodetic coordinates, X is longitude and Y is latitude

Table bookings.boarding_passes

Column Type Modifiers Description
ticket_no char(13) NOT NULL Ticket number
flight_id integer NOT NULL Flight ID
boarding_no integer NOT NULL Boarding pass number
seat_no varchar(4) NOT NULL Seat number

Table bookings.bookings

Column Type Modifiers Description
book_ref char(6) NOT NULL Booking number
book_date timestamptz NOT NULL Booking date
total_amount numeric(10,2) NOT NULL Total booking cost

Table bookings.flights

Column Type Modifiers Description
flight_id serial NOT NULL Flight ID
flight_no char(6) NOT NULL Flight number
scheduled_departure timestamptz NOT NULL Scheduled departure time
scheduled_arrival timestamptz NOT NULL Scheduled arrival time
departure_airport char(3) NOT NULL Airport of departure
arrival_airport char(3) NOT NULL Airport of arrival
status varchar(20) NOT NULL Flight status
aircraft_code char(3) NOT NULL Aircraft code, IATA
actual_departure timestamptz Actual departure time
actual_arrival timestamptz Actual arrival time

Table bookings.seats

Column Type Modifiers Description
aircraft_code char(3) NOT NULL Aircraft code, IATA
seat_no varchar(4) NOT NULL Seat number
fare_conditions varchar(10) NOT NULL Travel class

Table bookings.ticket_flights

Column Type Modifiers Description
ticket_no char(13) NOT NULL Ticket number
flight_id integer NOT NULL Flight ID
fare_conditions varchar(10) NOT NULL Travel class
amount numeric(10,2) NOT NULL Travel cost

Table bookings.tickets

Column Type Modifiers Description
ticket_no char(13) NOT NULL Ticket number
book_ref char(6) NOT NULL Booking number
passenger_id varchar(20) NOT NULL Passenger ID
passenger_name text NOT NULL Passenger name
contact_data jsonb Passenger contact information

Fact and Dimensional tables.

The following entity-relationship diagram shows a star schema optimized for queries created using the flight booking database.

To see the online version of the fact and dimensional entity relationship diagram (ERD) Click here

Installation

  1. Install Postgres from here https://www.postgresql.org/download/

  2. Clone the repo

    git clone https://github.com/saboye/Data-Modeling-with-Postgres.git
  3. Importing the database using psql

    psql -h localhost -d DATABASE -U postgres -f {FILE PATH}booking.sql

License

Distributed under the MIT License. See LICENSE for more information.

Contact

Your Name - @saboye - email

Project Link: https://github.com/saboye/Data-Modeling-with-Postgres

Acknowledgements

About

A project to design a fact and dimension star schema for optimizing queries on a flight booking database using PostgreSQL, a relational database management system. This schema is well-suited for a flight booking database, as it allows for efficient querying of data such as booking dates, flight routes, and passenger information.

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published