Flat File Database System

User text file, Post text file, Interactions text file

• Limitations of Flat File Database:
  • Data Redundancy: b/c there is no mechanism for enforcing relationships between entities
  • Slow Operations: b/c it lacks indexing or advanced optimization techniques
  • Slow Queries: b/c complex queries are not natively supported
  • Concurrent Updates: b/c they are not designed for multi-user environments
  • Handling Disk Failure: b/c they don’t have built-in fault tolerance or recovery mechanisms
  • Memory Management: b/c large flat files can require significant memory resources to read and process
  • Usability: b/c they don’t support structured querying languages

Relational Database

• Benefits of Relational Database

  • No Data Redundancy

  • Fast Operations

    • Efficient data deletion
    • User tuple removal
    • Fast deletion

  • Fast Queries

    • Index database
    • Apps in labeled folders
    • Location-based index
    • ex) SELECT * FROM Users WHERE LOCATION = “SEOUL”;

  • Concurrent Updates

  • Handling Failures (reversion)

  • Memory Management

    

  • Usability

    • SQL = Declarative
    • Python, C++ = Imperative

Relational Operators

• SELECT (Projection Operator)

  • select specific columns from a table

  SELECT Location FROM Users;
  

• WHERE (Selection Operator)

  • filters rows based on specified conditions

  SELECT * FROM Interactions WHERE ReactionType = ‘Like’;
  

• GROUP BY (Grouping Operator)

  • groups rows of same values
  • used with aggregate functions like SUM

  SELECT PostID, 
  	COUNT(*) AS ReactionCount
  FROM Interactions
  GROUP BY PostID;
  

• SUM (Aggregation Operator)

  • adds group values

  • defined by GROUP BY clause

    SELECT UserID,
    	COUNT(PostID) AS TotalPosts
    FROM Posts
    GROUP BY UserID;
    

• JOIN (Join Operator)

  • links rows from two different tables
  • combine information from both

  SELECT Posts.PostID,
  	COUNT(Interactions.ReactionType) AS TotalInteractions
  FROM Posts
  JOIN Interactions ON Posts.PostID = Interactions.PostID
  GROUP BY Posts.PostID