Data Modeling with Postgres

project description
Explore the docs »

View Demo · Report Bug · Request Feature

Table of contents

• About The Project
• List of DB Tables
• Metadata
• Fact and Dimensional tables
• Installation
• License
• Contact
• 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 STSetSRID(STMakePoint(-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

• Install Postgres from here

https://www.postgresql.org/download/

• Clone the repo

git clone https://github.com/saboye/Data-Modeling-with-Postgres.git

• 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

• https://www.postgresql.org/
• https://www.antwak.com/
• https://postgrespro.com/docs/postgrespro/9.6/apjs04
• https://postgrespro.com/docs/postgrespro/9.6/apjs04
• https://www.postgresqltutorial.com/
• https://www.draw.io