The Bright Stars Catalogue

Exercise with the BSC

We assume that the dump files are in ~/mpe2018/Data otherwise change the file path as appropriate.
Columns description is here.
As usual, the mysql> prompt is omitted. The database is always mpe2018db.

Load the table into the database via the SQL dump file, describe the content and count the number of rows (stars) in the catalogue:

use mpe2018db;
source ~/mpe2018/Data/simpleBSC.sql
source ~/mpe2018/Data/bsc.sql

describe simpleBSC;
describe BSC;
select count(*) from simpleBSC;
select count(*) from BSC;

Rename the table to have it in lower case letters (not necessary on Mac!) and add an index on RA and Magnitude:

rename table BSC to bsc;  -- only on Linux
alter table bsc add key (ra);
alter table bsc add key (vmag);

Let’s practice with SELECT, some “informative” queries (MIN(), MAX(), AVG(), ROUND()) and index creation.
Note how, if not modified by the user settings, MySQL is not case sensitive, apart the DB and table names.

Magnitude minimum, maximum and average of all stars in the catalogue:

select MIN(rad), MAX(rad) from bsc;
select MIN(decd), MAX(decd) from bsc;
select MIN(vmag), MAX(vmag), AVG(vmag) from bsc;
select ROUND(AVG(vmag), 2) from bsc where rad > 180 and decd > 0;
select ROUND(AVG(vmag), 2) from bsc where rad < 180 and decd < 0;

Name and magnitude of the 10 brightest stars. Sort in ascending order using ORDER BY and ASC:

select name, vmag from bsc ORDER BY vmag ASC limit 10;

Count the stars with a name (field name is not empty or NULL, i.e undefined):

select count(*) from bsc where name IS NOT NULL and name != ' ';

-- result 3143

Then the stars without a name:

select count(*) from bsc where name IS NULL or name = ' ';

-- result 5953

Let's create a new table temp with 4 columns reading from bsc - only the first 100 stars are inserted:

create table temp select name, ra, de, vmag from bsc limit 100;

describe temp;
select * from temp limit 10;

The brightest star in this table:

select * from temp order by vmag asc limit 1;

-- +------------+------------+-----------+------+
-- | name       | ra         | de        | vmag |
-- +------------+------------+-----------+------+
-- |  21Alp And | 00 08 23.3 | +29 05 26 | 2.06 |
-- +------------+------------+-----------+------+-

If you wanted to select the 100 brightest stars, then you need to use ORDER BY sorting in ASCending order:

create table temp100 select name, ra, de, vmag from bsc ORDER BY vmag ASC limit 100;

select * from temp100 order by vmag asc limit 1;
-- +------------+------------+-----------+-------+
-- | name       | ra         | de        | vmag  |
-- +------------+------------+-----------+-------+
-- |   9Alp CMa | 06 45 08.9 | -16 42 58 | -1.46 |
-- +------------+------------+-----------+-------+

Remove the tables:

drop table temp, temp100;

Recreate the temp table writing only the stars of the northern hemisphere and with a max magnitude of 3. Note that the decd column contains declination as fractional degrees (from −90 to +90):

create table temp select * from bsc where decd > 0 and vmag < 3;

We know that the polar star has declination close to 90 degrees, so let's search it in the 88 - 90 degrees range:

select * from temp where decd between 88 and 90;

Let's see which stars are close to the galactic plane (latitude range + or − 2 degrees):

select * from temp where glat between -2 and 2;

Erase objects with magnitude less than 1 (first check the number):

select count(*) from prova where vmag < 1;
delete from temp where vmag < 1;

Use update to set a magnitude of 0 for the stars closer than 10 degrees to the north pole:

update temp set vmag = 6 where decd > 0;

Perform more query at wish ...

Remove the table(s):

drop table temp;

Create a table selecting only the stars with a measure parallax, i.e. > 0. Only select coordinates, Vmag and parallax:

create table parallaxes select rad, decd, vmag, parallax from bsc where parallax > 0;

-- We have selected 3099 stars

Now we check if there is any relation between magnitude and parallax (on average).
We select groups of stars in a non linear way because magnitudes do not decrease linearly with distance. Note that the use of the "sub-query", between (), and the instruction limit with 1 or 2 parameters (N_skip, N_read). The first number is the number of rows to skip and the second those to read. So below we are asking to skip 100 rows and read the following 500.

Consider the 100 brightest stars:

select avg(vmag), avg(parallax) from (select vmag, parallax from parallaxes order by vmag limit 100) p;

-- result: 1.902800 |     0.0571600 

Read the next 500 brightest stars:

select avg(vmag), avg(parallax) from (select vmag, parallax from parallaxes order by vmag limit 100, 500) p;

-- result: 3.725460 |     0.0327700

Read the next 2500 stars:

select avg(vmag), avg(parallax) from (select vmag, parallax from parallaxes order by vmag limit 500, 2500) p;

-- result: 5.349928 |     0.0244048

We note that parallax "on average" decreases as the magnitude increases. Remove all the stars with parallax < 0.01:

delete from parallaxes where parallax < 0.01;

Continue practicing with more queries, eventually creating new test tables ...

Remove table(s):

drop table parallaxes;