Intro

Relational Database Fundamentals: an astronomical view

IMPRS primer course, Garching 4 − 9 June 2018

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