Introduction to Database Systems and Architecture

TL;DR

Database systems help you store, organize, and retrieve information efficiently and reliably. They prevent data loss and ensure multiple users can access data without problems. Understanding their architecture helps you choose and design the right system for your needs.

1. The Mental Model

Think of a database system as a super-organized digital filing cabinet for all your important data. It doesn't just store files; it manages them, keeps them safe, and lets you find exactly what you need quickly, even when many people are looking at different files at the same time.

2. The Core Material

A database system (DBS) is an organized collection of structured information, or data, typically stored electronically in a computer system. A database management system (DBMS) is the software that interacts with end users, applications, and the database itself to capture and analyze the data. Together, the database and the DBMS form a database system.

Why Use a Database System?

Photo by panumas nikhomkhai on Pexels

Imagine a world without databases. You'd be saving everything in spreadsheets or text files. What happens when:
* Data Redundancy: You have the same customer's address in three different files. Which one is correct?
* Data Inconsistency: You update it in one place but not another. Now your records contradict each other.
* Data Security: How do you control who sees sensitive information?
* Concurrency Issues: Two people try to update the same record at the same time. Whose change wins?
* Data Integrity: How do you ensure only valid data (e.g., age can't be negative) is entered?

Database systems solve these problems by providing:
* Data Sharing: Multiple users can access the same data.
* Data Consistency: Rules ensure data stays correct and uniform.
* Data Integrity: Constraints prevent invalid data entry.
* Security: Access controls limit who can see or change data.
* Concurrency Control: Manages multiple users accessing data simultaneously.
* Data Recovery: Mechanisms to restore data after failures.

Database Architecture

Photo by Brett Sayles on Pexels

Database architecture describes the overall design of a database system, including how data is stored, processed, and accessed. The most common way to describe database architecture is through a three-level schema architecture, often called the ANSI/SPARC architecture. This architecture separates how users view data from how it's actually stored.

The three levels are:

1. External Level (View Level): This is what individual users or applications see. It's a customized, simplified view of the database relevant only to their specific needs. You might see only your courses, not everyone else's.
2. Conceptual Level (Logical Level): This defines the overall structure of the entire database. It describes what data is stored, its relationships, and its constraints, independently of how it's physically stored. It's like the master blueprint of the whole system.
3. Internal Level (Physical Level): This describes how the data is actually stored on physical storage devices. It covers details like file organization, indexing, and storage allocation. This level is hidden from most users and even application programmers.

This separation provides data independence:
* Logical Data Independence: You can change the conceptual schema (e.g., add a new attribute) without affecting external schemas (user views).
* Physical Data Independence: You can change the internal schema (e.g., switch storage devices) without affecting the conceptual or external schemas.

Here's how these levels relate:

graph TD
    UserA["User View A (External Schema A)"] -->|Queries & Updates| ExternalLevel
    UserB["User View B (External Schema B)"] -->|Queries & Updates| ExternalLevel
    ExternalLevel["1. External Level (User Views)"] --> ConceptualLevel
    ConceptualLevel["2. Conceptual Level (Overall Logical Structure)"] --> InternalLevel
    InternalLevel["3. Internal Level (Physical Storage Details)"] --> PhysicalStorage["Physical Data Storage"]

Types of Database Systems

Photo by panumas nikhomkhai on Pexels

While the architecture generally applies, different models exist for organizing data:

• Relational Databases (SQL Databases): Data is stored in tables with rows and columns. Relationships are defined using primary and foreign keys. Examples: MySQL, PostgreSQL, Oracle, SQL Server. These are the most common type.
• NoSQL Databases: A broad category designed for specific use cases, often handling large amounts of unstructured or semi-structured data. Examples: MongoDB (document-based), Cassandra (column-family), Redis (key-value), Neo4j (graph).
• Object-Oriented Databases: Store data as objects, similar to objects in object-oriented programming. Less common today.
• Hierarchical Databases: Data organized in a tree-like structure.
• Network Databases: More complex than hierarchical, allowing records to have multiple parent/child relationships.

You'll mostly encounter relational and NoSQL databases in modern development.

3. Worked Example

Let's say you're building a simple library system.

1. External Level:
* A librarian's view: Sees all books, all borrowers, overdue status, and can add/remove books.
* A borrower's view: Sees only books they've borrowed, their due dates, and available books to borrow. They can't see other borrowers' info or change book details.

2. Conceptual Level:
This defines the entire database structure:
* A Books table with columns like BookID, Title, Author, ISBN, PublicationYear, AvailableCopies.
* A Borrowers table with columns like BorrowerID, Name, Address, PhoneNumber.
* A Loans table linking BorrowerID and BookID with LoanDate, DueDate, ReturnDate.
It describes that BorrowerID in Loans refers to BorrowerID in Borrowers, and BookID in Loans refers to BookID in Books.

3. Internal Level:
This is behind the scenes. For example, the Books table might be stored in a specific file format on disk. The BookID column might have an index to speed up searches. It details the actual physical storage blocks used for each table and index.

When a borrower searches for a book, their external view interacts with the conceptual schema which then translates to the internal level to fetch the data from physical storage. The internal storage details can change (e.g., migrating from one disk type to another) without the borrower ever knowing or having their "view" change.

4. Key Takeaways

• Database systems manage data efficiently, reliably, and securely for various applications.
• The three-level architecture (External, Conceptual, Internal) separates user views from actual data storage.
• Data independence allows changes at one level without affecting others.
• Relational databases (SQL) are excellent for structured, related data.
• NoSQL databases offer flexibility for unstructured data and massive scale.
• Database systems solve critical problems like data inconsistency and concurrency issues.
• Choosing the right database type depends on your specific data structure and application needs.

Common Mistakes to Avoid:
- Using spreadsheets for data that requires multi-user access or complex relationships.
- Not defining clear relationships between data entities in your design.
- Ignoring security or backup strategies for your database.
- Choosing a database technology purely based on hype without understanding your project's specific requirements.

5. Now Try It

Think about a simple online store. Spend 15 minutes sketching out what you imagine for its database's:
1. External Level: What would a customer see? What would an admin user see?
2. Conceptual Level: What main "tables" or data categories would you need (e.g., Products, Customers, Orders)? What key pieces of information (columns) would each contain, and how might they relate to each other?
3. Internal Level: Without going into too much detail, what kind of performance considerations or storage optimizations might be important here?

Success looks like clearly identifying different user perspectives, outlining the core data entities and their connections, and briefly mentioning physical-level concerns like speed or storage.