[ Relational Databases +=+ A Brief Explanation/Tutorial ]
by SiliconFuRy
siliconfury@apexmail.com
[ ------------ ]
[ Introduction ]
[ ------------ ]
Ok, let me get this straight, relational databases are NOT called relational databases
because they are related, but because of the mathematical algorithms which bind together
the tables...yaddayadda..... or something like that...
A Relational Database differs from a normal 'Flat' Database in the fact that a Relational
Database is made up of separate tables. The tables are linked by mathematical logics.
The tables can be calculated from a single flat database by the methods of 'Normalistion'.
Describing how relational databases work is no easy task, so I'll do the best I can. I've
spent about 10-12 weeks studying it at school, and got pretty far, and its midnight so I'll
try my best. Brb while I get a coffee.
Ok first, some jargon out of the way while the kettle is on...
[ ------ ]
[ Jargon ]
[ ------ ]
Anomalie = Posh word for an error, fuck-up, problem, etc.
Attribute = Relational Database word for a field. Basically one 'thing' in a database which
stores lots of different 'things'.
Baby-Databases = My own made up word for flat databases with only 1 table. These have no
normal form. This werd aint used in real life. :)
Entity = Techy word for thingymajiggerwhatsitspuss(thing,usually table).
Functional Dependances = This is where one attribute relies on another attribute.
Normalisation = The Process of simplifying tables down to a point where relationships can
work properly without any anomalies.
Primary Key = Identifier for a table. Each Table must have one, and each other attribute
must be functionally dependant on the key and nothing else if normalised properly.
Record = Single line in a table.
Table = Otherwise known as a relation. A single 'piece' or entity of a database. A record
in baby-database talk.
[ ------------- ]
[ Normalisation ]
[ ------------- ]
Ok, for a normalisation example, I'll just use one of the examples that came with my student
notes. Ok, first off, a school example database, in flat form first(No Normal Form).
Hopefully SWAT will allow this to be published as html, so all keys are bold italic. If not,
all keys are those with the <b><i> stuck before and after them. Usually keys are denoted by
underlining, but thats hard to do in html, or even ascii.
Pupil (oh yea, all tables must have a name)
---------------------------------------------------------------------------------------
| PupilID | Forename | Surname | DOB | Subject | Principal Teacher | Room | Grade |
---------------------------------------------------------------------------------------
| 950011 | Joe | Kool |12/5/84| English | Mrs Miller | 3 | A |
| | | | | Maths | Miss Taylor | 45 | B |
| | | | | Physics | Mrs Aurthur | 11 | B |
| 950012 | Anna | Kidd |3/6/84 | English | Mrs Miller | 3 | B |
| 950013 | David | Dodds |14/7/84|Geography| Miss Diamond | 5 | B |
| | | | |Computing| Mr Brown | 2 | B |
| | | | |Chemistry| Mrs Stevens | 33 | B |
| | | | | CDT | Mr Jack | 41 | B |
---------------------------------------------------------------------------------------
All gaps in the data are the same data as in the field above.
Well-trained eyes will spot that all that repeated data will clog up the system once it gets
about 5 or 6 hundred pupils in it studying almost all the same subjects. This repeated data
is called data redundances. To Normalise to First Normal Form, we must remove these from the
table and put them in their own table. To save on writing space and time, I will write the
table in another format as follows, but shoudl be easy to pick up.
Pupil(PupilID,Forename,Surname,DOB)
Subject(PupilID,Subject,Principal Teacher,Room,Grade)
The PupilID attribute is passed onto the next table as so we can link the two tables
together. You'll notice i have 2 primary keys in the Subject Table. This is because we need
both the PupilID and the Subject to find what teacher teaches that subject, and to find the
values of all the other fields. This multiple-attribute key is called a Concacenated Key.
Ok, so we have now reduced the amount of data taking up disk space in the database. Thats
one thing. But a number of anomalies have evolved. For One thing, if a pupil is to be
entered into the database, but hasn't sat any subjects yet, he cannot be entered into the
database, because of that concacenated field, if there is no subject, the key cannot be
made up, and so an Insertion Anomaly is identified.
[ ------------------------ ]
[ Second Normal Form (2NF) ]
[ ------------------------ ]
To remedy this, we take out all 'Partial Key Dependances', which basically mean taking
out all fields that only rely on one part of a concacenated key. This is what Second Normal
Form is. One thing to note is, that if there is no concacenated key in any table, then the
database is already normalised to Second Normal Form. The Attribute Subject in table Subject
can tell us the Principal Teacher, and the Room. We are now left with this:
Pupil(PupilID,Forename,Surname,DOB)
Subject(PupilID,Subject,Grade)
Principal Teacher(Subject,Principal Teacher,Room)
We now notice that the table Subject only has the concacenated key in it, and no partial
key dependances.
[ ----------------------- ]
[ Third Normal Form (3NF) ]
[ ----------------------- ]
In 3NF, we now take out all 'transitive key dependances', which means any non-key field
which functionally depends on another non-key field.
In the Principle Teacher table, the attribute Principal Teacher can depend on the non-key
field, Room. So we take these out into their own table, Accommodation, say. The field with
an asterisk(*) is a foreign key, which is a non-key field which is a key in another table.
Pupil(PupilID,Forename,Surname,DOB)
Subject(PupilID,Subject,Grade)
Principal Teacher(Subject,Principal Teacher*)
Accommodation(Principal Teacher,Room)
And that is as far as I am going with data normalisation. A lot of explaning and very
complicated at first. If you read this far, and havn't fainted with confusion, have a pat on
the back. If you understood it, I suggest you jump off a cliff for being too intelligent
(just kidding ;)~ ), and read on. If you have got this far and still need some explaining,
rake the web for more materials, as there is tonnes of it on this subject out there.
[ ------------- ]
[ Relationships ]
[ ------------- ]
At first I wasn't going to go into this, but I thought, what the fuck, and I didn't have
much else to review on either, so it passes time.
Ok, there are principally 3 main types of relationships between tables you can have.
One-To-One
One-To-Many
Many-To-Many
The last one we don't particularly like so much, because it means the database will become
ineffecient once it increases in size by some amount, and it also can mean that the database
wasn't normalised far enough. If you are in 3NF and you do seem to have Many-To-Many
relationships, just say fukkit, bung it in and cross your fingers it works.
Microsoft Access(which I might do a tutorial on, on basic relational database management
later), automatically works out what kinda of relationship type is present when you link
the tables. I think in FileMaker Pro you have to define them, but I won't go into that
because I really have FileMaker coz we have to use it at school and its really shite, for a
few more reasons I can't be arsed going into here..
So what do we mean by relationship types? Well, in the last example, we'll use the 3NF form
of it in our final database construction. The tables Pupil and Subject will be linked by the
common key, PupilID. Right, this is gonna get confusing, so brace yourselfs... One Pupil can
take many subjects, so in the Pupil end its a One-To-Many, k? And one subject may be taken by
many pupils, so the Many goes to the Subject table side. Here, below is a bad ascii showing
it a bit easier...
<-Pupil->1 ---------------- M<-Subject->
^ ^
One to Many relationship.
Lets link Principal Teacher and Accommodation as another example..
<-Principal Teacher->1-----------------1<-Accommodation->
This is a One-To-One Relationship, because one teacher teaches in one place, and one place
houses one teacher.
See what I mean by compilicated? If you see a course in this at school, don't take it unless
you aren't bothered about mind fucks. This mind fuck is worse than the film Perfect Blue.
[ -------- ]
[ The Rest ]
[ -------- ]
As far as SQL(Structured Query Language) goes, I don't know much about it(code wise), as we
aren't taught it in school, so when I find some time somewhere I might make a start in
learning it, and maybe even write a little tutorial for it.
James Love © 2000