project description
Explore the docs »
View Demo
·
Report Bug
·
Request Feature
- About The Project
- List of DB Tables
- Metadata
- Fact and Dimensional tables
- Installation
- License
- Contact
- Acknowledgements
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
| 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 |
| 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 |
| 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| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
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
-
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
psqlpsql -h localhost -d DATABASE -U postgres -f {FILE PATH}booking.sql
Distributed under the MIT License. See LICENSE for more information.
Your Name - @saboye - email
Project Link: https://github.com/saboye/Data-Modeling-with-Postgres