In this subject we learnt about the inner workings of relational database management systems – SQL views, indexes, disk access time and query optimisation. The project was to create an SQL script to improve a supplied database by creating various indexes and integrity constraints and writing a comparative analysis on the performance.
Some of the more interesting parts of the SQL script follows:
[sql]
–create materialised view of same data as above
CREATE MATERIALIZED VIEW MV_ORGANISATION_BIRD_COUNT AS
SELECT organisation_name, count(*) as bird_count
FROM sightings s, organisations o, spotters sp
WHERE s.spotter_id = sp.spotter_id and sp.organisation_id = o.organisation_id
GROUP BY organisation_name;
/
–run the views
SELECT * FROM V_ORGANISATION_BIRD_COUNT;
SELECT * FROM MV_ORGANISATION_BIRD_COUNT;
–query for sighting with biggest distance
SELECT S.sighting_id, sqrt((POWER((S.longitude – 151),2)) + (POWER((S.latitude – (-28)),2))) AS dist
FROM SIGHTINGS S
WHERE sqrt((POWER((S.longitude – 151),2)) + (POWER((S.latitude – (-28)),2))) = (
SELECT MAX(
sqrt((POWER((S.longitude – 151),2)) + (POWER((S.latitude +28),2)))
)
FROM SIGHTINGS S
);
–create function based index
CREATE INDEX IDX_HEADQUARTERS_DISTANCE ON SIGHTINGS (sqrt((POWER((longitude – 151),2)) + (POWER((latitude – (-28)),2))));
[/sql]