Database Systems Notes

Learning Objectives

  • Distinguish between data and information.
  • Understand the purpose and components of a database.
  • Recognize the importance of database design.
  • Trace the evolution from file systems to databases.
  • Identify limitations of file systems and benefits of database systems.

Data vs. Information

  • Data: Raw, unprocessed facts.
    • Example: "12345", "John", "25"
  • Information: Processed data with meaning.
    • Example: "Student ID: 12345, Name: John, Age: 25"
  • Key Point: Databases store data; we extract information.

Data

  • Data is a collection of facts
  • Data is unorganized.
  • Data does not depend on information
  • Data isn't sufficient for decision-making

Information

  • Information is organized
  • Information puts facts into context.
  • Information depends on data.
  • Information is sufficient for decision-making

What is a Database?

  • Definition: An organized collection of data, stored electronically.
  • Components:
    • Tables: Rows and columns (e.g., "Students")
    • Queries: Retrieve data (e.g., "Find students over 20")
    • Relationships: Link tables (e.g., Students to Courses)
  • Purpose: Store, retrieve, manage data efficiently.

A Real-World Database

  • Scenario: University Database
  • Tables: Students (StudentID, Name), Courses (CourseID, Title), Enrollments (StudentID, CourseID)
  • Query: "List all students in CS101"
  • Outcome: Quick, accurate retrieval.

The Role of Database Design

  • Definition: Planning how data is structured.
  • Why It Matters:
    • Efficiency: Fast retrieval
    • Scalability: Handles growth
    • Integrity: Prevents errors
  • Poor Design = Redundancy, Errors, Slow Performance.

Good vs. Poor Design

  • Good Database Design
    • CustomerID, Name, Email. Separate table with OrderID, CustomerID, Product
  • Poor Database Design
    • OrderID, Name, Email, Product all in a single table. This will lead to redundency

The Evolution of Databases

  • Before computers: Data storage relied on filing cabinets, which were slow, space-consuming, and prone to errors.
  • 1950s-1960s: Computers introduced flat-file systems storing simple text files, but they lacked relationships and caused redundancy.
  • 1970: Edgar F. Codd revolutionized data management with the relational model, leading to industry-standard databases like IBM's System R and Oracle.
  • The internet boom challenged relational databases' scalability, leading to NoSQL systems like MongoDB and Cassandra, which handled unstructured data and scaled horizontally.
  • Cloud computing transformed databases by improving accessibility, scalability, and cost-efficiency with systems like Amazon RDS, Google Spanner, and Azure SQL.
  • Looking ahead, databases are poised to become smarter and more autonomous.

From Paper to Databases

  • Manual Systems: Paper records (slow, error-prone).
  • File Systems: Flat files (e.g., spreadsheets, text files).
  • Database Evolution:
    • 1960s: Hierarchical (tree-like)
    • 1970s: Network (graph-like)
    • 1980s+: Relational (tables)
  • Relational databases became standard due to flexibility and efficiency.

How File Systems Worked

  • Separate Files: "Customers.txt", "Orders.txt"
  • No Relationships: Managed by custom programs.
  • Example: Spreadsheet with customer orders (no links).

File System Limitations

  • Data Redundancy: Repeated data across files.
  • Data Inconsistency: Updates not synced.
  • Limited Sharing: Programs can’t access the same file easily.
  • No Relationships: Data isolated.
  • Integrity Issues: No rules enforced.
  • Security: Hard to control access.

File System Problem Example

  • Scenario: "Customers.txt" and "Orders.txt" with duplicate customer data.
  • Problem: Update phone in one file, not the other → inconsistency.
  • Result: Confusion, errors, wasted space.

The Database Solution

  • Software to manage structured data (e.g., Microsoft Access).
  • Features:
    • Centralized Storage: One database.
    • Relationships: Linked tables.
    • Query Language: SQL for easy access.
    • Integrity: Enforced rules.
    • Security: User permissions.

Database System Benefits

  • No Redundancy: Single source of truth.
  • Consistency: Updates apply everywhere.
  • Scalability: Handles millions of records.
  • Multi-User Support: Many can access at once.

Key Takeaways

  • Data → Information via databases.
  • Design prevents chaos (redundancy, errors).
  • File systems evolved into databases to solve limitations.
  • Databases: Centralized, relational, efficient.