Q2 2013 Championship will NOT be held on 3 August

It is tough finding a time in the summer to play the Championship! Enough players said "no" to 3 August to trigger the rule that says: "Find a new date!" So the championship will not be held on 3 August. We will poll our players and find a better date - and for this second attempt, we will shift back to a weekday, not the weekend.

Q2 2013 PL/SQL Championship to be held on 3 August

The second quarter of 2013 is now history. And that means....it's time for the next championship competition!

The following players will be invited to participate in the Q2 2013 championship. The number in parentheses after their names are the number of playoffs in which they have already participated.

See the FAQ for an explanation of the three ways a player can qualify for the playoff.

And congratulations to all listed below on their accomplishment and best of luck in the upcoming competition! We have six first-time championship players and many veterans of this fine competition.

This championship will be different from past events in two important ways:

Everyone will play at the same time: 3 August 16:00 UTC. Which means we are also holding the championship on a Saturday, the first time ever that we play on the weekend. Our three players from the Asia-Pacific region (who will be playing in the very early morning) have graciously accepted this additional challenge. Everyone else is in Europe and the United States, so it shouldn't be too painful. :-) We shall see how it goes!

Name	Rank	Qualification	Country
Ajaykumar Gupta (1)	1	Top 25	Singapore
Rakesh Dadhich (2)	2	Top 25	India
Janis Baiza (6)	3	Top 25	Latvia
swart260 (4)	4	Top 25	Netherlands
mentzel.iudith (10)	5	Top 25	Israel
Stelios Vlasopoulos (7)	6	Top 25	Belgium
Chris Saxon (5)	7	Top 25	United Kingdom
Vinu Garg (3)	8	Top 25	India
Milibor Jovanovic (2)	9	Top 25	Serbia
Mike Pargeter (10)	10	Top 25	United Kingdom
Ivan Blanarik (5)	11	Top 25	Slovakia
Viacheslav Stepanov (9)	12	Top 25	Russia
Niels Hecker (11)	13	Top 25	Germany
Veera Marimuthu (1)	14	Top 25	Singapore
Vincent Malgrat (5)	15	Top 25	French Republic
Frank Schrader (11)	16	Top 25	Germany
kowido (9)	17	Top 25	Germany
Jeroen Rutte (4)	18	Top 25	Netherlands
Dieter Kowalski (4)	19	Top 25	Germany
Jerry Bull (8)	20	Top 25	United States
Siim Kask (10)	21	Top 25	Estonia
Jason H (1)	22	Top 25	United States
Leszek Grudzień (0)	23	Top 25	Poland
Tony Winn (2)	24	Top 25	Australia
Frank Schmitt (5)	25	Top 25	Germany
Peter Schmidt (3)	31	Correctness	Germany
Joaquin Gonzalez (5)	32	Wildcard	Spain
Chad Lee (8)	33	Correctness	United States
Matthias Rogel (2)	34	Wildcard	Germany
Naresh Kumar (0)	35	Wildcard	India
Jan Soubusta (0)	48	Wildcard	Czech Republic
Alexey Ponomarenko (0)	77	Wildcard	Ukraine
Yuan Tschang (6)	85	Correctness	United States
Randy Gettman (10)	94	Correctness	United States
Pavel Noga (0)	96	Correctness	Czech Republic
Michal Cvan (8)	107	Correctness	Slovakia
Livio (0)	332	Correctness	Luxembourg

Are "abandoned" quizzes included in rankings analysis?

JasonC asks this question:

Occasionally, I'll start a quiz, and it's about a subject I have no knowledge of at all - I can't even make an educated guess.  So I just close the window and do something else.

So then I wondered if my non-start gets logged somewhere as null points, and a VERY long time ?

The reason for this is that I notice I nearly always complete the quiz quicker than the AVERAGE time, but slower than the MEAN time, which suggests to me that a few excessively long times are skewing the average - maybe these could be people failing to complete the quiz?  None of this really matters: I've no complaints about my scores (well, they're lower than I would like, but that's another story!) - I'm just curious.

Excellent question! I thought I knew the answer but decided to look at the code, anyway. The code always tells the truth. :-)

Here's what the code tells me:

   PROCEDURE submit_saved_answers (comp_event_id_in IN INTEGER)
   IS
      l_comp_event      qdb_comp_events%ROWTYPE
                           := one_comp_event (comp_event_id_in);
      l_competition     qdb_competitions%ROWTYPE;
      l_answer_closed   BOOLEAN DEFAULT FALSE;
   BEGIN
      /* Assign an end date to all answers for which there is at 
           least one saved answer. */
      FOR rec
         IN (SELECT DISTINCT eva.compev_answer_id, eva.user_id
               FROM qdb_compev_answers eva, qdb_quiz_results qr
              WHERE     eva.compev_answer_id =
                           qr.compev_answer_id
                    AND eva.comp_event_id = comp_event_id_in
                    AND eva.ended_on IS NULL)
      LOOP
         UPDATE qdb_compev_answers eva
            SET ended_on =
                   qdb_player_mgr.user_end_time (
                      comp_event_id_in,
                      rec.user_id)
          WHERE eva.compev_answer_id = rec.compev_answer_id;
      END LOOP;
   END submit_saved_answers;

Look, a comment! I proudly proclaim in my trainings that my code is self-documenting, requiring no comments. But I am glad I broke my pledge here.

 

Bottom line: in the current scheme of things at the PL/SQL Challenge, we will not automatically set an end time for your answer when the quiz consists of a single question. For multiple-question competitions like the playoff, we will automatically set an end time if you answered at least one of the questions.

There can still, however, be some very long answer times that will skew the average. I have tried to isolate those in at least some of our calculations, but may not have caught them all.

Some Changes in Championship Rules (and more)

We have decided to institute a few changes in the rules and format for the quarterly championship, as well as rules for winners of other prizes.

First, regarding winners of prizes (weekly, monthly, etc.): while you can choose to remain anonymous on your public profile, you will not be eligible to receive a prize unless you have completed the following parts of your profile (which you can keep private):

On your Account-Personal page, provide your real and full name, as well as the country in which you reside. Then complete at least one of the three "My Website" fields with your LinkedIn account, professional website and/or other webpages that identify you and your profession. Your company website, combined with an email address in the same domain, is acceptable.

On the Account-Professional page, tell us the name of the company for which you work, the university you attend, or whatever is appropriate in your case.

Bottom line: we want to make sure that the players who win prizes are "real people" and not duplicate accounts, team efforts, or anything else. Of course, we can't stop you from putting in "phony" data, but we remain confident in the honesty and integrity of our players.

Second, regarding the quarterly championship:

1. The above rule applies to everyone who wishes to participate in the championship. In other words, even if you qualify by ranking, you will not be able to play in the championship without completing the minimal elements of your profile listed above. Only "real people" can compete!

2. Everyone will play in the championship at the same time. We will no longer offer multiple times at which it can be taken. We realize that this could cause hardship for some players (I'm thinking about the Pacific nations mostly), but we figure that if you are sufficiently honored and excited to be in the championship, you'll make it work.

In all cases, if a player does not provide the necessary information their status will be set to "Not Ranked" for the appropriate quiz.

We haven't finalized the time for the championship yet, but since most players are in the US and in Europe, we expect to aim for the end of the work day in Europe, late morning in the US.

Thanks once again for your dedicated play on the PL/SQL Challenge site. We will be unveiling new features in the coming months that will make it an even better at helping you become (more of) an expert in Oracle technologies.

Warm regards,
Steven Feuerstein

Proposal for new weekly quiz on Database Design

Introduction

In addition to writing PL/SQL code and constructing SQL queries, database developers are often called on to design table structures. Wouldn’t it be great if there was a weekly quiz to test and improve your knowledge of data modelling to help you with this? Well now there can be!

To complement the existing PL/SQL and SQL quizzes, we (Steven Feuerstein and Chris Saxon, a longtime PL/SQL Challenge player who is offering to administer this quiz) propose the creation of a new weekly "Database Design" quiz as part of the PL/SQL Challenge. The intended scope of this quiz is:

• Reading and understanding database schema diagrams
• Determining how to apply database constraints (e.g. foreign keys, unique constraints, etc. ) to enforce business rules
• Understanding and application of database theory (normal forms, star schemas, etc.)
• Appropriate indexing strategies and physical design considerations (e.g. partitioning, index organised tables, etc.)

Below is a starting set of assumptions and three example quizzes. Please have a read of these and send us your thoughts on whether or not you would be interested in a database design quiz and the nature of the questions.

Thanks!
Steven and Chris

Assumptions

All schema diagrams shown will be drawn using Oracle Data Modeler, using the following settings:

* The Logical Model Notation Type of Barker

* Relational Model Foreign Key Arrow Direction set to “From Foreign Key to Primary Key”

To indicate constraints on columns, the following will appear next to them:

• An asterisk indicates that the column is mandatory (i.e. not null)
• P means this forms part of the primary key for the table
• F means this forms part of a foreign key to another table
• U means this forms part of a unique constraint on the table

For normal forms, the following definitions are used:

First Normal Form (1NF):

• Table must be two-dimensional, with rows and columns.
• Each row contains data that pertains to one thing or one portion of a thing.
• Each column contains data for a single attribute of the thing being described.
• Each cell (intersection of row and column) of the table must be single-valued.
• All entries in a column must be of the same kind.
• Each column must have a unique name.
• No two rows may be identical.
• The order of the columns and of the rows does not matter.
• There is (at least) one column or set of columns that uniquely identify a row
• Date’s definition that all columns must be mandatory for a table to be in 1NF will not be included for the purposes of this quiz.

Second Normal Form (2NF):

• Table must be in first normal form (1NF).
• All nonkey attributes (columns) must be dependent on the entire key.

Third Normal Form (3NF):

• Table must be in second normal form (2NF).
• Table has no transitive dependencies.

Boyce-Codd Normal Form (BCNF)

• Table must be in third normal form
• Table has no overlapping keys (that is two or more candidate keys that have one or more columns in common)

* The only database objects that exist are those shown in the quiz or are available in a default installation of Oracle Enterprise Edition

* There is no relationship between different answers within a quiz. The correctness of one choice has no impact on the correctness of any other choice.

* All code (PL/SQL blocks, DDL statements, SQL statements, etc.) is run in Oracle's SQL*Plus.

* The edition of the database instance is Enterprise Edition; the database character set is ASCII-based with single-byte encoding; and a dedicated server connection is used. The version of the Oracle client software stack matches that of the database instance.

* All code in the question and in the multiple choice options run in the same session (and concurrent sessions do not play a role in the quiz unless specified). The schema in which the code runs has sufficient system and object privileges to perform the specified activities.

* When analyzing or testing choices, you should assume that for each choice, you are starting "fresh" - no code has been run previously except that required to install the Oracle Database and then any code in the question text itself.

* The session and the environment in which the quiz code executes has enabled output from DBMS_OUTPUT with an unlimited buffer size, using the equivalent of the SQL*Plus SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED command to do the enabling. The SET DEFINE OFF command has been executed so that embedded "&" characters will not result in prompts for input. Unless otherwise specified, assume that the schema in which the code executes is not SYS nor SYSTEM, nor does it have SYSDBA privileges.

* Index performance will be measured in terms of the “constistent gets” metric, as reported when enabling autotrace. This will be done using the command: “set autotrace trace” in SQL*Plus.

Q1: Topic: Understanding entity relationship diagrams

Difficulty: Beginner

Below is a schema diagram showing a table structure for storing order and product details in a database:

Given this structure, which of the following statements are true:

Questions

Q1. All orders must be linked to a product.

Q2. An order may be linked to more than one product.

Q3. A product may be associated with multiple orders.

Q4. It is not possible to have products with no linked orders.

Answers

A1. Correct. The ORDERS.PRODUCT_ID column is a mandatory foreign key to the PRODUCTS table, as denoted by the F (foreign key) and asterisk (mandatory) next to this column.

A2. Wrong. Only one value can be stored in the ORDERS.PRODUCT_ID column for a given ORDERS row.

A3. Correct. There is no unique constraint on the ORDERS.PRODUCT_ID column, therefore the same product can be listed on more than one row in the ORDERS table.

A4. Wrong. As the ORDERS.PRODUCT_ID column is mandatory, an entry must exist in the PRODUCTS table before an order can be linked to it. Therefore it must be possible to have a product with no associated orders.

Q3 Topic: 3rd normal form

Difficulty: Intermediate

A new social networking site is under development and work is taking place on the user profile page. The current requirements are a profile page should be able to display:

* A user’s email address

* Their name

* Their date of birth

* Their astrological star sign (determined from the day and month of their birth)

You’ve been asked to design the table to store this information and have come up with the following:

create table plch_user_profile (

 user_id             integer primary key,

 email varchar2(320) not null unique,

 display_name        varchar2(200),

 date_of_birth       date not null,

 star_sign           varchar2(10)

);

Unfortunately, the data architect isn’t happy with this, saying the table isn’t in third normal form! How can this be changed so it complies with third normal form while still meeting the requirements above?

Questions

Q1. Remove the unique constraint on the EMAIL column.

Q2. Remove the STAR_SIGN column from the table.

Q3. Change the DATE_OF_BIRTH column to accept null values.

Q4. Remove the DATE_OF_BIRTH column from the table.

Answers

A1. Wrong. Removing this constraint means we still have a dependency between DATE_OF_BIRTH and STAR_SIGN, which can lead to "update anomalies" if only one of these columns is changed. To meet third normal form, we must remove one of these columns.

A2. Correct. We can calculate a person's star sign from the day and month of their birth, so we can still display a person's star sign on their profile if we just store their date of birth.

A3. Wrong. There’s still a (non-prime) dependency between DATE_OF_BIRTH and STAR_SIGN. This means it is possible to enter a (date_of_birth, star_sign) pair that isn't valid in the real world (e.g. 1st Jan 1980, Libra)

A4. Wrong. Removing DATE_OF_BIRTH from the table does remove the non-prime dependency DATE_OF_BIRTH->STAR_SIGN so the table is now in third normal form. However, we can't determine the DATE_OF_BIRTH just from a peron's star sign, meaning we no longer meet the business requirement to display this!

Q3 Topic: Indexing strategies

Difficulty: Intermediate

We have the following table that stores details of customer orders:

create table plch_orders (

 order_id        integer primary key,

 customer_id     integer not null,

 order_date date not null,

 order_status    varchar2(20) check (

 order_status in ('UNPAID', 'NOT SHIPPED', 'COMPLETE', 'RETURNED', 'REFUNDED')),

 notes           varchar2(50)

);

This stores over a million orders that have been placed over the past ten years. The vast majority (>95%) of the orders have the status of COMPLETE. There are a few thousand different customers that place approximately one order per month.

Recently there’s been complaints from customers that their orders are taking a long time to arrive, so your boss wants a daily report displaying the PLCH_ORDERS.CUSTOMER_ID for orders that have been placed within the past month that have not yet been shipped. You put together the following query:

select customer_id

from   plch_orders

where  order_date > add_months(sysdate, -1)

and    order_status = 'NOT SHIPPED';

Unfortunately, there's currently no indexes on the columns used in this query so it's taking a long time to run! Which of the following indexes can we create that can benefit the performane of this query:

Questions

Q1. create index plch_unshipped on plch_orders (order_status);

Q2. create index plch_unshipped on plch_orders (order_status, order_date);

Q3. create index plch_unshipped on plch_orders (order_status, order_date, customer_id);

Q4. create index plch_unshipped on plch_orders (customer_id);

Q5. create index plch_unshipped on plch_orders ( customer_id, order_date, order_status);

Q6. create index plch_unshipped on plch_orders ( customer_id, order_status);

Answers

A1. Correct. Only a small percentage of orders will have the status "NOT SHIPPED" so an index on this will be beneficial.

A2. Correct. Adding the date to the index enables Oracle to filter the data even further, requiring fewer rows to be inspected in the table.

A3. Correct. This is a "fully covering" index, meaning the query can be answered without having to access the table at all.

A4. Wrong. CUSTOMER_ID doesn’t appear in the where clause of the query, therefore it is not available to Oracle to improve the execution of this query.

A5. Correct. This also a "fully covering" index, so the Oracle can answer the query by just inspecting the index without accessing the table. However, this will result in an “INDEX FAST FULL SCAN” rather than an “INDEX RANGE SCAN” as in answer three which is less efficient. This is because CUSTOMER_ID is the first column in the index, so Oracle is not able to access the NOT SHIPPED rows directly. Instead it must scan through the whole index

A6. Wrong. For an index to be considered by the optimizer then the first column(s) in the index must be in the where clause. If the leading columns have a low distinct cardinality, then Oracle can choose to perform an "index skip scan" operation. However, we have a large number of different customers in this case, so a skip scan is not possible.

Verification Code

create table plch_orders (

 order_id        integer primary key,

 customer_id     integer not null,

 order_date date not null,

 order_status    varchar2(20) check (

 order_status in ('UNPAID', 'NOT SHIPPED', 'COMPLETE', 'RETURNED', 'REFUNDED')),

 notes           varchar2(50));

insert into plch_orders

 select  rownum, mod(rownum, 3179),

         sysdate - ((1000000-rownum)/1000000)*3650,

         case when rownum <= 950000 then 'COMPLETE'

           else decode(mod(rownum, 5),

                       0, 'COMPLETE',

                       1, 'UNPAID',

                       2, 'NOT SHIPPED',

                       3, 'RETURNED',

                       4, 'REFUNDED')

         end,

         dbms_random.string('x', 50)

 from    dual

 connect by level <= 1000000;

commit;

exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);

set autotrace trace

PRO default query results in full table scan

select customer_id

from   plch_orders

where  order_date > add_months(sysdate, -1)

and    order_status = 'NOT SHIPPED';

create index plch_unshipped on plch_orders (order_status);

exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);

PRO Index range scan with table access by rowid

select customer_id

from   plch_orders

where  order_date > add_months(sysdate, -1)

and    order_status = 'NOT SHIPPED';

drop index plch_unshipped;

create index plch_unshipped on plch_orders (order_status, order_date);

exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);

PRO Index range scan with table accesss by rowid.

PRO Because order_date is included, this is index is more selective so results in fewer consistent gets

select customer_id

from   plch_orders

where  order_date > add_months(sysdate, -1)

and    order_status = 'NOT SHIPPED';

drop index plch_unshipped;

create index plch_unshipped on plch_orders (order_status, order_date, customer_id);

exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);

PRO Index range scan with no table access. The most efficient option

select customer_id

from   plch_orders

where  order_date > add_months(sysdate, -1)

and    order_status = 'NOT SHIPPED';

drop index plch_unshipped;

create index plch_unshipped on plch_orders (customer_id);

exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);

PRO Causes a full table scan as in the un-indexed example

select customer_id

from   plch_orders

where  order_date > add_months(sysdate, -1)

and    order_status = 'NOT SHIPPED';

drop index plch_unshipped;

create index plch_unshipped on plch_orders (customer_id, order_date, order_status);

exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);

PRO Results in an index fast full scan. This is less efficient than the other correct examples,

PRO but is still more efficient than a FTS, resulting in ~1/3 fewer consistent gets in my example

select customer_id

from   plch_orders

where  order_date > add_months(sysdate, -1)

and    order_status = 'NOT SHIPPED';

drop index plch_unshipped;

create index plch_unshipped on plch_orders (customer_id, order_status);

exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);

PRO Results in a full table scan as in the unindexed example

select customer_id

from   plch_orders

where  order_date > add_months(sysdate, -1)

and    order_status = 'NOT SHIPPED';