MOD 2: Understanding and Using the Data

DBMS (Database Management System)

: Software that serves as an interface between the database and its end-users or programs, allowing users to retrieve, update, and manage how information is organized and optimized.

Data Independence

: The property of a DBMS that allows you to change the database schema at one level without forcing a change at other levels.

Logical Data Independence and Physical Data Independence

Data Independence is split into

Logical Data Independence

changing the conceptual schema without altering user views

Physical Data Independence

changing physical storage structures without altering conceptual schemas

Three-Level Schema Architecture

: An ANSI-SPARC standard framework that separates a database into three levels to achieve data independence:

• External Schema

• Conceptual Schema

• Internal Schema

Three-Level Schema Architecture

External Schema (User View)

: What individual end-users see.

Conceptual Schema (Community View)

: The global logical structure of the entire database (tables, relationships, constraints).

Internal Schema (Physical View)

: How the data is physically stored on disks and hardware.

Data Modeling

: The process of creating a visual layout of an enterprise's data requirements, mapping out real-world entities, their attributes, and how they relate to one another.

E-R Diagram (Entity-Relationship Diagram)

: A graphical representation of a database's logical structure using specific shapes: Rectangles for Entities (objects/concepts), Ellipses for Attributes (properties), and Diamonds for Relationships.

Normalization

: A multi-step optimization process used to minimize data redundancy and eliminate update, insertion, and deletion anomalies by systematically decomposing large, messy tables into smaller, well-structured ones.

Normal Forms (1NF, 2NF, 3NF, BCNF)

The progressive stages of normalization:

1NF (First Normal Form)

: All attributes must contain atomic (indivisible) values. No repeating groups.

2NF (Second Normal Form)

: Must be in 1NF, and all non-key attributes must be fully functionally dependent on the primary key (eliminates partial dependencies).

3NF (Third Normal Form)

: Must be in 2NF, and no non-key attribute can be dependent on another non-key attribute (eliminates transitive dependencies).

Integrity Constraints

: Rules enforced by a DBMS to prevent accidental damage to the database and ensure data accuracy. Crucial types include:

Entity Integrity

: The primary key cannot be null and must be completely unique.

Referential Integrity

: A foreign key value must match an existing primary key value in the related table, or be completely null.

ACID Properties

: The four foundational criteria that guarantee database transactions are processed reliably:

• Atomicity

• Consistency

• Isolation

• Durability

ACID Properties:

Atomicity

: "All or nothing." If any part of a transaction fails, the entire transaction is rolled back.

Consistency

: A transaction must transform the database from one valid, constraint-compliant state to another.

Isolation

: Concurrent execution of transactions must leave the database in the same state as if they were run sequentially.

Durability

: Once a transaction commits, its changes survive even in the event of a total system crash.

Concurrency Control

: A database mechanism used to coordinate simultaneous transaction executions without letting them overwrite or corrupt one another (common techniques include Two-Phase Locking (2PL)).

Index

: A performance-tuning database structure (often shaped as a B-Tree or Hash index) that speeds up data retrieval operations on a table at the cost of additional write time and storage space.

Query Optimizer

: An internal component of a DBMS that analyzes an incoming SQL query and evaluates multiple execution paths to choose the most efficient physical plan (minimizing disk I/O and processing cost).

Data Warehouse (DW)

: A centralized, non-volatile, time-variant repository of integrated data collected from multiple operational systems, specifically optimized for query analysis and business intelligence reporting.

ETL (Extract, Transform, Load)

: The three-stage data integration process used to pull raw data from various sources, clean and format it to meet business rules, and load it securely into a target Data Warehouse.

NoSQL

: A broad class of database management systems designed to handle unstructured or semi-structured data at a massive scale, trading traditional ACID guarantees for horizontal scalability. Common types include Key-Value stores, Document stores, Column-family stores, and Graph databases.

Big Data (The 3 Vs)

: A term describing datasets so massive and complex that traditional database software cannot process them.

• Volume

• Velocity

• Variety

Big Data (The 3 Vs):

Volume

sheer scale of data

Velocity

the rapid speed at which data is created

Variety

the diverse structural formats of the incoming data.

Relational Algebra Operations

: The underlying mathematical operations behind SQL queries.

• Selection (σ)

• Projection (π)

• Join (⋈)

Selection (σ)

: Extracts specific rows based on a condition.

Projection (π)

: Extracts specific columns.

Join (⋈)

: Combines related tables based on common attributes.

DDL vs. DML vs. DCL:

The categories of SQL statements:

DDL (Data Definition Language)

: Changes database structure (CREATE, ALTER, DROP).

DML (Data Manipulation Language)

: Handles data instances (SELECT, INSERT, UPDATE, DELETE).

DCL (Data Control Language)

: Manages privileges (GRANT, REVOKE).

Stored Procedure

: A collection of pre-compiled SQL statements stored directly inside the database engine that can be executed as a reusable function to reduce network traffic.

Trigger

: A specialized stored procedure that automatically fires or executes in response to a specific event (such as INSERT, UPDATE, or DELETE) on a specific table.

Modification Anomalies

: Redundancy problems that occur in un-normalized databases:

• Insertion Anomaly

• Deletion Anomaly

• Update Anomaly

Insertion Anomaly

: Being unable to add a new record because some unrelated, required data is missing.

Deletion Anomaly

: Accidental loss of critical data when a completely different record is deleted.

Update Anomaly

: Having to update the exact same value in dozens of rows, risking inconsistent data if one row is missed.

Functional Dependency

: A constraint between two attributes where the value of attribute A uniquely and completely determines the value of attribute B (written as A→B).

Database Log File (WAL - Write-Ahead Logging)

: A recovery mechanism where any modification to a database is written to a dedicated, sequential log file on disk before it is applied to the actual database pages.

Checkpoint

: A recovery point created periodically by the DBMS. During system crashes, the system only needs to undo or redo log actions that occurred after the most recent checkpoint, drastically reducing system recovery time.

Distributed Database Transparency

: The degree to which a distributed system hides its physical distribution from users. Important types include Location Transparency (users don't need to know where data is physically hosted) and Replication Transparency (users don't need to know if data has copies on separate servers).

CAP Theorem

: A distributed systems rule stating that a distributed data store can simultaneously provide at most two out of three guarantees:

• Consistency

• Availability

• Partition Tolerance

CAP Theorem meaning?

Consistency

all nodes see the same data

Availability

every request receives a response

Partition Tolerance

(the system operates despite network dropouts

Data Lake

: A massive storage repository that holds vast amounts of raw, unstructured, semi-structured, and structured data in its native, unformatted state until it is needed for analysis.

Data Mart

: A highly focused, smaller segment of a Data Warehouse dedicated to an isolated business unit or department (e.g., a specific Data Mart just for the Finance or Marketing team).