stefanproell / flughafendb Goto Github PK
View Code? Open in Web Editor NEWThe Flughafen DB repository containing a large MySQL data set for training, learning and testing
flughafendb's People
Contributors
Stargazers
Watchers
flughafendb's Issues
Some department names are still in German for the English version
In this file
https://github.com/stefanproell/flughafendb/blob/master/english/flughafendb_schema_en.sql
in line 168
`department` enum('Marketing','Buchhaltung','Management','Logistik','Flugfeld') DEFAULT NULL,Perhaps a script to change all over the whole English database would be needed.
some places encoding is `utf8mb4mb4`
In this file,
https://github.com/stefanproell/flughafendb/blob/master/english/flughafendb_schema_en.sql
At many places, for example, lines 47, 63 have the encoding as utf8mb4mb4.
Is it proper? Should it not be utf8mb4 only?
I only checked for one file. Perhaps there are other files also.
For proper English translation, in `passengerdetails`, `w` needs to be replaced with `f` in `sex column
As seen below, employee table has the proper English shortform f but the passengerdetails has the German shortform w.
mysql> select sex, count(sex) from employee group by sex;
+------+------------+
| sex | count(sex) |
+------+------------+
| m | 815 |
| f | 185 |
+------+------------+
2 rows in set (0.00 sec)
mysql> select sex, count(sex) from passengerdetails group by sex;
+------+------------+
| sex | count(sex) |
+------+------------+
| m | 28752 |
| w | 7343 |
+------+------------+
2 rows in set (0.03 sec)
Generator script
if possible, provide scripts to generate and up2date schema.
Docker compose to boot up a mysql db including the flughafenDB
- docker compose mounting the script and imports it
- mention duration of import
- how to access db (maybe another container for some web based UI?)
`airlinename` field with type `varchar(30)` contains airlines name which are cut off to 20 characters limit instead of 30
Source:
flughafendb/tree/master/mysql-shell-dumps/flughafendb_small/
Expected Behaviour:
Full Name of the airline or cut off at 30 characters as field type is varchar(30)
Observed Behaviour:
It seems like the airlinename field with type varchar(30) contains airlines names which was already cut off to maximum 20 characters. This means that the names data used was already limited to 20 characters and then inserted into the varchar(30) type airlinename field.
Logs:
mysql>select iata, airlinename from airline limit 3;
+------+----------------------+
| iata | airlinename |
+------+----------------------+
| AF | Afghanistan Airlines |
| AL | Albania Airlines |
| AM | American Samoa Airli |
+------+----------------------+
-- airline table description.
mysql> describe airline;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| airline_id | smallint | NO | PRI | NULL | auto_increment |
| iata | char(2) | NO | UNI | NULL | |
| airlinename | varchar(30) | YES | | NULL | |
| base_airport | smallint | NO | MUL | NULL | |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)-- airline names and their lengths
| airlinename | length |
|---|---|
| Afghanistan Airlines | 20 |
| Albania Airlines | 16 |
| American Samoa Airli | 20 |
| Angola Airlines | 15 |
| Argentina Airlines | 18 |
| Australia Airlines | 18 |
| Azerbaijan Airlines | 19 |
| Bahamas Airlines | 16 |
| Belarus Airlines | 16 |
| Bhutan Airlines | 15 |
| Bolivia Airlines | 16 |
| Brazil Airlines | 15 |
| Bulgaria Airlines | 17 |
| Caicos Is Airlines | 18 |
| Central African Rep | 20 |
| Chad Airlines | 13 |
| Colombia Airlines | 17 |
| Croatia Airlines | 16 |
| Cuba Airlines | 13 |
| Cyprus Airlines | 15 |
| Czech Airlines | 14 |
| Dakhla And Laayoune | 20 |
| Denmark Airlines | 16 |
| Djibouti Airlines | 17 |
| Dominica Airlines | 17 |
| Ecuador Airlines | 16 |
| Egypt Airlines | 14 |
| El Salvador Airlines | 20 |
| Equatorial Guinea Ai | 20 |
| Eritrea Airlines | 16 |
| Estonia Airlines | 16 |
| Ethiopia Airlines | 17 |
| Falkland Is Airlines | 20 |
| Fiji Is Airlines | 16 |
| France Airlines | 15 |
| Gabon Airlines | 14 |
| Georgia Airlines | 16 |
| Ghana Airlines | 14 |
| Gibraltar Airlines | 18 |
| Greece Airlines | 15 |
| Guadeloupe Airlines | 19 |
| Haiti Airlines | 14 |
| Honduras Airlines | 17 |
| Hungary Airlines | 16 |
| Iceland Airlines | 16 |
| India Airlines | 14 |
| Iran Airlines | 13 |
| Isla De Pascua Airli | 20 |
| Italy Airlines | 14 |
| Ivory Coast Airlines | 20 |
| Jamaica Airlines | 16 |
| Jerusalem Airlines | 18 |
| Johnston Atoll Airli | 20 |
| Kazakhstan Airlines | 19 |
| Kenya Airlines | 14 |
| Kiribati Airlines | 17 |
| Korea Airlines | 14 |
| Kuwait Airlines | 15 |
| Kyrgyzstan Airlines | 19 |
| Laos Airlines | 13 |
| Lebanon Airlines | 16 |
| Liberia Airlines | 16 |
| Luxembourg Airlines | 19 |
| Macau Airlines | 14 |
| Melilla Airlines | 16 |
| Micronesia Airlines | 19 |
| Moldova Airlines | 16 |
| Myanmar Airlines | 16 |
| Namibia Airlines | 16 |
| Nepal Airlines | 14 |
| Nicaragua Airlines | 18 |
| Northern Mariana Is | 20 |
| Oman Airlines | 13 |
| Pakistan Airlines | 17 |
| Peru Airlines | 13 |
| Philippines Airlines | 20 |
| Poland Airlines | 15 |
| Puerto Rico Airlines | 20 |
| Qatar Airlines | 14 |
| Reunion Airlines | 16 |
| Romania Airlines | 16 |
| Russia Airlines | 15 |
| Rwanda Airlines | 15 |
| San Andres Airlines | 19 |
| Senegal Airlines | 16 |
| Sierra Leone Airline | 20 |
| Slovakia Airlines | 17 |
| Solomon Is Airlines | 19 |
| Spain Airlines | 14 |
| Sri Lanka Airlines | 18 |
| St Kitts Airlines | 17 |
| Sudan Airlines | 14 |
| Swaziland Airlines | 18 |
| Syria Airlines | 14 |
| Taiwan Airlines | 15 |
| Thailand Airlines | 17 |
| Togo Airlines | 13 |
| Trinidad Airlines | 17 |
| Tunisia Airlines | 16 |
| Uganda Airlines | 15 |
| Ukraine Airlines | 16 |
| United Arab Emirates | 20 |
| Uruguay Airlines | 16 |
| Uzbekistan Airlines | 19 |
| Vanuatu Airlines | 16 |
| Venezuela Airlines | 18 |
| Vietnam Airlines | 16 |
| Wake I Airlines | 15 |
| Western Samoa Airlin | 20 |
| Yemen Airlines | 14 |
| Yugoslavia Airlines | 19 |
| Zambia Airlines | 15 |
| Zimbabwe Airlines | 17 |
some tables are MyISAM
The dataset contains MyISAM or MEMORY tables. That should not be.
MySQL 8.0 is utf8mb4
MySQL 8.0 is utf8mb4 by default (https://dev.mysql.com/doc/refman/8.0/en/charset.html)
This dataset specifies the <8.0 charset of latin1. Consider moving to utf8mb4.
Flughafen does not exist and fails contraint.
In small, the row from fluglinie fails the constraint, as 813 does not exist in the flughafen table.
(15,'CE','Central African Rep',813)
The error:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`projekt-flughafen`.`fluglinie`, CONSTRAINT `fluglinie_ibfk_1` FOREIGN KEY (`heimat_flughafen`) REFERENCES `flughafen` (`flughafen_id`))
Translate weather
Translate the German weather phenomenons.
`weather` enum('Nebel-Schneefall','Schneefall','Regen','Regen-Schneefall','Nebel-Regen','Nebel-Regen-Gewitter','Gewitter','Nebel','Regen-Gewitter') DEFAULT NULL,
Load of this data is slow
Loading this data is slow as it's a set of SQL files containing INSERT statements.
Consider providing a MySQL Shell Dump of the data, which allow the MySQL Shell Load to load the data in many threads.
Demo: https://mysqlserverteam.com/mysql-shell-dump-load-part-1-demo/
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.