DATABASE MANAGEMENT SYSTEMS
TOPIC 14
OCTOBER 6, 2016
compiled by john baptist muwuluzi
COMPILED BY JOHN BAPTIST MUWULUZI 0
TOPIC 14: DATABASES
INTRODUCTION
Think of an address book you might buy in a bookshop. In it, you will write all your important
contacts; friends, family, relatives, companies, and other people in your circles. The address
book contains all names, addresses, location, phone numbers and whatever you need to contact
anyone at any time.
Examples of paper databases in everyday life include Telephone book, a card catalogue, Duty
Roster of employees, Duty Roster of students, Payroll, College Course offerings, Restaurant
menu, Cookbook, Movie listing, Encyclopedia, Shopping catalogue, Corporate Inventory, Party
Guest list, Dictionary, Recipe Cards, Television guides, Asset Tracking, Service Call management,
Classroom management, Event management, Contact management, Inventory management,
Personal Address book, Accounts Ledger, Orders management, Time and Billing, Expense
Report, etc.
The world generates an enormous amount of data from almost every aspect of life; school
records, credit cards, store merchandise, telephone systems, web sites, among others. Some
time ago, manual effort was used to track and report this information. Today, database
management systems manage this information.
The database is now such an integral part of our day-to-day life that often we are not aware we
are using one (Thomas M. Connolly et al, 2005).
A database is a shared collection of logically related data.
A database management systems software system that enables users to define, create,
maintain, and control access to the database.
Examples of such software packages are: Microsoft Access, FoxPro, Paradox, Oracle, Structured
Query Language (SQL), Crystal Reports, FileMaker Pro, Lotus Approach, Goldmine, Sybase,
Microsoft Pinpoint, Informix, Ingress, dBase III, dBase III+, dBase IV, dBase V, Approach,
Oracle, Open Office Base, Progress, Datapower2, Rapport, Postgres, etc.
FUNCTIONS OF A DBMS (DATABASE SOFTWARE)
a) Takes care of storage, retrieval and management of large data sets on a database.
b) Create a database structure to accommodate data that may be text, numbers, objects,
video, sound, etc.
COMPILED BY JOHN BAPTIST 1
MUWULUZI
c) Enter data easily and quickly. It lets you easily add new records, delete outdated records,
update records, etc.
d) Organize records in different ways ie sorted and indexed order.
e) Locate specific records ie search, find and replace.
f) Eliminate duplicate data say by editing eg deleting and retyping.
g) Create relationships between tables.
h) Ask questions about your data and get answers using queries.
i) Create data entry forms.
j) Create professional good-looking reports.
k) Change appearance of information, i.e perform some formatting, etc.
Primary Key
Record
Field
CHARACTERISTICS (FEATURES) OF A DATABASE MANAGEMENT SYSTEM (DBMS).
a) Shared – data is shared among different users and applications.
b) Persistence – Data exist permanently in the sense that, data can be live beyond the scope of
the process that created it.
c) Data Security - data is protected from unauthorized access using passwords. Provides
protection of databases through security, control, and recovery facilities.
d) Validity /Integrity/Correctness – Data should be correct with respect to the real world entity
that they represent. Auditing or error check and correction are easily done.
COMPILED BY JOHN BAPTIST 2
MUWULUZI
e) Consistency – whenever more than one data element in a database represents real-world
values, the values should be consistent with respect to the relationship.
f) Data Integrity – refers to both correctness and consistency of data. Correctness is being free
from errors while consistency is having no conflicts among related data items.
g) Large data storage – it is capable of storing enormous data amounts for personal and
organizational use.
h) Non-redundancy – Eliminates or decreases duplication of data in the same container. No
two data items in a database should represent the same real-world entity.
i) Structured data – data are well arranged in form of a tree and its branches for easier access
and retrieval.
j) Independence – The three levels in the schema (Internal, Conceptual and External) should be
independent of each other so that the changes in the schema at one level should not affect
the other levels.
k) Auto-save - Files are saved automatically as they are adjusted.
l) Online help - Provides to the user in form of hints, tips, wizards, help notes, context sensitive
help, etc.
TERMINOLOGIES USED IN DATABASES.
Primary Key: Is a field or collection of fields with unique values used to identify each record in a
table. It stores unique record identification numbers different from one another eg social
security number, Reg No., etc.
Foreign Key: Is a primary key of a table but used in another table. Eg when we include a primary
key from one table in a second table to form relationship between the two tables.
Foreign keys:
A field in one file that matches a primary key value in another file
Example: the advisor number is a foreign key in the STUDENT file that matches a primary key
value in the ADVISOR file
A foreign key need not be unique
A combination of two or more foreign keys can form a unique primary key value.
Referential integrity ensures that a foreign key value cannot be entered unless it matches
a primary key value in another file
Candidate Keys: – It is a field with unique values that can become a primary key though it is not
yet a primary key.
Field : A single and incomplete characteristic of an entity. It is a column in the list/table or a
combination of two or more bytes / characters. Each field describes a characteristic about a
record eg student’s lastname, firstname, Sex, Age, etc.
Field name: a column label or heading that describes a field eg Sex for Gender.
COMPILED BY JOHN BAPTIST 3
MUWULUZI
Field Description – A single explanation or particulars about a field.
Field value - the specific data contained in a field ie it combines with others to form a record.
Field length / Size – Is the maximum number of characters that can be stored for data in a
particular field.
Field data type – this specifies the type/category of data a field can contain.
Record – Is a row in a table that contains information about a given person, product, event,
asset, etc. It’s a collection of related and meaningful fields about an object/person. It’s also
called a tuple or Case or row. It is a complete meaningful entity in a table. A group of records
forms a file (table) ie database file.
Regno Lastname Firstname Sex Age Class Hall Address
02 Mirembe Ruth F 13 S2A Orion Box 18, Lugazi
Table/Relation (Database File)
A collection of related and meaningful records about a particular subject stored under a unique
name.
Database: collection of data organized in a way that allows automatic access, retrieval, and
application of that data. An organized collection of related information in form of tables. A
database is basically used internally by members of an organization. A database may also be
called a list.
Attribute: Property of an Entity
Entity: An Entity is a person, place, Object, Event, Idea, Commodity, etc.
Datatype: specifies and determines the kind/category of field values or information entered and
stored in the field containers.
Relationship: A relationship is defined an association or link among entities i.e how data in one
table are related to data in another table.
Field properties: These are traits or characteristics defining data entered in particular fields.
Report
Query
COMPILED BY JOHN BAPTIST 4
MUWULUZI
DATATYPES
Data type specifies and determines the kind/category of field values or information entered and
stored in the field containers.
There are various data types applied in Microsoft Access and these include; Text, Number,
Currency, Memo, Date/Time, Auto number, Yes/No, Object Linking and Embedding (OLE),
Hyperlink and Lookup Wizard.
These are defined in detail as follows: -
(a) Text (Character) - Alphabetic text or numbers that can’t be calculated. Examples of such
fields are names, addresses, subject names, Course names, Telephone number, etc. It can
contain up to 255 characters.
(b) Number (Integer) - Numerical data you can calculate but not relating to money eg Age,
height, weight, Course Duration, Score, Number of items in stock, etc. (whole number or
fractional).
(c) Currency - Are numerical monetary values that can be calculated and may have a currency
symbol or not such as £56000.05, 56,000.05/=, 56000.05, etc. Suitable for fields like salary,
Gross pay, Net pay, PAYE, School dues, etc.
(d) Memo - It is for lengthy descriptive text and numbers usually several sentences or
paragraphs. It can contain a maximum of 32,000 characters. Suitable for fields like Remarks,
Comments, particulars, Descriptions, etc.
COMPILED BY JOHN BAPTIST 5
MUWULUZI
(e) Date/Time – For month, date and time values that are in the form dd/mm/yy or dd-mm-yy ie
date / month /Year for the dates and Hr:Min:Sec ie Hour:Minutes:Seconds for time values.
Suitable for fields like Date of birth, Date of Joining, On/Off set date/time, Date/Time of
Departure/Arrival, etc.
(f) AutoNumber (Counter) – a number that automatically increments for each record you enter.
It stores sequential numbers entered automatically by MS Access starting with one. They are
unique ie different from each other and can make good primary keys. Suitable for fields like Reg.
No, ID No., Membership no. Employee Id, etc.
(g) Yes/No (Logical) – Where you can enter and Store only one value or answer out of the
available two options but not both. Suitable for fields like True/False, On/Off, Smoker/Non-
smoker, Ugandan/Non-Ugandan, In/Out, Cash/Credit, etc. It is also called Logical or Boolean
data type.
(h) Object Linking and Embedding (OLE) ¬– For object data and other binary info such as
sounds, symbols, graphics/pictures such as signatures, thumbprints, company logos, one’s
photo, etc.
(i) Hyperlink – Stores data in form of hyperlinks, which are the blue-coloured hotspots or
connections that can be clicked to open other pages or documents eg e-mail address, website,
Bookmarks, etc.
(j) Lookup Wizard – refers to a list of items in form of a listbox from which you can choose a
desired item during data entry especially if that data exists in another table or form. It’s suitable
for repetitive data such as marital status: Single, Married, Separated, Divorced, Widowed, Never
married, etc.
FIELD PROPERTIES
These are traits or characteristics defining data entered in particular fields. Common properties
include:-
COMPILED BY JOHN BAPTIST 6
MUWULUZI
(a) Field Size –ie this specifies the maximum length of a field the maximum number of characters
to be stored in that field eg 15 characters long beyond which the whole or only the extra
characters will be rejected. A field can contain 64 characters long including spaces.
(b) Format - Specifies how data is displayed which may be default (automatic) or custom (user
set). For example long/short date, scientific, standard number for number and currency fields.
(c) Input mask – Specifies the pattern or predefined format for data to be entered in that field eg
(--/--/--) ie dd/mm/yy for date or (--:--:--) Hh:Min:Sec as for time, etc. Others are: !(999)000-
0000 for telephone number.
(d) Caption value – This is a default label in a form or report eg DOB for date of birth, L/name for
lastname, F/name for firstname, M/S for marital status, etc.
(e) Default value – A value that appears in the field automatically even before you enter there
anything ie it works as a placeholder. Eg MSMN/__, U0063/__, for index no., 0.0 for numeric
fields, etc.
(f) Decimal places – Specifies whether or not number and currency values should have decimals.
If yes, how many.
(g) Required – Specifies whether or not an entry must be entered in that field ie if Yes you must
type an entry but if No, you may proceed without entering anything.
(h) Allow zero Length – A provision for a field to be left blank in case of unavailable data to be
entered later even if the setting for required is Yes. Nulls indicate that data may exist but it is
unknown. To enter a null, leave the required property as No and leave the field blank eg a
company without a fax machine/no., etc.
COMPILED BY JOHN BAPTIST 7
MUWULUZI
(i) Indexed - It specifies whether or not duplicates in the field should be allowed in order to
speed up the data search, sort, filter, etc.
(j) Validation Rule – An expression that defines data entry rules ie it limits values that can be
entered in that field eg “F” or “M” for sex, >=10 for age, “married” or “Single” for marital status,
etc.
(k) Validation text – An error message that appears to alert you when you enter a value
prohibited by the validation rule. Or error message that appears when a validation rule is
violated eg “Please only F or M for sex.”
DATA VALIDATION
Validation – Is the process of comparing the data entered with a set of predefined rules or
values to check if the data is acceptable. Validation is the name for the checks that detect
incorrect data, display an error message and request another input or just reject the data.
Data Validation – is the checking of input data for errors (e.g, of the correct data type) before
processing.
RELATIONSHIPS AMONG DATA
A relationship is defined an association or link among entities ie how data in one table are
related to data in another table. For example a customer places an order for some item. This is
an association between customer order and item.
Types of Relationships
There are three types of relationships namely;
One-to-one (1:1),
One-to-many (1:M) and
Many-to-many (M:N).
(a) One-to-one: where a particular field in one table has only one matching record in the
other table and vice-versa. E.g One Office is occupied by only one manager who is placed there.
Examples of One-to-one (1:1) relationships include;
One office manager heads one office
One vehicle ID number is assigned to one vehicle
One driver drives one delivery truck.
One faculty member is chairperson of one department
COMPILED BY JOHN BAPTIST 8
MUWULUZI
(b) One-to-many Relationship: Means that for one field in one table, there are several
matching records in the other table. Examples of one-to-many (1:M) relationships include;
Sex /gender (F/M) is shared by many persons.
One book publisher can publish many books.
One lecturer / teacher teaches many students.
One doctor attends to many patients.
(c)Many-to-many: Where a field in one table contains many records that have many other
matching records in the other table. This type of relationship is rare and difficult to process.
A student enrolls in one or more classes, and each class has one or more students
registered
A passenger buys tickets for one or more flights, and each flight has one or more
passengers
An order lists one or more products, and each product is listed on one or more orders
END
COMPILED BY JOHN BAPTIST 9
MUWULUZI
