We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
You are on page 1/ 23
Unit 3. Databases
Introduction, Definition of terms and functions of Database Management system
[Management of data is important in all organisations. Data in organisations forms part of tne stratage, tactical,
‘operational management and dacision making process. Electronic databases have changed the way datas
created, managed and stored using computerised information systems.
Definition of terms
Database - t's a collection intarrelated data files stored in an organised manner to permit easy retrieval
Database management system - Software that manages storage, processing and retrievsl of data in 8 computer.
Examples of database management system software include: Fox?ro, Dbase ill, Dase IV, MYSQL, Lotus
Approach, Microsoft Access Microsoft SOL server and Oracle.
Fling management systems - Computerised filing method that uses nor-integrated files calle fat files.
Functions of a database management sofeware
‘interfaces 2 datanase to other application programs
1 Safeguards database against unauthorized access.
up Allows staustcal analysis of data
1w Permits tne adition of new records.
i Allows modification of existing records.
vi Organises dats for easy access, retrieval and manipulation of records.
Types of database models
2) Flat Fle Holds only one set of data and it snot any different from manual files. Do not allow relationships to
be created amongst fle.
Nama Tompson Lucas
Patent ID RBr545/2018
Date of admission zamena
Block admixed c
Bed number HOSPIBLOCK C1004
) Hierarchical Model
Records are arranged in a form that resembles family tr¢e with lower level records subordinating to the upper
level records as shown in Figure below, The top mast record is called Roct cirectory To access lower level
records, parent records are frst opened, Allows one to mary relationships
eet arom
—=
ene eos Let ene€) Network model
Recembles hierarchical but has baen modified to allow flexible access co racords. A child racors can belang to
several parant records.
Allows many to many relationships.
[Root directory
I I I
Parent records
|—hild records
Network database model
d) Relational database model
Data Is organised in tables also called relations. Records are uniquely identified using a key field (primary
kay) Permits several types of relationships including oneo-one, one-to-many and many-to-many relationships.
Relationsnips are created amongst tables by linking a primary key to a foreign kay. Rows nold records (also called
tuples) wile columns hold fields (sso called attributes}
‘Admission table
+ | Patient 1D NRBI3452018 J Primary key
Patient name ‘Thompson Lucas
Date of admission (22/7/2014
Block admitted c
Bed number “HOSP/BLOCK C/004
Payment table
Patient 1D_ NRBI3452018 reciente
+ [Amount of bill 220772014
Date paid a
Balance HOSP/BLOCK C7004
Object oriented models
Designed as a collection of objects An object cons'ss of both data (state) and the Instructions (oehaviour! to
process the data. The process of combining state and behaviour in one object is called encapsulation, Allows
databases to hold multimedia data
Difference between Filing Management and Database Management Systems
Fling Management Systems Database Management Systems (08M5)
Use files that are not integrated. Use integrated les
Low data incegrany Use nnagrated les
‘lows sower cata modtfcaton ‘lows faster oata modification
Not compatible with other applications Compatible with other applicationsFeatures of a database management software
DBMS nave the following features:
Table - A data structure tnat's used to accept and organise records.
Form — A graphical interface used to enter data onto an underlying table.
Query Asool used to extract and analyse data,
Report generator - Used o create and present both soft and hardcopy summaries of a database
Macro - Used to automate frequently executed commands so snat they can be executed togarner
Programming module - Used to add new instructions in a database
Data organisation in a database
Data is organised from me smallest unit called a field to the largest unit called a database as explained below:
Fields
Ieconsists of 2 character or sat of cnaractars mat ge some place of information about an enciy.
Records
It is 2 set of related fields tnat give sensible information about an entity, for examole, a patients row in a table
showing patient's name, patients number, admission date, diagnosis and prescrition.
Tables
lt isa set of related records, for instance. a file showing all the patients admitted in a snecitic ward.
Databases
[cis set of interrelated files. For example, al patients, doctors and stock inventory files in s hospital
Field data types
Tex
{includes alphabetic letters, numbers and punctuations and can support up to 255 characters. Used when a
field wil not b€ mathematically manipulated.
Memo
I consists of letters, numbers and special characters. It supports up to # maximum of 32,000 characters.
Number
I consists of numeric numbers 010 9. The field can be mathamacically manipulaces.
Date or Time
identifies afield as ether date or time, Ie can be mathematically manipulated.
Currency
I identifies numeric values that have decimals parts ike monetary values
‘Auto number
Ik automatically sets the frst entry to one and subsequently automatically increments the values in the field by
YES or NO
Itallows logical values with two trutn values like rue or false to be entered.
OLE Object
OLE stands for Oajact Linking and Embedding. tis used wth graphical user interface applications to insert
JIimages, pictures, drawing and charts among othersField properties,
Define finer details of fields. They include
+ Fld sie I allows tne user to ser he number of characters a feld will consist of ether than the default 50. For
‘numeric fields, ce following ses are used:
+ Integer and long integer
‘Accept fields witn no decimais.
+ Byte Accept numbers berween 0 and 255.
+ Single and double Accept number with decimals,
Single supports up to 38 decimal places while double up to 308 decimal places.
Format
Dictates now data appears on tne screen wnen typed.
Decimal places It allows the user to specify tne number of decimal places.
Input mask I automatically formats the field entry into a specified manner.
Caption I gives a mare descriptive name for the field in a table or @ form
Default value
Ie s a value enat aucomacically appears in che table or form ifno otner encry is made.
Validation rule
Logical expression tnat controls the values entered in a table fel
Validation text
‘A message that appears once the validation rule is not observed
Required
Ic ensures that an entry's made in a fia before proceeding to the next Feld or record.
Allow zero length
Iallows tne user to proceed witout making an entry In te fel.
Indexes
{is used to organise records for easy searching. Note: Primary key is a special index used to speed up searching
and sorting records in ¢ table. ft uniquely identifies each record stored in @ table so that no record duplication is
made
Table Design Terms
Entity -A person, thing or a place about wnicn dats can be stored.
‘tribute - Date that can be used to describe an entity
Foreign key - A primary key transferred to another table to help create relationships
Referential integrity - A festure thet ensures that all records entered in the related table existin the primary
table
Normalisation - Process of removing repeating fields among relating tables. A table is created by spectfying
+ Fisk names
+ Field daca types
| Flald properves
Primary key
‘Table name A table can be linked to other existing table(s) in one of the following ways:
+ One-to-one relationship - A field in a table matches only one record in tne relating table.
+ One-to-many relationship ~ A field ina table matches several records in another table.
« Many-to-many relationship ~ Spectic records in stable match several records in encther table,Form design
[Aform is a graphical interface that enables the user to easily view and make data entries into an underlying
rable or query.
Terms:
+ Controls - Are objects placed on a form te snow data or execute seme commands. Examples include: text Box,
‘check box and command buttons among ethers.
“Bound control - Data is sourced from a table or query.
+ Unbound control - Data is typed cirectly. Does not source data from table or query.
The following form layouts exis:
*Columnar
Tabular
«Datasheet
‘Justified
Query design
‘The term query refers to a question used to instruct a database to retvieve dats or perform operations such as
Insertion, update and deletion cf data in a table. Used to analyse table(s) dats using ether of the following types
of query:
i) Select query It allows the user to speci the search criteria
Ii) Action query They are used to makes changes to records such as delete, update and acid a group of records
from one table to another or create 2 new table trom another table.
Four types of action Query
|) Make table query- Creates 8 new table from an existing table,
ii Delete query: Erases specified records from a table(s),
1i) Update - Modifies date in stable
'w)Append query: Adds data in a table from a able(s)
Calculations in a query
(Calculations in 2 quary can be parfarmad by using tne Total functions (Zor by creating basic formulae. & faw
formulae are deccrived eelowe
+ Min - Returns te lowest value from a field column.
+ Max - Returns the highest value from a feld column.
+ Count- Returns the number of items in field column.
+ Sum ~ Adds numerical data icems.
“Avg - Computes the mean of numeric data items.Specifying the search criteria
Conditional staternene(s] are used in the criteria row to extract specific records, Operators and wild cards are
used in specifying ene criteria racords.
0) Relational operators which include:
“less thant)
+ greater thant)
+ grester than or equaltol>=)
+ ess than or equal tof)
+s equal to¢
1) Logical operators which include:
“AND
“OR
+ NOT
“BETWEEN
i) Wild cards - sre special caracters used to subsctute other characters at the'r position In searcn or querying
process. They include:
+7 = substitutes 2 single character atts position (Example: 2am can extract Tom, Yorn, Com)
+ * - substitutes group of characters atts position(Example: P* can extract Paul, Poem)
+ #- supstitutes any single number Example: 245 wil match 208, 215,225, 235)
+ []~ suostiutes any single cnaracter witnin the brackets (Example: PIAE]L matcnes PAL, PEL)
substitutes any character not in brackets (Example: Gllaojt matches Gill Gut and not Good, cost)
Enforcing database security
Several data security tools are avalable in databases such as password protection, encryption scrambling up
characters so that they cannot make sense to attacking programs unless they are decrypted), hiding database
objects and user level securityKCSE Revision Questions
KCSE 2012 Paper |
Qn.20. a) Win the aid of a dlagram, dascrine tne nierarcnical dataoace mooel. (4 marks)
KCSE 2009 Pager 1
Qn.17. d) Name anc describe any two types of database models. (6 marks)
KCSE 2007 Paper 1
Qn 13. Diferendiate between the following pairs of terms #5 used in database design b) Table and query (2
marks)
KCSE 2012 Paper 1
(Qn.20. )1) Name three types of validation checks during dats encry. (3 marks)
KCSE 2011 Paper 1
(Qn.13, State the purpose of each of the following in database design. a) Input masé (1 mark) p) Default value (1
mark)
Papert
Qn.18. a) Using two examples, explain the term field properties as used in database design.
b) Below is an extract from 2 hospital database table.
Patient Ne Name Date Registered | Amount Paid | Remarks
LDK/O01 Mathew Olang | 04/05/08 250000 | TogoferxXray
LDk/908 —JoyChelmo 67/06/08 1200.00 | Medicine robe ordered
LDKI008 Joy Kamau 09/08/08 350000 | Te be admited for further check up
LDKI002 Gerald Wasike 02/04/05 00.00 To come back for reviewvid State with reasons the most suitable data types forthe following fields (8 marks)
Patient
[No Date registered
‘Amaunt paid
vi Which would be the most appropriate primary key feld for tne above table?(1 mark)
Jn) What isthe purpose ofa primany key file in database design?(1 mark)
KCSE 2008 Paper 1
Qn.18. The information below is maintainad by a gatron of 3 wildlife club in a school. Study and answer me
‘questions mnat follow.
wave | Ges | Adwisiontember | Mententiptimber | Gow
u ier on
aa Serengeti
Gime | aw | ate root be
cay N08 rox is
Gey W782 voor
eee
srry 3800 001 coo
Maio | 26 | 3905 om Soe
Jonhine | 2W | 3806 wor
pet
fe ans 001 oat
coy E05 ron ead
cern |W | 9 wea i
Peul IN 4013 M002, oe
2) Describe the field values, records an file G marks)
1) State the most appropriate primary key for the list. (1 mark)
Stace ine most appropriate data cype for tne fields:
Admission number (1 mark)
|) Membership number. (1 mark
0) fa database was to be created for thelist; Forms, Tables, Queries and Reports are likely to be used.
1) State tne purpose of each of these abjects. (4 marks)
1 Which objects cannot be Used to store the dats in the I'st? (3 marks)
211) Howy many field values are inthe fist?(1 mark)
1] Hew many records are in the list2(1 mark)
KCSE 2007 Paper 1
Qn.20. 2) Anead teacner keeps tne following student detals in a database: Name, Address, Town, Date of birth,
Marks scored, Fees paid
1) Neme the most appropriate primary key. Give a reason. (2 marks)
i For each item in the student's details above, indicate its most appropriate dats type as used in the database
(Bmarks|
11) Explain why input screens are better dats entry designs than entering data directly to 8 table (2 marks)
0 Distinguish between:
D Atable In word-processing application and 2 table in a database application (2.marks)KCSE 2007 Paper 1
Qn.19. BDA students’ database comprises of students’ details table and fees received table as shown below
Students’ details table
Surname
Midge Name
Fiest Name.
Admission Numeer
Fees recelved table
Date Amount
Receipt Number
Course
1) State the primary key field for each table
1 State tne field which should serve as the linking field for the wo tables. (2 marks)
KCSE 2015 Paper 1
(Qh.3. The figure nelow snows ralazonsnip betwaen tables ina database,
Table1
Performance ID Table 2
‘TermCode TemID
‘StdID Year
Mathemais ae
English ja__ |
Kiswahili na
Science
‘danafy wo primary and wo foreign keys used in tne relationship.
KCSE 2012 Paper 1
Qn.11. Describe the following types of relationsnins as used in Database design:
2)Oneto-one
»)One-to-many
KCSE 2009 Paper 1
Qn.17. Alecturer keeps tne following student details in a database: name, age, and course.
a) Were an expression you would use to compute the year of birt ofa student using this year asthe current
year. (2 marks)
_) What query expressions would the lecturer use to ist tne students whose age is anove 15 years and below 25
years? (@ marks)
‘Which expression would tne lecturer use to generate:
Tne numaer of students In :ne datsoase? 2 marks)
\W Tne mean age of te students n tne database? (2 marks)Unit 4. Desktop Publishing (DTP)
Introduction and Definition
Publishing isthe act of producing publications like newspapers, cards, pamphlets, pictures and calendars of
professicnal quality. In the madern eva, the pracess of publishing has changed because of the advent of
personal computers and digital princers cnat are able to produce high quality publications.
Definition
Desktop Definition isthe act of creating visual communications such as brochures, business cards, greeting
cards, web pages anc posters among others.
Desktop publishing software is application safsware for graphics design. Examples of DTP software include
Adobe Pagel/aker, Microsoft Publisher, Quick Xpress, Adobe in Design, Ventura, Serf page plus and Apole page
2
Purpose and Advantages of desktop publishing software
Can create complex text anc graphics.
|W Allows the user to create various page layouts.
ii} Allows the user to print the publications.
Advantages of using desktop publishing software
+ Gives the user power to maripulace tert and graphics and control ofthe page layout
+ The graphics and text manipulation capabilites of @ DTP sofware far ourweigh those of @ word processor
+ Items can be edited and formatted independently
+ Allows te user wien a wide range of templates
+ Allows 3 common sesting on master pages whicn can than be repeated on omer pages.
+ Allows production of commercial publications.
*Teallows te user to come up wth a wide varery of publications
+The Frames do not necessariy have to be arranged in 3 particular order.Designing a publication and Types of desktop publishing softwar
Designing a publication
Desktop publishing software allows the user to design a variety of publications for print such as business cards,
calendars, banners, bilboards, certificates, books, newspapers, among cthers
Graphical based: Manipulate graphic objects. Layout based: Assist the user to came up with a varity of layouts
Microsoft Publisher window
eeMs publisher window has the following features
2) The pasteboard - Areas that hold text and graphies during page layout.
) Toolbox - Set of buttons znat contains various tools that you can use to create and manipulate publications.
Rulers - Vertical and horizontal guides. They are used to guide placement of text and graghies to the required
postions,