Today we jumped right into interacting with a database using SQL. We created a PostgreSQL database in the cloud using Heroku. We loaded data into the database by running a pre-written SQL script. This script created a “bookstore” database that had tables such as publishers, books, stock, and authors among others. We then wrote queries to fetch specified information.
The five big SQL commands are: (they need to go in this order)
SELECT – you specify the column_names(s)
FROM – you specify the table_name(s)
WHERE – you set conditions to filter the results (filters what data gets input into the aggregate function if there is one)
GROUP BY – specify the condition to group the result-set from an aggregate function (SUM, COUNT, AVG, MIN, MAX…)
HAVING – condition to filter the results from an aggregate function
When I was working through the exercises, it was really useful to see the different tables and the columns/value types they held. For example, if I needed book titles with their isbn, I could look and see that I needed to join the editions table and the books table.
For another assignment, we were to draw an Entity Relationship Diagram for the following:
- A fleet has a name
- A ship has a name and a date_built
- A sailor has a name and a rank
- A sailor belongs to a ship and a ship has many sailors
- A ship has one captain
- A captain is a sailor
- A ship belongs to a fleet and a fleet has many ships
Originally I drew a table each for Fleet, Ship, Sailor, and Captain… but why make a separate table for Captain when captains are sailors? I decided to use good old excel, as our instructor Monica had shown us in the morning lecture.
It was so much easier! Using excel and colour coding the primary key/foreign key columns makes it easy to see the relationships between tables. Also, it’s just fun to use colours wherever possible…
Ship and Sailor have a “one to many” relationship: a Ship has many sailors. A Sailor belongs to a Ship. Our instructor David told us to remember “the many side is the side with the foreign keys.”
Ship has an id that is a primary key, that means the id numbers are not null and are unique. Sailor has a ship_id that is a reference to a ship’s primary key. In the Sailor table, ship_id contains foreign keys.
The same thing for Fleet: a Fleet has many ships. The Ship table contains a fleet_id column, these are foreign keys that refer to the Fleet table’s primary keys.
Since a captain is a sailor, we know that all the captains of the ships can be found in the Sailor table. A ship has one captain, so we can reference the sailor that is the ship’s captain with a captain_id column.
Summary – I realized that I need to be able to see the table names/columns as I’m writing queries. Now that I’ve discovered how useful excel can be for visualization, I can add it to my toolbox for writing SQL queries and designing databases!