SQL - 2

Introduction to NoSQL Databases

The session begins with the instructor prompting students to share their knowledge or research findings about NoSQL databases.

Understanding Structured vs. Unstructured Data

  • SQL Databases:
      - Store structured data (defined format of data)
      - Use structured query language (SQL) to manage this data.
      - Data organization is in the form of tables consisting of rows and columns, termed as relations.

  • Unstructured Data:
      - Examples include images, videos, and various multimedia content that cannot be neatly organized in tables.
      - Stored in NoSQL databases which are designed to handle more flexible data structures.

What is NoSQL?

  • Defined as a non-relational database.

  • Handles unstructured data and large-scale data.

  • Complies with flexible schemas, allowing for evolving data attributes without strict adherence to a predefined schema.

Characteristics of NoSQL Databases

  • Schema Flexibility:
      - Unlike SQL databases with a rigid schema, NoSQL allows for a flexible schema.
      - Example of a flexible schema is JSON format, where fields can be added or removed easily.

  • Scalability:
      - Refers to the ability of the system to handle growing amounts of data or a growing number of users.
      - Horizontal Scaling:
        - Accommodating increasing data loads by adding more servers or nodes.
        - Versus Vertical Scaling which involves enhancing the resources (e.g., CPU, RAM) of a single server.
      - Discussed with the analogy of distributing fruits to increasing numbers of people, demonstrating that a single resource may not suffice as demand increases.

Real-time Systems and Real-time Data

  • Real-time data processing refers to immediately handling data with minimal latency, enabling live interactions and updates.

  • The discussion emphasizes the limitations of SQL databases in dealing with large, real-time data streams, necessitating the use of NoSQL solutions.

Key Features of NoSQL Databases

  • Unstructured Data Handling:
      - NoSQL databases excel at managing data that doesn't fit traditional models (like relational database formats).

  • Flexibility in Schema:
      - Schema can evolve as data needs grow, allowing for easy modifications in how data is stored.

  • Systems are capable of managing massive datasets efficiently through distributed storage solutions.

Ticket Booking Database

The instructor transitions to a practical example focusing on designing a database schema for a ticket booking platform.

Workflow Discussion

  • Understanding and visualizing the entire workflow of the ticket booking process is crucial before designing the database schema.

  • Steps of ticket booking include:
      1. User registration or login.
      2. Browsing events.
      3. Selecting date and time.
      4. Choosing seats.
      5. Making payments.
      6. Receiving confirmation notifications.

Key Entities Identified

  • User Profiles/Users Table: Information capturing user details like name, email, password, contact number.

  • Events Table: Contains information about different events, their descriptions, timestamps, venues, and more.

  • Venue Table: Includes type, address, and capacity of venue locations hosting events.

  • Seating Table: Information about individual seats including availability, pricing, section, and seat numbers.

  • Booking Table: Details linking users, events, timestamps, and status of bookings.

  • Payment Table: Transaction details, payment status, and methods used.

  • Refunds Table: In case of cancellation, tracking refunds associated with bookings.

Columns for Each Entity

  • User Table: Should contain fields such as user ID, full name, contact number, email address, password, profile picture.

  • Event Categories: List of different types of events like movies, concerts, sports.

  • Events Table: Name, description, duration, rating, and cast info.

  • Seat Table: Seat numbers, row and section, available/not available status, pricing categories.

  • Booking Table: Includes user associated, timestamp, status, and potentially a QR code for entry confirmation.

  • Payment Table: Records transaction ID, amount, payment methods, and status (successful/failed).

Discussion on Atomic Transactions, Scalability, and Relationships

  • Atomicity: Transactions should either fully complete or fail, ensuring no partially processed transactions exist.

  • Discussed scalability in the context of accommodating a growing user base and increasing operational demands on the database system.

  • Relationships: The instructor emphasizes understanding how entities relate to each other, which is vital for database normalization and integrity.

Conclusion

The session wraps up with a reminder for students to download and install SQL Workbench for practical exercises in the next class, reinforcing the importance of hands-on experience in understanding data modeling and database management.