SQL Schema

Overview

The SQL schema has some differences from our Objectivity schema. Arrays cannot be represented in SQL Server, so we broke out the shorter, one-dimensional arrays f[5] as scalar fields f_1,f_2, The poor indexing in Objectivity/DB forced us to separate star and galaxy objects (for the 2x speedup), while in SQL we were able to merge the two classes and their associations. Object Associations were converted into Foreign Keys. Other than this, the schema conversion and data extraction and loading was remarkably easy.

Tables

The tables can be separated into several broad categories.

Photometric Observations

The first set of tables relate to the photometric observations.

Photo Table - Base table containing 100 attributes.
Profile Table - 30% of the least popular attributes are stored here, along with the 5x15 radial light profile array. The array is represented as a BLOB with some user-defined functions to access it.
Tag Indices - The Objectivity tag tables are not really tables in SQL but rather indices on the photo table

Observation Hierarchy

Another set of tables is related to the hierarchy of observations and the data processing steps.

Chunk Table - A data processing unit, consisting of a few runs.
Segment Table - Attributes common to a camera column of a run.
Field Table - 60 attributes common to a 2048x1489 5-color image which make up a camera column.
Photo Tables - see above.

Spectroscopic Observations

Another set of tables is related to the spectroscopic observations. They capture the process of target selection and eventual observation.

Spectral Target Table - lists selected objects from photometry to be targeted by a spectrograph fiber.
Spectro Object - all attribute of a spectrum of an object
Spectral Lines - attributes of an absorption or emission line that are part of the spectrum
Spectro Plates - parameters common to a whole plate

Catalog Crossreferences

Finally, there is a table to capture cross-reference information about SDSS objects also detected in other catalogs, if a unique identification can be made. This cross-reference table can evolve as the need arises.