Lighthouse W3D1 – Tables and more tables with SQL

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.

My handle reference for the tables!
My handle reference for the tables!

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…

    Tables and columns for our naval database
    Tables and columns for our naval database

    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!

Advertisements

One thought on “Lighthouse W3D1 – Tables and more tables with SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s