MODULE 6-DATABASES

Organized record pool!

 Database
• Set of well structured data kept for a common
purpose.
• Consists of one or more related data files.
• There are small, medium, large and very large
databases.
 Examples of databases
Database What it contains

Customer contacts database Customer names, addresses and telephone numbers

Vehicle registration database Car details

College database Student, course, staff details

Library database Member, book, book loans and returns details

Hospital database Staff, drugs, wards, patients details.

WHO database Known disease details.

Banking database Customer personal and accounts details.

 Basic types of databases
• Non-computerized database
• Computerized database
 Non-computerized database

• Data is stored in manual files.

• They are also called manual databases or
paper-based databases.
• Examples:
Data stored in exercise/counter books.
File cabinets with paper-based files.
 Computerized database
• An electronic database stored and processed
in the computer.
• Requires a database management system
(DBMS) program to create and process them.
• Its an electronic filing system.
• Data is usually well structured.
• Normally stored on large disks.
• Tapes are used to backup the database.
 Advantages of computerized
databases
• Fast to search desired records.
• Fast to sort records.
• Fast to store records.
• Fast to modify/update records.
• Fast to delete unwanted records.
• Often high database security provided through
passwords and regular database backup.
• Easy to backup database using backup programs.
 Disadvantages of computerized
databases

 More expensive to set up and run e.g require

purchase of computers, database program etc.
 Require skills training to use them.
 Require electrical power to operate.
 May be destroyed by hackers and viruses.
Database management system (DBMS)
• A complex program used to create and
process databases.
• All accesses to the database by users and
application programs is done by the DBMS on
their behalf.
 Examples of database programs
• MS Access
• MS SQL Server
• DB2 Universe
• Paradox
• FoxPro
• Informix
• Oracle
• Sybase
• DBASE V
• MySQL
• etc
 Database system structure
Relationship between a secondary storage
device, database, the DBMS, the OS, users and
application programs is shown below:

Users, application programs

DBMS
Operating system
Database
Secondary storage device
 Types of computerized databases
• Relational database
• Hierarchical database
• Network database
 Relational database
• Data is stored as a set of one or more related
tables and there are relationships between
tables.
• It is created and processed using a relational
database management system (RDBMS).
• All database program examples given above
are relational.
Advantages of relational databases
They are based on sound mathematical ideas
e.g set theory.
Store and process data very efficiently.
Easy to learn and use.
Their properties will make them increasingly
important in the future.
 Basic components of a relational
database
• Tables
• Forms
• Queries
• Reports

These database components are also called

database objects.
 Tables
• Store records.
• Each table is stored as a data file on disk.
• Easiest way to create a table in Access is to use
the design view option.
 Forms
• An input screen used to conveniently input
data into the database.
• Its used to input new records, update/delete
records from a particular table/tables.
• It must be well designed and user friendly.
 Queries
• It’s a specific data request created and saved
for future use.
• When a query is run it causes the DBMS to
extract the required records from the
database probably from several tables.
• In Access the easiest way to create a query is
to use the query wizard and then edit/modify
it in design view.
 Reports
• Information extracted from the database and
presented in a suitable format.
• It may be based on data from one or more
tables/queries.
• The report can be simply viewed on screen or
printed for distribution.
 Database security
• Measures that ensure that a database is not accessed
by unauthorized persons/that the data is always
correct and complete and available.
• Integral data- data that is totally correct and complete.
• One important function of the DBMS is to ensure that
the data in the database is always integral.
• The data must be protected from unauthorized access,
viewing and modification.
• Database corruption- Its when data in the database
becomes incorrect/incomplete.
• A database can become disintegral or corrupt due to a
number of causes.
 Common database threats/risks
• Accidents e.g.:
 Computer crashes
 Disk crushes
 Fire
 Floods
 Viruses

• Malicious activities e.g:

 Hackers – people who illegally break into computer systems.
 Disgruntled employees/employee sabotage.
 Terrorist bomb attacks.
 Database theft.
 Targeted viruses.
 Common database security measures
• Passwords- restrict access to the database to authorized
users only.
• Backup –making regular copies of the database and storing
one copy in a fireproof safe and the other copy is stored
offsite.
• Access permissions- different users/user groups are
assigned different access rights to tables in a database e.g.
full access, read only, write only or no access.
• Data encryption- converting data to an unreadable form.
• Restricting entry into the computer room e.g. use of access
tags, biometric systems such as fingerprint scanners.
 Components of a database
• A database consists of one or more tables
/data files.
• A data file/relation/table consists of a set of
similar records.
• A record/row/tuple is a group of related
fields.
• A field/column is single data item in a record.
Fields in a record are usually of different data
types .
 Database administrator
• A person/group of people responsible for
supervising all database operations in an
organization.
 Database facilities/Skills checklist
• Create new database.
• Create/configure a new table/record structure .
• Create table relationships.
• Enter/edit/delete records.
• Cut, copy, rename and delete database objects.
• Sort records by any field.
• Design simple and complex queries.
• Create simple and complex reports.
• Format tables, queries, forms and reports.
• Print tables, queries, forms and reports.
• Password protect a database.
 Record processing
• It includes:
New record addition
Record update
Record enquiry/searching
Record sorting
Record deletion
• Record processing is done in datasheet and
form view.
 Sorting and searching
• Sorting – rearranging records in a table into some
logical order e.g. alphabetic order, numeric order
or by date.
• There are different levels of sorting i.e. primary
sorting, secondary sorting and tertiary sorting.
• Searching – finding a particular record/group of
records in a table.
• The user often specify the search
condition/criteria.
• The criteria can be a single condition/multiple
conditions.
 Queries
• A database object used to retrieve desired
information from the database.
• The information is then displayed in an answer
table.
• The query can be a simple list of all the records in
a table/tables or more complex where records
are selected only if certain criteria are met.
• User can choose to display all or only some of the
fields of selected records.
 Types of query conditions
• Simple query condition.
• Complex query condition.
 Simple query condition
• A record is selected only if its value of a
particular field match a certain value or a
range of values.
• Wild card characters are used to create query
conditions/criteria.
 Wild card characters
• Special characters used to create queries to search for data
that we only know part of the field value.
• They are often used with the LIKE string function.
• The LIKE string function is used to process text and date
field values.
• Examples:
 To obtain a list of all pupils born in March.
 To obtain a list of all pupils whose surnames start with the
letter ‘M’ or within a given range of letters e.g. N to P.
• Table below summarizes the wildcards that we can use in
query selection criteria:
 Wild cards summary
Wild Meaning Examples Example result
card
* Any number of characters LIKE “S*” Smith, Smithers, Scott
? Any single character LIKE “Ca?” Cat, Cab, but not Cart
[ ] Any character in the brackets LIKE “10[YG]” 10Y, 10G etc
[-] Any character within a range of LIKE “10[A-M]” 10B, 10G but not 10T
characters
! Any character not in the LIKE “10[!YG]” 10B, 10T but not 10Y
brackets or 10G
 Complex query condition
• Records are selected only if they meet two or
more conditions.
• They are also called compound/multiple
query conditions.
• They usually make use of the OR and AND
logic operations.
 OR condition
• At least one of two or more conditions must
be true for a record to be selected.
• In MS Access it requires that the different
criteria be placed on different lines in the
query design view.
 AND condition
• All the conditions must be true for a record to
be selected.
• In MS Access it requires that the different
criteria be placed on the same line in the
query design view.
 Reports
• A database object that effectively represents data from the database on
screen/in printed form for distribution.
• It can be used to analyze and summarize data e.g. it can display count of
records in the report, maximum, minimum and average values of numeric
fields, sort records in it etc.
• User has control over size and appearance of all parts of a report hence
can display information the way he wants.
• A report has different parts: header, detail, page footer and report footer.
• Information on a report can be sorted by any record field and user can
choose to display all or only some of the fields of desired records.
• A report can be based on a single table, multiple tables or a query.
• A report can be very simple e.g. based on a list of all records from a table
or it can be more complex where it is based on a complex query.
 Types of reports
• Columnar report – a report with one record printed
per page.
• Tabular report – a report with one record per row of a
printout.
• Each report type is suitable for particular situations.
• Easiest way to create a report in MS Access is to use a
report wizard. The basic report created can then be
edited/modified in design view.
• NB All query results (answer tables) and reports must
always be displayed and printed in landscape page
layout unless you are told otherwise.
 Database views
• There are three basic ways in which a
database can be viewed/processed:
Design view-used to create new database
objects directly/modify object designs.
Datasheet view- used to enter and process
records.
Form view- used to enter and process records
in a more convenient way using forms.
 Database marking criteria
• There are three items assessed:
Production – has the candidate produced the
required database object?
Accuracy – Is the object free from accuracy
errors/spelling and grammatical errors?
Format/layout- Is the database object
professionally presented?
 Common database errors
• Critical errors e.g:
 Incorrect search/query results
 Missing/null field values in some records
• Accuracy errors e.g:
 Input data errors – each data error will cause
separate accuracy errors.
 Check that all fields are wide enough to display all
the data.
 Check that you have sorted by the correct field
and in the correct order (ascending/descending).
Organized data pool!