DatabasesWhat is a database?
Access
How to make a
new table
Data Types
Input Masks
Data Validation
Lookup values
Primary Keys
Queries
Aggregate Queries
Forms
Reports Resources Links
|
Databases
What is a database?
A database is simply an organised collection of data.
Lets think about that statement a little more closely.
A database is simply an organised collection of data.
Most people have access to a huge amount of information. They regularly
use newspapers to find out what the weather is going to do, what's on
tele tonight, which horses are running at the York races today, and
much much more. Even the news stories themselves are stored in a structured
way.
Our society's use of information does not stop there, lets examine
a typical year 7 students day:-
| 7:00 a.m. |
Alarm clock goes off, John wakes up and
listens to the radio. The presenter reads the news, tells him
the weather forecast and what is on T.V. that evening. |
| 7:15 a.m. |
John goes to the bathroom and gets in the
shower. The central heating system has turned itself on at a preset
time and made sure that the water is warm and the radiator is
on to warm John's clothes. |
| 7:25 a.m. |
John goes downstairs and has his breakfast.
Afterwards, he checks his timetable and packs the correct books
for today's lessons. He also looks at his homework timetable to
make sure he hasn't forgotten any work for today's lessons. He
looks at today's date and sees that he has a football match tonight.
Because he isn't sure if they are playing away, he rings his
friend up on his mobile phone. he doesn't need to remember Alex's
number because the phone has a built in address book. |
| 8:00 a.m. |
The bus is late, and John checks the timetable
at the bus stop. If he is quick he can run to the next stop and
catch a different bus that might get him there a bit quicker.
he decides to risk it. |
8:50 a.m. |
His form tutor takes a register. There are
exams in one part of the school today, and John has to copy out
some room changes which the teacher has written down on a sheet
of paper. |
Can you see the eleven databases that John has used already that morning?
On a computer, we have to develop a system that stores information
in a way that is easily sorted or organised, possible using several
different techniques.
For example, John might want to see which lessons he has today, because
he wants to check which books he has, but another time he uses his timetable
he might want to see when he next gets English, or what homework he
has today. A computer based timetable would be able to use the same
data to provide John with this information, by simply displaying it
in a different way.
When we use a computer to store a database, we need to tell
the computer exactly what information we are going to store. This is
done by creating a holder for the information, called a table.
The database table that we are going to create to store our timetable
will have many entries, or records. Because Horbury School use
a ten day timetable and there are five lessons a day, the database will
have fifty records.
Each record in the database actually contains several pieces of information.
We need to tell the computer exactly what information we are storing.
This is done by setting fields to store the data in.
A field is simply one small piece of information, that is usually
needed for every entry in the database. If we think about the timetable
database, every single entry on the database need these fields:-
- Day
- Lesson number
- Subject
- Group
- Teacher
- Room number
The timetable may also have details of any homework that you should
be set that day.
Once we have told the database that this is the information that we
are interested in, we can make it sort the information in some way,
and even to reject inappropriate data, so the timetable database will
not let you add a date that is on a weekend, or add a lesson six when
you only have five lessons a day!
Each entry in the database is called one record. In our timetable
database every lesson you have is one record, and each record will have
a day, lesson number, subject, group, teacher and room number.
Data Types
The computer is quite efficient at storing data, but we can help it
store the information in a much more compact form by telling the database
system a little bit more about the fields. Every record of the timetable
database needs a day and lesson, and these two fields can be stored
as a number. The subject of each lesson could be stored as a number,
but this would be hard to understand (can you image trying to remember
that subject number 6 was Geography?)
We will look at the kind of ways that a database can store data in
a lot more detail later, but here is a summary of some of the different
ways that most databases can store information:-
|
Data type
|
Description
|
|
Number |
When a field is set to be a number data type, the computer
can store the information in a very compact way.
Numbers can be Integer or Decimal
Integer numbers are whole numbers that do not have a decimal
part, e.g. 1, 2, 3, 10, 1000 etc.
Decimal numbers are stored with a fractional part to each
number, e.g. 1.2, 16.234, 10.43255, 1.0 etc.
It is sometimes difficult to decide whether a field should have
a data type of number or text. A good rule to remember is that
if you can treat the contents of the field like a number by using
it an a sum that produces a meaningful result, then it should
be a number data type, otherwise use a text data type.
For example, a bank account number can be multiplied by 2, but
the result is meaningless so a text field should be used. |
|
Text or
Character |
This is a less efficient way of storing data, but is sometimes
the only way that certain information can be stored. Text fields
can store any of the following characters:-
- lower case letters, a-z
- UPPER CASE letters A-Z
- numbers 0,1,2,3,4,5,6,7,8,9
- punctuation ,.;:'@#~()*&^%$£"{} etc.
- spaces
- mathematical symbols +- * etc.
|
|
Date |
It is quite common for a field to have to store a date, and
a computer database can help us make sure that the date has been
entered properly.
A date is stored in three parts,
day/month/year
The day part can be any value between 1 and 31, but sometimes
the values 29,30,and 31 will be unavailable. The database will
automatically check that a correct date has been entered.
The month part will be a number between 1 and 12.
The year will usually be displayed as a four figure number, but
may also be displayed as just two figures, even though the actual
date is stored as four. Depending on the value of the year, the
number of days that are in the month "february" will
change, and the database will need to reject inappropriate values.
Date fields can also be displayed in different ways depending
on which country you are in.
In the United Kingdom, we usually use day/month/year, but in
the United States of America, dates are stored as month/day/year.
The database can display the same information in deferent ways
depending on which country the system is being used in. |
|
Time |
When a database need to record a time, it stores hours, minutes,
seconds and fractions of a second.
The time can be displayed using different formats, for example:-
are all the same but displayed differently. |
|
Choice |
Databases can store simple two way choices in a choice field.
The timetable database may be able to hold information about whether
a particular lesson will have any homework set. This information
could be stored as a yes/no choice.
Other examples of choice fields are:-
- male/female
- M/F
- Yes/No
- True/False
|
Click here to return to the top of the page
Click here to start to look at how Access works.
| Page last updated on
October 31, 2002
|
|
|
