Introduction to Databases

• Databases are structured systems for storing and managing data.

Database Models

• Hierarchical databases organize data in a tree-like structure with parent-child relationships.
• Network databases extend the hierarchical model by allowing many-to-many relationships.
• Relational databases use tables with rows and columns to store data and define relationships.
• Object-oriented databases represent data as objects with attributes and methods.
• NoSQL databases encompass various non-relational models, optimized for flexibility and scalability.

Database Management Systems (DBMS)

• A DBMS is a software system used to define, create, maintain, and provide controlled access to databases.
• Key functions include data definition, data manipulation, data security, and data integrity.
• Examples of popular DBMSs include MySQL, Oracle, PostgreSQL, and MongoDB.

Database Design

• Database design involves creating a logical and physical structure for storing data.
• Normalization is used to minimize redundancy and improve data integrity.
• The design process includes requirements analysis, conceptual design, logical design, and physical design.

ER Diagram

• An Entity-Relationship (ER) Diagram is a visual representation of data entities and their relationships within a system.
• It is a high-level data model describing the structure of a database.

Components of an ER Diagram

• Entities represent real-world objects or concepts (e.g., Customer, Product).
• Attributes are properties or characteristics of entities (e.g., CustomerID, ProductName).
• Relationships define how entities are related to each other (e.g., a Customer places an Order).

Types of Entities

• Strong entities have a primary key and exist independently.
• Weak entities depend on another entity (the "owner" entity) and do not have a primary key of their own.

Types of Attributes

• Key attributes uniquely identify an entity.
• Simple attributes are atomic and cannot be further divided.
• Composite attributes can be divided into smaller sub-attributes.
• Single-valued attributes have only one value.
• Multi-valued attributes can have multiple values.
• Derived attributes can be calculated from other attributes.

Types of Relationships

• One-to-one: One entity is related to only one other entity.
• One-to-many: One entity can be related to many other entities.
• Many-to-one: Many entities can be related to one entity.
• Many-to-many: Many entities can be related to many other entities.

Cardinality and Participation Constraints

• Cardinality specifies the minimum and maximum number of instances of one entity that can be related to another entity.
• Participation constraints define whether the existence of an entity depends on its relationship with another entity (total vs. partial participation).

ER Diagram Design Process

• Identify entities in the system.
• Identify attributes for each entity.
• Define relationships between entities.
• Determine cardinality and participation constraints for each relationship.
• Refine the ER diagram to ensure it accurately represents the data requirements.

Relational Algebra

• Relational algebra is a procedural query language used to manipulate and retrieve data from relational databases.
• It provides a formal foundation for relational database operations.

Basic Operations

• Select (σ): Filters rows from a table based on a specified condition.
• Project (π): Selects specific columns from a table.
• Union (∪): Combines the rows of two tables, removing duplicates.
• Intersect (∩): Returns the common rows between two tables.
• Difference (-): Returns the rows that are in one table but not in another.
• Rename (ρ): Renames a table or attribute.

Complex Operations

• Cartesian Product (×): Combines each row of one table with each row of another table.
• Join (⋈): Combines related rows from two tables based on a join condition.
• Division (÷): Returns the tuples from one relation that match all tuples from another relation.

Join Operations in Detail

• Theta Join: Joins rows based on a general condition.
• Equijoin: A type of theta join where the condition involves equality.
• Natural Join: Joins tables based on attributes with the same name and data type.
• Outer Join: Includes all rows from one or both tables, even if there is no match in the other table.
  • Left Outer Join: Includes all rows from the left table.
  • Right Outer Join: Includes all rows from the right table.
  • Full Outer Join: Includes all rows from both tables.

Example Queries

• Retrieve all customers with a specific last name: σ lastName = "Smith" (Customers).
• List the names and addresses of all employees: π name, address (Employees).
• Combine customer and order information using a natural join: Customers ⋈ Orders.

Significance

• Relational algebra is a theoretical foundation for SQL and other query languages.
• Understanding relational algebra helps optimize database queries.

NoSQL Databases

• NoSQL databases are non-relational databases designed to handle large volumes of unstructured or semi-structured data.
• They provide flexible schemas and are horizontally scalable.

Types of NoSQL Databases

• Key-Value Stores: Data is stored as key-value pairs (e.g., Redis, Memcached).
  • Simple and fast for retrieving data based on keys.
• Document Databases: Data is stored in JSON-like documents (e.g., MongoDB, CouchDB).
  • Flexible schema and good for managing semi-structured data.
• Column-Family Stores: Data is stored in column families, which are containers of columns (e.g., Cassandra, HBase).
  • Highly scalable and suitable for large datasets.
• Graph Databases: Data is stored as nodes and edges, representing relationships between data points (e.g., Neo4j).
  • Optimized for querying relationships and connections.

Advantages of NoSQL Databases

• Scalability: Designed to handle large amounts of data and high traffic.
• Flexibility: Schema-less design allows for easy adaptation to changing data requirements.
• Performance: Optimized for specific data models and access patterns.
• Availability: Support for distributed architectures ensures high availability.

When to Use NoSQL Databases

• Handling large volumes of data.
• Managing unstructured or semi-structured data.
• Applications with high read and write loads.
• Applications requiring flexible schemas.
• Social networks, IoT, and real-time analytics.

CAP Theorem

• Consistency: Every read receives the most recent write or an error.
• Availability: Every request receives a (non-error) response, without guarantee that it contains the most recent write.
• Partition Tolerance: The system continues to operate despite arbitrary partitioning due to network failures.
• NoSQL databases often trade off one of these properties to optimize for others.

BASE Properties

• Basically Available: Guarantees availability by relaxing consistency requirements.
• Soft State: State of the system may change over time, even without input.
• Eventually Consistent: The system will eventually become consistent once it stops receiving updates.