TOPIC 2

Big Data Computing

Topic 2: Databases and Big Data

Persistence, Transactions, Querying, Indexing and SQL

Database System Overview

  • Definition: A database system is a computer-based solution designed for efficient record keeping and information management.

  • Functions:

    • Stores, organizes, and manages data to support decision-making within an organization.

    • Database as a structured collection of interrelated data representing information about a particular enterprise.

Basic Database Concepts

  • Maintains a single, organized repository for multiple applications.

  • Stores data separately from the programs accessing it, allowing flexibility and scalability.

  • Uses a generic and regulated approach for adding, modifying, and retrieving data securely.

  • Organizes data to support easy integration with future applications and evolving business needs.

  • Resource: GeeksforGeeks - Difference between OLAP and OLTP

Purpose of Database

  1. Centralized Data Repository

    • Unified collection designed for multiple applications in an organization.

    • Serves as a single source of truth for storing, retrieving, and modifying information crucial for operations and decision-making.

    • Enables ongoing updates and quick access for tasks like control, monitoring, and planning.

    • Users can query the database for insights or informed business decisions.

  2. Supports Continuous Operations

  3. Improves Over File-Processing Systems

    • Traditional file systems store data in separate files requiring different programs for each function, leading to inefficiency and errors.

    • Resource: GeeksforGeeks - ACID Properties

Purpose of Database (Continued)

  1. Solves Key Limitations of File Systems

    • Data Redundancy: Eliminates duplicate storage across files.

    • Data Inconsistency: Ensures uniform and reliable data across applications.

    • Poor Adaptability: Provides flexibility to evolve with business needs.

    • Lack of Standardization: Enforces consistent formats and access rules.

    • Security Gaps: Centralized control enhances data protection.

    • Incorrect or Outdated Data: Maintains integrity through validation and constraints.

  2. Outcome: A Database Management System (DBMS) provides centralized control, accuracy, and efficiency, enabling seamless data management across the organization.

Database Abstraction

  • Major Purpose: Provide users with necessary information, hiding unnecessary details.

  • Complexity Management: Hides complexity of data storage and maintenance for easy user interaction.

Levels of Database Abstraction

  1. Internal Level (Physical Level)

    • Closest to physical storage and describes how data is stored on hardware (e.g., files, indexes, access paths).

    • Focuses on data structures and storage efficiency.

  2. Conceptual Level (Logical Level)

    • Represents what data is stored and its relationships within the database.

    • Defines structure in logical terms like tables, attributes, and constraints, independent of the physical storage.

  3. External Level (View Level)

    • Closest to end users defining how data is viewed by individual users/apps.

    • Allows customized views depending on users’ needs and permissions.

Separation of Levels

  • Changes in one level should not directly affect the others, ensuring data independence.

    • Physical Data Independence: Changes in storage structure do not affect the logical schema.

    • Logical Data Independence: Changes in logical schema do not affect external views.

Key Concepts in Database Management

  1. Database Schema

    • A blueprint or structural design defining how data is organized, including tables, columns, data types, constraints, and relationships.

  2. Data Constraints

    • Rules defining the type of data in each column, ensuring accuracy, validity, and consistency.

    • Common constraints include:

      • PRIMARY KEY

      • FOREIGN KEY

      • UNIQUE

      • NOT NULL

      • CHECK

    • Defined during table creation or modification.

  3. Data Dictionary (Metadata)

    • Stores structural details like schema definitions and table properties, maintained automatically by the DBMS.

  4. Database Instance

    • Encompasses the complete operational environment of a database, ensuring efficient read/write operations during runtime.

  5. Query

    • A request for data retrieval or manipulation using a language like SQL, serving as the primary interface between users and the database.

  6. Data Manipulation

    • Operations that modify data within the database including insertion, deletion, and updating of records through DML (Data Manipulation Language) commands in SQL.

  7. Data Engine

    • Core component responsible for processing and executing database queries, optimizing them and managing transactions.

Normalization

  • Definition: The process of organizing a database to minimize redundancy and dependency by dividing complex tables into smaller, structured ones.

  • Purpose: Create an efficient, consistent, and scalable database easy to update and manage.

  • Key Benefits:

    • Reduces data duplication and inconsistency.

    • Simplifies modification and maintenance.

    • Ensures logical organization and clarity of relationships.

    • Prevents update, insertion, and deletion anomalies.

Normalization Rules

  • Follows several normal forms with specific rules:

    • First Normal Form (1NF): Eliminates repeating groups, ensuring each field holds atomic values.

    • Second Normal Form (2NF): Removes partial dependencies, requiring non-key attributes to depend on the whole primary key exclusively.

    • Third Normal Form (3NF): Eliminates transitive dependencies between non-key attributes.

    • Reference: GeeksforGeeks - Types of Keys in Relational Models

Types of Database Keys

  1. Primary Key

    • Uniquely identifies every tuple (row) and disallows duplicate values and NULL entries.

    • Can be single-column or composite.

  2. Candidate Key

    • Minimal attribute set capable of uniquely identifying a tuple. Example: (StudentID + Course) uniquely identifies records.

  3. Composite Key

    • A primary key composed of multiple columns.

  4. Foreign Key

    • An attribute referencing the primary key in another table.

Types of DBMS Architecture

  1. 1-Tier Architecture

    • User works directly with the database on the same system (e.g., Microsoft Excel).

    • Advantages:

      • Simple and cost-effective.

      • Suitable for personal or standalone applications.

    • Disadvantages:

      • Limited to single-user access.

      • Poor security and hard data sharing.

  2. 2-Tier Architecture

    • Client-server model; the client communicates directly with the database server.

    • Example: Library Management System.

    • Advantages:

      • Easy access and fast retrieval.

      • Cost-effective compared to 3-tier.

    • Disadvantages:

      • Limited scalability and security issues.

      • Tight coupling requirements between client and database changes.

  3. 3-Tier Architecture

    • Includes an application server layer between the client and server for communication.

    • Example: E-commerce store.

    • Advantages:

      • Enhanced scalability and data integrity.

      • Improved security; client-server direct access limited.

    • Disadvantages:

      • More complex and difficult interactions.

      • Higher setup and maintenance costs.

Advantages of Databases

  • Reduces redundancy and controls inconsistency.

  • Facilitates sharing; multiple users/applications can collaborate.

  • Enforces data standards across naming conventions and rules.

  • Enhances security via authentication and access management.

  • Maintains integrity using constraints and referential integrity rules.

  • Improves system performance/efficiency for data operation management.

Disadvantages of Databases

  • Security risks due to potential unauthorized access.

  • Integrity challenges if constraints aren't enforced.

  • High hardware requirements for large databases.

  • Performance overhead with complex queries or large user volumes.

  • System complexity necessitating skilled administrators.

Structured Query Language (SQL)

  • A database often consists of one or more tables, each identified by a name (e.g., "Customers" or "Orders").

  • Tables contain records (rows) with data.

SQL Commands Overview

Command

Description

CREATE DATABASE

Creates a new database

CREATE TABLE

Creates a new table

ALTER TABLE

Modifies a table

DROP TABLE

Deletes a table

ALTER DATABASE

Modifies a database

SELECT

Extracts data from a database

UPDATE

Updates data in a database

DELETE

Deletes data from a database

SQL Data Types

Numeric

  • Types: bit, tinyint, smallint, int, bigint, decimal, numeric, float, real

Date/Time

  • Formats: Date, Time, Datetime, Timestamp, Year

Character/String

  • Types: Char, Varchar, Varchar(max), Text

Unicode Character/String

  • Types: NChar, NVarchar, NVarchar(max), NText

Binary

  • Types: Binary, Varbinary, Varbinary(max), image

Miscellaneous

  • Types: Clob, Blob, XML, JSON

SQL Operators

  • Three Types of Operators:

    • Arithmetic Operators

    • Comparison Operators

    • Logical Operators

SQL Arithmetic Operators

  • Operators include: + (addition), - (subtraction), * (multiplication), / (division), % (modulus).

SQL Comparison Operators

  • Operators include:

    • =, !=, >, <, >=, <= (checking equality, inequality, and relational comparisons).

SQL Logical Operators

  • Common operators:

    • AND, OR, NOT, EXISTS, IN, LIKE (conditional filters for queries).

SQL Keys

  • Types of Keys:

    • Primary Key (PK)

    • Foreign Key (FK)

    • Unique Key

    • Alternate Key

    • Composite Key

SQL Primary Key

  • A column or columns uniquely identifying each row; cannot have NULL values or duplicates.

SQL Foreign Key

  • A field in one table that links to a primary key in another table; can have NULL and duplicate values.

SQL Unique Key

  • Uniquely determines a row which isn't primary; may allow NULL values, and more than one unique key can exist per table.

SQL Constraints

  • Set rules for table data: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

  • Constraints enforce data integrity and rules during operations.

SQL Constraints (Detailed)

  • NOT NULL: Column cannot accept NULL values.

  • UNIQUE: Ensures all column values are distinct.

  • CHECK: Limits column values to certain conditions.

  • DEFAULT: Provides default column values when none specified.

  • AUTO INCREMENT: Automatically generates a unique number for each new record.

SQL Commands Classification

Type

Command

DDL

CREATE, ALTER, DROP

DML

INSERT, UPDATE, DELETE

TCL

COMMIT, ROLLBACK, SAVEPOINT

DQL

SELECT

DCL

GRANT, REVOKE

Here are 20 questions along with their answers:

  1. What is Big Data?
    Answer: Large and complex data sets that traditional data processing software cannot manage.

  2. What are the three main types of databases?
    Answer: Relational, NoSQL, and NewSQL databases.

  3. What does SQL stand for?
    Answer: Structured Query Language.

  4. What is a primary key?
    Answer: A unique identifier for each record in a database table.

  5. What is the purpose of normalization in databases?
    Answer: To minimize redundancy and dependency by organizing data efficiently.

  6. What is a foreign key?
    Answer: A field in one table that links to a primary key in another table.

  7. What does DBMS stand for?
    Answer: Database Management System.

  8. What are ACID properties in database systems?
    Answer: Atomicity, Consistency, Isolation, and Durability; they ensure reliable processing of database transactions.

  9. What is data redundancy?
    Answer: The unnecessary duplication of data within a database.

  10. What is a data dictionary?
    Answer: A collection of metadata that defines the structure of a database and its elements.

  11. What is a query?
    Answer: A request for information from a database, typically made using SQL.

  12. What is the difference between OLAP and OLTP?
    Answer: OLAP (Online Analytical Processing) is for data analysis, while OLTP (Online Transaction Processing) is for managing transactional data.

  13. What is a composite key?
    Answer: A primary key that consists of two or more columns.

  14. What are the benefits of using a database?
    Answer: It reduces redundancy, improves data integrity, enhances security, and facilitates data sharing.

  15. What are the disadvantages of a database?
    Answer: They can be complex, have high hardware requirements, and introduce security risks.

  16. What is a database schema?
    Answer: The structure of a database defined by a blueprint that describes how data is organized.

  17. What is meant by data consistency?
    Answer: Ensuring that data across multiple databases or systems remains accurate and reliable.

  18. What are SQL constraints?
    Answer: Rules applied to data in a table to enforce its integrity and validity.

  19. What is the purpose of indexing in databases?
    Answer: To speed up data retrieval operations by providing quick access paths to data.

  20. What is a database instance?
    Answer: The complete operational environment of database management, representing the state of data at a given time.