Jul 20 2010

Some notes on SQL: 5 – database design

This is the fifth in a series of blog posts on SQL, the first covered creating a database, the second selecting information from a database, the third commands to modify the structure and contents of an existing database, the fourth on advanced selection. This post covers database design, as such it is a little lighter on the code examples. No claim of authority is made for these posts, they are mainly intended as my notes on the topic. These notes are based largely on Head First SQL.

The goal of database design is to produce a database which is straightforward and efficient to search. This is done by splitting data into a set of tables, with lookups between those tables used to build the desired output results.

Efficient database design is normally discussed with reference to “normal forms“, the goal being to reach the highest order normal form. In practice, pragmatism is applied which means it may be sensible to hold back a little on this.

First normal form – each row of data must contain atomic values, and each row of data must have a unique identifier, known as a Primary Key. “Atomic values” are essentially the smallest pieces into which data can be sensibly divided, this may depend on application. So, for example, in some cases a house address may be kept as a single text field whilst in others it might be divided into Number, Street, Town etc. Furthermore to be “atomic” data should not be repeated (i.e. a table containing interests should not contain columns “interest_1”, “interest_2″…The Primary Key may be a single column of ‘synthetic’ numbers (i.e. they don’t have any other purpose), or it may be a pre-existing column in the table, or it may be a combination of columns which case it is called a Composite Key. Primary and Composite Keys are indicated using the PRIMARY KEY keyword :

CREATE TABLE customer
(
sid        INTEGER,
last_name  VARCHAR(30),
first_name VARCHAR(30),
PRIMARY KEY (sid)
);
For a composite key, this form is used:
PRIMARY KEY (column_1,column_2,column_3)
Second normal form the table is in first normal form, and in addition contains no ‘partial functional dependencies’, this happens naturally with synthetic primary keys. Partial functional dependency means that a non-key column is dependent on some but not all of the columns in a composite primary key.

Third normal form the table is in second normal form, and in addition contains no ‘transitive dependencies’. Transitive functional dependency is when any non-key column is related to any of the other non-key columns. This page has a nice example, if we have a table with columns: {Project_id, manager_name, manager_address} then manager address and manager name are transitively dependent: change manager name and we change manager address. To address this in third normal form we split the table into two tables {Project_id, manager name} and {Manager_name, manager_address}. As the author writes:

In a normalised relation a non-key field must provide a fact about the key, the whole key and nothing but the key.

Relationships between tables in a database are indicated like this:

CREATE TABLE orders
(
order_id     INTEGER,
order_date   DATE,
customer_sid INTEGER,
amount       DOUBLE,
PRIMARY KEY (order_id),
FOREIGN KEY (customer_sid) REFERENCES customer(sid)
);
(Example borrowed from here). PRIMARY KEY and FOREIGN KEY are examples of ‘constraints’, primary keys must be unique and a foreign key value cannot be used in a table if it does not exist as a primary key in the referenced table. The CONSTRAINT keyword is used to give a name to a constraint (a constraint being one of NOT NULL, UNIQUE, CHECK, Primary Key, Foreign Key). CHECK is not supported in MySQL.

Keywords: PRIMARY KEY, CONSTRAINT, FOREIGN KEY, REFERENCES, CONSTRAINT

Jul 15 2010

On choice

Choose life. Choose a job. Choose a career. Choose a family. Choose a big fucking television. Choose washing machines, cars, compact disc players and electric tin openers. Choose good health, low cholesterol and dental insurance. Choose fixed interest mortgage payments. Choose a starter home. Choose your friends. Choose leisure wear and matching luggage. Choose a three-piece suite and higher purchase a wide range of fucking fabrics. Choose D.I.Y. and wondering who the fuck you are on Sunday morning. Choose sitting in a large couch watching mind-numbing spirit-crushing game shows stuffing fucking junk food in your mouth. Choose rotting away at the end of it all, pissing your last in a miserable home, nothing more than an embarrassment to the selfish fucked-up brats you’ve sworn to replace yourself. Choose your future, choose life. But why would you want to do a thing like that? I chose not to choose life. I chose something else. – Trainspotting by Irving Welsh (Screenplay by John Hodge)

For the last 20 years or so politicians have been keen on offering us choice, my message is “I don’t want choice”!

Choice of schools is something of an academic question for me since I don’t have any children but I grew up in rural Dorset and there the offer of choice would have been hollow. There were two primary schools in my village : one Roman Catholic and one Church of England, following that we went to the local “Middle School” one mile away – next nearest offering five miles away, followed by an upper school five miles away and the nearest alternative 10 miles and above away (to be honest I don’t even know where the alternative would be)… and this in an area with a rural transport system, not an urban one. A great deal of effort is expended in trying to rank schools, there’s evidence showing this process is not very accurate – the vast majority of schools are statistically indistinguishable. And who says schools are so important for education? My educational success is down, in large part, to the support of my parents but no-one seems to mention that. No one wants to say: actually your child’s education is very much down to you.

We get choice in medical care these days too but how am I supposed to judge the quality of a doctor or a hospital? Set some bright people a target and they’ll do a fine job of hitting it but is the target really representing the thing you want? People are actually quite keen to go to the hospital that’s close to them. Do we really expect patients to make an informed choice of which hospital is best for them from a medical point of view. I’m pretty sure I couldn’t make an accurate choice of the best hospital for medical care. Best hospital for me is easy: it’s the one about half a mile from my house. And what’s the message you’re sending when you’re offering a choice of hospital or doctor and providing data that purports to represent quality?:

“Here’s a bunch of hospitals – make sure you chose the best one. Do you feel lucky?”

I’d much rather you made sure that it didn’t matter which hospitals I went to.

People don’t actually like lots of choice, academic research on jam shows that consumers are more likely to buy jam from a choice of 6 types than from a selection of 24 types, too much choice confuses and causes unhappiness. This chimes with my experience, to a large extent I’ve given up being a rational economic agent, live’s too short to sweat over a choice of 100 different TVs.

This problem of ranking difficult to rank things is quite general, I experience it myself at work in my targets. I’ve come to the tentative conclusion that for people working in areas without clearly quantifiable outputs (number of strawberries picked, widgets sold, football games won), ranking really amounts to three buckets: sack, ok, promote. Your sack and promote buckets should really be pretty small. Yet we expend great effort on making more precise gradings. More interestingly I remember as I sat through an interminable college meeting discussing with an English fellow the marking of students. Normally for degree courses there’s a certain amount of second marking, in physics where there are definite answers second marking works fairly well but for my colleague in English one marker could mark a First and the other a 2.2/3rd, for the same essay!

Don’t give me choice, give me uniformity!

Jul 14 2010

Some notes on SQL: 4 – Advanced select

This is the fourth in a series of blog posts on SQL, the first covered creating a database, the second selecting information from a database, the third commands to modify the structure and contents of an existing database. This post covers more advanced commands for selecting information from a database and ways of manipulating the results returned. No claim of authority is made for these posts, they are mainly intended as my notes on the topic.

SQL supports CASE statements, similar to those which are found in a range of programming languages, they are used to write multiple comparison sequences more compactly:


UPDATE my_table
 SET    new_column = CASE
WHEN column1 = somevalue1 THEN newvalue1
WHEN column2 = somevalue2 THEN newvalue2
ELSE newvalue3
END;

The CASE statement can also be used in a SELECT:


SELECT title,
price,
budget = CASE price
WHEN price > 20.00 THEN ‘Expensive’
WHEN price BETWEEN 10.00 AND 19.99 THEN ‘Moderate’
WHEN price < 10.00 THEN ‘Inexpensive’
ELSE ‘Unknown’
END,
 FROM   titles

(This second example is from here)

The way in which results are returned from a SELECT statement can be controlled by the ORDER BY keyword with the ASC (or ASCENDING) and DESC (or DESCENDING) modifiers. Results can be ordered by multiple keys. The sort order is numbers before letters, and uppercase letters before lowercase letters.

SELECT title,purchased
 FROM   movie_table
 ORDER  BY title ASC, purchased DESC;

ASCENDING order is assumed in the absence of the explicit keyword.

There are various functions that can be applied to sets of rows returned in a query to produce a single value these include MIN, MAX, AVG, COUNT and SUM. The  functions are used like this:

SELECT SUM(sales)
 FROM   cookie_sales
 WHERE  first_name = ‘Nicole’;

This returns a sum of all of the “sales” values returned by the WHERE clause. Related is DISTINCT which is a keyword rather than a function so the syntax is slightly different:

SELECT DISTINCT sale_date
 FROM   cookie_sales
 ORDER  BY sale_date;

This returns a set of unique dates in the sale_date column.

The GROUP BY keyword is used to facilitate the use of functions such as SUM etc which take multiple arguments to produce a single output, or to reduce a list to distinct elements (in these circumstances it is identical to the DISTINCT keyword but execution may be faster). The format for GROUP BY is shown, by example below:


SELECT first_name, SUM(sales)
 FROM   cookie_sales
 GROUP  BY first_name;

This will return a sum of the “sales” by each person identified by “first_name”. A final keyword used to control the output of a SELECT statement is the LIMIT keyword which can take one or two parameters the behaviour for the two forms is quite different. One parameter form:

SELECT * FROM your_table LIMIT  5;

This returns the first five results from a SELECT. Two parameter form:

SELECT * FROM your_table LIMIT  5, 5;

This returns results 6,7,8,9 and 10 from the SELECT. The first parameter is the index of the first result to return (starting at 0 for the first position) and the second parameter is the number of results to return.


Keywords: CASE, WHEN, THEN, ELSE, ORDER BY, ASC, DESC, DISTINCT, MIN, MAX, AVG, COUNT, SUM, GROUP BY, LIMIT

Jul 12 2010

How does a magnet work?

How does a magnet work? This question arose on “I’m a Scientist, Get me out of here“, a fine piece of science communication which involved putting scientists in contact with school children. This is my attempt at an answer, which says a bit more about science in general but is utterly untimely. The short answer to the question is that magnets are made from atoms which act like little magnets and in a proper magnet are all lined up, but as an answer this is somewhat unsatisfactory.

From a scientific point of view, what you’d commonly call magnets are just one group of magnetic materials – the ferromagnets. They are accompanied in early magnetism courses for aspiring physics students by paramagnetic and diamagnetic materials. A ferromagnetic material, like iron, is strongly attracted to a magnet, a paramagnetic material is weakly attracted and a diamagnetic material is very weakly repelled. Diamagnetism and paramagnetism are useful for scientific research but it is ferromagnetism where all the practical applications are found. Iron, cobolt and nickel are the only ferromagnetic elements.

At this point I am ashamed to admit I nearly missed out on a tortured analogy to explain magnetism but fortunately I caught myself in time! Imagine, if you will, a crowd bearing vuvuzelas. Individuals in this crowd can blow their vuvuzelas in any direction they please, however much we might wish they didn’t. In a ferromagnet groups of vuvuzela players spotting their neighbours spontaneously face the same direction to play their devilish instruments. The whole crowd may not be blowing them in the same direction but groups of them will. They can be marshalled to all blow their horns in the same direction by a band leader, and once pointing in the same direction they will continue to face that way, even in the absence of the band leader.

The individuals in this group are atoms in a material, and the vuvuzelas represent the magnetic field of a single atom. Groups of players facing in the same direction represent magnetic domains and the band leader represents an applied magnetic field. The point about ferromagnets is they massively enhance an a magnetic field applied by something like a coil of wire with a current flowing through it – this is how you make an electromagnet. The difference between a “magnet” and any old bit of ferromagnet is that in a “magnet” all the domains have been lined up to face the same way.

In paramagnetic materials vuvuzelas players ignore their neighbours and play away in random directions, they respond in a somewhat feeble fashion to the directions of the band leader.

In diamagnetic materials the crowd have no vuvuzelas but use their hands as a substitute, rather petulantly they face the opposite direction to that proposed by the band leader. In scientific language the hands represent induced magnetic dipole moments.

But why is an atom magnetic? An atom could be magnetic because the electron orbiting the nucleus acts like a little current loop, which gives it a magnetic field like a little bar magnet (posh name for “little bar magnet” is “magnetic dipole moment”) but actually the majority of the magnetic dipole moment of an atom comes from the intrinsic magnetic dipole moment of individual electrons.

We really don’t know why the electron acts as a magnetic dipole, it is ascribed to a property known as ‘spin’ but it can’t be spin as we normally define it since electrons are, as far as we can tell, point-like – nobody has every managed to measure the diameter of an electron. Therefore how can we meaningfully describe it as spinning? In a sense the origin of the electron magnetic dipole moment is not important, it exists, we know what it is and we can use the measured value in our calculations for designing magnetic materials. This question of the “why” of fundamental properties of sub-atomic particles is what string theory seeks to address. For most scientists the answer is unimportant for practical applications, but for physicists in particular it is a nagging unpleasantness that we don’t know why.

Magnetism and electricity have been known since antiquity but as two very separate phenomena, and unsurprisingly really. Magnetism is a property of some funny rocks (lodestone) whilst electricity is a property of rubbed materials, and lemons. The connection between the two is far from obvious, the link was made in the early part of the 19th century. This is a recurring theme in science, we blithely teach that such and such is true, implying that it’s truth is pretty much self-evident and elide the fact that for most of history we have not believed these things and that it has taken the painstaking work of a number of very great minds to reveal these self-evident truths. I must admit to being a little unsure of the history in this area, taught in England the key figures were Michael Faraday who did much of the experimental work in linking electricity and magnetism followed by James Clerk Maxwell who formulated a mathematical theory. In the experimental area in particular there were many other participants in the story, I suspect who takes centre stage depends on where you are taught.

So there you have it: magnets work because the vuvuzela players copy each other and play in the same direction.

Jul 10 2010

The Lake District – Santon Bridge

Wast Water with Great Gable

Once again we return to the Lake District for a holiday. This time to Santon Bridge, close to the foot of Wast Water, at Hallflat Farm where we have a one bedroom converted cowshed rather more modern feeling than our previous stays in the Lake District with rather fine exposed wooden beams making it feel almost tent-like. The farm has been turned over completely to tourism, with half a dozen or so rentable units. We have a little area to sit out in the sunshine and neighbours to whom we can merely nod if we so desire. Shopping seems to be best done at the village store in Gosforth, a few miles down the road. There are local great-spotted woodpeckers the male apparently sports a natty red beret, the female is a little more subtle. Mrs SomeBeans managed to capture some video, I’ve never had a clear sight of a greater-spotted woodpecker – here they’re regular visitors to the bird feeder outside the kitchen.

It is traditional for each Lake District holiday that I show Mrs SomeBeans a campsite with it’s distant toilet block and flimsy canvas walls and highlight the comparison with the great luxury in which we stay. I saw much of Europe and the UK from a tent as a child, and whilst this no doubt greatly broadened my horizons I intend never to sleep under canvas (or nylon) again.

Last year we stayed at Chapel Stile in Great Langdale, a fine location from which we walked everyday with only one brief trip in the car to the head of the valley to ascend Bow Fell (whose head remained steadfastly in cloud). The cottage we stayed in was good, with an excellent kitchen the only drawback slightly restricted parking (not a problem if you never use the car) and rather limited outdoor seating. My parents honeymooned at the Dungeon Ghyll Hotel (famous amongst climbers), a mile or so up the road from our cottage. For shopping we used the local village general store which furnished our needs admirably, providing in addition to food, two very light macs which turned out to be rather useful. The previous two years we stayed in Borrowdale, at Rosthwaite, again providing walks from the door for most of a week. A slightly quieter location with fewer neighbours. Shopping this time was in Keswick, or rather Booths – a rather fine slatey version of Waitrose.

Alfred Wainwright is the undisputed king of the Lakes for people like us, not only was he intensely well-organised – planning his fantastic guides to be completed over a 13 year period which he hit almost to the week, he was fond of peaks and hated people. We differ only slightly in that we like the valleys too, and enjoy the wide variety of nature. A successful days walk for us is one in which me meet no other people and see some interesting nature: sundews, butterwort (carnivorous plants), miscelleous beetles, birds, orchids, and rocks. It’s unlikely that Wainwright strode along the broader, flatter paths with the Imperial March from Star Wars running through his mind, which is what Mrs SomeBeans and I do – it’s the syncopation of marching foot on gravel path.

What we did on our holidays this year (sounds like a school project):

Sunday: We venture out on a short walk of our own devising over to Nether Wasdale, part way around it starts raining VERY heavily, and we trudge through the rain for an hour and a half or so. Towards the end my well water-proofed boots are squelching vigorous and I struggle to identify any part of my body which is not wet. On our return to the cottage I wring out my socks. In the afternoon we head out to the local countryside: visiting Seascale, neighbour of the Sellafield nuclear reprocessing plant, Drigg (low level waste disposal). Ravenglass (we return here for a walk on Friday), sporting the terminus of a dinky steam railway. Later we head up towards Eskdale Green, a mile or so from Boot where the Cumbrian shooter finally met his own end. We’d passed flowers by the roadside on the way to Seascale (a grim settlement whipped by the wind, featuring much gray concrete render). Eskdale, in which Boot lies, is different – almost fairy tale with heavily mossed deciduous woodland and little crags.

Steam train at Ravenglass

Monday: we do a walk from “the book” (#12 Wast Water and Irton Fell), taking in the foot of Wast Water and heading up Irton Fell. The walk is very varied with some meadow on the valley floor, lake side, steep brackened climb, forestry woodland, and high moor. The view up Wast Water towards Great Gable (scaled on a previous holiday) is excellent. Nature-wise we catch our first sight of sundew, a very large caterpillar and note we are walking on granite, not the slate of Borrowdale. Very few people seen.

Tuesday: a trip to Ennerdale to walk around the Water and then onto St Bees where we looked in rock pools. We did the book-walk (#13) around Ennerdale Water, although it’s a very simple affair. The path on the left of the lake is a forestry road, and hence well-made, we met no-one on this stretch although a few vehicles passed us in each direction. The path back along the right of the lake is much more rugged and was much busier, this is part of the coast to coast path. For lunch, naturally overlooking the lake, we were joined by a frog. St Bees and Egremont seem to have seen better days, they both have quite significant buildings (St Bees a smart school, a hall and a large church) which don’t seem consistent with their current situation. Honeysuckle seems to be the flower of the week.

Wednesday: another trip of our own devising, today the weather was uncertain in the morning so we made a relatively late start to walking (10am). We parked close to Nether Wasdale and went up to Whin Rigg and then on to Illgill Head then dropping down into Wasdale Head. As it was clouds wafted across us for an hour or so in the morning then it cleared, and as we finished walking just before 4pm it was fairly clear and sunny. For the first time on a walk, Mrs SomeBeans and I parted ways at Wasdale Head. I walked along the near shore of Wast Water, whilst Mrs S. took the road. This was for fear of the screes, probably justified, there are two bands of scree: the first is fairly civilised with a path across it. The second is rather more challenging, with no discernable path just a jumble of boulders of man-size and above. I collected an impressive scrape on the approach to the screes, spotting a distant Mrs S across the lake apparently pulling ahead I hurried on rather quicker than was wise and ended up head first in bracken! I am forming a fine scab, the like of which I have not had since childhood.

Thursday: We walked up Overbeck to Dore Head from where fantastic views of Pillar and into Mosedale are available. The original plan was to head up on a great loop over Red Pike but this turned out to be a bit over-ambitious. We contemplated Yewbarrow which looks quite difficult to get onto, Wainwright appears to agree but finds a easier route. Cows were used in the valley, and each cowpat had it’s own little forest of mushrooms.

Friday: A wet day with intermittent showers most the morning as we did the Ravenglass and Muncaster walk (#15), this is a fairly long walk (7 miles) well suited to the poor weather, with a large fraction of the route under cover of trees and relatively low altitude – even so we briefly entered cloud on the Muncaster ridge. Highlights were the ruins of a Roman Bath on the outskirts of Ravenglass, these ruins comprise walls several metres above ground level including complete arches and an alcove, very few Roman ruins in the UK extend above ground level. A second highlight was a infestation of tiny frogs on a forest path.

Tiny frog!
Ravenglass Roman Baths

All in all not a bad holiday, weather about par for the Lake District with 2 days seriously affected by rain, and the rest threatening rain at some point. Not as much big mountain walking as we have done in the past, but more varied with trips to the seaside. The area feels quieter than the central areas of the Lake District, with free or honesty box parking. Once again mobile phones entirely useless for the entire length of the holiday due to lack of reception.