database

In-Depth Database Revision Guide

1. Database Basics

  • Database: An organized collection of structured information, typically stored electronically in a computer system.

  • DBMS: Database Management System, software that handles the storage, retrieval, and updating of data in a database, e.g., MySQL, Oracle, SQL Server.

  • Schemas: The structure that defines the organization of data within the database, including tables, views, indexes, and more.

2. Types of Databases

  • Relational Databases: Use tables to store data, based on relational model principles, e.g., SQL databases like MySQL, PostgreSQL.

  • NoSQL Databases: Non-relational databases designed for large-scale data storage, flexibility, and scalability, e.g., MongoDB, Cassandra.

3. SQL (Structured Query Language)

  • Basic Commands:

    • SELECT: Retrieve data from one or more tables.

    • INSERT: Add new data into a table.

    • UPDATE: Modify existing data in a table.

    • DELETE: Remove data from a table.

  • Joins: Combining data from multiple tables based on a related column, e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

  • Indexes: Data structures that improve the speed of data retrieval operations on a database table.

4. Normalization

  • Definition: The process of organizing data to minimize redundancy and improve data integrity.

  • Normal Forms:

    • 1NF: First Normal Form, ensures that the table columns contain only atomic (indivisible) values.

    • 2NF: Second Normal Form, requires 1NF and ensures that all non-key attributes are fully functionally dependent on the primary key.

    • 3NF: Third Normal Form, requires 2NF and ensures that no transitive dependencies exist (non-key attributes are not dependent on other non-key attributes).

5. Transactions

  • Definition: A unit of work that is performed against a database.

  • ACID Properties:

    • Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction is aborted.

    • Consistency: Ensures that a transaction transforms the database from one valid state to another.

    • Isolation: Ensures that transactions do not interfere with each other.

    • Durability: Ensures that the results of a committed transaction are permanent, even in the case of a system failure.

6. Locking and Concurrency Control

  • Locking: Mechanism to control concurrent access to data, ensuring data integrity and consistency.

  • Types of Locks:

    • Exclusive Lock: Ensures that no other transaction can access the locked resource.

    • Shared Lock: Allows multiple transactions to read the resource but not modify it.

7. Database Design and ER Diagrams

  • ER Diagrams (Entity-Relationship Diagrams): Visual representation of the database schema, showing entities (tables), attributes (columns), and relationships between entities.

  • Primary Key: A unique identifier for each record in a table.

  • Foreign Key: A field (or collection of fields) in one table that uniquely identifies a row of another table, ensuring referential integrity.

8. Database Administrator (DBA) Role

  • DBA Responsibilities: Managing database systems, ensuring database performance, performing backups and recovery, security management, and troubleshooting.

9. Constraints

  • Referential Integrity: Ensures that foreign keys correctly and consistently reference primary keys in related tables.

  • Entity Integrity: Ensures that each table has a primary key and that the primary key is unique and not null.

  • Other Constraints: UNIQUE, NOT NULL, CHECK, DEFAULT.

10. Transaction Management

  • Definition: Ensuring that database transactions are processed reliably and adhere to ACID properties.

  • Important Commands:

    • BEGIN TRANSACTION: Marks the start of a transaction.

    • COMMIT: Saves all changes made during the transaction.

    • ROLLBACK: Undoes all changes made during the transaction.

11. Anomalies in Database Normalization

  • Update Anomaly: Occurs when data is updated in one place but not in another.

  • Insertion Anomaly: Occurs when certain attributes cannot be inserted into the database without the presence of other attributes.

  • Deletion Anomaly: Occurs when the deletion of certain data results in the unintended loss of additional data.

12. CREATE TABLE Statements

  • Syntax and Usage:

    sql

    CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES other_table (column_name)
);

  • Composite Primary Keys: Combining multiple columns to serve as a unique identifier for records.

  • NOT NULL Constraint: Ensures that a column cannot have a NULL value.

Feel free to let me know if you need further details on any specific topic or if you have any other questions. Good luck with your exam preparation! 🌟