DatabasesThe concept of what is a database is not exactly taxing in theory, but sometimes it is difficult to visualise what one is. The easiest example that you will be aware of is that little book full of all your friends telephone numbers and addresses. For each of the entries you will record the name, address and telephone number of each person in the book. What you have done is to create a "record" of that person's details, so when you fill in one entry you create 1 record. Of course a little book like this will hopefully not contain just one person's details (although I have to admit that my own social circle is not much larger than 1, my last telephone bill was a little under £3). You will probably enter at least 20 or so people, so you have 20 or records and this makes up a "file".So a file consists of lots of records, and each of the records are made up of name, telephone and address, these 3 things are known as "fields". Not exactly exciting stuff but the definitions are important.In your time you have probably received numerous forms to fill in and may have had a form that is made up of lots of little boxes that you have to fill in with capital letters and only in black ink. A bit like the one below:I know that sometimes there are never enough boxes to get in all your details. For example someone with the surname "Alexanderson-Smith" may not have enough room. In Folkestone we have a road called "Bouverie Road West" - it's often difficult to get that one on a form as well. This issue is the first thing that you have to deal with when setting up a database. Let's consider the field "surname", how many characters should we allow, "Alexanderson-Smith" has 18 characters in it, is that going to be long enough? What about if Miss Alexanderson-Smith were to get married to a Mr Featherstonehaugh and she wanted to keep her own name -
18 characters isn't enough for Mrs Alexanderson-Smith-Featherstonehaugh.This may be a little exaggerated but it does help to show a point, when you set up a field you have to think about how many characters you will allow, there is no right answer to this question, but you will have to be able to give a reason for why you chose the length of field that you did. Personally I would go for 20 characters for surname, ones that are longer could be shortened to fit.Fields at GCSE level will fall into 3 major categories:Text (alphanumeric) - letters and numbers Numeric ...
This is a preview of the whole essay
18 characters isn't enough for Mrs Alexanderson-Smith-Featherstonehaugh.This may be a little exaggerated but it does help to show a point, when you set up a field you have to think about how many characters you will allow, there is no right answer to this question, but you will have to be able to give a reason for why you chose the length of field that you did. Personally I would go for 20 characters for surname, ones that are longer could be shortened to fit.Fields at GCSE level will fall into 3 major categories:Text (alphanumeric) - letters and numbers Numeric - just numbers Date - well dates funnily enough For those smart arses who want to point out that there are Boolean, picture, calculation fields etc. Well why are you reading this if you know it already? Most fields are pretty obvious to work out which category they fall into, surname, address and postcode are all text fields as they can contain letters and numbers.Numeric fields will be those such as "money owing" or "number of detentions". One important point to note is that a telephone number is not a numeric field for 2 reasons. 01345 484950 has a "space" in the middle so that is not a number. So you can get around that by typing 01345484950, but if you do then it will come up in the database as 1345484950, there's no 0 at the front. This makes sense we don't write 19 as 019, so if there is a zero at the front of the number then the database just gets rid of it. But in phone numbers we need the zero at the front so make telephone number a text field.Date fields; well "date of birth", "date of detention", "date blah blah blah" - straightforward really.So what is the point of a Database? Well the main reason is that with time you may well put many records into a database and you may well need to search through them to find certain people. Let me take you back to the phone book example earlier on, imagine it's Friday night and you fancy a snog. But it's late and you can't get to travel to some of the towns that you have phone numbers for potential snogging victims. You will have to search through all the records to find those people who live close enough to call and be able to get over their house in time. But if you have a great big book full of loads of names and addresses (as I am sure that you have), then searching through it will take some time and by the time you have finished looking through, it will have got dark and your mum will be telling you to come in for your tea (or something)!!! How I bet you wished that you had a computerised database, you could have just typed in a query like find all records where town=Folkestone (assuming that you live in Folkestone of course). Hey presto, you have a list really very quickly and with a quick jog down the road I'm sure that the snog will follow.This may not be the sort of example that an exam is going to test you about, but it is a little more interesting than what happens with databases in the real world. Normal examples of databases are maybe a debt collector searching through a database of thousands of clients who owe him cash and then sending round a large man to break their legs. I mean these large men cost lots of money so sorting through bits of cards by hand would not be cost effective. OK, so this isn't the best real world example so if you want one imagine a dentist searching through his records to find those customers who have appointments in the next few weeks and just sending out letters to them. Happy now? The whole point is that you can search a database to find those people on the database who match a certain criteria like they owe money, have appointments due or live in certain towns.Setting up a DatabaseSo let's set up a database and let's start with the simplest one being that old address book. I am going to start by being really pedantic now, but this will get you into some good habits for your project work. Don't have a field called "name". Names can be broken down into 3 sections, "title", "first name" and "surname". The reason for having a title field is pretty obvious in that some names can be male or female "Robin", "Jo" or "Alex" are good examples. Even the author of what you are reading at the moment genuinely has an "Uncle Silv" who is married to my "Auntie Tony" although I do admit "Tony" is actually spelt "Toni", and "Silv" is short for "Silvio" but I'm sure nobody really cares. So we should start with these 3 fields and obviously they will all be text fields, but how long should they be?Title text4 characters(All titles can be shortened i.e. "Dr", "Miss" is the longest)First nametext10 characters(10 should do)Surnametext20 characters(enough room for double-barrelled names)NOTHING AND NOBODY can tell me that what I have put so far is not right, many of you may well think that "first name" should have more characters in it than 10, but you have an opinion and so do I. Never forget that certainly if I had put "first name" as a numeric or date field then you could have told me that I am wrong but you can't be criticised about your opinion. There is no such thing as a perfect field length so pick what you think and stick to it - you can always change it later if you have to, but now we come to a key issue. Memory.You have to really pay some attention to how many characters you will allow each field to have and try to reduce the amount of characters wherever you can. Consider the field "first name", if you set this to 20 characters long then you may well have a problem. Let's say we add the name "Sarah". This is 5 characters long and should therefore take up 5 bytes of memory. But the problem is that if you set the field length to 20 then "Sarah" is stored with 15 empty spaces after it, e.g. "Sarah---------------". In other words regardless of what name you actually put in, every name will take up 20 bytes. With a very large database this could cause memory problems. So each time you set up a field then it is a good idea to try and minimise the number of characters that you put in.Ok, let's be honest you can't really shorten something like "Sarah" but you could use a coding system to shorten entries into your databases. Take an example of a company that keeps a database of its manual employees. "Cleaning" is a duty that could be shortened to "CL", two characters will be better than just one consider, "Cleaning" and "Cooking" for example. Also if you have one character then the maximum amount of duties that you can have would be 26. So if you can use a coding system for a field then try and do so, it also will get you better marks in your coursework as well.OK, so at this stage we have thought about the length that a field needs to be, what type of field that it will be and if the field will need to be coded. Now here comes the absolutely essential bit. Every time that I teach a group of children I always try to emphasise this point about 6 or 7 times, however there are always a few who manage to completely ignore this request and just plough on into their projects regardless.When you build a database you will have to have a "key" field and this is going to have to be unique. Let me explain, in our school there are 3 children with the name Christopher Smith and each of them needs an user id to log onto our network. User ids in our school are given out by putting the surname first and then the initial of the first name, for example David Jones would be JONESD. But we have a problem when there are 3 Chris Smiths. So we have a SMITHC, a SMITHCH and a SMITHCHR. What this means is that every person on our network has a unique user id to log on with. The same has to be true of your database. The very first field in any database that you decide to set up must be such a key field, otherwise confusion will be caused if 2 people have the same name.
