A Database Management System (DBMS) is a comprehensive collection of tools,
features, and interfaces that empower users to effectively handle, analyze, and access data.
Serving as an intermediary between users and databases, a DBMS enables organized and
efficient data interaction.
The components of a DBMS encompass various vital elements:
Interfaces: DBMS offers diverse interfaces tailored for users, database administrators, and
related systems. These interfaces facilitate user-database interaction, query submission, and
data retrieval. Database administrators leverage interfaces to administer the database,
performing tasks such as modifying database structures, managing security, and ensuring
data integrity.
Data Manipulation Language (DML): A DBMS incorporates a data manipulation language
that empowers users to retrieve, insert, update, and delete data within the database. SQL
(Structured Query Language) is a widely utilized DML in relational databases.
Schema: The schema defines the database's structure, organization, and relationships. It
includes descriptions of tables, fields, data types, constraints, and indexes. The schema serves
as a blueprint, guiding how data is structured and stored.
Physical Data Repository: The physical data repository represents the actual storage of data
on physical media such as hard drives or solid-state drives (SSDs). It manages data storage
and retrieval while ensuring data persistence and durability.
Database Administrator (DBA) Tools: DBMS provides tools and utilities to assist database
administrators in managing and administering the database. These tools facilitate tasks such
as creating and modifying database structures, monitoring database performance, managing
security and access control, and handling backup and recovery.
Data Dictionary: The data dictionary contains metadata, which encompasses information
about database objects, their definitions, relationships, and properties. Acting as a central
repository, the data dictionary stores and manages metadata, ensuring consistency and data
integrity.
Query Optimization: DBMS incorporates query optimization techniques that enhance the
performance of database queries. These techniques analyze queries and determine the most
efficient way to retrieve data, considering factors such as indexes, join operations, and data
access paths.
Concurrency Control: DBMS implements mechanisms to manage concurrent access to the
database by multiple users. It ensures isolated and consistent execution of transactions,
preventing conflicts and maintaining data integrity.
These interconnected components collaboratively establish a robust and efficient environment
for managing and accessing data. The DBMS tackles the complexities of data storage, retrieval,
security, and integrity, allowing users to focus on their specific data management needs (Tilley,
2016).
In the realm of database management, key fields assume a vital role in organizing,
accessing, and maintaining data structures. These keys serve to establish relationships between
tables and uphold data integrity. The four primary types of keys are primary keys, candidate
keys, secondary keys, and foreign keys. Each key type possesses a unique purpose in the
database design process.
Let us delve into a brief exploration of these key types and their significance in
structuring and navigating databases.
A primary key is a field or combination of fields that distinctly and minimally identifies a
specific member of an entity. It contains solely the necessary information to identify the entity.
For instance, in a customer table, the customer number can serve as a primary key.
Consider a hypothetical online bookstore. Each book listed in the bookstore possesses a unique
identifier known as the "ISBN" (International Standard Book Number). In this case, the ISBN
can be designated as the primary key in the book table since it uniquely identifies each book.
A candidate key refers to a field or combination of fields that potentially serve as a primary key.
It possesses the qualifications to be the primary key but is not currently designated as such. In the
bookstore example, the combination of "Author + Title" could serve as a candidate key.
Continuing with our online bookstore scenario, suppose each book is associated with a unique
genre code and an author's name. In this context, the combination of "Genre Code + Author
Name" could potentially be a candidate key. However, for the sake of simplicity and efficiency,
the ISBN is selected as the primary key.
A secondary key is a field or combination of fields that enables the retrieval or access of records
but does not serve as the primary key. Secondary key values do not necessarily need to be
unique. In our bookstore example, the "Publication Year" field could function as a secondary key.
It allows users to retrieve books published in a specific year or sort books based on their
publication year.
A foreign key, on the other hand, represents a field in one table that corresponds to a primary key
value in another table. It establishes a relationship between the two tables, aiding in maintaining
referential integrity and enforcing relationships. In our bookstore scenario, consider a
"Customer" table and an "Order" table. The "Customer ID" field in the "Order" table can serve as
a foreign key, matching the primary key value in the "Customer" table.
Lastly, a combination key refers to a primary key composed of two or more fields. It uniquely
identifies a record by considering multiple fields in combination. In our example, imagine a
"Book Review" table where users can submit reviews for books. The combination of "ISBN +
Customer ID" can serve as a combination key in this table, ensuring each review is uniquely
associated with a specific book and a specific customer.
These examples illustrate how primary keys, candidate keys, secondary keys, foreign keys, and
combination keys can be applied in a hypothetical scenario (Various types of keys in DBMS,
n.d.).
Entity-relationship diagrams (ERDs) serve as powerful tools employed in database design to
visualize the relationships and interactions between entities within a system. Entities can
represent various elements such as patients, doctors, medical procedures, or medical records.
ERDs offer a graphical representation of these relationships, serving as a blueprint for
constructing physical data structures.
When constructing an ERD, entities are commonly depicted as rectangles, while relationships are
represented by diamond shapes. Entity rectangles are labeled with singular nouns, and
relationship diamonds are labeled with verbs, typically arranged top to bottom and left to right.
Let's explore an example scenario in the context of an Electronic Medical Records (EMR)
system:
Scenario: Electronic Medical Records System
Entities: Patient, Doctor, Appointment, Medical Procedure
Relationships:
Patient schedules Appointment with Doctor
Doctor performs Medical Procedure on Patient
ERD Representation: The Patient entity is connected to the Appointment entity through the
"schedules" relationship. The Doctor entity is connected to the Appointment entity through the
"performs" relationship. Additionally, the Doctor entity is connected to the Medical Procedure
entity through the "performs" relationship.
By utilizing an ERD in the EMR system scenario, we can visually represent the relationships
between entities. The ERD would include rectangles representing the entities Patient, Doctor,
Appointment, and Medical Procedure, along with diamond shapes representing the relationships.
The relationship lines would be labeled with verbs such as "schedules" and "performs,"
indicating the interactions between the entities.
Employing ERDs enables analysts to gain a comprehensive understanding of how entities
interrelate within the EMR system. This facilitates efficient database design and data
management, enabling accurate and secure storage and retrieval of medical information. ERDs
contribute to the seamless functioning of the Electronic Medical Records system by providing a
clear blueprint for structuring the database (Peterson, 2023).
