Understanding Relational Databases and SQLAlchemy

Quiz 2: part 1

Relational Database

Organizes data into structured tables (relations) that connect through shared data fields.

Tables

Collections of rows (records) and columns (attributes) representing entities (e.g., users, products).

Primary Key

Unique identifier for each record in a table, ensuring that no two rows share the same primary key value.

Foreign Key

Column in one table that references the primary key in another table, establishing relationships between tables.

SQL

The primary language used to query, update, and manage relational databases.

CRUD Operations

Create: Inserting new records into a table; Read: Selecting and retrieving data from tables; Update: Modifying existing records; Delete: Removing records from tables.

Atomicity

Ensures that all parts of a transaction are completed; if one part fails, the entire transaction is rolled back.

Consistency

Ensures that a transaction only brings the database from one valid state to another, adhering to all predefined rules and constraints.

Isolation

Ensures that transactions are executed in isolation, meaning one transaction's changes are invisible to others until completed.

Durability

Ensures that once a transaction is committed, it remains saved, even if there is a power loss or crash.

Transaction

A sequence of operations treated as a single logical unit of work, which must either fully complete or not occur at all.

SQLAlchemy Engine

Maintains a stable connection to the database and enables running SQL queries.

SQLAlchemy Core

Enables stable database connections and includes abstractions to run SQL queries.

SQLAlchemy Entities

Represent tables in our SQL database.

DeclarativeBase

Class from which all entities in the CSXL repository inherit.

UNCBasketballTicketEntity

Entity used to represent basketball tickets in SQLAlchemy.

Pydantic Models

Define data structure for API validation, while SQLAlchemy entities define database structure.

OrganizationEntity

Entity that represents the organization table in SQL.

SQL Table Name

Defined by OrganizationEntity as organization.

Primary Key in Organization Table

Field that uniquely identifies each row in the organization table.

CRUD Operations List

Create: Add new rows to the table; Read: Read existing rows from the table; Update: Modify existing rows on the table; Delete: Remove rows from the table.

All-or-Nothing Principle

Ensures that all requested changes occur or none do if an error occurs, maintaining data consistency.

SQLAlchemy Query

A request for data that can be built with more method calls.

GET /api/courses

API endpoint associated with the SQLAlchemy query select(CourseEntity).

List of Scalars

Data type of data in data = self._session.scalars(query).all().

SQLAlchemy Session

Object that represents SQLAlchemy's active connection to our database.

Many-to-Many Relationship

Models users having many reservations, and reservations for groups of users.

Association Table

Needed in a Many-to-Many relationship to track individual relationships between two entities.