I'm OK, You're Abnormal part of SQL for Web Nerds by Philip Greenspun

Abnormal

Movie ID	Title	Year Released	Producer	Director	Writer	Composer	Steadicam Operator
1	Alien: Resurrection	1997	Bill Badalato	Jean-Pierre Jeunet	Joss Whedon	John Frizzell	David Emmerichs
2	The Age of Innocence	1993	Barbara De Fina	Martin Scorsese	Edith Wharton, Jay Cocks, and Martin Scorsese	Elmer Bernstein	Larry McConkey
3	Heathers	1989	Denise Di Novi	Michael Lehmann	Daniel Waters	David Newman	--

If you memorize this material, you can attract Ms. Ryder's attention with such questions as "Didn't you think Larry McConkey's Steadicam work in The Age of Innocence was much better than David Emmerichs's in Alien 4?" Or if you want to impress with your knowledge of American literature, say "It is amazing how rich a script Jay Cocks and Martin Scorsese produced considering the thinness of the material they had to start with from Edith Wharton's original novella."

Suppose that Winona Ryder is sufficiently impressed by your knowledge to come back to your suite at the Regent Beverly Wilshire. Taking advantage of the in-room Ethernet connection, you've left a remote Emacs window up and running on your laptop. Ms. Ryder sees your SQL queries in the source code for your coaching app and shouts out "Hey, these data aren't even in First Normal Form (1NF)."

My this is a faux pas. If your data aren't in First Normal Form, there isn't even an egghead DBMS nerd term for what you've got. Loosely speaking, your data are abnormal. What is wrong with your data model? For Alien and Heathers you've got a single name in the Writer column. For The Age of Innocence, you've got a trio of names. This is known as a repeating group or a multivalued column and it has the following problems:

• you might not have enough space if the number of values in the column grows larger than anticipated
• the combination of table name, column name, and key value no longer specifies a datum
• the basic INSERT, UPDATE, and SELECT operations are not sufficient to manipulate multivalued columns
• programmers' brains will have to adapt simultaneously to unordered data in table rows and ordered data inside a multivalued column
• design opacity. If you use multivalued columns even once, people will never know what to expect when they look under the hood of your design; did you use multiple tables to express a many-to-one relation or multivalued columns?

For modelling one-to-many relationships, Oracle supports two collection
datatypes: varrays and nested tables. For example, a purchase order has
an arbitrary number of line items, so you may want to put the line items
into a collection.

   Varrays have a maximum number of elements, although you can change
   the upper bound. The order of elements is defined. Varrays are stored
   as opaque objects (that is, raw or BLOB).

   Nested tables can have any number of elements, and you can select,
   insert, delete, and so on the same as with regular tables. The order
   of the elements is not defined. Nested tables are stored in a storage
   table with every element mapping to a row in the storage table.

If you need to loop through the elements in order, store only a fixed
number of items, or retrieve and manipulate the entire collection as a
value, then use varrays.

If you need to run efficient queries on collections, handle arbitrary
numbers of elements, or do mass insert/update/delete operations, then
use nested tables. If the collections are very large and you want to
retrieve only subsets, you can model the collection as a nested table
and retrieve a locator for the result set.

For example, a purchase order object may have a nested table of line
items, while a rectangle object may contain a varray with 4 coordinates.

First Normal Form

Abigail: I am but God's finger, John. If he would condemn Elizabeth, she will be condemned. Arguments against normalization continue to sway practitioners. Fabian Pascal says that this costs dearly and reveals the poor understanding of sound database principles by even those who profess to be experts in the field. It is both a major reason for and a consequence of deficiencies in SQL implementations and for technology regressions, such as ODBMS and OLAP, that have come to haunt SQL DBMSs in the town of Salem.

Event Registration

• How to pick up Jewish babes in Hollywood
• Using your SQL programming expertise to get a seat at the Academy Awards
• What I learned about normalization from Winona Ryder

-- one row for every different talk that we give

create table talks (
	talk_id		integer primary key,
	talk_title	varchar(100) not null,
	description	varchar(4000),
	speaker_name	varchar(100),
	speaker_bio	varchar(4000)
);

-- one row for every time that we give a talk

create table events (
       event_id		integer primary key,
       -- which of the three talks will we give
       talk_id		references talks,
       -- Location
       venue_name	varchar(200) not null,
       street_address	varchar(200) not null,
       city             varchar(100) not null,
       -- state if this is in the US
       usps_abbrev      char(2),
       -- country code is this is a foreign city
       iso		char(2) default 'us',
       -- Date and time
       start_time	date not null,
       end_time		date not null,
       ticket_price	number
);

Second Normal Form

-- one row for every different talk that we give

create table talks (
	talk_id		integer primary key,
	talk_title	varchar(100) not null,
	description	varchar(4000),
	speaker_name	varchar(100),
	speaker_bio	varchar(4000)
);

-- one row for every place where we give a talk

create table venues (
       venue_id		integer primary key,
       venue_name	varchar(200) not null,
       street_address	varchar(200) not null,
       city             varchar(100) not null,
       -- state if this is in the US
       usps_abbrev      char(2),
       -- country code
       iso		char(2) default 'us'
);

-- one row for every time that we give a talk

create table events (
       event_id		integer primary key,
       -- which of the three talks will we give
       talk_id		references talks,
       -- Location
       venue_id		references venues,
       -- Date and time
       start_time	date not null,
       end_time		date not null,
       ticket_price	number
);

Third Normal Form

create table ticket_price (
       up_to_n_minutes	  	integer,
       price			number
);

Reference

• Chapter 4 of Access Database Design & Programming
• Chapter 1 of Transact-SQL Programming