Database Management Systems

• A database is an organized collection of structured information, or data, typically stored electronically in a computer system
• Databases are designed to efficiently store, manage, and retrieve large amounts of data
• Database management systems (DBMS) are software systems used to interact with databases
• A DBMS allows users to define, create, query, update, and administer databases

Key Concepts and Components

• Data Model: An abstract model that organizes elements of data and standardizes how they relate to one another
  • Examples: relational model, object-oriented model, hierarchical model, network model
• Schema: The structure of the database, including the tables, fields, and relationships between them
  • Defines how the data is organized
• Instance: A snapshot of the data in the database at a particular point in time
• Data Definition Language (DDL): Used to define the database schema
  • Includes commands to create, alter, and drop tables and other database objects
  • Example: CREATE TABLE, ALTER TABLE, DROP TABLE
• Data Manipulation Language (DML): Used to interact with the data in the database
  • Includes commands to insert, update, delete, and retrieve data
  • Example: INSERT, UPDATE, DELETE, SELECT
• Data Control Language (DCL): Used to control access to the data in the database
  • Includes commands to grant and revoke privileges
  • Example: GRANT, REVOKE
• Transaction: A logical unit of work that consists of one or more database operations
  • Transactions must be atomic, consistent, isolated, and durable (ACID properties)

Relational Model

• Organizes data into tables (relations), with rows representing records and columns representing fields
• Key Concepts:
  • Table: A collection of related data organized in rows and columns
  • Row (Tuple or Record): A single instance of data in a table
  • Column (Attribute or Field): A characteristic or property of the data
  • Primary Key: A unique identifier for each row in a table
  • Foreign Key: A field in one table that refers to the primary key of another table, establishing a relationship between the tables
• Relationships:
  • One-to-One: Each record in table A is related to only one record in table B, and vice versa
  • One-to-Many: Each record in table A can be related to multiple records in table B, but each record in table B is related to only one record in table A
  • Many-to-Many: Each record in table A can be related to multiple records in table B, and vice versa
• Normalization: The process of organizing data to reduce redundancy and improve data integrity
  • Involves dividing large tables into smaller, more manageable tables and defining relationships between them
  • Normal forms (1NF, 2NF, 3NF, BCNF) represent different levels of normalization

Database Management System (DBMS)

• A software application that allows users to define, create, query, update, and administer databases
• Provides an interface between the user and the database
• Functions:
  • Data Storage and Retrieval: Efficiently storing and retrieving data
  • Data Integrity: Ensuring the accuracy and consistency of data
  • Data Security: Protecting data from unauthorized access
  • Concurrency Control: Managing concurrent access to the database by multiple users
  • Backup and Recovery: Providing mechanisms for backing up and restoring data in case of failure
• Types of DBMS:
  • Relational DBMS (RDBMS): Based on the relational model (e.g., MySQL, PostgreSQL, Oracle, SQL Server)
  • NoSQL DBMS: Designed for handling large volumes of unstructured or semi-structured data (e.g., MongoDB, Cassandra, Redis)
  • Object-Oriented DBMS (OODBMS): Based on object-oriented programming concepts
  • In-Memory DBMS (IMDBMS): Stores data in memory for faster access

SQL (Structured Query Language)

• A standard programming language for managing and manipulating data in relational database management systems (RDBMS)
• Used for tasks such as querying, inserting, updating, and deleting data, as well as managing database schemas and access control

Basic SQL Commands

• SELECT: Retrieves data from one or more tables
  • SELECT column1, column2 FROM table_name WHERE condition;
• INSERT: Adds new data into a table
  • INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• UPDATE: Modifies existing data in a table
  • UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
• DELETE: Removes data from a table
  • DELETE FROM table_name WHERE condition;
• CREATE TABLE: Creates a new table in the database
  • CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
• ALTER TABLE: Modifies the structure of an existing table
  • ALTER TABLE table_name ADD column_name datatype;
  • ALTER TABLE table_name DROP COLUMN column_name;
• DROP TABLE: Deletes a table from the database
  • DROP TABLE table_name;

SQL Query Structure

• A basic SQL query consists of the following clauses:
  • SELECT: Specifies the columns to retrieve
  • FROM: Specifies the table(s) to retrieve data from
  • WHERE: Filters the rows based on a specified condition
  • GROUP BY: Groups rows that have the same values in one or more columns
  • HAVING: Filters the groups based on a specified condition
  • ORDER BY: Sorts the result set based on one or more columns
  • LIMIT: Restricts the number of rows returned

SQL Operators

• Comparison Operators: =, !=, >, <, >=, <=
• Logical Operators: AND, OR, NOT
• BETWEEN Operator: Specifies a range of values
  • WHERE column_name BETWEEN value1 AND value2;
• LIKE Operator: Used for pattern matching
  • % represents zero or more characters
  • _ represents a single character
  • WHERE column_name LIKE 'pattern%';
• IN Operator: Specifies a list of values
  • WHERE column_name IN (value1, value2, ...);
• IS NULL Operator: Checks if a value is null
  • WHERE column_name IS NULL;
  • WHERE column_name IS NOT NULL;

SQL Joins

• Used to combine rows from two or more tables based on a related column
• Types of Joins:
  • INNER JOIN: Returns rows when there is a match in both tables
  • LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table
  • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table
  • FULL OUTER JOIN: Returns all rows when there is a match in either the left or right table
  • CROSS JOIN: Returns the Cartesian product of the tables

SQL Aggregate Functions

• Perform calculations on a set of values and return a single value
• Common Aggregate Functions:
  • COUNT: Returns the number of rows
  • SUM: Returns the sum of values
  • AVG: Returns the average of values
  • MIN: Returns the minimum value
  • MAX: Returns the maximum value
• Example:
  • SELECT COUNT(*) FROM table_name;
  • SELECT AVG(column_name) FROM table_name WHERE condition;

SQL Subqueries

• A query nested inside another query
• Can be used in the SELECT, FROM, or WHERE clauses
• Types of Subqueries:
  • Scalar Subquery: Returns a single value
  • Multiple-Row Subquery: Returns multiple rows
  • Correlated Subquery: Refers to a column from the outer query

SQL Transactions

• A logical unit of work that consists of one or more SQL statements
• Transactions must be atomic, consistent, isolated, and durable (ACID properties)
• Commands:
  • BEGIN TRANSACTION (or START TRANSACTION): Starts a new transaction
  • COMMIT: Saves the changes made during the transaction
  • ROLLBACK: Undoes the changes made during the transaction

SQL Indexes

• A data structure that improves the speed of data retrieval operations on a database table
• Create an index on one or more columns of a table
• Types of Indexes:
  • Clustered Index: Determines the physical order of data in a table (only one per table)
  • Non-Clustered Index: Creates a separate data structure that points to the data in the table (multiple allowed)
• Command:
  • CREATE INDEX index_name ON table_name (column1, column2, ...);

Views

• A virtual table based on the result-set of an SQL statement
• A view contains rows and columns, just like a real table
• Can be created from one or many tables
• Command:
  • CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;