Data Structures

Data Structures can be defined as containers that hold and organize data in specific formats. Data structures also determine how data are stored, accessed or modified.

Data structures can be categorized into two main types;

Primitive structures
Non-primitive structures

Primitive Structures

Primitive are the most fundamental data structures that have predetermined size and can hold single values at a particular location. Most common examples include; INT, CHAR, BOOLEAN, and FLOAT.

Non-primitive Structures

Non-primitive structures are used to store and manipulate a collection of data elements. They can further be divided into Linear and Non-linear structures.

Linear Structures arrange elements sequentially linking each element to it’s previous and next one. Examples include; arrays, stacks, queues, and strings.

Non-linear Structures on the other hand organize data in a non-sequential manner. Examples include; graphs and trees.

What is Data Persistence?

Data persistence refer to whether or not data remains stored in the memory of an electronic device even after it has been powered off.

Memory can be divided into (2) main types, namely;

Volatile and
Non-volatile memory

Volatile memory serves as the primary storage for data structures while a program is running. Data in volatile memory is often stored temporarily until the program execution is done.

Examples include; cache memory and RAM.

Non-volatile memory allows for persistent storage of data even after the device is switched, ensuring data persistence during program execution.Examples include; ROM, HDD, etc.

What is a Database

Database can be defined as a system used to store, retrieve, and manage large amounts of information. There are (2) main types of Databases namely;

Relational Databases:

Relational Databases store data in tables with rows and columns, and the tables are linked through relationships. SQL(Structured Query Language) is the language used to interact with databases.

Examples of relational databases include; MySQL, PostgreSQL, and Oracle database.
Non-relational Databases:

Non-relational databases do not use tables.

Data here are stored in various formats like key, value pairs, documents, or graphs.

Examples of Non-relational databases include; MongoDB, Cassandra, and Redis.

Database user roles

The key players in Database include;

Database Administrator(DBA): This is the personnel responsible for managing and maintaining a database, ensuring smooth operations, performance, security and data integrity.
Software Developer: These are the masterminds behind applications that interact with Databases.
End users: They use Database to perform a range of tasks including managing the data, generating reports and making predictions to drive decision making.

Relational Database Management Systems

Database management system (DBMS) is a system software allowing users create, store, retrieve, and run queries on data stored in a database.

DBMS acts as the interface between end users or application and a Database.

Client-Serve architecture for a DBMS

Client-server architecture for DBMS is a framework in which DBMS lives.

Clients are represented by computers and applications while server is where the database and DBMS lives.

Advantages of Client-Server Architecture

Centralized data management.
Security and access control.
Allows for scaling as the need for a user base grows.
Simultaneous access to a database by multiple clients and users.

Disadvantages of Client-Server Architecture

Maintenance and upgrades of the server infrastructure requires resources.
Network dependency can disrupt effective communication.
As the server act as a central point, an outage or failure can disrupt the whole system.
Increased network latency can affect real-time or performance-sensitive applications.

Purpose of Database Management System

Data Security: DBMS incorporates security features to protect data from unauthorized access, manipulation or breaches.
Data Manipulation: They also allow users perform operations such as inserting, deleting, and modifying records.
Data backup and recovery: DBMS allow regular backup and recovery used to restore data in case of system failure.
Data Integrity: DBMS implement various constraints ensuring that data remains accurate, consistent, and reliable.
Data Sharing and Collaboration: These systems also enable multiple users access and work with the same data concurrently.
Data Scalability: DBMS are able to handle increasing volumes of data and growing user demands.

Types of DBMS

Database Management Systems can be categorized as follows:

Hierarchical DBMS: These type of DBMS organize data in tree like structure where each parent node/record is linked to one or more child node, forming a parent-child relationship. e.g. IBM’s Information Management System.
Network DBMS: In Network DBMS, data is organized in graph like structure where records are connected by links that represent their relationships. A child node can have multiple parent nodes unlike hierarchical model. e.g., Integrated DBMS.
Relational DBMS: Relational DBMS organize data in tables made up of rows and columns, where each table represent an entity or relationship between entities. e.g., Oracle, MySQL, PostgreSQL, Microsoft SQL Server, SQLite, etc.

Relational DBMS support CRUD operations; CRUD is an acronym for;

Create: Creation of new records by inserting data into database tables.

Read: Enables retrieval of data from database tables using SQL statements.

Update: Allows modification of existing data in database through SQL statements.

Delete: Enables permanent removal of records from database table using SQL statement.

Basics of SQL

SQL (Structured Query Language) is a standardized language for interacting with relational databases.

It is a language that is supported by many relational databases such as; MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite.

SQL can further be sub divide into sub categories such as:

Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Data Control Language (DCL)

Data Definition Language (DDL)

Data Definition Language can be defined as an SQL sub language that is responsible for defining how data are structured in a database.

It is made up of commands used to build, amend, or remove SQL tables.

Some common commands used in data definition include: CREATE, ALTER, TRUNCATE and DROP.

CREATE: This is a DDL statement used to create new SQL databases or tables.

When creating tables, CREATE specifies the structure of the table, defining the columns and their data types.

Creating a database requires appropriate permissions or privileges depending on the database management system being used.

Syntax
```
  -- Creating a database and using it
  CREATE DATABASE database_name;

  USE database_name;
```
```
  -- Creating a table
  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      column3 datatype[constraints],
      .
      .
      .
  );
```
Constraints are applied on columns to enforce integrity and define rules for the values stored in those columns.

Some of the commonly used constrains include;
1. NOT NULL which ensures that a column cannot contain NULL values.
2. UNIQUE which ensures that the values in a column or combination of columns are unique across the table.
3. PRIMARY KEY is used to uniquely identify each row in a column.
4. FOREIGN KEY is used to establish a relationship between two tables based on a column.
ALTER: This is a statement that is used to modify the structure of an existing database object. It can be used to add, modify, or delete a column in a table.

Note that ALTER is commonly used to modify columns of a database object/table.

Syntax

```sql -- To add a column ALTER TABLE table_name ADD column_name datatype;

-- To rename a column ALTER TABLE table_name RENAME COLUMN old_name to new_name;

-- To change datatype of a column ALTER TABLE table_name MODIFY COLUMN column_name datatype;

-- To delete a column ALTER TABLE table_name COLUMN column_name;


* **TRUNCATE:** This is a statement that is used to remove all data from a table, resetting it to an empty state.

    TRUNCATE operation is more convenient and faster compared to deleting individual rows.

    ```sql
    -- To delete data within a table
    TRUNCATE TABLE table_name;

DROP: This is a statement used to remove/delete entire database objects such as tables or schemas, from a database.

```sql -- To drop a table DROP TABLE table_name;

-- To drop a database DROP DATABASE database_name;


    2. ## Data Manipulation Language (DML)


Data Manipulation Language is an SQL sub language that is responsible for manipulating data in a database.

It is the language that is most commonly used to add, edit, or delete data from a database.

The main Data Manipulation Language statements include: INSERT, UPDATE, and DELETE.

* **INSERT:** This statement is used to add new data to a database table by inserting new records.

    The INSERT statement has two key parts, the **INSERT INTO** clause which specifies where data is being inserted into, and the **VALUES** clause which specifies the values being inserted.

    *Syntax*

    ```sql
    -- Inserting new records into a table
    INSERT INTO
        Database_name.table-name
        (column1, column2, column3, ...)
    VALUES
        (value1, value2),
        (value1, value2),
        (. . .);

UPDATE: Is a statement that allows us to modify existing data within a database table. It only modifies the data within a table without altering the table structure.

UPDATE contains two key parts: the UPDATE and SET clauses are the key parts, and sometimes you can optionally include the WHERE clause to specify a condition for update.
```
  -- Updating a table's columns
  UPDATE database_name.table_name
  SET
      column1  =value1,
      column2 = value2, ...
  WHERE 
      condition;
```
DELETE: This is a statement used to remove specific records from a database table.

It provides the means to selectively delete data based on specified conditions allowing for the precise removal of unwanted or outdated records.

It also contains two key parts, the DELETE FROM which specifies where we’re deleting from, and a WHERE clause which specifies a condition.

    -- Deleting 
    DELETE FROM
         database_name.table_name
    WHERE 
        condition;

Querying Data