Distributed Databases and the Internet - Part I

Platform for Web-Based Database Applications

​

The Dynamic Web: Powered by Data

• Consider common web interactions:
• Your Amazon shopping cart persists between visits.
• Your Facebook feed displays personalized, up-to-date content.
• Online banking shows your current balance and transaction history.
• These dynamic experiences are enabled by underlying database systems interacting seamlessly with web applications.

Recap: Distributed Data Processing

• In previous lecture, we explored concepts of distributed data processing and various architectures for distributed databases.
• Key takeaways included reasons for distribution (scalability, availability, performance) and associated challenges.
• Today, we examine a primary application domain for these distributed systems: powering large-scale web applications and services on the internet.

Today's Lecture Objectives

• Understand the necessity of integrating databases with web environments.
• Identify the components and structure of a typical platform architecture for web-database applications.
• Recognize key database design considerations specific to web application requirements.
• Outline the fundamental requirements for integrating distributed databases and web environments.

Lecture Roadmap

• The Need for Web-Database Integration: Why static HTML is insufficient.
• Platform Architecture: Examining the multi-tier structure (Client, Web, App, DB).
• Database Design Considerations: Challenges specific to web applications (Scalability, Performance, Concurrency, Security).
• Basic Integration Requirements: Connecting the application and database tiers.

Beyond Static Web Pages

• Static Websites:
• Content is pre-written and fixed within HTML files.
• Served directly by the web server as-is.
• All users see the same content.
• Updates require manual editing of HTML files.
• Example: A simple informational brochure site.
• Dynamic Web Applications:
• Content is generated "on-the-fly" based on various factors.
• Leverages server-side logic and database interaction.
• Content can be personalized, interactive, and reflect real-time data.
• Example: Online stores, social networks, booking systems.

Why Integrate Databases with the Web?

• Web applications require mechanisms to manage and utilize data effectively. Database integration provides:
• Data Persistence: Long-term storage of application data.
• Dynamic Content Generation: Tailoring content based on stored data and user input.
• Support for User Interaction: Enabling complex user actions and data manipulation.
• Scalability and Availability: Handling large volumes of data and users reliably (often leveraging distributed databases).

Core Functions Enabled by Web-Database Integration

• Persistence:
• Storing user profiles, credentials, and preferences.
• Maintaining product catalogs, inventory levels, and order histories.
• Archiving posts, comments, messages, and logs.
• Dynamic Content:
• Displaying personalized recommendations or dashboards.
• Generating search results based on user queries.
• Showing real-time information (stock prices, notifications).
• User Interaction:
• Processing user logins and registrations.
• Handling form submissions (contact forms, orders, profile updates).
• Allowing users to create, modify, or delete content (posts, uploads).

Examples of Data-Driven Web Applications

• E-commerce Site (e.g., Amazon):
• Data: Products, categories, prices, customer accounts, orders, reviews, inventory.
• Retrieval: Product searches, displaying order history, personalized recommendations.
• Social Media Platform (e.g., Facebook, Twitter):
• Data: User profiles, posts, comments, likes, connections/followers, messages.
• Retrieval: Generating news feeds, displaying profiles, searching for users/content.

Examples of Data-Driven Web Applications

• Online Banking Portal:
• Data: Customer accounts, balances, transaction histories, payee details, security logs.
• Retrieval: Displaying account summaries, transaction details, facilitating transfers.
• University Portal:
• Data: Student records, course catalogs, schedules, grades, registration information, faculty details.
• Retrieval: Displaying course options, student timetables, academic records.

Platform Architecture for Web-Database Applications

• Web applications rely on a structured architecture to handle requests, process logic, and manage data.
• A common model is the Multi-Tier Architecture, separating concerns into distinct logical layers.
• This separation facilitates development, maintenance, scalability, and security.

Typical Multi-Tier Architecture

• Logically divides the application system into specialized tiers:
• Client Tier: User Interface (Browser)
• Web Server Tier: Request Handling & Static Content
• Application Server Tier: Business Logic & Database Interaction
• Database Tier: Data Storage & Management

Tier 1: Client Tier

• Role: Presents the user interface and captures user input.
• Components: Typically a web browser on the user's device (desktop, mobile).
• Technologies: HTML (structure), CSS (styling), JavaScript (client-side interactivity, sending requests).
• Function: Renders content received from the server, sends user requests (e.g., form submissions, link clicks) to the web server via HTTP(S).

Tier 2: Web Server Tier

• Role: Acts as the front door for incoming requests from clients.
• Components: Web server software (e.g., Apache HTTP Server, Nginx, Microsoft IIS).
• Function:
• Receives HTTP(S) requests from clients.
• Serves static files directly (HTML, CSS, images, client-side JS).
• Forwards requests for dynamic content to the Application Server Tier.
• Can handle load balancing, SSL termination, caching.

Tier 3: Application Server Tier

• Role: Executes the application's core business logic and orchestrates data access.
• Components: Application server software and the application code itself.
• Technologies: Server-side programming languages and frameworks (e.g., Python/Django/Flask, Java/Spring/Jakarta EE, Node.js/Express, PHP/Laravel, Ruby/Rails, C#/.NET).
• Function:
• Receives requests forwarded by the web server.
• Processes business rules and logic.
• Connects to and interacts with the Database Tier: Formulates queries (often SQL), sends them to the database, receives and processes results.
• Generates dynamic content (e.g., HTML pages) to be sent back to the client via the web server.

Tier 4: Database Tier

• Role: Persistent storage and management of the application's data.
• Components: Database Management System (DBMS) software.
• Examples: PostgreSQL, MySQL, Oracle Database, SQL Server, MongoDB, Cassandra.
• Function:
• Receives data access requests (queries, updates) from the Application Server Tier.
• Executes queries, performs data manipulation (CRUD operations).
• Ensures data integrity, consistency, and security.
• Returns results to the Application Server Tier.

Illustrative Data Flow: User Login

Data Flow Explanation: User Login Example

1. Client: User enters credentials and clicks 'Login'. Browser sends an HTTP POST request with credentials.
2. Web Server: Receives the request. Recognizes it's for a dynamic resource (e.g., /login) and forwards it to the Application Server.
3. Application Server: Receives request data. Executes login logic:
• Validates input.
• Constructs a database query (e.g., SQL) to find the user and verify the password (hashed).
• Sends the query to the Database Server via a database connection.

Data Flow Explanation: User Login Example

4. Database Server: Executes the query against stored user data. Returns the result (user record or indication of failure) to the Application Server.

5. Application Server: Processes the database result. If successful, establishes a session; if not, prepares an error message. Generates the next page (e.g., dashboard or login form with error).

6. Web Server: Receives the generated page/response from the Application Server.

7. Client: Web Server sends the HTTP response back to the browser, which renders the resulting page for the user.

Key Components & Communication

• Client Tier <-> Web Server Tier:
• Communication: Primarily HTTP/HTTPS.
• Data Format: HTML, CSS, JavaScript, JSON, XML, form data.
• Web Server Tier <-> Application Server Tier:
• Communication: Various protocols (e.g., FastCGI, WSGI, proxy pass via HTTP). Depends on technology stack.
• Data Format: Request/response objects specific to the protocol/framework.
• Application Server Tier <-> Database Tier:
• Communication: Specific database network protocols (e.g., PostgreSQL wire protocol, MySQL protocol). Often managed via database drivers/connectors.
• Data Format: SQL commands, result sets (rows/columns), potentially other formats for NoSQL databases.

Database Design for Web Applications: �Key Considerations

• Designing databases for web applications goes beyond standard relational modeling.
• The nature of the web introduces specific challenges and priorities that must be addressed during design and implementation.
• We will focus on: Scalability, Performance, Concurrency, Security, and Availability.

Consideration 1: Scalability

• Challenge: Web applications often face massive, potentially unpredictable user loads (e.g., viral content, sales events). Databases must handle this growth.
• Design Priority: Design for horizontal scaling (adding more machines) rather than solely vertical scaling (making one machine bigger).
• Techniques:
• Replication: Creating copies of the database for load balancing read requests and improving availability.
• Sharding (Partitioning): Splitting data across multiple database servers based on a key.
• These techniques directly relate to the distributed database concepts discussed previously.

Consideration 2: Performance / Latency

• Challenge: Web users expect fast response times. Slow database queries directly impact user experience.
• Design Priorities:
• Query Optimization: Writing efficient SQL, analyzing query execution plans.
• Effective Indexing: Crucial for speeding up data retrieval operations (SELECTs) based on common query patterns. Indexing is even more critical in high-load web environments.
• Caching: Storing frequently accessed data in faster memory (either within the database system, at the application layer, or dedicated caching servers like Redis/Memcached) to reduce database load.

Consideration 3: Concurrency

• Challenge: A high number of users may attempt to access and modify the same data simultaneously (e.g., booking the last seat, multiple users commenting on a post).
• Design Priorities:
• Concurrency Control Mechanisms: Utilizing database features to manage simultaneous access and prevent data corruption.
• Locking: Understanding and potentially tuning database locking strategies (row-level, table-level) to balance consistency and performance.
• Transaction Isolation Levels: Choosing appropriate isolation levels (e.g., Read Committed, Serializable) to define how transactions interact and what phenomena (dirty reads, phantom reads) are permitted.

Consideration 4: Security

• Challenge: While not directly exposed, the database is a prime target via vulnerabilities in the web application layer.
• Design & Integration Priorities:
• Prevent SQL Injection: The application layer must use mechanisms like prepared statements or parameterized queries. Database design should anticipate this (avoiding dynamic SQL generation where possible).
• Input Validation: While primarily an application concern, understanding data types and constraints in the DB helps.
• Principle of Least Privilege: The database user account connecting from the application server should only have the permissions strictly necessary for its operation (e.g., SELECT/INSERT/UPDATE on specific tables, not administrative rights).
• Data Encryption: Consider encryption for sensitive data both at rest (in the database) and in transit (between application and database).

Consideration 5: Availability

• Challenge: For many web applications (e-commerce, critical services), downtime translates to significant financial loss and user frustration. Databases must be highly available.
• Design Priorities:
• Redundancy: Eliminating single points of failure.
• High Availability (HA) Configurations:
• Replication: Using standby databases (replicas) that can take over if the primary fails (Failover).
• Clustering: Grouping multiple servers that work together, providing load balancing and failover capabilities.
• Backup and Recovery: Robust strategies for regular backups and tested recovery procedures are essential.

Web App vs. Traditional Desktop App Databases

• Web Applications:
• Concurrency: Often very high, unpredictable bursts.
• Scalability: Needs to handle potentially millions of users; horizontal scaling is key.
• Availability: High expectations (often 24/7).
• Security: Exposed indirectly via the web; SQL injection is a major concern.
• Latency: User tolerance is generally low.

Web App vs. Traditional Desktop App Databases

• Traditional Desktop Applications (Single User / Small Workgroup):
• Concurrency: Lower, more predictable.
• Scalability: Needs are typically more limited and predictable.
• Availability: Requirements may be less stringent (e.g., business hours).
• Security: Different threat model (physical access, network access within organization).
• Latency: User tolerance might be slightly higher in some contexts.

Bridging the Gap: Connecting Web Applications and Databases

• We've established the multi-tier architecture and the specific design needs for web application databases.
• A critical aspect is enabling communication between the Application Server Tier and the Database Tier.
• This integration requires fulfilling several fundamental technical requirements.

Fundamental Integration Requirements

1. Connectivity:

• A reliable network path must exist between the application server(s) and the database server(s).
• Appropriate network protocols (typically TCP/IP) must be enabled and firewalls configured correctly.

Fundamental Integration Requirements

2. Interface / API (Application Programming Interface):

• The application code needs a standardized way to interact with the database.
• This involves mechanisms to:
• Establish and manage connections.
• Formulate and send queries (e.g., SQL statements).
• Receive and process result sets.

Fundamental Integration Requirements

3. Data Format Compatibility:

• The application must be able to parse and understand the data format returned by the database.
• This includes handling various data types (numbers, strings, dates, etc.) correctly.

4. Authentication & Authorization:

• Authentication: The database must securely verify the identity of the connecting application (e.g., using username/password, certificates).
• Authorization: Once authenticated, the application's permissions (what data it can access and what operations it can perform) must be enforced based on the principle of least privilege.

Lecture Summary

• Web applications depend on databases for persistence and dynamic content generation.
• A common platform uses a multi-tier architecture (Client, Web Server, Application Server, Database Server).
• Designing databases for the web requires specific attention to:
• Scalability: Handling large, variable loads.
• Performance: Ensuring low latency for user experience.
• Concurrency: Managing simultaneous user access.
• Security: Protecting data accessed via the application.
• Availability: Minimizing downtime.
• Basic requirements for integration include connectivity, a defined interface (API), data format compatibility, and secure authentication/authorization.