Relational Database Fundamentals: an astronomical view
Prepared by Luciano Nicastro (INAF – OAS Bologna)
Operative systems: Linux and Mac OS – RDBMS: MySQL and MariaDB
Course aims
Learn the basic principles of relational databases and how they can be used to manage astronomical data.
Course keywords
Relational databases, MySQL, astronomical catalogues, astronomical data management, DB access via custom programs, LAMP architecture.
Course program
Note: the agenda is not strict and will be adjusted if needed.
Day 1
- Morning – presentation+examples: Relational databases principles; MySQL: installation and management; introduction of use cases in astronomy.
- Afternoon – hands-on: MySQL installation and management verification and support; astro-examples in more details.
Day 2
- Morning – presentation+examples: DB structure; indices; SQL commands overview; Views, Triggers, Stored routines (functions and procedures); SID and DIF: indexing the sky; astro-examples.
- Afternoon – hands-on: SID or DIF installation; managing an astronomical catalogues; adding stored astro-routines; astro-examples in more details.
Day 3
- Morning – examples: data import/export; multi-table queries: sub-queries, temporary tables, joins; DB optimization; DB storage engines.
Day 4
- Morning – examples: adding User-defined Functions (UDFs); the LAMP application stack: accessing DB tables from the web, tabular and graphical view; MySQL Workbench / phpMyAdmin.
- Afternoon – hands-on: data import/export; adding astro-UDFs (written in C/C++) and use cases; creating a simple HTML+JavaScript+PHP page to browse an astronomical data table.
Day 5
- Full day hands-on practice: SQL practicing: importing / manipulating your favorite data; accessing MySQL from C, Python, PHP, Julia, IDL. Use cases open discussion.
References
System requirements can be found here.
For MySQL installation and more, see the reference page.
SQL
w3schools SQL Tutorial
SQL – Quick Guide
Additional useful links:
Apache HTTP server:
https://httpd.apache.org/
PHP:
https://php.net/
https://php.net/manual/
phpMyAdmin:
https://phpmyadmin.net/
XAMPP:
https://www.apachefriends.org/
https://www.apachefriends.org/faq_linux.html
https://www.apachefriends.org/faq_osx.html
WAMPServer: https://www.wampserver.com/en/
For Mac OS users:
Xcode
MacPorts
Homebrew
Homebrew documentation
MacPorts MySQL howto