Unit 9, Database Basic Principles and Structure
BTEC First for IT Practitioners
advancing learning, changing lives
OnCourse - Tutor Notes
Databases are very widely used in business, but are one of the least well understood ‘office’ applications. This is
probably because they can become highly complex and a certain amount of planning and designing is required.
The unit has a emphasis on practical skills but there is some basic theory required in order to make sense of the
practical tasks. This topic should probably be the first lesson in this unit and as such concentrates on the theory.
Many learners may already have some practical knowledge of using database products such as Microsoft Access
but it is important that you ensure all your learners have a good understanding of these basic principles.
Slide 1 – Title slide
Slide 2 – The slides begin with the question ‘What is a database’, which might sound a silly question but it can
be quite difficult to describe what a database is and does without getting too technical. The first point
to make is that although we might not have spent much time creating or using databases ourselves,
they certainly affect many aspects of our lives. Lots of information about us is stored on databases,
student and employee records, our bank and credit card transactions, car details, phone billing records
and so one. Whenever we go to a supermarket checkout the EPOS (Electronic Point of Sale) system is
interacting with a database of stock records, and whenever we visit a Internet on-line shop and search
for the products we want we are also interacting with a database. You might like to ask you students
how many examples of computer databases they can think of that they are aware of. It shouldn’t be
too difficult to come up with a pretty long list.
Having hopefully agreed that databases are widely used, the next question to ask, is why use a
database. A comparison is drawn here with other ‘Office’ software. Word processors as the name
suggests process or manipulate text (and images), spreadsheets are primarily designed for processing
numbers (with formulae and function etc.), so, logically databases are designed for processing data.
However data is a very broad term, so what does it mean. Your student’s will probably come up with
answers like ‘information’, ‘facts’, ‘details’, all of which are correct. Strictly data is ‘that which is
given or known’, think of any well know object either real such as a person, a car, a house, a computer
etc. or conceptual like a bank account and it is not difficult to think of data items which would apply
to that object. Data and information are related but slightly different. Data is raw or unprocessed,
while information is data that has been processed in some way to make it meaningful or useful. So a
list of all the students in a school or college is data, but that data sorted into class or course lists and
printed on a register is information.
Slide 3 – Our simple definition of a database is ‘a collection of data’. The term database has come to be used
for both the data itself and the software that manipulates that data. This can be a little confusing, so
make sure your learners understand this. DBMS (database management system) is a term that is not
used so much these days, but it is the proper name for the type of software that creates and
Microsoft dominate the low end database software market with their Access product which many of
your learners will have heard of. However industrial scale databases often use highly sophisticated and
expensive software designed for multiple users from companies such as Oracle. There is lots of
information about this companies products and customers on their web site (www.oracle.com) but
much of it is written in quite technical language. MySQL is an open source database (i.e. it is available
free of charge) also designed for large multi-user database systems. You can find out more about
MySQL at their web site (www.mysql.com) where there is also a lot of information about both products
and customers (Web search engine Google for example uses mySQL).
There isn’t any requirement for your learners to know very much about these products, it is just worth
making them aware that Microsoft Access is not the only database software.
The key point about a database is that it stores data in a structured way. You could argue that Word
Processors and spreadsheets also store data, but they don’t apply a structure to the data in the same
way that a database does. The next slide explains more about the way databases are structured.
816d362f-f32b-4a34-92d4-23c36418cecb.doc Page 1 of 4
Slide 4 – The structure of a database if defined by the tables and the fields within those tables. Every database
has one or more tables. A table is a singe store of related information. Typically a table will store data
about a real world thing or concept, such as a car, student, hospital patient, or bank account. Many
databases have multiple tables, so the database itself holds data about an application area, while the
tables within that database hold data about things or objects within the application area. So a college
or school database might have tables for students, teacher and courses, while a database for a doctors
surgery might have tables for patients, doctors and appointments. The design of multi-table databases
and the relationship between tables is a complex subject, beyond the scope of this topic. You need to
make your learners aware that databases can have relationships between tables (so students can be
related to courses and patients can be related to appointments) but there is no need to go into the
technical detail at this level.
Within the tables themselves data is structured by being split into fields. These are typically shown as
the columns within a table and set the name and the type of data for the individual data items held in
Records on the other hand are the rows within the table and contain all the data (fields) for a single
object or thing within the table.
The structure is not that difficult to understand, but an important question to ask is ‘why have the
structure in the first place?’. This goes back to primary purpose of a database, which stores data so
that we can find the records we are looking for and answer questions about our data. By structuring
the data into fields we can easily search for a record or records which match what we are looking for.
This might be all the students over 21, everyone’s bank account that is overdrawn, all the phone calls
made last month and so on. Each of these depend on a field based criteria (students age, account
balance, date of call).
Slide 5 – Since finding records is a important part of the purpose of a database, it is very important that each
record can be uniquely identified. It might be nice for you if your bank account got muddled up with
Tony Blair’s, but he might not be so happy! While uniquely identifying records in a small database
might not seem to be a problem, real world commercial databases can be vast, with millions of
records. For this reason it is standard practice to define one of the fields in each table as the primary
key. This is usually a made up value, such as a numeric code (such as student number) or a
combination of letters and numbers (such as a National Insurance number or car registration number).
The database software will not allow you to create a new record with the same primary key as another
records so its uniqueness is guaranteed. Another advantage of the primary key is that the database
software maintains an index of primary keys which means that it can search for and find a record using
the primary key much more quickly that it can using a non-key field. It is also possible to set other
fields to be key fields (secondary keys) which can either be unique or allow duplicates. This is useful if
you often need to search for records using this field as the search criteria, because the software can
search more quickly using the key’s index. However you should avoid making too many fields key fields
as this will slow the overall performance of the database down and make the database files very large.
Slide 6 – As well as just tables, databases usually have an number of different parts (object) which allow the
user to manipulate and present the data in different ways.
Queries are an important part of most databases since they are the tools which help to find records
and answer questions about the data. Queries are basically saved search criteria, so for example if you
needed a list of all students were over 21, you could create a query which search the student table for
records where the age field contained a value of 21 or greater. If this was a list you needed regularly
you could save the query and run it whenever you needed the list. A later topic looks at queries in
Reports are primarily designed for formatting data for output to the printer. Additional text, lines and
boxes and calculated totals can be added to the data from a table or query to make a meaningful and
easy to read printed report.
Forms are primarily designed for inputting or outputting data on screen. Like reports, additional text
and layout features can be added to make the data clear and easy to read.
Most database software also provides application building tools (such as macros and code modules)
which combined with queries, forms and report allow technical users to build a complete application
based on the database. This is subject is beyond this level, but it is worth mentioning to you learner
that this is possible and they may learn about it at higher levels.
816d362f-f32b-4a34-92d4-23c36418cecb.doc Page 2 of 4
Slide 7 – In terms of advantages of using a database we can look at these in comparison to using a non-IT system
and in comparison to using other IT software.
Compared to a paper based filing system, with a database creating and inserting records is quicker
because you don’t have to search through the files to find the correct place to put a new one
(assuming the paper files are in alphabetical order). Paper based records cannot easily be sorted in
different orders (by surname, by date of birth etc.), but this can be easily done with a database.
Searching for records is quicker with a database, and with a paper filing system you can only easily
search by the order they are stored in (perhaps numerical order by code number or alphabetical by
name), but with a database you can search by any field. Amendments to individual paper based records
might be quite simple (although you have to find the record first) , but if you needed to insert another
field, this is a much lengthier process with a paper filing system. Storage space is another
consideration with large amounts of data. Storing millions of records in filing cabinets would take a
very large amount of space. With a paper based system when files are out of the filing cabinet being
worked on, they are not available to other users, this is not the case with a database. Finally putting
together a report which draws data from many different records is very difficult with a paper based
system, since you would need to extract the relevant data from every paper file, which if there were a
lot of them would make the task impossible, however databases excel at this kind of task and what
would take hours or days in a paper based system can be done in minutes.
There are many other advantages of a database over a paper system, see if your learners can come up
with some, but try to avoid simple answers like ‘its easier and quicker’ and get them to explain why
this might be so.
Slide 8 – The benefits of a database over storing data in a Word Processing file should be fairly obvious. The lack
of structure makes this solution only marginally better that a paper based system. However you can
create simple databases in spreadsheet software, since the rows and columns provide a field and
record structure, and Microsoft Excel for example has some simple database facilities. Where only a
small number of records are required (in the hundreds) Excel provides a reasonable solution without
the more complex set up and skills required with Access. However using a database rather than a
spreadsheet for a has some major advantages:
Suitable for multiple table databases, can create relationships between tables
Databases can deal with very large amounts of data and allow multi-user access
Complex searches can be created and saved using queries, spreadsheets only allow simple
searches which cannot be saved.
Database software allows the creation of sophisticated forms and reports, which many data
input and output easier and more user friendly.
Database software allows creation of complete end-user applications.
Slide 9 – The first two disadvantages are to do with cost of ownership. The software itself may be quite
expensive but the expertise required to create and maintain a database is much more expensive.
Microsoft Access is probably the most difficult Office application to learn how to use, and large scale
commercial database software (such as Oracle) is much more complex. To support sophisticated multi-
user database system both technical experts (who demand high salaries) and powerful computer
hardware are required. Related to this is the fact that unless the database is designed carefully in the
first place it may not have the required functionality and/or it may perform slowly.
One problem that databases can suffer from is inaccurate data. Quite a lot of data input to databases
is done in large volumes and great care needs to be taken to ensure it is entered correctly, since
inaccurate data may mean that the analysis and interpretation is incorrect. It can also lead to
problems such as customers being sent incorrect bills, orders being sent to the wrong place and other
such mistakes. Where ever possible data should be validated before it is accepted by the system, to
reduce possible errors.
Another issue, no so much a disadvantage, but a requirement of using databases that store personal
data, is the Data Protection Act. This act was passed in response to concerns that the large volume of
data held about individuals could be misused, or if inaccurate could lead to problems for individuals,
such as being refused credit due to inaccurate details about their credit history being held on
databases. Organisations that hold personal data on computer databases are required by the act to
register with the Data Protection registrar and they must conform to the requirements of the act.
816d362f-f32b-4a34-92d4-23c36418cecb.doc Page 3 of 4
These include ensuring the data held is accurate and secure and that it should only be used for the
purpose for which it was gathered.
There is a simple multiple choice quiz that can be used to check your learners have remembered some of the
details from the PowerPoint presentation
This simple activity is designed to get your learners to think about the advantages of a database and relate them
to a case study application. Try to encourage them to give real examples in their reply to the e-mail and avoid
bland ‘quicker, faster, better’ type answers. You might want to discuss what sort of uses Tim could put his data
to before asking them to write the reply. You could also give them the answers to the questions that they want
to ask Tim (i.e. make them up!) before they write their reply.
816d362f-f32b-4a34-92d4-23c36418cecb.doc Page 4 of 4