Intro to databases week 2 - A relationship with Algebra

This week was serious meat and potatoes business: relational algebra and some SQL.

I know what you're thinking: what's relational algebra? Well it's like your logic & computation class met SQL and decided it didn't want to have a good structure and be a real language. So relational algebra gives us some example ways to perform operations on database tables in a bit of pseudo code. Useful keywords in this area include the following including simplified description:

SELECT - pull some columns out of a table.
PROJECT - pull some rows out of a table.
UNION - join two tables that share columns
INTERSECT - join two tables that share columns only on the rows that match
DIFFERENCE - subtract one table from another, giving you the first table with the values in the second removed from it when they match
PRODUCT - makes a super table that is the combination of the two multiplied out in almost every combination. it generated a bit of a mess but is used as part of the join operation.
JOIN - add some columns from one table to another to make a super table where the keys match.
DIVIDE - this one is kind of weird and hard to describe without a picture, but it's included for keyword completeness.

After getting all the keywords loaded up we had some exercises to do. Those exercises really served to provide the syntax for using relational algebra as if it was SQL in the final homework for the week. That seemed a bit akward like we should have just finished loading up SQL commands and then wrote it like that. But it is what it is.

From there we went on to reading but not much homework related to SQL. That's where it gets to the serious business part with real structure to send commands to a DBMS.  The big take away for me from this weeks section on SQL is that it seems like something that is straightforward to get the basics of but then tricky to become really good at. Being able to ask questions of a database is fine but the trick seems like it will be figuring out what questions to ask and how to organize the data to generate useful information. Then there is the ever looming problem of creating a database that turns into a mess so it seems important to follow along closely with these beginning sections.

Next week should be interesting as we continue our exercises but start to use an Oracle DMBS to run databases on our own computer. I am looking forward to the hands on work coming up.