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

• 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