Database Management Systems (DBMS)

• Efficient and secure data management is critical for organizations making informed decisions.
• A Database Management System (DBMS) provides an integrated environment for managing data.
• The chapter explores DBMS principles, design, operation, architectural components, and security measures.
• The chapter uses an Outcome-Based Education (OBE) approach for understanding and applying knowledge with ethical and sustainability considerations.

Learning Objectives

• Explain the fundamental concepts and functions of a DBMS
• Analyze database architectures and their practical applications.
• Evaluate the role of database administration, backup, and security
• Apply database concepts to support the ICT-SDG Nexus

Introduction to DBMS

• A DBMS is essential for storing, manipulating, and retrieving data in an organized and efficient manner.
• DBMS acts as an interface between users/applications and databases
• DBMS facilitates data insertion, deletion, updating, and querying.
• A DBMS is a specialized software platform designed to store, organize, and manage data efficiently.
• The primary purpose of a DBMS is systematic and efficient data handling while ensuring integrity, security, and availability.
• A DBMS abstracts database structure complexity
• The DBMS presents a simple view, hiding details of data storage and maintenance.
• Data security controls access ensuring authorized users can perform actions.
• This includes user authentication and authorization for protecting sensitive information.
• Data integrity ensures data remains accurate, consistent, and reliable over its lifecycle.
• Data integrity involves setting unique constraints, foreign keys, and check constraints
• Data Backup and Recovery provides mechanisms for backing up and restoring data.
• Data can be quickly restored without losing significant amounts of data
• Concurrency control manages simultaneous user access to the same data.
• Database transactions are performed securely and consistently without interference.
• The DBMS enables users to perform complex queries for specific information retrieval.
• Complex queries are important for data analysis, reporting, and making informed decisions.

OBE Integration

• Cognitive: Students will define a DBMS and explain its key functions and roles.
• Affective: Students will appreciate maintaining data integrity, security, and efficiency.
• Psychomotor: Students will diagram a DBMS architecture and label its core components.

Role in Addressing SDGs through Innovative Technologies

• DBMSs play a vital role in addressing Sustainable Development Goals
• They provide a robust platform for storing and managing data
• DBMSs enable organizations and researchers to collect, analyze, and interpret large datasets
• For example managing patient records to analyze health trends, in healthcare
• DBMS is a strategic asset empowering organizations to address global challenges
• DBMSs provide the technological backbone for data-driven decision-making related to SDGs

Empowering Data-Driven Solutions for SDGs

• DBMSs offer a robust platform for managing vast and diverse datasets.
• They ensure data is accurate, secure, and readily accessible
• They collect large volumes of data from various sources
• Sensors, surveys, and digital records are critical for monitoring progress toward SDG targets.
• Advanced analytical tools integrated with DBMS enable the processing of data
• Tools identify trends, patterns, and correlations
• Crucial analysis helps understanding such as climate change, health disparities, and economic challenges.
• Reliable data helps policymakers and business leaders design targeted interventions
• Innovative solutions directly contribute to the achievement of SDGs.

Examples of DBMS Applications in SDG-Driven Initiatives

• Healthcare (SDG 3): Managing patient records and clinical data to analyze disease patterns and treatment outcomes enables public health policies.
• Environmental Conservation (SDG 13/15): Storing environmental data like air quality indices, water pollution levels, and biodiversity aids conservation strategies.
• Education (SDG 4): Tracking student performance, resource allocation, and digital learning leads to better educational policies promoting equality.
• Economic Growth and Infrastructure (SDG 8/9): Supporting enterprise resource planning (ERP) systems and financial analytics optimizes operations for sustainable innovation.

Integrating DBMS into the ICT-SDG Nexus

• DBMSs enable seamless data integration from disparate sources
• The integration supports the ICT-SDG nexus by combining and analyzing data relevant to sustainability
• Scalable DBMS solutions (RDBMS, NoSQL, and NewSQL) adapt to growing data needs.
• DBMSs provide the flexibility to scale operations to track global climate patterns or manage local health records.
• The ability to manage complex datasets and support advanced analytics makes DBMSs indispensable for research.
• They provide the empirical basis for SDG progress and data-driven interventions

Types of DBMS and Their Suitability for Different Applications Including SDGs

• Choosing the right DBMS is critical for an application's success
• Organizations can support SDGs by ensuring the right data management approach
• Three major types of DBMSs is Relational, NoSQL, and NewSQL

Relational Database Management Systems (RDBMS)

• RDBMS uses a table-based structure; data is organized into rows and columns.
• Tables are interrelated via keys (primary and foreign keys) for retrieval across multiple tables.
• RDBMS are ideal for applications requiring high structure and complex capabilities with transactional integrity.
• RDBMSs are commonly used in banking systems, CRM systems, ERP systems and healthcare systems
• RDBMSs ensure data integrity, transactions to support robust healthcare management for SDG 3
• For SDG 4,they underpin educational platforms for student performance tracking and resource allocation
• Help manage accurate and timely urban data for SDG 11

NoSQL Database Management Systems

• NoSQL databases diverge from the table-based model
• They support key-value pairs, documents, wide-column stores, and graphs.
• They are ideal for handling unstructured data
• NoSQL DBMSs excel in environments needing scalability and performance with large data volumes
• Typical use cases include big data analytics, real-time web applications, and Internet of Things (IoT).
• NoSQL systems efficiently store and process data from environmental sensors for SDG 13
• They support platforms for large-scale learning environments for SDG 4
• Their scalability and flexibility support applications requiring real-time data aggregation.

NewSQL Database Management Systems

• NewSQL databases merge the features of RDBMS and NoSQL systems.
• They maintain the relational model and ACID guarantees while offering scalability.
• NewSQL approach supports complex queries and high transaction rates in distributed architectures.
• NewSQL is best suited for applications needing consistency and the ability to handle large workloads.
• Use cases include financial trading platforms, high-speed e-commerce sites, and inventory management systems.
• NewSQL can support real-time financial data projects for SDG 1

Conclusion for DBMS

• RDBMS excels in scenarios focused on finance, healthcare, and education.
• NoSQL shines in handling web applications with environmental and educational initiatives.
• NewSQL provides a balanced solution for applications that need both relational models with scalability

Components and Functions of a DBMS

• The DBMS architecture is modular to ensure efficient, reliable, and secure data management
• Key components include database engine, API, query processor, transaction manager, and storage manager.

Database Engine

• Database engine is responsible for storing data, processing queries, and ensuring data is managed securely.
• The engine provides core functionality for CRUD operations
• It enforces transaction control to uphold ACID properties ensuring database operations occur reliably.
• The database engine guarantees accurate data storage, retrieval, and manipulation.
• It supports financial systems, healthcare with electronic health records for SDG 3
• Education management systems for SDG 4.

Application Programming Interface (API)

• The API acts as a bridge between the database and external applications
• Developers use APIs to send queries, update records, and retrieve data.
• The API enables communication between software applications and the DBMS.
• Developers can integrate data management functions directly into their applications.
• Functionality drives sustainable development for platforms monitoring environmental data (SDG 13) and educational resources (SDG 4).

Query Processor

• The query processor interprets and executes queries written in SQL.
• It converts these queries into instructions that the database engine can execute.
• Optimizes query performance through techniques of query rewriting and index utilization for data retrieval
• Efficient processing enhances user experience, particularly with large datasets for smart cities (SDG 11) and financial systems (SDG 9).

Transaction Manager

• The transaction manager oversees all transactions within the DBMS.
• The transaction manager ensures the ACID principles
• The transaction manager coordinates multiple transactions to maintain data integrity.
• This is paramount for banking, healthcare, or e-commerce.
• The transaction manager provides the framework to guarantee each transaction is completed fully
• This supports initiatives like financial inclusion (SDG 1) and resilient infrastructure (SDG 9).

Storage Manager

• The storage manager is responsible for the physical storage of data, such as organization of files and data compression
• For instance, helping with clean water and sanitation for SDG 6
• Secure and efficient environmental data enables continuous monitoring and analysis for SDG 13
• Secure and efficient data storage enables accurate insights

Integration and Impact on SDGs

• Integrating the modules of a DBMS effectively can support a range of applications
• A Health RDBMS has a reliable database engine, secure APIs ensuring patient records are accurate, consistent, and confidential for SDG 3.
• A smart city system uses powerful query processor and scalable storage for SDG 11.
• Effective data acquisition, storage enables monitoring for SDG 13

Database Architecture

• Modern database system structure is explored by examining database models and architecture.
• Critical concepts are critical designing robust, scalable, and secure data management.

Database Models

• Four primary database models—hierarchical, network, relational, and object-oriented
• Each model provides a distinct data organizing with different applications.

Hierarchical Model

• Data is organized in a tree-like structure with parent-child relationships.
• Fixed relationships make data retrieval efficient when data follows a hierarchy.
• Model is simple with one-to-many relationships is simple and intuitive
• Model struggles to handle changes in data structure or to support many-to-many relationships
• Model is not well-suited for complex applications with high degree of data interconnectivity.
• Model is suitable for projects like ecological systems that use strict taxonomy.

Network Model

• Model organizes data in a graph format, allowing records to have multiple parent-child relationships.
• This structure supports many-to-many relationships.
• Model can represent complex data relationships entities with intricate data interconnections.
• Model better suits scenarios where relationships are fluid and interconnected.
• Network database can be complex and potentially increase development and maintenance costs.
• Users may find structure less intuitive compared to simpler models.
• Network model is ideal for mapping complex relationships where relationships are not hierarchical.

Relational Model

• Model organizes its data into tables where each row is a record and each column is an attribute.
• Relationships between tables are established through common keys..
• Use of tables makes it easy to understand and manipulate data using SQL.
• Its flexibility supports a broad range of applications, from data tracking, reporting and monitoring for SDGs
• Its flexibility allows it to support a broad range of applications, from simple data tracking to complex analytical operations that requires sophisticated optimization strategies.

Object-Oriented Model

• Data is stored in objects like object-oriented programming.
• Each object contains data (attributes) and behavior (methods) as a software object
• Model naturally fits applications using object-oriented programming allowing for integration.
• It handles data types and relationships that traditional databases cannot.
• Object-oriented models are more complex to design and manage.
• Model requires a deeper understanding of object-oriented programming
• it is effective for projects involving such as environmental apps that requires close integration between data and behavior

Three-Schema Architecture

• Architecture divides database architecture into schemas to promote data abstraction and independence
• Internal schema is the lowest level detailing where data is physically stored and optimized.
• Includes data structures, indexing methods, and storage pathways.
• Conceptual schema is the middle level that provides a unified view of the database with relationships.
• Defines data storing and its relationship without physical details.
• External Schema is the highest level of user interfaces.
• Defines how individual users interact with the database through views.
• Each user may see a tailored view.

Internal Schema

• The purpose is to describe data at the hardware with hidden details from users/app developers.
• Features include physical storage with physical file structures, and data encryption.
• Optimization focuses on effient data storage for fast access ensuring high performance.

Conceptual Schema

• The purpose of the schema is to represent the entire database without involving physical storing
• It defines logical data model by establishing relationships and integrity constraints
• It provides a global view of the database
• If changes happen it does not affect the conceptual layers like data modifications.

Extrenal Schema

• The purpose of this schema is to define a user specific view to meet a various groups of users and needs
• Differs based on the security of different level of users
• Offers data presentation making the data flexible and user-friendly.
• The external schema grants the user access to the app needed through both security and efficient manner.

Overall benefits

• Data Abstraction is abstracting the details.
• Data Independence provides independence
• Enhance Security provides targeting the security measures.
• Flexibilility and Scalability provides evolution to operate independently at any level.

Data Independence

• Data independence provides the capacity to change in order to evolve either at different levels without impacting
• Provides reducing maintenance costing and data robustness to operate. Both can be separated distinctly into two categories; "Logical Data Independence" and "Physical Data Independence
• "Logical Data Independence" has the simpler term of allowing the data to change without disturbing code and applications.

Data Independence (Key Points)

• Has abilty to increase or decrease to track performance with business needs.
• "Non-disruptive Changes, by adding a column withput disturbing current data.
• Example, A health system being able to track what type of health to capture.

Physical Data Independence

• Modifying that data to not disturb what its suppose to do even physically internally or at the exteral view of data.
• Has Optimization to create fast storage even with hard drive change.
• Seamless Upgrades by doing it behind the scenes for appliations to keep up for what as before.
• Signifance allows for the continuous improvememnt of a systems to not be interupted and use the new resources.

Overall Benefits

• Flexibility and Mantainabiity keeps the system adaptalbe to changes easily.
• Cost Efficeincy by not re writing the internal layers from app rewrites.
• Robuseness and Scalability keeps them adaptable.
• Enhanced Data Security

Roles and Responsibilities of a DBA Definition & Overview

• A DBA is responsible for managing and maintaining a database system to meet an organization's operational needs
• The DBA ensures optimal performance, and resilience against threats.

Key Responsibilities

• Planning the design by working with system archtiects for development needs.
• They also use Requirements and Blueprints for databases.
• Ensure proper inegration with existing IT infrastructures.
• They monitor performance to make adjustment if there's any trouble shooting problems and degradation.
• Schema by designing and modifying data to help ensure compliance and assist with troubleshooting.
• They enforce to maintain the system accuracy, maintaing the current and consistent data.
• Performance Tuning and Optimization to better the performnce
• Manage the system and allocate resources

User Managemen and access control:

• Manage and create to ensure the responsibility.
• They implement Role - Based Acsees and ensure no one is unauthorized to gain access. They also document what audits that need to go through before and create what type of security it is.

Policy and Procedure Enforcements

• Procedure to make sure team members are follow with certain compliancy or standars.
• the DBA role helps ensure security with the database, and how easy the acsessability is.

Database Maintenance

Back ups

• Purpose, a primary safeguard for any failures like hardware.
• Approach to implement the back is to make sure it can get critacal data, or make the data full to access.
• Scheduleing the proper to make sure any current changes can be implemented.
• Recovery Planning
• An objective for a recovery managment to minimize the down time in cases of what element it is.
• It will test regurarly to determine a solution is needed.
• Software Updates.

Performance

• Keeping softwares that track key Indicators with a threshold of how much can be exceeded to keep up.
• Optimize to reduce any execution time.
• Adjust the parameters to enhance over all performance.
• Ensure the performance to prevent issues to make sure resources can be allocated.

Routine Maintence Tasks

• Indexing make sure all the new information will be organized when inputing.
• Clean up by archiving and remove old files to start fresh again.
• Remove any unesscary files and logs to keep the system clean. By doing these steps adminster can keep it efficeint for the long term.

User mangamenmt

Important aspects

• Access control with restricted data to prevent unauthorized access or exposure
• Data integrity to only add to qualiffied personel to manage alterations to maintain data consistancy.

Key Security Measurers

• Transform the way data will be unreadable to protect it that can be intercepted or stollen. Acess to stream line the permissnions for management by update the responsibilities Authentication its multi faced to keep up with credential info.
• Regular Security audits help prevent to better maintain the security.

The Role of DBA

• Manage and make sure users has accounts that has the right level of permissions.
• A continuous process to keep an eye out that someone has access or a unusual activity.
• regular updates or software or data.
• Training so that users are educated about the best security.

Back up and Recovery

• A robust backup strategy can help data, especially in data.
• Three types, one to back up integrity with reliability, a data that consistenly changes, and lastly manage any data loss.

Full Backup

A full back creates a complete snapshot that for any data protection

Incremental back up.

Captures the changes that are made since the last to reduce window of the storage.

Differential Backups

The diffence in last made data sets the different with the information, it is better to use excel to the date to save and the time.

• each strategy is its own and should be used to make each for each type to protect it.

Different data management.

• the model has its own change.
• The the change rate is low and does to change to a set amount of timme/

Security

• what are the 4 types that data base are in ?

Injection

• a attack that puts some code in to fields.

Unauthorized

• lack of proper access and just hack the system.

Maleware

• damage to computer.

Insider

• a person with in the chain.
• they can corrupt data and cause problems.

How to stop

1. Incurption its data thats coded to not be able to be read,

access and a contorler, which you have a high level a security through audits and to prevent high attack.

Follow

• Get certified like GDPR
• Make sure to follow all security measures

Normalizing

To help with trade offs :

1. asses the performence
2. Understans query to see what is best.
3. Balance and weigh out the needs to which will fit,

Denormalize

is to remove some type of redudancy data to to remove somthing to help remove.