DDA Test

Atomic (ACID)

Transactions are executed completely or not at al

Consistency (ACID)

Transactions maintain the database’s integrity constrains (DBMS have set of rules that ensure that the data in the database is accurate, consistent, and reliable)

Isolated (ACID)

A transaction cannot influence or see the incomplete effects of another concurrent transaction

Durable (ACID)

Once a transaction is declared complete (COMMIT), its results will not get lost regardless of system failures

Primary key

1 or more attributes that uniquely determine a record in a table

Foreign key

Attributes in a table that form a reference to the primary key of another table

Facts

Numerical properties (measures), combined with a formula (like SUM or AVG) at a specific granularity

Dimensions

Attributes that characterize the facts, typically textual, which can have hierarchical groupings (e.g. County —> city)

Roll-up

An operation that decreases detail by aggregating data

Drill-down

An operation that increases detail to show final granularity

OLTP

Handles day-to-day transactions, highly normalized (reduces redundancy), Queries: Simple, fast, write-heavy

OLAP

Performs complex data analysis and reporting, denormalize (e.g. star schemas and cubes), Queries: Complex, read-heavy

Inner Join

Returns only rows that match in both tables based on the condition

Left Join

Returns all rows from the left table, and matching rows from the right table (or NULLs)

Right Join

Returns all rows from the right table, and matching rows from the left table (or NULLs)

Full Outer Join

Returns all rows from both tables, filling with NULLs where there is no match

Cross Join

Returns the Cartesian product of all rows (all possible combinations). Occurs if a WHERE join condition is forgotten

Self Join

Joins a table with itself using table aliases to compare rows within the same table

Aggregate functions

Functions like AVG(), COUNT(), MAX(), MIN(), and SUM() computes values over multiple rows

WHERE vs HAVING

WHERE filters data before aggregation, HAVING filters groups after aggregation

CREATE TABLE

Defines new table with columns and data types

ALTER TABLE

Modifies an existing table structure (e.g. adding a column)

UPDATE

Modifies existing records based on a SET condition

Conceptual Design

Creating an Entiy-Relationship (ER) model using UML

Logical Design

Translating the UML schema into an actual table structure with primary and foreign keys

Realisation

Preparing data, creating tables in SQL, and filling the in

Normalized Design

Every dimension gets it’s own separate table, referencing the fact table via foreign keys

Inlined Design

Dimension attributes are stored directly inside the fact table, bypassing the need for separate tables.

Use inlined designs when: a dimension has few possible values, short string lengths, in not reused across other cubes, represents an easily computable value like a data

Routes

Define hoe the application responds to client requests for specific endpoints via HTTP methods like GET and POST

Views (Templates)

Engines like EJS separate presentation from application logic
Embedding server-side Javascript code between <% %> tags to dynamically generate HTML

GET

Sends form data via the URL and is strictly for data retrieval

POST

Sends data hidden in the HTTP request body and is used for inserts, updates, or deletes

Client-side rendering (CSR)

The server sends the data and the code that will generate the visualization/chart from the data to the client. The client then executes the code it received on the data it received to generate the chart.
Favorable when the processing power of the server is a concern

Server-side rendering (SSR)

Executes the code on the chart data and send the results (a visualization) to the client.

Union

Combines the results of 2 queries (A⋃B)

Intersect

Returns only elements present in both query results (A∩B)

Except

Returns all elements from the 1st query that are not in the 2nd (A\B)

Cartesian Product

Matching every tuple in set A with every tuple in set B (A×B)

Standard Views

Visual tables defined by a query
Do not store data themselves, but provide security, data abstraction, and a simplified schema for specific applications

Updatable Views

Views can only be updates if they maintain a direct 1-to-1 lossless mapping to a base table
No joins, aggregate functions, or derived columns can be present

Materialized Views

Physical tables that actually store the results of a query on disk

They are much faster for reading live data but require manual refreshing and are highly utilized in OLAP environments for data cubes

Indexes - Purpose

Hidden data structure that speed up query retrieval but slow down data modification operations (inserts, updates, deletes)

B-Trees

Balanced trees that keep keys in sorted order, making them highly efficient for range queries (between, <, >, order by)

Composite indexes

Follow the ‘most-left prefix‘ principle
If an index in on (last_name, first_name, city), querying only by first name will skip the index

Normalization

Organizes relational tables into normal forms (1NF, 2 NF, 3NF) strictly to reduce data redundancy and maintain integrity
NF: normal form

NoSQL

Databases like document stores or key/ value stores
They abandon strict relational schemas and normalized structures in favor of maximum flexibility, speed, and raw scalability