databases

Database

collection of information, model of the real world, single shared authoritative source about the domain

Database Management System (DBMS) Functions

stores data (insert/update/delete), access data (lookups and complex queries), standard format/language, declarative (what not how), structure data to embody domain, query processing and optimization, schema, metadata, security/privacy, concurrency control, transactions, parallelism, maintain data consistency (enforce constraints), redundancy/availability, extensibility/scalability, data distribution, recovery, interaction APIs, indexes, separates code from data, factors out core functionality that would otherwise need to be coded

Entity-Relationship Diagrams

diagrammatic model of the real world

Entities

main objects in the domain being modeled

Relationships

how entities interact in the domain

Identifying attribute

an attribute that is unique for each instance of the entity (ex: ID)

Derived attribute

an attribute whose value can be calculated or derived from other attributes or entities in the database

Set-valued attribute

an attribute that can have multiple values for a single entity (not used for values that are important enough to be entities)

Composite attribute

an attribute that can be broken down into smaller sub-attributes with independent meanings

One-to-One Relationship

each entity in one set is associated with at most one entity in the other set

One-to-Many Relationship

an entity in one set can be related to many entities in another set, but each entity on the other side is related to only one in the first

Many-to-Many Relationship

entities in both sets can have multiple associations with each other

Total Participation

every instance of an entity must participate in the relationship at least once

Partial Participation

some instances of an entity may or may not participate in the relationship

Aspects of Relationships

can relate an entity to itself, can have attributes, can connect more than two entities, must be uniquely identified by the collection of the entities’ identities (primary keys)

Weak Entities

an entity whose existence is dependent on an “owning” or “parent” entity, partial identifying attribute (primary key)

SQL CREATE TABLE

creates a new table in the database

PRIMARY KEY

a column (or set of columns) that uniquely identifies each record in a table (must be unique and not null)

FOREIGN KEY

a column that links one table to another by referencing a primary key in another table (establishes relationships between entities)

NOT NULL

a constraint that ensures a column cannot have empty values

UNIQUE

ensures that all values in a column are different (no duplicates allowed)

DEFAULT

sets a default value for a column when no value is provided

CHECK

a constraint that limits the values allowed in a column based on a condition (ex: age >= 18)

DROP TABLE

deletes a table and all its data

ALTER TABLE

modifies an existing table’s structure (add, remove, or change columns)

SELECT

retrieves data from one or more tables

FROM

specifies which table(s) the data is retrieved from

WHERE

filters rows based on a condition

GROUP BY

groups rows that have the same values in specified columns, often used with aggregate functions

HAVING

filters groups (not individual rows) created by GROUP BY

ORDER BY

sorts the results in ascending or descending order

JOIN

combines rows from two or more tables based on a related column

INNER JOIN

only matching rows in both tables

LEFT JOIN

all rows from the left table + matches from the right

RIGHT JOIN

all rows from the right table + matches from the left

FULL JOIN

all rows from both tables (matches or not)

INSERT INTO

adds new rows to a table

UPDATE

modifies existing data in a table

DELETE

removes rows from a table

DISTINCT

returns unique values in a result set

AS

temporarily renames a column or table

ON DELETE CASCADE

when a record in the parent table is deleted, all related rows in the child table are deleted

ON UPDATE CASCADE

when the primary key value in the parent table changes, the foreign key values in related child rows are automatically updated

SET NULL

sets the foreign key in child rows to null when parent is deleted/updated

SET DEFAULT

sets the foreign key to its default value when parent is deleted/updated

NO ACTION/RESTRICT

prevents deletion/update if child rows exist

Relation

a table in a database

Tuple

a single row in a relation

Attribute

a column in a relation

Schema

the structure/definition of a relation (its name and attributes)

Selection (σ)

filters rows based on a condition (like WHERE in SQL)

Projection (π)

selects specific columns (attributes) from a relation

Union (u)

combines tuples from two relations, removing duplicates

Set Difference (-)

removes tuples in one relation but not in another

Cartesian Product (x)

combines every tuple from one relation with every tuple from another

Rename (ρ)

renames a relation or its attributes

Join (⨝)

combines tuples from two relations based on a common attribute

Theta Join

join with any condition (ex: =, >, <)

Equi-Join

join using equality

Natural Join

automatically joins on all attributes with the same name

Intersection (∩)

returns tuples common to both relations

Division (÷)

find entities in one relation that are related to all tuples in another relation

Limitations of Relational Algebra

every input and output must be a relation (table), can’t handle individual values, does not support operations like SUM, AVG, MAX, MIN, COUNT, no grouping or sorting, results have no guaranteed order, assumes data is complete (no NULLs), can’t declare order of operations, only understands simple atomic values

View

a virtual table that doesn’t store data but displays data from one or more tables through a saved SQL query

Simplifies complex queries

can grant access to a limited view of underlying table, improving security, is stored in the schema and can be used again

Subquery

a query nested inside another query

Single-row Subquery

returns one value

Multi-row Subquery

returns a list of values

Multi-column Subquery

returns multiple columns

Correlated Subquery

refers to columns from the outer query, running once per outer row

Assertions

complex integrity constraints involving multiple rows/tables (not supported by any major DBMS)

Indexes

builds a data structure to facilitate efficient lookups, makes queries faster but costs more space and slower updates

Triggers

when an event occurs, check the condition, and if satisfied, execute the action

Events

insert, delete, or update on a table

Conditions

typically expressed via an SQL query

Actions

could be arbitrary code, commonly SQL Insert/Update/Delete

Grant Statements

used to assign privileges to roles

Basic Privileges

select, update(column), delete, insert, references

Grant + View

commonly used to grant access to certain data only to those with authority

Transaction Atomicity

guarantees that either all operations succeed or none are applied

Key-Value Store

a type of NoSQL database where data is stored as key-value pairs, each key is unique and the value can be simple or complex

Declarative Query Language

you describe what you want, not how to compute it (SQL)

With Grant Option

allows a user to grant a permission to others

Streaming Data Model

main data in the domain is arriving over time, events trigger actions/queries

Graph Databases

model data as nodes (objects/entities) and edges (connections/relationships), often used to quantify over many connection types

NULL = NULL

doesn’t evaluate to TRUE in SQL, it evaluates to UNKNOWN (behaving like FALSE)