Aug 02 2010

A set of blog posts on SQL

This is a roundup post for a rather specialist set of posts I wrote on SQL (Structured Query Language), a computer language for creating and querying databases. Basically the posts are my notes on the language which I’m learning because a couple of programming projects I have in mind will need it. The main source for these notes is the Head First SQL book. I’ve used a another book in this series (Head First Design Patterns) – I quite like the presentational style. The code in the posts is formatted for display using this SQL to HTML formatter.

Topics covered:
Some notes on SQL: 1 – Creation
Some notes on SQL: 2 – Basic SELECT
Some notes on SQL: 3 – Changing a table
Some notes on SQL: 4 – Advanced SELECT
Some notes on SQL: 5 – Database design
Some notes on SQL: 6 – Multi-table operations
Some notes on SQL: 7 – Subqueries and views

Of course you can find SQL cheatsheets elsewhere.

The Head First SQL book also has material on transactions and security, if I get a renewed bout of enthusiasm I will add a post on these items.

I used MySQL via its command line client to do the exercises, because it’s about as straightforward as you can get. Notepad++ recognises SQL as a language and will do syntax highlighting, so I type my commands into it and copy them into the MySQL command line client. MySQL is pretty straightforward to install. I also installed Microsoft SQL Server Express 2008, which turned out to be a bit more of a struggle but on the plus side integration the C# .NET, which is what I normally program in, looks better than for MySQL.

I’ve been using with the SQL Server via SQL Management Studio (a graphical interface to databases) on the general election data compiled by The Guardian. First contact with actual data, as opposed to learning exercises has proved interesting! A lot of things that are fiddly to do in a spreadsheet are straightforward using  SQL.

SQL was designed in the early 1970’s, with commercial implementations appearing towards the end of the decade. It’s influence visible is visible in more modern languages, such as the LINQ extensions to C# (this influence is pretty explicitly stated). Some of the ideas of database design (normalisation) seem relevant to object-oriented programming.

It’s been an interesting learning experience, my scientific background in programming has me stuffing pretty much any sort of data into an array in the first instance. SQL and a database look like a much better solution for many situations.

Aug 02 2010

Some notes on SQL: 7 – Subqueries, combining selections and views

This is the seventh, and final, 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, advanced selection. The fifth post covered database design. The sixth post covered multi-table database operations. This post covers subqueries and views. No claim of authority is made for these posts, they are mainly intended as my notes on the topic.
This is largely a wrapping up blog post to cover a couple of items.As an alternative to the joins described in a previous post, “subqueries” can often be used. A subquery is essentially an entire query embedded in another query. Subqueries can be used with UPDATE, INSERT and DELETE statements, whilst joins cannot. However, joins can be used to bring columns from multiple tables. There are no special keywords involved in creating a subquery. There is some discussion on the pros and cons of subqueries and joins here on Stackoverflow.

In an uncorrelated subquery the so-called inner query can be evaluated with no knowledge of the contents of the outer query. This expression contains an uncorrelated subquery:

UPDATE raw_candidate_data
SET    gender = ‘f’
WHERE  firstname IN (SELECT firstname
FROM   firstname_gender_lookup
WHERE  gender = ‘f’); 

The inner query is the clause in brackets, in this instance it is a shorthand way of building a list for an “IN” comparison. Often an inner query returns a single value, i.e. for an average or maximum in a list.

This expression contains a correlated subquery:

UPDATE raw_candidate_data
SET    party_id = (SELECT party_id
FROM   parties
WHERE  raw_candidate_data.party = parties.party_name); 

The inner query requires information from the outer query to work, this expression acts as a look up.

Complex queries can be given aliases using the VIEW keyword, for example:

CREATE VIEW web_designer AS SELECT mc.first_name, mc.last_name, mc.phone,
mc.email FROM my_contacts mc NATURAL JOIN job_desired jd WHERE jd.title=‘Web Designer’; 

Can subsequently be used by:

SELECT * FROM   web_designers; 

The view web_designers can be treated just as any other table.

The results of multiple select commands can be combined using the keywords: UNION, UNION ALL, INTERSECT and EXCEPT. UNION returns the distinct union of the results of all the selects, i.e. with no repeats, UNION ALL includes the repeats. INTERSECT returns items that are common to both SELECTs and EXCEPT returns those items that are returned by the first SELECT but not the second. The general form for these combination operations is as follows:

SELECT title FROM   job_current
 UNION
 SELECT title FROM   job_desired
 UNION
 SELECT title FROM   job_listings
 ORDER  BY title; 

Each SELECT statement must return the same number of columns and each column must be coercible to the same datatype. Only a single ORDER BY statement for the set of SELECTs can be used.

Keywords: UNION, UNION ALL, INTERSECT, EXCEPT, VIEW

Jul 28 2010

Spider silk

Photo by Fir0002/flagstaffotos (GFDL license)

I’ve never worked on spider silk myself, but my work on synthetic polymers and biological physics took me to conferences where spider silk work was presented and it always struck me as a very interesting. Spider silk has a rather impressive set of material properties, yet it is produced rapidly at the back end of a spider under everyday conditions. This is a pretty electron micrograph of spider spinnarets from where the silk comes (warning: page includes creepy crawlies).

I introduced molecules, and proteins back in this post. Proteins are the key molecules used to make organisms, an organism’s DNA are the instructions to make a set of proteins. Spider silk is made from protein. A spider is able to produce a whole range of silks with different physical properties: dragline silk is used to make the outer-rim and spokes of a web and is strong and tough; capture-spiral silk is sticky, stretchy and tough; tubiliform silk is used for egg cases and is the stiffest; aciniform silk used for wrapping prey is the toughest; minor-ampullate silk used to make temporary scaffolding for building a web (it’s not as strong but very stretchy). From a technical point of view “strong” refers to how hard it is to stretch something, and “tough” refers to how hard it is to break something. Spider silk is similar to silkworm silk but it is stronger and more extensible.

The properties of spider silk arise from it’s microstructure, essentially the protein molecules make a very fine net held together with little crystals. The fact that crystals form is a function of the protein structure, exactly how many and what distribution of crystals form is influenced by how the spider treats the silk-protein solution as it comes out of it’s spinnarets. Precisely how the spider achieves this isn’t entirely clear, the protein starts off in a liquid solution, the spinnarets force the liquid out into the air whilst changing things such as the salinity, concentration and pH of the liquid and “Hey, presto” it turns into silk! It would be nice if we could farm spiders for their silk unfortunately this is difficult, they just don’t get on with each other.
The strength of natural materials is often compared to that of steel, but there is a trick to watch out for here: the comparison is often based on weight. Steel is about x10 denser than silk, so your strand of equivalent strength is rather fatter if it is made from silk.

The closest synthetic material to spider silk in terms of it’s strength per weight is Kevlar. Kevlar is processed using hot sulphuric acid under high pressure which as you might imagine is not very nice. Spider silk, on the other hand is made at room temperature and pressure from an aqueous solution of benign materials.  Not only this, a spider can eat the silk it’s already made and use it to make more silk. As scientists, this makes us more than a little bit jealous.

Not only is spider silk interesting of itself, but from a material scientist point of view, it really isn’t fun to make and use new polymers (you need to build expensive plant to make them, you need to work out your ingredient supply chain, you need to check for safety and environmental problems). If, on the other hand, you can get the properties you want from one of your pre-existing polymers by changing the microstructure then life is much easier. Spider silk may provide hints as to how this might be done.

The neat thing about this story is that it illustrates an important point: we can genetically engineer bacteria and goats to produce the protein in spider silk but not make nice silk-like stuff. Knowing the sequence of amino acids that a spider is making is not enough to make silk. In much the same way knowing the proteins that go up to make up a human is rarely enough to understand, let alone cure, a disease. 

Scientists have done research on the effect of different drugs on web spinning, filmmakers have made some fun of this experiment* (warning: contains spiders). Other interesting biomaterials include, mollusc adhesive and slug slime and I’ve already written about why butterflies are blue.

Update: Curtesy of @happymouffetard, the evolutionary origin of spider-silk spinnarets appears to be hair follicles, according to this article.


*Thanks to Stephen Curry for pointing me to the “spiders on drugs” video.

Jul 25 2010

Some notes on SQL: 6 – Multi-table operations

This is the sixth 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, advanced selection. The fifth post covered database design. This post covers multi-table database operations. No claim of authority is made for these posts, they are mainly intended as my notes on the topic.

Good database design leads us to separate information into separate tables, the information we require from a SELECT statement may reside in multiple tables. There are keywords and methods in SQL to help with extracting data from multiple tables. To assist with clarity aliases, indicated using the AS keyword, allow tables to be given shorter, or clearer, names temporarily. Various JOIN keywords enable lookups between tables, as with other aspects of SQL there are multiple ways of achieving the same results – in this case ‘subqueries’.

The AS keyword can be used to populate a new table with the results of a SELECT statement, or it can be used to alias a table name. In it’s aliasing guise it can be dropped, in shorthand. This is AS being used in table creation:
CREATE TABLE profession
(
id         INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
profession VARCHAR(20)
) AS
SELECT profession
FROM   my_contacts
GROUP  BY profession
ORDER  BY profession;
The following two forms are equivalent, the first uses the AS to alias, the second uses an implicit alias:
SELECT profession AS mc_prof
 FROM   my_contacts AS mc
 GROUP  BY mc_prof
 ORDER  BY mc_prof;

 SELECT profession mc_prof
 FROM   my_contacts mc
 GROUP  BY mc_prof
 ORDER  BY mc_prof;


The following examples use two tables boys which is a three column table {boy_id, boy, toy_id} and toys a two column table {toy_id, toy}.

boy_id boy toy_id
1 Davey 3
2 Bobby 5
3 Beaver 2
4 Richie 1
toy_id toy
1 Hula hoop
2 balsa glider
3 Toy soldiers
4 Harmonica
5 Baseball cards

Cross, cartesian, comma join are all names for the same, relatively little used operation which returns every row from one table crossed with every row from a second table, that’s to say two 6 row tables will produce a result with 36 rows. Although see here for an application.
SELECT t.toy,
b.boy
FROM   toys AS t
CROSS JOIN boys AS b;
Notice the use of the period and aliases to reference columns, this query will produce a 20 row table.

Inner join combines the rows from two tables using comparison operators in a condition, an equijoin returns rows which are the same, a non-equijoin returns rows that are different. These are carried out with the same keywords, the condition is different. This is an equijoin:
SELECT boys.boy,
toys.toy
FROM   boys
INNER JOIN toys
ON boys.toy_id = toys.toy_id;

The ON and WHERE keywords can be used interchangeable; in this instance we do not use aliases furthermore since the columns in the two tables (toys and boys) have the same name we could use a natural join:
SELECT boys.boy,
toys.toy
FROM   boys
NATURAL JOIN toys;


Natural join is a straightforward lookup operation, a key from one table is used to extract a matching row from a second table, where the key column has the same name in each table. Both of these versions produce the following table:

boy toy
Richie hula hoop
Beaver balsa glider
Davey toy soldiers
Bobby harmonica



A non-equijoin looks like this:
SELECT boys.boy,
toys.toy
FROM   boys
INNER JOIN toys
ON boys.toy_id<>toys.toy_id
ORDER  BY boys.boy;

the resultant in this instance is four rows for each boy containing the four toys he does not have.

Outer joins are quite similar to inner joins, with the exception that they can return rows when no match is found, inserting a null value. The following query
SELECT b.boy,
t.toy
FROM   boys b
LEFT OUTER JOIN toys t
ON b.toy_id = t.toy_id;

produces this result

Boy toy
Richie Hula hoop
Beaver balsa glider
Davey Toy soldiers
NULL Harmonica
Bobby Baseball cards

That’s to say each row of the toys table is taken and matched to the boys table, where there is no match (for toy_id=4, harmonica) a null value is inserted in the boy column. Both LEFT OUTER JOIN and RIGHT OUTER JOIN are available but the same effect can be achieved by swapping the order in which tables are used in the query.


In some instances a table contains a self-referencing foreign key which is the primary key of the table. An example might be a three column table, clown_info, of “clowns” {id, name, boss_id} where each id refers to a clown name and the bosses identified by boss_id are simply other clowns in the same table. To resolve this type of key a self-join is required this uses two aliases of the same table.
SELECT c1.name,
c2.name AS boss
FROM   clown_info c1
INNER JOIN clown_info c2
ON c1.boss_id = c2.id; 


Notice both c1 and c2 alias to clown_info.

Keywords: AS, ON, INNER JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, UNION, INTERSECT, EXCEPT

Jul 22 2010

The Periodic Table

Understanding the Periodic Table is very much like making love to a beautiful woman, there’s no point rote-learning the location of the different elements if you don’t know what they do… langtry_girl*

The Periodic Table of the Elements is a presentation of the known elements which provides information on the relationships between those elements in terms of their chemical and physical properties. An element is a type of atom: iron, helium, sulphur, aluminium are all examples of elements. Elements cannot be broken down chemically into other elements, but elements can change. An atom is comprised of electrons, protons and neutrons.

This is all very nice, but if you look around you: at the wallpaper, the computer screen, the table – very little of what you see is made from pure elements. They’re made from molecules (pure elements joined together), and the molecules are arranged in different ways which may be completely invisible. So in a sense the periodic table represents the bottom of the tree of knowledge for people interested in materials, other scientists may be more interested in what makes up the elements.

The periodic table, approximately as it is seen today, was discovered by Dmitri Mendeleev in 1869, he designed it based on the properties of the elements known at that time. For a scientist the Periodic Table is pleasing, it says of the elements: “this many and no more”. It also stands as one of the great scientific predictions: Mendeleev proposed new elements based on his table constructed from the known elements and ,behold, they appeared with roughly the properties he expected.
Mendeleev’s periodic table was a work of organisation, it later turned out through the discovery of quantum mechanics that the periodicity and order found in the table can be derived from the behaviour of electrons in atoms.
To reverse a little, there is scope for more elements in the periodic table, they appear tacked on at the end of the table and are made artificially. The experimental scheme to achieve this is to fire atoms of existing elements into each other in the in the hope that they’ll fuse, occasionally they do, but the resulting atoms have a fleeting existence. They are rarely found in any number and vanish in fractions of a second, they are not elements of which you can grab hold. This has always struck me as being akin to flinging the components of a car off a cliff and claiming you have made a car when momentarily the pieces look like a car as they plummet to the ground.
I had a struggle here deciding whether to describe the periodic table as being designed, invented, or discovered. I stuck with discovered, because discovering is what scientists do, inventing is for inventors and designing is for designers ;-) It does raise an interesting philosophical question which has no doubt been repeatedly discussed down through the ages.

As a design, shown above, the periodic table is a cultural icon which everyone knows. Even if they don’t understand what it means, they know what it stands for – it stands for science. How to make sure people know your scene is set in a lab or your character is a scientist? Bung in a periodic table. It has been purloined to organise other sorts of information, such as Crispian Jago’s rather fine “Periodic Table of Irrational Nonsense“, some more examples here. There is a song.

At various times in my life I’ve been able to name and correctly locate all the elements in the periodic table, normally takes a bit of effort and some mnemonics to help. Increasingly now, I can remember the mnemonics but not the elements they refer to.

Different parts of the periodic table are important to different sorts of scientists. To organic chemists carbon (C), hydrogen (H), oxygen (O), nitrogen (N) hold the majority of their interest with walk on parts for some of the transition metals (the pink ones in a block in the middle) which act as catalysts. Inorganic chemists are more wide ranging, only really forbidden from the Noble Gases (helium (He), neon(Ne), argon (Ar), krypton (Kr), xenon (Xe)) which refuse to react with anything. Semi-conductor physicists are after the odd “semi-metals”: silicon (Si), indium (In), gallium (Ga), germanium (Ge), arsenic (As). For magnets there’s iron (Fe), cobalt (Co), nickel (Ni) along with other transition metals and the Lanthanides. The actinides are for nuclear physicists, radiation scientists and atomic bomb makers. Hydrogen is for cosmologists. In this view, as a soft condensed matter physicist, I am closest to the organic chemists.

I’m rather fond the periodic table, it is the scientist’s badge, but I’m scared of fluorine.

*To be fair to langtry_girl, I pondered on twitter “Trying to finish the sentence: “Understanding the Periodic Table is very much like making love to a beautiful woman…” and I think hers was the best reply. It is, of course, a reference to Swiss Toni.