R191196H NOMATHAMSANQA MASHINGAIDZE

Question 1

You have been approached as a database consultant, by an organization which is having

problems related to file storage associated with the manual file system. You are
required to suggest a better system. Discuss factors that might influence the
organization to set up an electronic database system
[25]

An Electronic Database System is a software that defines, creates, edit, maintain, control
and gives access to the database for data related purposes. They enable end users to create,
read, update and delete data in the database. Whereas a Manual File System is based on
the collection of application programs that provide services for the end-users of enter data.
Each program is specifically design per task and can’t be used simultaneously with each
other. Users of Manual File System do not have the privilege to update, edit and delete data
in the database per programme.

The file based Data Management Systems contains numerous files that are kept in sundry
different locations in a system or even across various systems. So, there is are chances of
multiple copies of the same file which lead to data redundancy. Data redundancy is the
repetition or superfluity of the same data in a system. This problem is reduced with
Electronic Database System which prevents data redundancy as it is a single database and
any updates on the data is reflected immediately into the system. This makes no chances of
the system encountering any duplicate data. Data consistency is ensured in the database
because there are no duplicates of data files that can be found in the system.

In a database, the users of the database can share the data amongst themselves in a much
easier way as the users will be logged in the same system. There are various levels of
authorisation to access the meta-data and consequently the data can also be shared based
on the correct authorisation protocols being followed. Meta-data that can be accessed by
the company administration is different from the meta-data that can be accessed by a first
line management. An Electronic Database System is designed to coordinate multiple users
accessing the same data and can edit, delete and create data simultaneously. This gives a
room for easy data transfer within the system and privacy of data as the data is accessed by
authorized users only. It is more convenient in retrieving and accessing data in the system
by users.

Data security is a vital measure in Electronic Database System. Only authorised users should
be allowed to access database and their identity to access the database information should
be authenticated using a username and a password, pin or biological recognition. When a
user is using password to grant access into the database, it is recommended or the
password to contain characters and symbols to avoid hacks, this makes it an unexpected
password. Unauthorised users should not be granted access to the database under any
circumstances as it violates the integrity constraints. Data security has the advantage of
cutting out unnecessary changes that can be done by unauthorized users and it reports
unusual log in attempts immediately. Database can be made secure with help of data
privacy. The privacy rule in database means that only the authorized users can access a
database according to its privacy constraints. There are levels of database access and a user
can only view the data he/she is allowed to respectively to the users’ level. Authorized users
must secure their passwords to avoid data leaking in the company. Data privacy also
consider who can access which data and who can view a certain type of data which gives
privacy to certain data that can include worker`s salaries that is not supposed to be accessed
by any regular user.

Database Management system automatically takes care of backup and recovery so the users
do not get worried about backup and recovery in cases of system crushes or get corrupted.
The users don’t need to backup data periodically because this is taken care of by the DBMS
as it periodically uploads a snap of data in the system to create an image of database that
can be retrieved when the system requires. Database management system can recover lost
data unlike manual file system that has prone attacks that destroys any data. Backup and
recovery gives an upper hand to company as it ensures business continuity without meta-
data being lost. Automatic data recovery and a backup gives an insurance to company`s data
at a good price for privacy protection.

All the meta-data appears consistently across the database and the data is the same for all
the users viewing the database. Moreover, any changes made to the database are
immediately reflected to all the users thus there is no data inconsistency. Updating errors
and language errors are reduced at the entry of data in to the electronic database system,
the system corrects automatically errors which also include duplicated data files. More so,
data integrity states that the meta-data is accurate and consistent in the database. Data
integrity is very important as there is multiple database in a Database Management System
so all the information must be correct and thus avoids junk information. All of these
databases contains meta-data that is visible to multiple users simultaneously. So it is
necessary to ensure that the data is correct and consistent in all the databases and all the
users.

The Electronic Database System has the ability of retrieving data fast and reduces data
entry. In a database there is data that is accessed frequently and at the same time there is
data that is accessed occasionally. So it is recommended to store frequently accessed meta-
data in a formation that it can be accessed quickly than the occasionally referred data. This
makes it informal and fast to access data from the database so it reduces retrieval costs
when accessing data. Electronic database has the concept of easy entry that comes with the
system that reduces data entry and has an advantage of easy retrieving of data. Therefore,
electronic database management is convenient for a company that deals with vast data than
the Manual File System.

Using Electronic Database System if a new application is required is easy since it develops a
new application so long as the required data is available from the system. Less time will be
consumed in creating a new application as the stored data is the same data used over and
over. Creating a new application is something that the Manual File System does not do but
instead when using the Manual File. When using the Manual File System, users have to
create another program when different files have to be created which is a long way.
This gives us a light to conclude that Electronic Database System is a solution to problems of
companies that uses the Manual File System for file storage.
Question 2

Databases can be divided into two categories which are spatial databases and
multimedia databases. Explain in detail those databases outlining how they function
[25]

Multimedia database is the assembly of interrelated multimedia data that includes text,
graphics (sketches, drawings), images, animations, video, audio and have enormous
amounts of multisource. The incorporation of multimedia database system improves the
quantity and quality information manipulated by a computer user in the system, computer
aided design and information manipulated. The area of intelligent multimedia content
analysis and retrieval techniques is an emerging sector. The framework that manages
different types of multimedia data which can be stored, delivered and utilized in different
ways and also represented in a wide diversity of formats on a wide array of media sources.is
known as multimedia database management system. These types of data usually contain
large data objects, require real time handling, and maybe uninterrupted raw data, a
characteristic that distinguishes these types from alphanumeric data.

The multimedia database systems are mostly used when it is required to administrate a
huge amount of multimedia data objects of different types of data media such as optical
storage, video tapes and audio records, so that they can be used efficiently for as many
applications as needed. The Objects of Multimedia Data are text, images, graphics, sound
recordings, video recordings and signals that are digitalized and stored in the multimedia
database system.

Text has character represent raw data, registering data describe the coding and descriptive
data may include information for layout and logical structuring of the txt or keywords.
Images has pixels represent raw data, registering data include the height and the width of
the picture and descriptive data are individual lines, surfaces and subjects.
Audio sequences has the digital sample values created by a simple PCM coding represent
the raw data, registering data represent the properties of the audio coding
Video sequence has pixels’ matrices that represent the raw data, registering data provides,
in addition to other information, the number of images per second and descriptive data
provides a scene description for example “Brooklyn`s birthday party”

Multi-media database contents media data. Media data is the actual data representing
images, audios and videos that are captured, digitalized, processes, compressed to be
stored in the database.
Multimedia database contents media feature data. Media feature data contains feature
derived media data, for example it contains information about the distribution of colours,
the kinds of textures and different shapes present in an image. This is also referred to as
content dependent data.
It also contains media format data. Media format data contains format information of the
data after it goes through the acquisition, processing and encoding phases.
Amongst the multimedia data content is the media keyboard data. Media keyboard data
contains the keyboard descriptions. Using an example of a video, this might include data,
time, and place of the recording, the person who recorded and the scene recorded to
mention a few. This is also called as content descriptive data.

Multimedia database has characteristic of co-responding storage media. Under this

characteristic data must be stored and managed according to their specific characteristics of
the storage media.

Another characteristic is descriptive search methods. Query must be descriptive and object
oriented. There are query languages such as SQL (structured query language) these types of
languages enables formulating database queries and each entry has its state information
that can be retrieved correctly.

Multimedia database has a characteristic to view specific and simultaneous data access.
Some data can be accessed through different queries by different application also large
transactions must be done in a reliable fashion, since it takes long time.

Real time data transfer is another multimedia database. Data transfer of real time activity
gets higher priority than other database activities making it have a first preference.
Database Management System must perform read and write operations of continuous data
in real time. Primitives of multimedia operating system should be used to real time transfer
of continuous data.

Multimedia database has a characteristic of management of large data management.

Database management system must be capable of handling and managing large amounts of
data. There is need of appropriate referencing mechanism.

Format independent interface is a characteristic of multimedia database for Database

Management System to hide internal storage format and offer conversion to formats
requested by the application (GIF, TIFF, SUN Raster) This allows changing to new storage
technology without any impact on the multimedia applications.

Lastly on multimedia character is the device independent interface. It hides details of device
controls, but after information on specific characteristics of available storage media (read
only, write-only, write-many)
When the Multimedia Data is accessed or compared it gives information in the following
prototype table below;
The features of a multimedia database

Medium Elements Configuration Typical size Time dependent Sense

Text Printable Sequence 10 KB no visual/

characters (5 pages) acoustic

Graphic Vectors, Set 10 KB no visual

regions
Raster image Pixels Matrix 1 MB no visual
Audio Sound/volume Sequence 600 MB yes acoustic
Audio CD

Video-Clip Raster image/ Sequence 2 GB yes visual

graphics (30 min.)

Multimedia database has a function to explain users message to the audience in an

interactive way. User content is no longer boring to read but now contains activity to keep
hold of users. It includes activities of pictures, videos, audios. It provides audience with
additional information. Such activities of media engage a large audience.

Multimedia database has a function of its applications that allows users to communicate
with the computer system in a variety of ways. Users has the room of speaking, writing,
moving objects and pictures. There is also need to convert data into computer acceptable
form as the data acceptable is the same type of data used by user in multimedia database.

Multimedia database has a function to consider every user despite of user’s abilities making
it multi –sensorial. Users have different types of data input that considers every user thus
includes talking and hearing for audios, typing for txt, visual for images and videos. This also
gives a wide variety of users of data
Spatial database is a database that is elevated for storing and querying data that represents
objects demarcated in a geometric area. Advanced types of spatial databases handle
sophisticated structures such as 3D objects, topological coverage’s and linear networks.
Spatial data is usually stored as coordinates and topology, and this type of data that can be
mapped. There are two types of spatial database which are spatial database and non-spatial
database. Spatial has the attributes to the shape, size and location of the features.
Non- spatial data refers to other attributes associated with the feature such as name,
length, area and volume, phone numbers and email address of people. In spatial database
the interactions with end users is as a result of displayed data (maps) on the screen, access
other data by clicking on the available map (hyper maps), address queries and perform
operations.

A spatial database is a collection of spatial data types, operators, indexes, processing

strategies and can work with many post relation database management system as well as
programming languages like Java and Visual Basic.net. Spatial database management system
is a software module that can work with an underlying database management system. It
also supports spatial data modules, spatial abstract data types (ADTs) and a query language
from which these ADTs are callable. It is also a software that supports spatial indexing,
efficient algorithms for processing spatial operations and domain specific rules for query
optimization. An example of spatial database in use is the Oracle spatial data cartridge
which shows that it can work with Oracle and has spatial data types for example polygon. It
is also capable from SQL3 query language and also has spatial indices for example R-Trees.

There are types of spatial database that includes traditional database. Traditional relational
database management system supports simple data types that includes numbers, strings
and date. Another spatial database type is Post relational database. Spatial database
support user defined abstract data types which is polygon which can be added. Polygon is a
polyline where last point and first point are the same. Choices of post-relational spatial
database management which are Object oriented (OO) database management system and
Object relational (OR) database management system.

Values of spatial database are clearly seen when used by Army Field Commander when they
use spatial geographical maps to visualise moving enemy troops mostly at night in battles.
Spatial database values are mostly seen used by Insurance Risk Management companies
when they have to inform their clients on properties likely to be affected by upcoming
natural disasters. Spatial database is also observed its value through the use by medical
doctors. Medical doctors check in a database for similar patient treated if someone has
been treated the same problem before checking geologically locations for both patients.
The storage of spatial database is as follows as an example;

Create table census blocks

Name string,
Area float,
Population number,
Boundary polygon,

One of the spatial database components is recall. This component is a software module that
can work with an underlying database management system and it also supports spatial data
modules, spatial ADTs and a query language rom which these ADTs are capable. It supports
spatial indexing, algorithms for processing spatial operations and domain specific rules for
query optimization
Question 3

Catholic University is planning to implement a database system to store students’ data.

They are hardly conversant with database technology and as a database consultant you
have been approached by the University to assist in the setting up of the system. You are
presented with two database options (ORACLE and MS ACCESS) and they want you
to evaluate each option in term of capacity, security, control aspects, flexibility and
economic aspects. Your evaluation should give proof where necessary [25]

Microsoft Access is an easy to use tool for creating business applications, from templates or
from scratch. With its rich and intuitive design tools it can help users create appealing and
highly functional applications in a minimal amount of time.

Microsoft Access security feature has been modified to block cyber-attacks and accounts
been hacked. To control what a user or a group of users can do on database objects, user
can secure an account database. As user creates data objects, users might consider who will
use them and what types of activities the users should be allowed to perform with the
objects. These activities might include viewing, modifying and deleting database objects or
information. Database security is primarily an issue in a multi-user environment although a
user can also secure on a single user computer

Permissions are what a user can do with an object such as entering and editing data or just
reading data. Users inherit rights and permissions from the groups to which they belong.
Personal IDs and account names are case-sensitive. It is a good practice to write down
account name and it`s PID and to store them in a secure place in case the user need to
rebuild the system database file. These items cannot be recovered if a user forgets them.

Microsoft Access does not have a large database capacity. It is mostly recommended not to
insert more than a few millions of rows in a table in Access. This is informed because it does
not have enough capacity to capture millions of user data. Microsoft Access has a database
capacity of the range 1-2 gigabyte per user and these capacity is very small for an
organization to capture in the database.

Access on the other hand is most commonly used as a single user database but since the
release of Microsoft Access 97, it can be augmented with Visual Basic code and can be easily
support 100+ users. Simultaneous users are now able to reach a top limit of 200 users.

Microsoft Access is a database serve and stand-alone in memory desktop database. It is

designed for a single user with a single connection on a single machine. It is easy and simple
to use for personal use and easy to understand more than Oracle database but its simplicity
reduces many functions which includes relational database that a sophisticated Oracle
database have.
Microsoft Access is a single platform user. This means it can only be installed and used on
Windows Operating System machines. This is a major step back to machines that uses
different operating system which includes Linux and Solarise. Some companies and
organisation cannot use Microsoft Access.

Microsoft Access is one of the more cost-friendly database applications. If an interested user
or a company wishes to buy a database application Microsoft Access is affordable and does
not require licence renewal often which is in some way cheap again. Most of the times
Microsoft Access comes with Microsoft Office package. Other database programs are
expensive and sophisticated for users.

Microsoft Access is flexible over other database application as it is easy to use with tools for
creating business applications from templates or from scratch up to perfection. It is
affordable and comes with in a package with other applications.

Oracle Database is a relational database management system (RDBMS). A relational

database management system that implements object oriented features such as user
defined type, inheritance and polymorphism is called an object-relation database
management system (ORDBMS). Oracle database as extended the relation model to an
object-relational model, making it possible to store complex business models in a relation
database.

Oracle database holds the majority of world`s relational database and this makes Oracle
database an attractive target for cyber-attackers seeking for high value data theft. Oracle’s
powerful preventive and detective security control includes transparent data encryption,
encryption key management, data classification and discovering and data masking. Data
protection with data encryption and redaction, Oracle protects the data at rest and in use.
Capabilities such as key storage and management address both regulatory needs
management. Oracle database protection is protected by their Oracle data safe, Oracle
advanced security, Oracle key vault and Oracle sub netting. Oracle data control will mitigate
two of the most common database attacks. Stolen application, service accounts
compromised/rogue administration. The data access control in Oracle database is well
under functions by their Oracle database vault, Oracle label security and Oracle real
application security. Oracle database is secure ensures that user data is not tempered with
through prompt updates.

Oracle database is a serious enterprise database. Oracle database is costly to purchase and
maintain. It is definitely one of the database management system products in terms of
efficient database administration and application. Oracle database has a whopping 40.8% in
Europe with IBM coming second with a distant 29.4%. This indicates that Oracle is a serious
enterprise database company.
It is designed for multiple users across multiple locations. Oracle is scalable, it can be used
for a single user or massively multi-users access. Tables worth 100`s millions of rows are not
rare in an Oracle Enterprise database

Oracle can be installed and used on multi operating system platforms. These platforms
include Linux, Windows, OS X, Solaries. This has an advantage to Oracle database as any
company regardless of which operating system they use they can also use oracle.

Oracle can handle large data with ease. It has large capacity to store database information
from the users. Oracle can support a very large database that can be of tens of terabyte in
size or larger. Only using Oracle database can a user create millions of millions of rows in
database and does not affect the database and can be handled by the capacity

Oracle database flexibility covers up the peer to peer communities that are available to help
solve almost all problems that users may be facing using Oracle database. These
communities are a platform that has question and question frequently asked with answers
more of problems and solutions available. This helps users If they face any inconvenience
using Oracle database.

To evaluate a database system that can be used by the Catholic University of Zimbabwe to
store students’ data from the given databases Oracle and Microsoft Access. Using Oracle
database can be the best option. Oracle database offers a larger storing capacity, a
complexed security system that prevents cyber-attacks and account hackings and Oracle
offers a better control aspect than Microsoft Access.
Question 4

Describe the problems of lost update, inconsistent read and phantom phenomenon
which arise as a result of concurrency in a database
[25]

Transactions per second (tps)is the measure of workload of an operational database

management system. The co-ordination of the simultaneous execution of transactions in a
multi-user database system is known as concurrency control. The objective of concurrency
control is to ensure the serializability of transactions in multi-user database environment.
Concurrency is important because simultaneous execution of transactions over a shared
database can create several data integrity and consistency problems. Operations that causes
concurrency in database are reading and writing data objects. There are three major problems
in database concurrency that includes lost update, inconsistent read and phantom
phenomenon.

The problem of lost update in concurrency of database starts to occur when two concurrent
transactions, T1 and T2, are updating the same data element and one of the updates is lost
(over written by the other transactions). Suppose we have a table named “product” that stores
name, id and item in stock of a product. It is used as an online system that displays the
number in stock of a particular product and so needs to be update each time a sale a sale of
that product is made.

The table can look like this;

ID NAME ITEM IN STOCK

1 LAPTOPS 12

Now considering a scenario where a user arrives and initiates the process of buying a laptop.
This will initiate a transaction. This will be transaction one. At the same time the other user
logs into the system and initiates a transaction, this can be transaction two. Transaction one
reads the item in stock for laptop which is 12. A little later transaction two reads the value
for items in stock for laptops which will still be 12 at this point of time. Transaction two then
sales three laptops before transaction one sells 2 items. Transaction two will then complete
its execution first and then update items in stock to nine since it sold three of the 12
laptops. Transaction one commits itself. Since transaction one sold two items, it updates
items in stock to 10. So this leads to a concurrency problem in database which is lost
update.

Lost update can be prevented. One of the ways is to recommend the use of optimistic
concurrency control to perform what is called optimistic locking on the data. Optimistic
concurrency control typically uses four phases in order to help to ensure that update is not
lost and users can also begin a time stamp, which is used to record pin points on the
beginning of every transaction.

There is also use of validation. This is ensured by checks on other transactions have not
modified any data that is used by the current transaction (including any transaction that
have completed or are still active after the current transactions start time).

Lastly is the commit or rollback method. If there are not conflicts, the transactions can be
committed. Otherwise the transactions can be aborted to prevent lost update from
occurring or any other resolutions methods can be employed.

Second major problem in concurrency of database is phantom phenomenon. Phantom

phenomenon occurs within a transaction when the same query produces different sets of
rows at different times. This generally happens when a second transaction inserts some new
rows in between the first and second query execution of the first transaction that matches
the where clause of the query executed by the first transaction.

For instance, there is an index of the id column of the child table and that the user wants to
read and lock all rows from the tables having an identifier value larger than 100, with the
intentions of updating some column in the selected rows later. The query scans the index
starting from the first record where id id bigger than 100. The table will contain rows having
id values of 90 and 102. If the locks set on the index records in the scanned range do not
lock out inserts a new row into the table with an id o 101. If the user executes the same
select within the same transaction, you would see a new row with an id of 101 in request set
returned by the query. I user regard a set of rows as a data item, the new phantom child
would violate the isolation principle of transaction that a transaction should be able to run
so that the data has already does not change the transaction

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index
row locking with gap locking. InnoDB performs row level locking is such a way that when it
searches or scans a table index, it shares or exclusive locks on the index records it
encounters. Thus the row level locks are actually index record locks.

The third problem that arise a problem of concurrency in database is inconsistent read.
When the transaction reads the object x twice and x has different values the problem is
called inconsistent read. It happens because between the two reads another transaction has
modified the value x. There are two kinds of consistence read;
Ghost update (a)If two transaction access concurrently to the same object and they view
their modification each other; notice that all objects are already present into the database.
Ghost update (b) If one of two transactions insert a new object into the database and
another transaction access use that data.
Common causes of Inconsistence are incorrect installation paths and operating system.
Recovery rules of inconsistence is it takes at least as long to recover aborted transactions as
it took to execute them originally. If the database has become inconsistence user can check
for this error message by typing the following command but this is only acceptable on
windows operating systems;

Findstr DM9050\
%II_SYSTEM% \ingres\files\errlog.log