Feb 272015
 

Following the database design championship playoff over on the PL/SQL Challenge, one player asked:

“Could you please advise me some resources (boooks, courses) so that I can improve?”

Of course I can! This is what I love to hear – people looking for resources they can use to increase their skills. This is information I’m sure others will benefit from, so here goes:

select materials
from   my_readings
where  topic = 'Database design';

I’ve found The Database Programmer blog to be immensely helpful for explaining normal forms and constraints. This provided the inspiration for the “shift patterns” question using the school class schema developed aross the “Table Design Patterns” series.

Another fun paper for explaining normalization is “Data Normalization, Denormalization and the Forces of Darkness

Many people struggled with the orders “physical co-locating data” question. The inspiration for that question came from a number of places:

* Martin Widlake’s IOT series
* Iggy Fernandez’s “No to SQL and No to NoSQL” which has an example using table clusters at the bottom. His “The Twelve Days of NoSQL” series on the same blog also provides great ideas.
* The Data Warehouse Insider Blog article on attribute clustering

One key thing to remember is that physical data location can matter hugely for database performance. Unfortunately this is one area that people often overlook. The lack of index-organized tables in most Oracle databases is a great example of this.

To understand more about indexing – particularly across different database platforms – Use the Index, Luke! is a great site. If you really want to understand indexes in Oracle, take a look at Richard Foote’s blog (note – this does get very detailed!).

Of course, no list about designing for Oracle would be complete without Tom Kyte’s books. Reading these helped me understand how Oracle functions.

Finally I have to wrap up with a quick nod to the Oracle docs. Not exactly bedtime reading, but skimming through topics there has provided inspiration for a number of questions on the PL/SQL Challenge. :)

Of course this isn’t a complete list (that would span many pages!). These are some of the resources I’ve found most useful (and refer back to often).

So I’d like to hear from you. Which resources have you found most valuable when it comes to learning about designing (Oracle) databases? Please share your thoughts in the comments – I’d love to hear what other people have found useful.

Oct 052014
 

Today celebrates one year of the database design quiz on the PL/SQL Challenge!

Here’s a look back at some of the stats for the quiz to date with some trivia related to the numbers :)

select count(*)
from   quizzes;

COUNT(*)
--------
      52

-- The number of white keys on a piano
-- the smallest number with 4 representations 
-- as the sum of 4 positive squares

select count(*)
from   players;

COUNT(*)
--------
     691

-- The mass (in kilos) of a typical dairy cow
-- ~7 times the average adult body power 
-- consumption (in watts)

select count(*)
from   answers_submitted;

COUNT(*)
--------
   13723

-- Approximately the length of the Great Wall 
-- of China (in miles) and the mass of 
-- a large dinosaur (in stones)

select name, score
from   (select name, score
        from   players
        order  by score desc
)
where  rownum <= 5;

NAME             SCORE
---------------- -----
mentzel.iudith   20618
Justin Cave      20038
Joaquin Gonzalez 19284
Krzysztof Helbin 19242
Elic             19127

-- Iudith and Justin are doing a great job, 
-- the only players to break 20,000 to date!

select avg(rating) 
from   player_surveys;

AVG(RATING)
-----------
       4.13

-- 50 times the half life of uranium 241 
-- (in hours)

-- Great to see the average rating above 4 :)
-- I have to thank my reviewers, particularly 
-- Yuan Tschang, for helping keep the quality 
-- high!

Thanks to everyone who’s taken part, pointed out my mistakes and added to the discussions for the quiz over the past year.

If you’ve not played yet, today’s a great time to get started. Just head over to www.plsqlchallenge.com, register for a free account and get playing!

Jan 202014
 

I’ve done more analysis of the first quarter of the Database Design quiz on the PL/SQL Challenge. This time I’ve had a look at the quiz survey results to see what did and didn’t like, whether or not the quizzes are too hard and what people want to see more or less of.

Here’s the figures:

select round(avg(rating), 2) rating_out_of_5
from   quizzes;

RATING_OUT_OF_5
---------------
           4.14

-- The degrees Culloden in Scotland is 
-- West of the Greenwich Meridian

-- The 3 highest rated quizzes
select * from (
  select quiz || ':' || player_rating
  from   quizzes
  order  by player_rating desc
)
where rownum <= 3;

QUIZ 
-----------------------------------------
Function-based indexes - 2013-11-29: 4.28
Entity Subtyping - 2013-11-08?: 4.27
Second Normal Form - 2013-10-25: 4.23

-- 3 lowest rated quizzes
select * from (
  select quiz || ':' || player_rating
  from   quizzes
  order  by player_rating
)
where rownum <= 3;

QUIZ 
----------------------------------------
Data Types - 2013-11-22: 3.92
Referential Integrity - 2013-11-15: 4.06
Normalization - 2013-12-20: 4.08

-- Which were the hardest quizzes? 
-- We have a tie!
select * from (
  select quiz || ':' || too_hard_count quiz
  from   quizzes
  order  by too_hard_count desc
)
where rownum <= 2;

QUIZ
----------------------------------
Unique Constraints - 2013-10-18: 3
Partitioning - 2013-12-27: 3

-- And for the easiest we have:
select * from (
  select quiz || ':' || too_easy_count
  from   quizzes
  order  by too_easy_count desc
)
where rownum <= 2;

QUIZ
---------------------------------
Data Types - 2013-11-22: 6
First Normal Form - 2013-12-06: 5

-- How well did players know the topics?
-- What did people not know before the quiz:
select * from (
  select quiz || ':' || didnt_know_count
  from   quizzes
  order  by didnt_know_count desc
)
where rownum <= 3;

QUIZ
---------------------------------------
Function-based indexes - 2013-11-29: 11
Partitioning - 2013-12-27: 7
Indexing nulls - 2013-11-01: 5

-- And what was very well known:
select * from (
  select quiz || ':' || very_well_count
  from   quizzes
  order  by very_well_count desc
)
where rownum <= 3;
QUIZ
-------------------------------------
Database constraints - 2013-10-11: 16
Primary keys - 2013-12-13: 13
First Normal Form - 2013-12-06: 11

-- Finally, when it comes to future quizzes, 
-- what do people what to see?
-- More quizzes please:
select * from (
  select quiz || ':' || more_quizzes_count
  from   quizzes
  order  by more_quizzes_count desc
)
where rownum <= 3;

QUIZ
-------------------------------------
Second Normal Form - 2013-10-25: 23
Indexing Nulls - 2013-11-01: 21
Database Constraints - 2013-10-11: 19

-- In general there aren't any topics people 
-- don't want to see more of. The maximum 
-- number of people selecting "One was enough" 
-- on a quiz is one - this has happened 6 times

Overall I’m happy with how it’s gone – and it seems in general players are too! Only one quiz has an average rating less than 4/5 :) I have to thank my reviewers – Andrew Wilson, Kent Graziano and Yuan Tschang for their excellent and invaluable feedback. Without their help I’m sure the feedback scores would be at least a bit lower…

I find the quantative stats interesting and useful when planning what to write quizzes on in the future. The raw numbers can hide some qualitative details though.

Which topics haven’t been covered that you would like to see quizzes on? Which quizzes/topics did you particularly like or dislike and why? If you’ve got any feedback then please share it. You can leave your thoughts in the comments or contact me directly – either way I’m keen to hear from you!

Jan 062014
 

The first quarter of the database design quiz on the PL/SQL challenge finished last week. Being a numbers geek, here’s some facts and figures from how it’s gone so far:

select count(*)
from   quizzes;

COUNT(*)
--------
      13

-- Or about the same mass (in kg) 
-- of a gold bar :)

select count(*)
from   players;

COUNT(*)
--------
     463

-- Also the numbers of days it takes the 
-- planet Ceres to orbit its sun

select count(*)
from   answers_given;

COUNT(*)
--------
    3145

-- that's about 13 times the length of a 
-- Hidenburg Zeppelin!

select name, score
from   (select name, score
        from   players
        order  by score desc
)
where  rownum <= 5;

NAME             SCORE
---------------- -----
Justin Cave       5090	
Andres            4559
Frank Puechl      4477
mentzel.iudith    4439
Krzysztof Helbin  4409

-- Congrats to Justin, 
-- the clear winner for this quarter

select sum(answer_time) TIME_DAYS_HH:MI:SS
from   player_answers

TIME_DAYS_HH:MI:SS
------------------
17 13:36:45

-- Whoa, that's a lot of time taken!
-- there's a few players with abnormally 
-- high times, let's strip those out...

select sum(answer_time) TIME_DAYS_HH:MI:SS
from   player_answers
where  answer_time < interval '5' hour;

TIME_DAYS_HH:MI:SS
------------------
8 17:19:57

-- That's still nearly 9 days!

If you’ve been playing, hope you’ve enjoyed the quiz so far and will continue to play through 2014! If not, then join the game over on www.plsqlchallenge.com