Database Fundamentals Quiz

Web Databases (SQL and NoSQL)

• Web databases are used to store and retrieve data for web applications.
• SQL databases organize data in tables with rows and columns.
• NoSQL databases offer flexible schemas and scalability.

Web Application Architecture

• Web browsers (e.g., Chrome, Firefox, Safari) interact with web servers (e.g., Apache, Node) for data retrieval.
• The storage system contains the database (e.g., MySQL, MongoDB) where data is stored.
• Data transfers between components occur via the HTTP protocol over a Local Area Network (LAN) and the internet.

Why/When Do We Need a Database?

• Data volume: Handling large amounts of data that cannot be stored in memory.
• Data persistence: Ensuring data is accessible even during user logouts, power outages, or network issues.
• Accurate data manipulation: Data manipulation like bank transactions requires precise, controlled manipulation.

Web App Storage Properties

• Always available: Ensuring data access even with concurrent requests.
• Scalable: Handling concurrent requests from various geographic locations.
• Reliable/fault-tolerant: Data integrity even if parts of the system fail.
• Well-organized to easily extract and organize application data.
• Easily adapted to evolution of the application.
• Easy to understand and maintain.

Relational Databases (SQL-based)

• Data is stored in tables; Relations are used to link data between tables.
• Tables improve data organization, easily representing relationships.
• Queries easily process data from multiple tables.

Relational Database System (Details)

• Tables are comprised of rows/tuples/records, which consist of columns/attributes/fields of a fixed type.
• Data within a table is organized into predefined fixed categories that are stored in columns.
• Columns have typed entries for specific categories of information (number, dates etc).
• Data rows are identified using a unique key (primary key).

Database Schema

• Schema defines the structure of the database.
• Database structure consists of table names, table columns, and constraints.
• Table columns are defined with specific types.

Example of a Table

• Data is displayed in rows and columns and identified using a unique primary key.
• The table structure has columns for identifying information along with typed data entries. (name, date, number, etc.)

SQL Example Commands

• CREATE TABLE statement defines a table structure.
• INSERT INTO statement adds a record/row.
• DELETE FROM statement removes a record.
• UPDATE statement modifies a record.
• SELECT statements retrieve data.

Keys and Indexes

• Primary keys uniquely identify each row in a table.
• Secondary keys (indexes) are additional keys to speed up data retrieval.
• Indexes can be used to more quickly identify specific rows of data by using a lookup function.

Table Joins

• Joins combine information from multiple tables into one single table.
• Tables can combine into a more convenient single view of the data in a database.

The Problem with Joins

• Joins retrieve related documents/rows from multiple tables, which creates performance issues.
• Joins are expensive in terms of calculation because of the many steps involved in finding the related rows.

SQL Databases have ACID Semantics

• Atomicity: All operations succeed or fail entirely.
• Consistency: Data validation rules are maintained.
• Isolation: Concurrency control to prevent interference between transactions.
• Durability: Committed data is reliably stored in the database.

Atomicity of Transactions

• Transactions execute all steps completely or rollback in case of an error.
• Error handling allows transaction operations to recover from an error.

Consistency

• Data validation rules ensure that data in the database remains accurate after a transaction.
• Rules include checks on the data integrity/accuracy of the data.

Isolation

• Concurrent transactions do not interfere with each other.
• Concurrent operations must be isolated/separated to prevent issues.

Durability

• Committed transactions are permanently stored in the database.
• Database operations (data writing in particular) remain in the database after a transaction.

ACID: Pros and Cons

• Pros: Simpler reasoning about system actions, guaranteed correctness in failures.
• Cons: Performance costs related to guarantees, cannot guarantee availability when network fails. The CAP Theorem applies here.

Class Activity: [Scenarios related to databases and their implications for choosing appropriate database type]

• The activity entails evaluating several use cases. Database selection is crucial in these situations.

Solution to the Activity [Database selection justification]

• The solution would justify the appropriate database choice (SQL or NoSQL) for each scenario of the activity presented.

What are NoSQL Databases?

• They manage unstructured or semi-structured data.
• They have adjustable database structures/schemas. Very flexible.
• They offer high scalability.
• Designed for large-scale data processing in real-time applications like web applications.

NoSQL Databases (Specific Details)

• They do not natively support table joins and require manual programming implementation.
• They often do not strictly follow database ACID properties but instead provide eventual consistency.

CAP Theorem [Brewer'99]

• Trade-offs exist for a variety of scenarios involving databases for web applications.
• Consistency, availability, and partition tolerance are three properties related to database systems and their interactions with network environments.
• Selecting a particular strategy often implies a specific trade-off related to a situation or operational scenario.

BASE principle [NoSQL database methodology]

• Basically available, soft state, and eventually consistent.
• It's a principle that is often associated with NoSQL database systems
• It emphasizes availability and flexibility rather than strict consistency like databases based on SQL methodologies. Eventual consistency is a key element to understand.

Eventual Consistency

• NoSQL databases eventually become consistent. It's the point in time at which the database contents are fully consistent.
• It is often not immediate and can take a while for the data to be perfectly balanced and consistent .

SQL Vs. NoSQL - Comparison

• Types: SQL has one main type; NoSQL has multiple types (e.g., key-value, document, graph).
• Examples: SQL examples include MySQL, SQLite, and Oracle Database; NoSQL includes MongoDB, Cassandra, and others.
• Data Storage Model: SQL stores data in rows and columns; NoSQL stores data in documents.
• Schemas: SQL schemas are fixed, NoSQL schemas are adjustable.

SQL Vs. NoSQL - Scaling

• SQL databases typically scale vertically (increasing single server power); NoSQL typically scales horizontally (distributing data over multiple machines).
• Scaling choices influence whether the system can handle load and availability.

SQL Vs. NoSQL - Transactions

• SQL supports transactions; NoSQL may offer certain levels of transaction functionality, but is frequently not fully supportive of transactions.

SQL Vs. NoSQL - Consistency

• SQL databases require strong consistency, while NoSQL databases may provide tunable or eventual consistency (trade-off with scalability and availability).

Class Activity: [Appropriate database selection based on scenario]

• Students evaluate several situations to determine the appropriate database.

MongoDB (Document-Oriented NoSQL)

• Documents are the key elements of data organization, analogous to tables in SQL.
• Data is stored in JSON format in MongoDB.
• Databases are composed of collections.
• Documents have a 'unique identifier' field to uniquely identify them in a collection.

MongoDB: Data Types

• Valid JSON data types are present for a MongoDB database.

MongoDB: Example Dataset

• JSON/BSON format example dataset for a MongoDB structure.

Databases and Collections (MongoDB structure)

• A database is composed of multiple collections, analogous to SQL tables.
• Collections hold many records in MongoDB format. Each record is a document.

Insert into a Database (MongoDB)

• db.collectionName.insert(document in JSON) method is used for inserting new documents. Inserting data usually occurs through programming code.

Finding Objects (MongoDB Queries)

• db.collectionName.find() (returns all documents)
• db.collectionName.find(JSON_object) (returns only documents satisfying criteria)

Examples of Queries (MongoDB)

• Query examples (finding data using specific criteria).

Object_id (MongoDB key)

• _id is a unique identifier for each document in a MongoDB collection.
• It's automatically generated, ensuring uniqueness and database integrity.

Update (MongoDB Updates)

• db.collectionName.update(query, updateObject) updates existing entries.

Remove (MongoDB Removal)

• db.collectionName.remove(query) removes existing entries from a MongoDB collection based on criteria in the query.

Operations on Each Record (MongoDB functions)

• Examples of using .forEach() in MongoDB to operate on specific elements or criteria.

Table Joins in MongoDB

• Table joins are not supported by MongoDB directly. Manual programming/implementation is required to handle these kinds of joins.

Example: Join Operation (MongoDB implementation)

• Example that shows how to do a join in MongoDB by extracting the necessary fields and documents to represent a join.

Class Activity: [Join operation for two different collections]

Solution to the Activity (MongoDB/Javascript join operation)

• Example of joining two MongoDB collections together using code.