Schéma : nycflights

nycflights, R-project, relationnel, pattes de corbeau, crowfoot

Présentation du schéma nycflights, pour l’entrainement à PostGres.

• L3 MIASHS/Ingémath
• Université Paris Cité
• Année 2024-2025
• Course Homepage
• Moodle

Ce jeu de données est construit à partir du package nycflights13.

The nycflights13 package contains information about all flights that departed from NYC (e.g. EWR, JFK and LGA) to destinations in the United States, Puerto Rico, and the American Virgin Islands) in 2013: 336,776 flights in total. To help understand what causes delays, it also includes a number of other useful datasets.

This package provides the following data tables.

• ?flights: all flights that departed from NYC in 2013
• ?weather: hourly meterological data for each airport
• ?planes: construction information about each plane
• ?airports: airport names and locations
• ?airlines: translation between two letter carrier codes and names

Schema nycflights Generated using DbSchema

Table nycflights.airlines

Idx	Name	Data Type
* 🔑 ⬋	carrier	text
🔍	name	text

Table nycflights.airports

Idx	Name	Data Type
* 🔑 ⬋	faa	text
	name	text
🔍	lat	double precision
🔍	lon	double precision
	alt	double precision
	tz	double precision
	dst	text
	tzone	text

Table nycflights.flights

Idx	Name	Data Type
🔍	year	integer
🔍	month	integer
🔍	day	integer
	dep_time	integer
	sched_dep_time	integer
	dep_delay	double precision
	arr_time	integer
	sched_arr_time	integer
	arr_delay	double precision
⬈	carrier	text
	flight	integer
🔍 ⬈	tailnum	text
* 🔍 ⬈	origin	text
🔍 ⬈	dest	text
	air_time	double precision
	distance	double precision
🔍	hour	double precision
🔍	minute	double precision
*	time_hour	timestamptz

distance est la distance orthodromique (earth distance) entre origin et dest en miles (pas en miles nautiques). On peut le vérifier avec les fonctions de l’extension earthdistance.

le vol est effectué si dep_time n’est pas NULL

dep_time, sched_dep_time, arr_time, sched_arr_time sont construits de la façon

Foreign Keys

Type	Name	On
	flights_fk	( carrier ) ref nycflights.airlines (carrier)
	flights_fk_planes	( tailnum ) ref nycflights.planes (tailnum)
	flights_fk_origin	( origin ) ref nycflights.airports (faa)
	flights_fk_dest	( dest ) ref nycflights.airports (faa)

Table nycflights.planes

Idx	Name	Data Type
* 🔑 ⬋	tailnum	text
	year	integer
	type	text
	manufacturer	text
	model	text
	engines	integer
	seats	integer
	speed	integer
	engine	text

Table nycflights.weather

Idx	Name	Data Type
* 🔑 ⬈	origin	text
	year	integer
	month	integer
	day	integer
	hour	integer
	temp	double precision
	dewp	double precision
	humid	double precision
	wind_dir	double precision
	wind_speed	double precision
	wind_gust	double precision
	precip	double precision
	pressure	double precision
	visib	double precision
* 🔑	time_hour	timestamptz

Foreign Keys

Type	Name	On
	weather_fk	( origin ) ref nycflights.airports (faa)