Ministry of Higher Education Republic of Cameroon

Yaounde International Business School Peace – Work - Fatherland

YIBS – Yaounde
Department of Computer Engineering

TOPIC: APPLIED COMPUTER I

Chapter I : DATABASE CONCEPTS
Year : HND 2 (Two) By: MVOGO BILEGUE Edouard

The term database refers to a collection of related data from which the users can efficiently
retrieve the desired information. In addition to the storage and retrieval of data, certain other
operations can also be performed on a database. These operations include adding, updating
and deleting data. All these operations on a database are performed using a database
management system (DBMS). Essentially, a DBMS is a computerized record-keeping system.
In this topic we will be introduced to the basic terminology used in a database management
system.

I. DATA, INFORMATION AND KNOWLEDGE

I.1 Definitions
Data can be anything such as a number, a person's name, images, sounds and so on. Hence,
data can be defined as a set of isolated and unrelated raw facts (represented by values), which
have little or no meaning because they lack a context for evaluation (e.g. ‘Monica’, ‘36’,
‘chief’ …). When the data are processed and converted into a meaningful and useful form, it
is known as information. Hence, information can be defined as a set of organized and
validated collection of data. For example, 'Monica is 35 years old and she is a chief'.

Strictly speaking, data refer to the values physically recorded in the database, whereas
information refers to the conclusion or meaning drawn out of it. With respect to database,
these terms are synonymous.

Other than data and information, one more term, knowledge, is frequently used with database
technology. Knowledge is the act of understanding the context in which the information is
used. It can be based on learning through information, experience and/or intuition.
 Figure 1. Data, Information and Knowledge

Data Model: A data model is a representation of a real world situation about which data is to
be collected and stored in a database. A data model depicts the dataflow and logical
interrelationships among different data elements.

I.2 Prerequisites of Information

Information is the processed data, on which decisions are taken and the subsequent actions are
performed thereafter. For the decisions to be meaningful and useful, the information must
possess the following qualities:

• Accurate: To be useful, information must be accurate at all levels because all further
developments are based on the available information.

• Timely: Information is appreciated only if it is available on time.

• Complete: Complete information tends to be comprehensive in covering the issue or

topic of interest.

• Precise: Information should be to the point, containing all the essential elements of
the relevant subject areas.

• Relevant: Information is relevant if it can be applied to a specific situation, problem

or issue of interest.

I.3 Need for Information

Information is an important part of our day-to-day life. Almost all activities are affected by
the quantity as well as the quality of information. Some of the common usages of information
are discussed as follows.

I.3.1 Information and Decision-making:

Decision-making is the process of identifying, selecting and implementing the best possible
alternative. The right information, in the right form and at the right time is essential to make
correct decisions.

I.3.2 Information and Communication:

Information is vital for communication and is a critical resource for performing work in
organizations. Business managers spend most of their day in communicating with other
managers, subordinates, customers, vendors and so on. A manager must keep track of the
information flow from the sources inside and outside the organization.

I.3.3 Information and Knowledge:

The future is shaped by our actions today, and these actions are based upon our knowledge.
Therefore, for achieving higher levels of success, one must be well informed and should have
clarity of information.

I.3.4 Information and Productivity:

Information helps in making sense of our environment, which assists in achieving the
performance objectives. In fact, productivity is directly related to the availability and value of
the information and its application in the related context.

II. DATABASE: DEFINITION

A database is a collection of non-redundant data which can be shared by different application

systems. Although databases are generally computerized, instances of non-computerized
databases from everyday life can be cited in abundance. A dictionary, a phone book, a
collection of recipes and a TV guide are all common examples of non-computerized
databases. The examples of computerized databases include customer files, employee rosters,
books catalogue, equipment inventories and sales transactions.

II.1 Stages in Creating a Database

The process of creating a database can be broadly divided into two main stages:

• Data analysis involves using a formalised methodology to create a database design.

Two widely used methods are Entity Relationship Modelling (ER) and
Normalisation.

• Physical implementation of that design in a database system.

II.2 Database terminologies

Within the database, the data are organized into storage containers, called tables. Tables are
made up of columns and rows. In a table, columns represent individual fields and rows
represent records of data. The following are the basic database terms.

II.2.1 Field

A field represents one related part of a table and is the smallest logical structure of storage in
a database. It holds one piece of information about an item or a subject. For example, in a
database maintaining information about employee, the fields can be Code, Deptt, Name,
Address, City and Phone (see Figure 2).

II.2.2 Record

A record is a collection of multiple related fields that can be treated as a unit. For example,
fields Code, Deptt, Name, Address, City and Phone for a particular employee form a record.
Figure 2 contains nine records (0101–0109) and each record has six fields.
 II.2.3 Table

A table is a named collection of logically related multiple records. For example, a collection
of all the employee records of a company form employee table. Note that every record in a
table has the same set of fields. Depending on the database software, a table can also be
referred to as a file. The collection of multiple related files (tables) forms the database.

Figure 2. Fields and Records in a Table

II.2.4 Data Type

A data type determines the type of data that can be stored in a column. Although many data
types are available, the four most commonly used data types are Character, Numeric,
Boolean and DateTime. The values for this data type vary widely depending on the database
management software being used.

Data type Character Numeric Boolean DateTime

Field Name Name Salary Is_Married Joining_Date
Data Placide 450000 False (No) 02/10/98
II.1.5 Data Dictionary
Apart from the data, the database also stores metadata, which describes the tables, columns,
indexes, constraints and other items that make up the database. In simple words, metadata is
the data about data. This metadata is stored in an area called the data dictionary. Hence, a data
dictionary defines the basic organization of a database.
Most database systems keep the data dictionary hidden from users to prevent them from
accidentally destroying its contents. Different users use the dictionary in different ways.
• The database administrator (DBA) needs a dictionary to ensure consistency among
the data items, to educate users about the database content and to help ensure that
different department defines the same data in the same way.
• The programmers may use it to ensure that they have the name and coding of the
data items or segments correct in their programs.
• Managers may use it as a guide to decide what data could be made available to them.
III. DATA CONCEPTS

III.1 Physical Data Concepts

Physical concepts of data refer to the manner in which the data are physically stored on the
hardware (like hard disk). Fundamentally, it involves the physical organization of the records
of a file for the convenience of storage and retrieval of data. Usually, the files are organized in
three fashions: sequential, direct and indexed sequential.
III.1.1 Sequential Files
In sequential files, the data are stored and/or retrieved in a logical order, that is, in a sequence.
The records are stored one after the other in an ascending or descending order, based on the
key field (which is unique for each record) of the records. Generally, these files are stored on
sequential storage devices such as magnetic tapes and punched cards. In such files, to retrieve
a record, all the records must be traversed sequentially before reaching to the desired record.
An analogy to sequential files may be taken as an audio cassette.
III.1.2 Direct Files
Direct files facilitate accessing any record directly or randomly without having to traverse the
sequence of records. These files are also known as random or relative files. Even though only
one item can be accessed at a time, that item may be stored anywhere in the file. For example,
in case of CDs
III.1.3 Indexed Sequential Files
Essentially, indexed sequential technique is a hybrid of sequential and direct file organization.
The indexed file organization uses a separate index file, which contains the key values and the
location of the corresponding record. The records are organized in an orderly sequence and
the index table is used to access the records without searching through the entire file.

Figure 3. Indexed Sequential Access

III.2 Logical Data Concepts
Once the requirements of the user have been specified, the next step is to construct an
abstract or conceptual model of a database based on the requirements of the user. The
conceptual model represents various pieces of data and their relationships at a very high level
of abstraction. It mainly focuses on what data are required and how it should be organized
rather than what operations are to be performed on the data. The conceptual model can be
represented using Entity-Relationship model (E-R model). The E-R model views the real
world as a set of basic objects (known as entities), their characteristics (known as attributes)
and associations among these objects (known as relationships).
III.2.1 Entity
An entity is any object in the system that we want to model and store information about.
Entities are usually recognizable concepts, either concrete or abstract, such as person, places,
things, or events which have relevance to the database. Some specific examples of entities are
Employee, Student, Lecturer. An entity is analogous to a table in the relational model.
An entity occurrence is an instance of an entity. For example, in the student entity, the
information about each individual student details is an entity occurrence, An entity occurrence
can also be referred to as a record. By convention, entities are represented by rectangles:
III.2.2 Attributes
An attribute is an item of information which is stored about an entity. For example, the entity
'lecturer' could have attributes such as staff id, surname, forename, date of birth, telephone
number, etc. By convention, an attribute is represented by a diamond linked to the
corresponding entity:
III.2.3 Relationship
Relationship is an association, dependency or link between two or more entities and is
represented by a diamond symbol. A relationship describes how two or more entities are
related to each other. For example, the relationship Buys (shown in Figure 4) associates the
CUSTOMER entity with ITEMS entity.

Figure 4. Entities, Attributes and Relationship

III.3 Types of Relationship
Even though a relationship may involve more than two entities, the most commonly
encountered relationships are binary, involving exactly two entities. Generally, such binary
relationships are of three types and called cardinality: one-to-one, one-to-many and many-
to-many.
• One-to-one Relationship (1:1)
One-to-one is where one occurrence of an entity relates to only one occurrence in another
entity, eg if a man only marries one woman and a woman only marries one man, it is a one-to-
one (1:1) relationship.

Fig 5 : One-to-One
• One-to-many Relationship (1:M)

A one-to-many relationship is where one occurrence in an entity relates to many

occurrences in another entity. For instance one manager manages many employees, but
each -employee only has one manager, so it is a one-to many (1:m) relationship.
 Fig 6: One-to-Many
The crowbar represents the Many occurrence.
• Many-to-many Relationship (M:M)

In many-to-many relationship, one record in a table can be related to one or more records in a
second table, and one or more records in the second table can be related to one or more
records in the first table. For example, One teacher teaches many students and a student is
taught by many teachers.

Fig 7 Many-to-Many relationship

III.4 Keys
A key is a data item that allows us to uniquely identify individual occurrences or an entity
type. You can sort and quickly retrieve information from a database by choosing one or more
fields (ie attributes) to act as keys. For instance, in a student's table you could use a
combination of the last name and first name fields (or perhaps last name, first name and birth
dates to ensure you identify each student uniquely) as a key field. There exist many types of
keys:

• Primary Key:

A field or a set of fields that uniquely identify each record in a table is known as a primary
key. This implies that no two records in the relation can have same value for the primary key.
For example, your student number is a primary key as this uniquely identifies you within the
college student records system. An employee number uniquely identifies a member of staff
within a company. An IP address uniquely addresses a PC on the internet.

A primary key is mandatory. That is, each entity occurrence must have a value for its
primary key.

• Candidate Key:

In a table, there can be more than one field that can uniquely identify each record. All such
fields are known as candidate keys. One of these candidate keys is chosen as a primary key;
the other keys that are not chosen as primary key are known as alternate keys or secondary
keys.
 • Foreign Key:

A field of a table that references the primary key of another table is referred to as foreign key.
Figure 13.3 illustrates how a foreign key constraint is related to a primary key constraint.
Here, the field Item_Code in the PURCHASE table references the field Item_Code in the
ITEM relation. Thus, the attribute Item_Code in the PURCHASE relation is the foreign key.

NOTE: The key composed of more than one field is known as composite key. Sometimes, it is
also known as concatenated key or structured key.

Figure 8. Foreign Key

• Simple Key
Any of the keys described before (ie: primary, secondary or foreign) may have one or more
attributes. A simple key consists of a single attribute to uniquely identify an entity
occurrence, for example, a student number, which uniquely identifies a particular student. No
two students would have the same student number.
• Compound Key
A compound key consists of more than one attribute to uniquely identify an entity
occurrence. Each attribute, which makes up the key, is also a simple key in its own right.
For example, we have an entity named enrolment, which holds the courses on which a student
is enrolled. In this scenario a student is allowed to enrol on more than one course. This has a
compound key of both student number and course number, which is required to uniquely
identify a student on a particular course.

Fig 9: a compound key

Student number and course number combined is a compound primary key for the enrolment
entity.
• Composite Key

A composite key consists of more than one attribute to uniquely identify an entity occurrence.
This differs from a compound key in that one or more of the attributes, which make up the
key, are not simple keys in their own right.
For example, you have a database holding your CD collection. One of the entities is called
tracks, which holds details of the tracks on a CD. This has a composite key of CD name, track
number.

Fig 10: a composite key

CD name in the track entity is a simple key, linking to the CD entity, but track number is not a
simple key in its own right.

Application exercise
For each of the following entities, list possible primary keys. Then, suggest secondary keys, if
any: Student, Course, Unit, Result, Classroom, Lecturer, Department, Attendance

IV. DATA INTEGRITY

Integrity ensures that the data in a database is both accurate and complete, in other words,
that the data makes sense. There are at least five different types of integrity that need to be
considered: Domain constraints, Entity integrity, Column constraints, User-defined
integrity constraints, Referential integrity. The data analysis stage will identify the
requirements of these.

• Domain Constraints: A domain is defined as the set of all unique values permitted
for an attribute. For example, a domain of Date is the set of all possible valid dates, a
domain of Integer is all possible whole numbers, and a domain of day-of-week is
Monday, Tuesday ... Sunday.

• Entity Integrity: It implies that no component of a primary key is allowed to have a

NULL value.

• Column Constraints: During the data analysis phase, business rules will identify any
column constraints. For example, a salary cannot be negative; an employee number
must be in the range 1000 - 2000, etc.

• User-Defined Integrity Constraints: Business rules may dictate that when a specific
action occurs, further actions should be triggered. For example, deletion of a record
automatically writes that record to an audit table.

• Referential Integrity: It implies that if a foreign key is defined in one table, any of its
value must exist as a primary key in another table.
V. DATABASE MANAGEMENT SYSTEM

V.1 Definition
To carry out operations like insertion, deletion and retrieval, the database needs to be
managed by a software package. This software is called a database management system
(DBMS). Hence, DBMS can be defined as a collection of interrelated data and a set of
programs to access that data.

Database system: Database system is a general term that refers to the combination of a
database, a database management system and a data model. This system is responsible for the
following data manipulation acts; data controlling, data retrieving, data maintenance and data
definition.

Figure 11: a database system

V.2 Advantages and Limitations of a DBMS

A good database management system (DBMS) should provide the following advantages over
a conventional system:

Advantages

• Reduction in Data Redundancy: Data redundancy refers to duplication of data. In

nondatabase systems, each application has its own separate files. This can often lead to
redundancy in stored data, which results in wastage of space.

• Reduction in data Inconsistency: Data inconsistency is when different versions of

the same data appear in different places in a database. This causes unreliable
information, because it is difficult to determine which version of the information is
correct.

• Sharing of Data: Sharing of data allows the existing applications to use the data in the
database simultaneously.
 • Improvement in Data Security: DBMS can ensure that the only means of accessing
the database is through the authorized channel. Hence, data security checks can be
carried out whenever access is attempted to sensitive data. To ensure security, DBMS
provides security tools such as user codes and passwords.

• Maintenance of Data Integrity: Data integrity means the consistency and accuracy of
the data in the database.

• Better Interaction with Users: Centralizing the data in a database also means that
users can obtain new and combined information that would have been impossible to
obtain otherwise. In addition, use of a DBMS allows the users, who do not know
programming, to interact with the data more easily.

However, the following can be viewed as some of the limitations of a database:

Disadvantages

• Database systems are complex, difficult, and time-consuming to design

• Substantial hardware and software start-up costs

• Damage to database affects virtually all applications programs

• Extensive conversion costs in moving form a file-based system to a database system

• Initial training required for all programmers and users

V.3 examples of Database Management System (DBMS)

Some of the database management systems are:

• Microsoft Access : This is the database management system developed by Microsoft.

• MySQL : MySQL is open source database management system, one of the most
popular dbms on the web. It is reliable, fast and also flexible.

• Oracle : Developed by Oracle corporation. It is object relational database

management system. The original version of Oracle software was developed by
Software Development Laboratories (SDL). Oracle is regarded to be one of the safe
DBMS.

• Microsoft SQL Server : Microsoft developed this relational database server. The
primary function of this software is to store and retrieve the data as requested by other
applications, whether those applications are on the same computer or running on other
computers across the network (including internet).

• Filemaker : Filemaker began as a MS-DOS based computer program named nutshell.

It is a cross platform RDBMS developed by Filemaker Inc.