Database concepts

Relational database
management systems
Please do not copy without permission. © ALX 2024.
Database concepts

What is a database management system?

|
A database management system (DBMS) is system software that allows users to create, store,
retrieve, and run queries on data stored in a database. It acts as an interface between an end user
or an application and a database.

Computers

Human users can

.Database. DBMS interact via interface

Applications

Interaction is through
built-in workloads for
specific functions
Popular database management systems include MySQL,
SQLite, SQL Server, PostgreSQL, Oracle database, etc.
2
Database concepts

Client-server architecture for a DBMS

|
The client-server architecture is the framework in which the DBMS lives. The clients are
represented by computers and applications, while the server is where the database and
the DBMS live.

Server Computers
Client

.Database. DBMS

The server provides a function

Internet
or service (i.e., data
Applications
manipulation) to one or more
clients and can live anywhere
in the world as long as there is
access through the Internet.

3
Database concepts

Client-server architecture pros and cons

Advantages Disadvantages

● Centralised data management ● As the server acts as a central point, an

outage or failure can disrupt the whole
● Simultaneous access to a database by
system
multiple clients and users
● Maintenance and upgrades of the server
● Security and access control
infrastructure require resources
● Allows for scaling as the need for a user base
● Network dependency can disrupt effective
grows
communication

● Increased network latency can affect

real-time or performance-sensitive
4
applications
Database concepts

The purpose of database management systems

01. Data security 04. Data integrity

Incorporate security measures to protect data from Implement various constraints, ensuring that data
unauthorized access, manipulation, or breaches. remains accurate, consistent, and reliable.

02. Data manipulation and storage 05. Data sharing and collaboration

Allow users to perform operations such as inserting, Enable multiple users to access and work with the same
deleting, and modifying records. They manage the data concurrently, ensuring data consistency.
allocation of storage space, efficient storage
mechanisms, and data file management.

03. Data backup and recovery 06. Data scalability

Allow for regular backups of the database, which can be Handle increasing volumes of data and growing user
used to restore data in case of system failures, data demands. Scalability options include partitioning or
corruption, or other unexpected events. clustering to distribute and manage data across
multiple servers.
5
Database concepts

Types of database management systems

| Database management systems can be categorized based on various criteria, such as

the number of users or the data model. The most common types include:

01. ● Data are organized in a tree-like structure, where each parent node or record is
linked to one or more child nodes, forming a parent-child relationship.
Hierarchical DBMS ● For example: IBM's Information Management System (IMS)

● Data are organized in a graph-like structure, where records are connected

02. by links that represent their relationship. Child nodes or records can have
multiple parent nodes or records, unlike the hierarchical model.
Network DBMS
● For example: IDMS (Integrated Database Management System)

Data are organized into tables consisting of rows and columns, where each
03. ●
table represents an entity or a relationship between entities.
Relational DBMS ● For example: Oracle, MySQL, Microsoft SQL Server, PostgreSQL, SQLite

6
Database concepts

Relational database management systems

| Relational database management systems (RDBMSs) are the most widely used DBMSs
because of their efficiency in data standardization, querying, and relationships.

Standardization Data querying Data relationships

RDBMSs adhere to Through SQL, users can query They handle relationships
industry-standard query databases to extract specific between tables, hence allowing
language. SQL (Structured Query information and perform data for the modeling of complex
Language)... SQL provides a manipulations based on various associations, and they support
standardized and efficient way to conditions, filters, and sorting efficient querying. and retrieval of
interact with databases. requirements. related data.

7
Database concepts

RDBMSs support CRUD operations

CRUD is an acronym that represents the four

| basic operations (create, read, update,

delete) that can be performed on data within
an RDBMS.

Why CRUD operations?

Their support enables the standardization of the

creation, retrieval, modification, and deletion of
data, providing a robust foundation for managing
and manipulating relational data.

CRUD operations are usually supported by query

languages like SQL.
Database concepts

RDBMSs support CRUD operations

● Enables the creation of new records by inserting data into database tables.
Create ● Usually achieved through query statements, e.g. INSERT INTO for SQL.

● Enables the retrieval of data from database tables using SQL statements such as SELECT.
Read ● The RDBMS executes the query and retrieves the matching data.

● Allows modification of existing data in a database through SQL UPDATE statements.

Update ● Verifies the constraints and applies the changes to the matching records, updating
the data in the database accordingly.

● Enables permanent removal of records from the database tables using SQL DELETE
statements.
Delete ● Verifies constraints and deletes the matching records from the table, permanently
removing them from the database.
Database concepts

ACID properties in RDBMSs

| Relational database management systems should have ACID properties which ensure data
validity and compliance. ACID is an acronym for atomicity, consistency, isolation, and durability.

A transaction is a single unit of work

involving one or more operations, performed
For example, in the event of a power outage, the
on a database with the aim of reading or
absence of ACID properties could mean that some
modifying the data.
of the modifications made to the database would
not be saved causing inconvenience.

Transactions should follow ACID properties

that guarantee the utmost data reliability
and integrity.

10
Database concepts

ACID properties of queries

● Ensures that all operations in a transaction or query (to read, write, update,

A Atomicity
or delete data) are treated as a single unit.
● Meaning either the entire query is executed successfully if run, or none of it
is executed.

● Ensures that transactions or queries only make changes to tables in predefined,

C Consistency
predictable ways.
● This guarantees that errors or corruption in our data do not result in unintended
consequences that compromise the integrity of the database table.

● Ensures that transactions or queries by multiple users on the same

I Isolation
database do not interfere with or affect one another.
● Each query request can occur as though it were occurring one by one, even
though they are simultaneously occurring.

D
● Guarantees that modifications made to our data through successfully
Durability executed transactions will be permanently saved.
● This will remain the case in the event of a system failure.