More Lessons

Lesson 2 is on Constraints (NOT NULL, Unique, Primary Key etc) and has a nice logical build up going from NOT NULL and Unique constraints to the Primary key ones. Theory-wise it explains that constraints were devised for preventing various shortcomings such as:  

• Preventing null values
• Ensuring column values are Unique
• Providing additional validations, something that brings us to the Unique column and table constraints

It mentions that a Primary Key is a combination of NOT NULL and Unique, from which you can deduct that a Unique key can be Null, something not mentioned in the class.

Further, the difference between Primary Key vs Unique key is condensed down to the Primary Key only being defined once per table while Unique keys can appear multiple times per table. Although that is correct in terms of implementation, it is shallow in terms of theory. A real world example, like looking up entries in an employee directory having the Primary Key on the phone number column and a Unique key on the surname column, would be much more useful.

The next section is on Foreign Key constraints covering how they can relate tables and how they can prevent inconsistent relationships thus avoiding the anomaly of orphan rows. The lecture is nicely brought to an end with a talk on the use of the Check constraints. Again, the video production and especially the animation is really well made and gets the point across effectively

Lesson 3 is about relationships and normalization. This should be interesting. Theory-wise it is much better, explaining with very practical although simplified examples the first two normal forms and the anomalies they prevent, but sadly there’s no material on the 3rd normal form.

It also demonstrates how to remove redundancy by dividing a table into more sub tables and then relating them, as well as how to handle many-to-many relationships.

So a many-to-many relationship is broken down into mediator table called a join table, whose rows are composed from both Primary Keys of the originating tables appearing as Foreign Keys in the join table. It does not mention however that the join table should have both Foreign Key's as a combined Primary Key.

It elaborates further on relationships by looking at cardinality, but omitting to talk on their other aspects of optionality  and identification. Keep it simple I guess, nevertheless I think that this section managed to serve its cause.

A nice transition is made the next section, mentioning that to extract meaningful information out of those relationships we will have to query them with the aid of Joins. This covers inner and outer joins (left and right but not full), with a break in between to talk about aliases. This proves very handy as joins get more complicated and span multiple tables, as aliases can shorten the nomenclature involved in a complex join as well as give meaningful names to our result sets.

The examples are simple and straight forward and make use of set based notation for making the relationships easier to visualize:

We finally reach Lesson 5 and sub queries. It is mentioned that a sub query can bring up the same results as an inner join and that their difference is nothing more than preference. A sub query is easier to read while an inner join is more performant. I would say this is only true until you hit a sub query that can't be easily flattened into a simple join.

Then the IN and NOT IN operators are covered but not the ANY, SOME, or ALL. Lastly, the focus is on correlated sub queries, but the truth is that I couldn’t find anything correlated in the presented queries such as: 

select from movies 
where duration>(select avg(duration) from movie)
  

I also noted there was no discussion of the EXISTS and NOT EXISTS operators, nor of the corellated versions of IN and NOT IN.

What next?

After the course completion you don’t get a certificate. Instead there's a video with recorded directions on where to move next, what resources and references to look for.

That aside, the CodeSchool platform has many related screencasts leveraging the basics acquired into building something useful. One example is a Soup to bits SQL walthrough where the instructors builds a database-enabled application from scratch:

Conclusion

If you are a beginner and have just attended the course for the sake of it without  expecting it to have an immediate impact on your day job or on a project at large then you won't have achieved much. To get the maximum benefit from it and come away  with knowledge that sticks, you need to expand on the basics you’ve learned on a  project of your own.

That brings up the theoretical/concept background foundations one has to possesses in order to work with the relational model. In the course you do get to see the  implementation side of the relational model through the use of SQL, but it doesn't go far enough for the theoretical mindset necessary for this particular area of Computer Science.

Not having a good foundation, and not thinking in sets, can easily lead to erroneous SQL code which subsequently could  lead to a stream of issues ranging from getting back the wrong answer to hideous performance typified by Cartesian products.

It's not like say jQuery where one can just start hacking code. SQL and relational databases go deep and their learning is a lifelong aspect. You’ll have to grab a pen and consume lots paper before touching the keyboard.

And that is how I recommend this course, as complementary aid to a good book or work activity, being ideal for beginners wanting to use the gathered information as soon as possible, as well as to those already familiar with the general concepts but in need of getting themselves up to date with current technologies. If you have a good programming background you will get maximum benefit and get up to speed quickly and efficiently. 

More Information

Code School
Database Path

Related Articles

Web Design Course From Code School 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on, Twitter, Facebook, Google+ or Linkedin. 

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info