CM10228 / Programming Ib:   Lecture 20

Relational Databases & SQL

I. The Problem is Storage (and efficiency)

• Contemporary commercial databases can be thought of as servers for storing things.
• They provide security
• they handle concurrency
• you just have to put stuff in them.
• Nevertheless, there are still aspects of their use that are very much like the rest of programming
• You need to know what you want
• You need to come up with names for it.
• You need to think about representation.
• One thing you don't really need to think about is space.
• Space matters. A lot.
• But someone has thought about it for you.
• They've produced a system called relational databases, and a set of heuristics for using them efficiently, called normal form.
• How data tends to be stored:
• Fairly randomly in files, as you no doubt did for your dungeon.
• can be very efficient, may minimise the amount of space.
• is completely bespoke.  If you lose the software, you may have a lot of time guessing what the data is.
• may even be hardware dependent.
• The next idea was a table
• Lotus 1-2-3 put a lot of highschool-dropout programmers out of work!
• Wikipedia credits it with the success of the PC
• Actually, PCs started really selling when they got cheaper than a secretary's annual salary, so you could see direct benefit to making fewer people more productive.
• Tables have rows and columns
• rows represent individual entities,
  
• columns represent attributes.
• in databases, columns frequently have types and can do type checking
  
• Tables are kind of like objects.
• Tables can be improved on, though.  (skip green for now) Consider this:

• 	species planet of origin	languages	healthiest gravity
  Kirk	earth	english	1.0
  Spock	vulcan	vulcan, english, swahili	1.5
  Bones	earth	english, spanish, kelvan	1.0
  Uhura	earth	swahili, english, vulcan, romulan, russian, andorian	1.0

• some information is redundant, because it could be determined in another field. 
  
• This is a waste of space.
• violates second normal form
  
• some fields have to be arbitrary large, because we don't know how much information goes into them.
• this can also make computations a bit more complex as you may need to loop over attributes in a single cell
• violates first normal form
  
• The principles that help you avoid these mistakes are called normal forms.
• normal forms help you reduce a big table to a set of relations.
• go back and do the green lines
• this is efficient not only for storage but for processing.
• ideally, information in a database can be aggregated and manipulated very quickly.
• this depends on tricks similar to pointer arithmetic
• scuppered if you don't know the exact length of a field
  

II. Relations

• each table that's a relation has an index
• each entity has a unique identifier 
• "unique" means, they can't be repeated
  
• a key for search (in fact, often called keys)
• any cell has at most one value in it
• can also be null
• all the data in a row should depend only on the index
• if it could be determined by some other attribute of the table, it should be in a different relation
• To recover all the information back, you need to join the tables
• to do this, one table must make reference to the other table's index
• the main index is often called the primary key
• the other indecies are then called secondary key(s)

• officers (primary key)	name	planet-of-origin (secondary key)
  1	Kirk	terra
  2	Spock	vulcan
  3	Bones	terra
  4	Uhura	terra

  
  

  planets (primary key)	gravity	atmosphere
  terra	1.0	M
  vulcan	1.5	M++
  kelva	.9	M--
  andoria	1.2	C

  
  

  Swahili-speakers (primary key)

  2

  4

  2387

  923412

• note there's no particularly good way to do a 2x2 matrix in a normalised relational databases
• would probably also be a table listing all the language tables
• relations break down when the entity–attribute model breaks down
  

III. SQL

• structured query language
• seeing data
  
• select * from officers
• select * from planets where gravity > 1.0
• select officers.name from officers, planets where officers.planet-of-origin == planets.index and gravity > 1.0
  
• select count * from Swahilli-speakers
  
• changing data
  
• insert, delete, update
• sysadmin (create, drop)
• To be honest, I'm not being careful about the syntax here, hopefully though you get the idea!