1.

Introduction to Database Systems

• A database is a systematic collection of structured data to make it easily
accessible.
• The DBMS acts as an intermediary between the user and the raw data files.
• Data redundancy is minimized by ensuring a single piece of data is stored in
one place.
• Data integrity is maintained through built-in rules that prevent "garbage"
entries.
• Metadata, or "data about data," defines the structure and constraints of the
system.
• Concurrent access allows multiple users to read or write data at the same
time.
• Security features ensure that only authorized users can view or modify
specific data.
• Backup and recovery sub-systems protect against data loss during hardware
failures.
• Abstraction hides the complex physical storage details from the end-user.
• Popular systems include MySQL for web apps and PostgreSQL for advanced
features.

2. Database System Architecture

• The architecture defines how the DBMS is partitioned into different functional
layers.
• 1-Tier Architecture: The user, the app, and the database sit on the same
machine.
• 2-Tier Architecture: A client-server model where the application talks directly
to the DB.
• 3-Tier Architecture: The most common web model, adding an Application
Server layer.
• The 3-tier model enhances security by preventing the user from touching the
DB directly.
• Internal Level (Physical) describes how data is actually stored on the hard
disks.
• Conceptual Level (Logical) describes what data is stored and their
relationships.
• External Level (View) provides customized windows into the data for different
users.
• Data Independence allows changing the physical storage without breaking the
application.
• Scaling a 3-tier system is easier because you can upgrade servers
independently.
3. Data Models
• Data models provide the "blueprint" for how data elements relate to one
another.
• The Hierarchical Model organizes data in a tree-like structure (parent-child).
• The Network Model allows children to have multiple parents, forming a
graph.
• The Relational Model (the industry standard) uses tables to represent data.
• Entity-Relationship (ER) Models use diagrams to map real-world objects
and links.
• Object-Oriented models store data as objects, similar to programming
languages.
• Semi-structured models (like XML or JSON) allow for more flexible data
entries.
• Data models define the "Schema," which is the logical structure of the
database.
• Constraints within these models ensure that data stays within logical bounds.
• Choosing the right model depends on the complexity and volume of the data
involved.

4. Relational Database Concepts

• Data is organized into Relations, which the average user simply calls Tables.
• Tuples are the rows of the table, representing a single unique record.
• Attributes are the columns, representing the properties of that record.
• The Domain defines the set of legal values for a specific attribute (e.g.,
integers).
• A Primary Key is a unique identifier that ensures no two rows are identical.
• Foreign Keys link tables together, creating a web of related information.
• Referential Integrity ensures that you can't have a link to a record that doesn't
exist.
• The "Schema" defines the table names, column names, and their data types.
• The "Instance" refers to the actual data stored in the database at a specific
moment.
• Relational databases rely on set theory to perform complex data operations.

5. Relational Algebra & Calculus

• Relational Algebra is a procedural language; it tells the system "how" to get
data.
• Selection (σ): Filters rows based on a specific condition (like a filter).
• Projection (π): Chooses specific columns while discarding the rest.
• Union (∪): Combines results from two tables with the same structure.
• Join (⋈): Combines rows from two tables based on a related column.
• Relational Calculus is non-procedural; it describes "what" data is needed.
• Tuple Relational Calculus (TRC) focuses on finding tuples that satisfy a
predicate.
 • Domain Relational Calculus (DRC) focuses on filtering data at the attribute
level.
• These theories provide the mathematical proof that SQL queries will work
correctly.
• A DBMS "Query Optimizer" often converts SQL into Relational Algebra to
execute it.

6. Structured Query Language (SQL)

• SQL is the declarative language used by almost all relational database
systems.
• DDL (Data Definition Language): Commands like CREATE and ALTER define
structure.
• DML (Data Manipulation Language): INSERT, UPDATE, and DELETE manage
records.
• DQL (Data Query Language): The SELECT command is used to retrieve data.
• DCL (Data Control Language): GRANT and REVOKE manage user
permissions.
• SQL is designed to be readable, using English-like keywords for its
operations.
• The WHERE clause is essential for filtering data to avoid processing entire
tables.
• SQL is standardized by ANSI/ISO, but many brands (Oracle, SQL Server) add
"flavors."
• It allows for "Set-based" processing, meaning you act on groups of data at
once.
• SQL remains the most in-demand skill for data analysts and backend
developers.

7. Advanced SQL
• Joins (Inner, Left, Right, Full) allow you to pull data from multiple tables at
once.
• Subqueries are queries nested inside another query for complex filtering.
• Views are "virtual tables" that save a complex query for easy reuse later.
• Stored Procedures are sets of SQL code that can be saved and run
repeatedly.
• Triggers are scripts that run automatically when a specific event (like a
delete) occurs.
• Aggregate Functions like SUM, AVG, and COUNT perform math on data
groups.
• The GROUP BY clause organizes data into buckets for summary reporting.
• Window Functions allow for advanced calculations across sets of rows (like
rankings).
• Common Table Expressions (CTEs) make complex queries more readable
using WITH.
• Advanced SQL is critical for "Big Data" analysis and complex business logic.

8. Database Design & Normalization

 • Normalization is the process of organizing data to reduce "anomalies."
• Update Anomaly: When you have to change data in five places instead of
one.
• Deletion Anomaly: When deleting one fact accidentally deletes another
unrelated fact.
• 1NF (First Normal Form): Ensures all columns contain atomic (indivisible)
values.
• 2NF (Second Normal Form): Removes "partial dependencies" on the
primary key.
• 3NF (Third Normal Form): Removes "transitive dependencies" (non-key
attributes).
• BCNF is a stricter version of 3NF used for even more complex scenarios.
• "Denormalization" is sometimes done intentionally to speed up read-heavy
systems.
• ER Diagrams are usually drawn before normalization to visualize the data
flow.
• Good design saves storage space and ensures the database stays fast as it
grows.

9. Transaction Management
• A transaction is a logical unit of work, like transferring money between
accounts.
• Atomicity: The "all or nothing" rule; if one part fails, the whole thing rolls
back.
• Consistency: The database moves from one valid state to another valid
state.
• Isolation: Transactions happening at the same time don't "see" each other's
work.
• Durability: Once a transaction is committed, it stays saved even if the power
cuts out.
• The COMMIT command saves all changes made during the current
transaction.
• The ROLLBACK command undoes changes if an error occurs during
processing.
• Transaction logs keep a record of every change to allow for "undoing"
mistakes.
• Savepoints allow you to roll back to a specific part of a long transaction.
• This concept is the "gold standard" for financial and high-stakes data systems.

10. Concurrency Control

• Concurrency control manages how multiple users interact with the same data.
• Lost Update Problem: When two users update the same row and one
overwrites the other.
• Dirty Read: When a user reads data that is currently being changed but not
yet saved.
 • Locking: The DBMS "locks" a record so other users can't change it until it's
finished.
• Shared Locks allow multiple people to read but none to write.
• Exclusive Locks prevent anyone else from even reading the data while it's
being updated.
• Deadlock: When two transactions are waiting on each other and nobody can
move.
• Timestamping: Giving every transaction a unique time to decide who goes
first.
• Optimistic Concurrency assumes conflicts are rare and only checks at the
very end.
• Pessimistic Concurrency assumes conflicts will happen and locks data early.

11. Recovery Management

• Recovery management ensures that a database can return to a consistent
state after a crash.
• Causes of failure include system crashes, power failures, or disk errors.
• Log-Based Recovery uses a "Write-Ahead Log" to track changes before they
happen.
• Checkpointing creates a "safe point" in the log to speed up the recovery
process.
• Shadow Paging maintains two copies of the database page to swap if one
fails.
• Full Backups copy the entire database; Incremental Backups only copy recent
changes.
• Remote Backup involves storing data in a different physical location for safety.
• Immediate Update recovery applies changes to the DB as soon as they are
logged.
• Deferred Update recovery waits until the transaction is committed to update
the DB.
• Recovery is the "insurance policy" of a database system.

12. Indexing & File Organization

• Indexing is a data structure technique used to speed up the retrieval of
records.
• A Primary Index is automatically created on the primary key of a table.
• Clustered Indexes physically sort the data on the disk based on a key.
• Non-Clustered Indexes create a separate lookup table (like a book's index).
• B-Trees are the most common structure used to implement fast indexing.
• Hashing uses a math function to jump directly to a specific data location.
• Heap File Organization stores records in no particular order (very slow for
searching).
• Sequential File Organization stores records in a sorted order for easy range
scans.
• Too many indexes can actually slow down INSERT and UPDATE operations.
• Proper indexing is the #1 way to fix a slow database application.
13. Query Processing & Optimization
• Query processing is the set of activities involved in extracting data from a DB.
• Parsing: The DBMS checks the SQL syntax and verifies table/column names.
• Evaluation Plan: The system creates several "maps" of how it could find the
data.
• Optimization: The "Optimizer" chooses the plan with the lowest estimated
cost.
• Cost is measured in terms of CPU usage, memory, and disk I/O
(input/output).
• The system uses statistics about table sizes to make these smart decisions.
• Scan vs. Seek: The optimizer decides if it should read the whole table or use
an index.
• Sorting and Join order are the two most expensive parts of query execution.
• Execution is the final step where the DBMS actually runs the chosen plan.
• Good developers use EXPLAIN commands to see how the optimizer is thinking.
14. Database Security
• Security protects the database against both intentional and accidental threats.
• Authentication verifies the identity of the user (passwords, biometrics).
• Authorization (Privileges) determines what a logged-in user is allowed to do.
• Role-Based Access Control (RBAC) groups permissions by job title (e.g.,
"Manager").
• Encryption scrambles data so it's unreadable if the physical disks are stolen.
• SQL Injection is a common attack where hackers try to run malicious code
via forms.
• Auditing keeps a trail of who accessed what data and when they did it.
• Views can be used for security by hiding sensitive columns from certain
users.
• Network security (firewalls) prevents outside entities from reaching the DB
server.
• Compliance laws (like GDPR) mandate strict rules for handling personal data.

15. Distributed Databases

• A distributed database is a single logical DB spread over multiple physical
sites.
• Transparency makes the user feel like they are using one single local
database.
• Fragmentation breaks a table into pieces stored in different locations.
• Replication keeps copies of the same data in multiple cities to survive
disasters.
• Horizontal Fragmentation splits a table by rows (e.g., US customers vs. EU
customers).
• Vertical Fragmentation splits a table by columns (e.g., Names in one site,
Credit cards in another).
 • Synchronous replication updates all sites at once; Asynchronous updates
them later.
• The "CAP Theorem" states you can't have Consistency, Availability, and
Partition tolerance all at once.
• Distributed systems are harder to manage but much harder to "crash" entirely.
• They are essential for global apps like Facebook, Google, and Amazon.

16. NoSQL & Modern Databases

• NoSQL stands for "Not Only SQL" and handles non-tabular data.
• Document Stores (MongoDB): Store data in JSON-like formats, great for
web apps.
• Key-Value Stores (Redis): Extremely fast systems used for temporary
"caching."
• Graph Databases (Neo4j): Focus on the links between data (perfect for
social networks).
• Wide-Column Stores (Cassandra): Handle massive amounts of data across
many servers.
• NoSQL databases are "Schema-less," meaning you can change data
structure on the fly.
• They prioritize "Horizontal Scaling" (adding more cheap servers) over "Vertical
Scaling."
• Base (Basically Available, Soft state, Eventual consistency) is their design
philosophy.
• NoSQL is ideal for "Big Data" where the structure is messy or rapidly
changing.
• Most modern companies use a mix of both SQL and NoSQL for different
tasks.

17. Data Warehousing & Data Mining

• A Data Warehouse is a central repository used for reporting and data
analysis.
• It collects data from many different sources (Sales, HR, Web) into one place.
• ETL (Extract, Transform, Load) is the process of moving data into the
warehouse.
• Warehouses are "Read-Optimized," meaning they are built for fast, complex
searching.
• OLTP (Online Transaction Processing) is for daily tasks; OLAP is for analysis.
• Data Mining uses algorithms to find hidden patterns in these massive
datasets.
• It helps companies predict future trends, like which customers are likely to
quit.
• Data Marts are smaller versions of a warehouse focused on one department
(like Marketing).
• "Big Data" tools like Hadoop often work alongside warehouses to handle raw
info.
• This is the foundation for Business Intelligence (BI) and Artificial Intelligence
(AI).