Database Management System Overview

FAT-12 File System

• File systems have drawbacks like data redundancy and inconsistency
• Data access is difficult
• Data isolation problems exist
• Integrity problems occur
• Atomicity of updates is a concern
• Security problems occur due to multiple user access

DBMS vs File System

• DBMS solves problems like data redundancy, inconsistency, data isolation, and integrity issues caused by multiple users accessing a database
• Unlike file systems, DBMSs offer better data management features

Physical and Logical Schema

• Physical schema describes how data is stored in the database
• Logical schema describes how data is organized and structured from a user's perspective
• Changes to the physical schema don't require changes to the logical schema and vice-versa.
• Data retrieval from the physical schema is easier
• Data independence, from the physical structure, is difficult to achieve, and any change requires changes at the logical and application level

Database Languages

• DDL (Data Definition Language): Used for creating, altering, and deleting database objects like tables, views, and indexes
• DML (Data Manipulation Language): Used for manipulating data within a database in read, insert, update, and delete manner
• DCL (Data Control Language): Used for controlling user access to database objects like granting and revoking permissions
• TCL (Transaction Control Language): Used for managing transactions such as committing, rolling back, and saving points

Relational Database Operations

• Select: Retrieves rows that meet a specified condition
• Project: Selects specific columns from the table
• Union: Combines two tables into a single result
• Intersection: Selects rows common to two tables
• Difference: Selects rows from one table not found in another table
• Cartesian Product: Combines all rows from two tables
• Join: Combines rows from two tables based on a specified condition

Aggregate Functions

• Aggregate functions perform calculations on multiple rows and return a single value
• Examples include COUNT, SUM, AVG, MIN, MAX

Strong vs Weak Entities

• Strong entities have a primary key that does not depend on any other entity
• Weak entities have a primary key that depends on a strong entity

Subqueries

• Single-row subqueries return a single value
• Multi-row subqueries return multiple values
• Correlated subqueries depend on the outer query
• Nested subqueries are SQL queries embedded within another query

Constraints

• Primary key: Uniquely identifies each row in a table
• Foreign key: Establishes a relationship between two tables
• Unique constraint: Ensures that all values in a column are unique
• Not null constraint: Ensures that a column cannot contain null values
• Check constraint: Ensures that values in a column satisfy a specific condition

Normalization

• Normalization is a database design technique to reduce data redundancy and improve data integrity
• Different Normal Forms (1NF, 2NF, 3NF, BCNF) exist, with higher forms being stricter, as violations are addressed at previous forms.

ACID Properties

• Atomicity: All operations within a transaction are treated as a single, indivisible unit. Either all succeed or all fail.
• Consistency: A transaction brings the database from one valid state to another.
• Isolation: Transactions are isolated from each other, preventing interference between them.
• Durability: Once a transaction is committed, its changes are permanently stored in the database, even if a system failure occurs.

Locking Mechanisms

• Shared locks allow multiple transactions to read a data item concurrently.
• Exclusive locks prevent other transactions from accessing a data item.
• Locking conversions upgrade or downgrade locks as needed to optimize concurrency and prevent deadlocks.

Transactions

• A transaction is a logical unit of work that consists of multiple database operations.
• Transactions manage simultaneous changes in the database efficiently.

Deadlocks

• A deadlock occurs when two or more transactions are blocked indefinitely, waiting for each other to release resources.
• Deadlocks can be avoided by careful transaction design and resource management.

Starvation

• Starvation occurs when a transaction is repeatedly delayed, even though it's not blocked by other transactions.
• Starvation may occur due to resource priority or other factors outside the deadlock management.

Indexing

• Indexing speeds up data retrieval by creating a lookup table for faster search
• Different indexes (Sparse and Dense) exist, optimizing access times for varied data retrieval methods.

Hashing

• Static hashing uses a fixed-size hash table, limited to a fixed size, limiting its scalability and often using chaining or overflow buckets for handling collisions.
• Dynamic hashing dynamically adjusts the hash table size as needed, optimizing storage and access to data, often using splitting (or re-organization) techniques
• Dynamic hashing is preferred for scalability and adaptability to varied data volumes; while static hashing offers simplicity and better performance for static data loads.

Database Types

• Cloud-based databases offer scalability, fault-tolerance, and geo-redundancy.
• Real-time databases support transactional operations under high concurrency.
• Cloud databases are distributed databases stored across multiple servers for increased reliability and redundancy.

RAID Levels

• RAID levels like RAID-5 combine striping and distributed parity for fault tolerance, but have performance tradeoffs depending on data access pattern.

SQL (Structured Query Language)

• SQL is a domain-specific language used to manage and manipulate data in relational database systems.
• SQL commands manage query operations, data retrieval (SELECT), data insertion (INSERT), data updates (UPDATE), and data deletion (DELETE).

Explore the fundamentals of Database Management Systems (DBMS) and their advantages over traditional file systems. Learn about the concepts of data redundancy, integrity issues, and the distinctions between physical and logical schemas. This quiz provides insights into the complexities of data management and organization.