CHAPTER ONE

DATABASE MANAGEMENT SYSTEM (DBMS)

A Database Management System (DBMS) is a collection of interrelated data and a set of

programs used to access that data. It allows users to define, create, maintain, and control access
to databases. The goal of a DBMS is to provide an environment that is convenient and efficient
for users to retrieve and store information.

A database management system (DBMS) is a software package with computer programs that
control the environment, and use of a database. It allows organizations to conveniently develop
databases for various applications. Database administrators (DBAs) and other specialists have
database is an integrated collection of data records, files, and other objects. A DBMS allows
different user applications to concurrently access the same database. DBMS may use a variety of
database models, such as the relational model or object model, to conveniently describe and
support applications.

A Database Management System (DBMS) is a software system designed to create, store,

manage, and retrieve data efficiently. It provides a systematic and organized way of handling
large volumes of information while ensuring data security, integrity, and consistency. DBMS
serves as an intermediary between users, applications, and the physical data stored in a database.

Definition of Key Terms

Data: Raw facts, figures, statistics having no meaning that can be processed into meaningful
information (e.g. 1, ABC, 19 etc.).

Record: Collection of related data items, e.g. in the above example the three data items had no
meaning. But if we organize them in the following way, then they collectively represent
meaningful information.
Roll Name Age
1 ABC 19
Table or Relation: Collection of related records.

Roll Name Age

1 ABC 19
2 DEF 22
3 XYZ 28
The columns of these relations are called Fields, Attributes, and Domains. The rows are called
Tuples or Records.

Database: A structured collection of related data stored in a computer system.

Information: Processed data that is meaningful to users.

Metadata: Data about data, describing the structure, format, and constraints of data stored in the
database.

In a database, data is organized strictly in row and column format. The rows are called Tuple or
Record. The data item within one row may belong to difference data types. On the other hand,
the columns are often called Domain or Attribute. All the data item within a single attribute are
of the same data type.

DBMS Functions
There are several functions that a DBMS performs to ensure data integrity and consistency of
data in the database. The ten functions in the DBMS are:

 Data Dictionary Management: Data Dictionary is where the DBMS stores definitions
of the data elements and their relationships (metadata). The DBMS uses this function to
look up the required data component structures and relationships. When programs access
data in a database they are basically going through the DBMS. This function removes
structural and data dependency and provides the user with data abstraction. In turn, this
makes things a lot easier on the end user. The Data Dictionary is often hidden from the
user and is used by Database Administrators and Programmers.
 Data Storage Management: This particular function is used for the storage of data and
any related data equipment forms or screen definitions, report definitions, video,
procedural code, and structures that can handle video and picture formats. Users do not
need to know data is stored or manipulated. Also involved with this structure is a term
called performance tuning that relates to a databases efficiency in relation to storage and
access speed.
 Data Transformation and Presentation: This function exists to transform any data
entered into required data structures. By using the data transformation and presentation
function the DBMS can determine the difference between logical and physical data
formats.
 Security Management: This is one of the most important functions in the DBMS.
Security management sets rules that determine specific users that are allowed to access
the database. Users are given a username and password or sometimes through biometric
authentication (such as a fingerprint or retina scan) but these types of authentication tend
to be more costly. This function also sets restraints on what specific data any user can see
or manage.
 Multiuser Access Control: Data integrity and data consistency are the basis of this
function. Multiuser access control is a very useful tool in a DBMS, it enables multiple
users to access the database simultaneously without affecting the integrity of the
database.
  Backup and Recovery Management: Backup and recovery is brought to mind
whenever there is potential outside threats to a database. For example if there is a power
outage, recovery management is how long it takes to recover the database after the
outage. Backup management refers to the data safety and integrity; for example backing
up all your mp3 files on a disk.
 Data Integrity Management: The DBMS enforces these rules to reduce things such as
data redundancy, which is when data is stored in more than one place unnecessarily, and
maximizing data consistency making sure database is returning correct/same answer each
time for same question asked.
 Database Access Languages and Application Programming Interfaces: This function
provides data access to the database through a query language. A query language is a
non-procedural language one that lets the user specify what must be done without having
to specify how.
 Database Communication Interfaces: This refers to how a DBMS can accept different
end user requests through different network environments. An example of this can be
easily related to the internet. A DBMS can provide access to the database using the
Internet through Web Browsers (Mozilla Firefox, Internet Explorer, and Netscape).
 Transaction Management: This refers to how a DBMS must supply a method that will
guarantee that all the updates in a given transaction are made or not made. All
transactions must follow what is called the ACID properties.

Characteristics of DBMS
 Self-Describing Nature of the Database: A DBMS stores both the data and the
metadata (data dictionary). This means the system can describe its own structure tables,
fields, data types, and constraints.
 Data Abstraction: DBMS hides the internal complexity of data storage and presents data
at different levels, which are the; Physical level, Logical level, and the View level. This
makes it easier for users and developers to interact with the system.
 Data Independence: The ability to change the database structure without affecting the
application programs. There are two types which is the Logical data independence and
the Physical data independence. This makes systems more flexible and adaptable.
 Data Security: DBMS provides strong security through features like: User
authentication, Access control (privileges), Role-based permissions. These prevent
unauthorized access and protect sensitive data.
 Data Integrity: DBMS enforces rules and constraints to maintain the accuracy, validity,
and consistency of data. For examples, the Primary keys, Foreign keys, NOT NULL,
UNIQUE, CHECK constraints
 Concurrency Control / Multi-User Access: DBMS allows many users to access the
same data simultaneously without causing: Conflicts, inconsistencies, lost updates. It
ensures coordinated multi-user operations.
  Backup and Recovery Management: DBMS provides tools to automatically back up
data, restore lost or corrupted data, and recover from system failures. This ensures data
durability and reliability.
 Data Sharing and Minimization of Redundancy: DBMS allows authorized users and
applications to share the same centralized data. It also reduces unnecessary data
duplication, improving: Storage efficiency, Consistency, and Data quality.

Activities of DBMS
The following are the various activities of a DBMS:

 Enter
 Manipulate
 Retrieve
 Display
 Select
 Sort
 Edit
 Index data

Major Features of a DBMS

 Data Security: The DBMS can prevent unauthorized users from viewing or updating the
database. Using passwords, users are allowed access to the entire database or a subset of
it known as a "subschema." For example, in an employee database, some users may be
able to view salaries while others may view only work history and medical data.
 Data Integrity: The DBMS can ensure that no more than one user can update the same
record at the same time. It can keep duplicate records out of the database; for example, no
two customers with the same customer number can be entered.
 Interactive Query: A DBMS provides a query language and report writer that lets users
interactively interrogate the database. These essential components give users access to all
management information as needed.
 Interactive Data Entry and Updating: A DBMS typically provides a way to
interactively enter and edit data, allowing you to manage your own files and databases.
However, interactive operation does not leave an audit trail and does not provide the
controls necessary in a large organization. These controls must be programmed into the
data entry and update programs of the application.
 Data Independence: When a DBMS is used, the details of the data structure are not
stated in each application program. The program asks the DBMS for data by field name;
for example, a coded equivalent of "give me customer name and balance due" would be
sent to the DBMS. Without a DBMS, the programmer must reserve space for the full
 structure of the record in the program. Any change in data structure requires changing all
application programs.

Microsoft Access
Access is a relational database-management system. It ensures that the user can access
information in an electronic format because Access 2000 manages data electronically, it enables
you to manipulate and extract the data with ease and flexibility. In comparison, working with
paper filing systems can be time-consuming and overwhelming. For example, suppose you are
working with a personnel database and you need to pull the names and extensions of all
employees who work in a particular department. If your paper files are organized alphabetically
by last name, you may need to check every file to determine the department in which each
employee works. This could take hours.

As you may know, a database is a collection of related information stored in tables. In a

relational database, two or more tables can be linked to appear as one table. Also, repetition of
data items is eliminated, except for those items the tables are linked by.

Microsoft Access Data Type

After you name a field, you must decide what type of data the field will hold. Before you begin
entering data, you should have a grasp of the data types that your system will use.

Features of Microsoft Access

Microsoft Access is a relational database management system (RDBMS) with a wide array of
features designed for ease of use and powerful data management. Its key features include:

Relational Database Management: Access is a true relational database, allowing you to:

 Store data in separate, specialized tables.

 Eliminate data redundancy by linking these tables together. Combine data from multiple
tables to appear as a single view for querying and reporting.

Relational Database: In relational databases such as Access, data is stored in tables made up of
one or more fields. (Access calls a column field). The data stored in each column must be of a
single data type such as Character, Number or Date. A collection of values from each column of
a table is called a record or a row.

Different tables can have the same column in common. This feature is used to explicitly specify
a relationship between two tables. Values appearing in column A in one table are shared with
another table.

Data Manipulation and Extraction: It provides powerful tools to work with your data.
  Ease and Flexibility; Quickly find, filter, and extract specific information from your
database.
 Efficiency; drastically reduces the time required for tasks that are overwhelming with
paper-based systems (e.g., finding all employees in a specific department).

Interactive Query Capability: Access includes a robust query system such as the:

 Query Language: Uses a functional, non-procedural query language (like SQL) that lets
users specify what data they want without needing to program how to get it.
 Interactive Interrogation: Users can interactively ask questions of the database to retrieve
specific management information.

Data Security: Access helps protect your data through:

 User Authorization: The ability to control access using passwords.

 Sub schemas: Granting users access to only a specific subset of the database, not the
entire thing (e.g., some users can view salaries, while others can only see work history).

Data Integrity: The system helps maintain accurate and consistent data by:

 Concurrency Control: Preventing multiple users from updating the same record
simultaneously.
 Duplicate Prevention: Enforcing rules to avoid duplicate entries (e.g., no two customers
with the same ID).

Interactive Data Entry and Updating: Access provides user-friendly interfaces for:

 Forms: Creating custom forms for easy and intuitive data entry and editing.
 Direct Management: Allowing users to manage their own files and databases
interactively.

Data Independence: This feature simplifies application development; Application programs

request data by field name (e.g., "customer name") rather than being tied to the physical structure
of the data. This means changes to the database structure don't necessarily require changes to the
programs that use it.

Data Types and Structure: Access requires and enforces data structure:

 Defined Data Types: You must define what type of data (e.g., text, number, and date)
each field will hold before entering data, which helps maintain consistency and quality.

Database Communication Interfaces

Access can operate in networked environments. It can accept user requests through different
networks, including providing access to the database via the internet through a web browser.
Types of Relationships
In Access, you define these relationships using the Relationships window to link tables based on
a common field (a primary key in one table and a foreign key in another). This allows you to
create queries, forms, and reports that combine data from multiple sources seamlessly.

Here are the three types of relationships and how they are implemented in Access:

 One-to-Many Relationship
 Many-to-Many Relationship
 One-to-One Relationship

One to Many Relationships

The one to many relationship is the most common relationship type. In a one to many
relationship, a record in Table A (the "one" side) can have many matching records in Table B
(the "many" side), but a record in Table B has only one matching record in Table A. For
example, a mother can have many children but each child can have only one biological mother.
Another example, one teacher teaches many students but each student has one main teacher for
that subject.

Many to Many Relationships

In many to many relationships, this relationship requires a junction table (or bridge table) to be
created. Many records in Table A can relate to many records in Table B. You create a third table
(the junction table) whose primary key is a combination of at least two fields the foreign keys
from both Table A and Table B. This creates two separate one-to-many relationships: one from
Table A to the junction table, and one from Table B to the junction table.

A record in one table (table 1) can have many matching records in a second table (table 2), and a
record in table 2 can have many matching record in table 1. For example, a student can enroll in
many courses, and a course can have many students.

One to One Relationship

This is the least common relationship and is used for specific organizational or security reasons.
Each record in Table A can have only one matching record in Table B, and vice versa. For
example, each employee has exactly one corresponding record in the confidential data table (e.g.,
for salary, review notes).