Intro to Databases Week 5

If indexes are supposed to speed up performance of query, what does the author mean by a slow index?

I believe that by saying a "slow index" Markus Winand is saying that just because your table(s) have indexes doesn't meant that the lookup time can't be slow.  That there are a couple of reasons you might have a slower than expected lookup time when using indexes.

One reason might be that depending on the data stored in the database when querying your query might have to perform multiple leaf node reads.

Second, that data stored in the table is often spread across different table blocks, i.e., the data you're looking for might not be in two rows near each other.  And so depending on the backing store, that can add additional unforeseen time.

Read More

Intro to Databases Week 4

This week marks the halfway point of the course, and it's really flown by quick.  I've really felt like I've learned a lot so far.  There's obviously becoming more comfortable with SQL syntax, but there's also the understanding of what's going on behind each command as well.  That's helped shed light on some of the real power of relational databases.  Things that I previously thought must have been done all internally to a program, can actually be achieved with some simple SQL queries.  I've also found learning about normalization very interesting, and both at times confusing and other times very easy to understand. 

Things I'm still curious about are other database types like nosql, apache Cassandra, or elasticsearch.   I'm beginning to understand how performance is affected by choices we make when designing our tables and queries, but there still seems like a lot of ground to cover there.  Another question I have is around how does scaling and redundancy work for a database and keeping it all in sync.  Also database heath, what are ways to make sure a database is sane and validated.

Read More

Intro to Databases Week 3

Someone described normalization rule as "a non-key column depends on the key, the whole key, and nothing but the key, so help me Codd."  Key refers a primary or other candidate key of a table.  If the key has multiple columns, then "whole key" means  all columns together and not just some part of the key.  Explain in your words what 3rd normal form is and why it is important.

Third normal form, to me, is when a key that does not need to be dependent on the table's index can be removed and made its own table.  This helps with reliable and consistent data across the database.

What is an SQL view.  How is it similar to a table? In what ways is it different?

A SQL View is essence a virtual table comprised of the results from a SQL statement.  Basically the View isn't a table itself in the database, rather, it's composed of data from the database and structured in a way that makes sense for the moment.  It's similar to a table, in the sense that you can query against the view, get data out of the view.  However, unlike a table, it doesn't actually store any data in the the database.

Read More

Intro to Databases Week 2

1.  SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ).    Most of the time the join will use equality between a primary and foreign key.   Think of example where joining on something other than keys would be needed.  Write the query both as an English sentence and in SQL.  If you can't think of your own example, search the textbook or internet for an example.

I'm thinking of a time you wouldn't need to have an equality relationship between the primary and foreign keys would be when you want to combine all the data to see all possible combinations of the data.

For example if a business wants to see all possible ways to combine several items, like a restaurant would want different ways to combine a meal and different beverage options.

The English query would be something like what are all the possible combinations of different meals and beverages.

The SQL query would be something like "select * from meals, beverages"

1. What is your opinion of SQL as a language?  Do you think it is easy to learn and use?  When translating from an English question to SQL, what kinds of questions do you find most challenging?

The general concepts of the SQL language are easy to grasp, but as they say, the devil is in the details.  I felt like I had a pretty good handle of things the first week, but this second week I've definitely felt as though the trickier SQL queries took me a lot longer to process how to put them together in order to achieve the desired result output.  

Week 1 I felt as though I could read a prompt, and naturally understand the SQL query that would return the desired data, but Week 2 I've felt as though I've struggled doing that same approach.  It's been a lot more trial and error, I don't read the prompt and immediately understand the right approach to building the query.  This is especially true for queries that involve subqueries or rely on data from 3 or more tables.

Read More