Databases / Data Flashcards
Schema
Blueprint / structure of DB.
Key Components
- Tables
- Columns/fields
- Views
- Indexes
Benefits of having multiple DB Schemas
- Allows for better organization
- Separation of DB objects into logical groupings
- Easier to manage
- Security and access control allowing for fine-grained control of who can access the data
- Modularity: Schemas can represent diff modules / components of an application allowing for modular design and development
Flyway
Open source DB migration tool that helps manage changes to a DB. (Similar to GIT)
Helps keep DB in sync, organized and consistent.
Flyway Versioned Migrations
Uses versioning mechanism to apply migrations in a specific order. Each is given a unique version number
Benefits of Flyway
- Automates the process of applying DB migrations
- Ensures same migrations are applied in same order across all env
- Version control making it easy to track changes over time
DB Object
Any defined obj in a DB that can store or reference data.
Created using Data Definition Language (DDL) like CREATE, ALTER, DROP
DB Object Examples
- Tables: rows and columns
- Views: virtual tables representing the result of a stored query
…
Views
Virtual tables defined by a query
- simplify complex’s queries
- encapsulate complex logic
- present data in a specific format without altering underlying tables
View Details
Views do not store data but dynamically generate results based on the query when accessed
What 4 properties a transaction must exhibit
ACID
- Atomicity: ensures all operations are completed successfully. If any operations fail the transaction is rolled back and DB is left unchanged
- Consistency: guarantees a transaction will bring the DB from one valid state to another
- Isolation: ensures that all operations of a transaction are isolated from others. Intermediate states within a transaction are not visible to other transactions
- Durability: ensures changes by a committed transaction are permanent even in the case of system failure
DB Transactions
A sequence of operations performed as a single logical unit of work
Example Transaction Scenario
- Begin Transaction
BEGIN;
- Debit amount from account A
UPDATE accounts SET balance = 200;
- Update amount from account B
UPDATE accounts SET balance = 1;
- Commit Transaction
COMMIT;
** If the debit operation fails the transaction is rolled back
ROLLBACK
unnest
used to expand an array into a set of rows
“unnests” the array elements and returns them as individual rows
Used in replacement of writing multiple queries. Basically a bulk query.
unions
used to combine the result of two or more select statements
- if one part fails the entire query fails
Compound Key
Also known as a composite key
- A key in a DB table that consists of 2 or more columns which together uniquely identify a record in a table
- Used when a single column isn’t sufficient to uniquely identify a record
EX:
CREATE TABLE OrderItems (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);
OrderId and ProductID together form the compound key.
Graph DB
- designed to store and manage data as nodes (entities) and edges (relationships)
- optimized for complex relationships and interconnected data
Graph DB Nodes
Represents entities like people, products, cities
Graph DB Edges
Represents relationships between notes like friend of, bought, located in
Graph DB Properties
Attributes associated with nodes or edges
Graph DB Traversal
Querying the DB by following relationships between nodes
Graph DB Advantages
- efficient relationship handling like recommendation handler
- flexible schema by allowing dynamic changes without rigid table structures
- fast queries on connected data
Popular Graph DBs
Amazon Neptune
When to use a graph DB
- friend recommendations
- analyzing social interactions
- finding influencers in a network
- fraud detection
Composite Table
Store data usually FKs to represent complex relationships and the ability to uniquely identify a row
