Databases / Data

Question 1

Schema

Answer 1

Blueprint / structure of DB.

Key Components
- Tables
- Columns/fields
- Views
- Indexes

Question 2

Benefits of having multiple DB Schemas

Answer 2

• 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

Question 3

Flyway

Answer 3

Open source DB migration tool that helps manage changes to a DB. (Similar to GIT)

Helps keep DB in sync, organized and consistent.

Question 4

Flyway Versioned Migrations

Answer 4

Uses versioning mechanism to apply migrations in a specific order. Each is given a unique version number

Question 5

Benefits of Flyway

Answer 5

• 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

Question 6

DB Object

Answer 6

Any defined obj in a DB that can store or reference data.

Created using Data Definition Language (DDL) like CREATE, ALTER, DROP

Question 7

DB Object Examples

Answer 7

• Tables: rows and columns
• Views: virtual tables representing the result of a stored query
  …

Question 8

Views

Answer 8

Virtual tables defined by a query

• simplify complex’s queries
• encapsulate complex logic
• present data in a specific format without altering underlying tables

Question 9

View Details

Answer 9

Views do not store data but dynamically generate results based on the query when accessed

Question 10

What 4 properties a transaction must exhibit

Answer 10

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

Question 11

DB Transactions

Answer 11

A sequence of operations performed as a single logical unit of work

Question 12

Example Transaction Scenario

Answer 12

1. Begin Transaction

BEGIN;

2. Debit amount from account A

UPDATE accounts SET balance = 200;

3. Update amount from account B

UPDATE accounts SET balance = 1;

4. Commit Transaction

COMMIT;

** If the debit operation fails the transaction is rolled back

ROLLBACK

Question 13

unnest

Answer 13

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.

Question 14

unions

Answer 14

used to combine the result of two or more select statements

• if one part fails the entire query fails

Question 15

Compound Key

Answer 15

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.

Question 16

Graph DB

Answer 16

• designed to store and manage data as nodes (entities) and edges (relationships)
• optimized for complex relationships and interconnected data

Question 17

Graph DB Nodes

Answer 17

Represents entities like people, products, cities

Question 18

Graph DB Edges

Answer 18

Represents relationships between notes like friend of, bought, located in

Question 19

Graph DB Properties

Answer 19

Attributes associated with nodes or edges

Question 20

Graph DB Traversal

Answer 20

Querying the DB by following relationships between nodes

Question 21

Graph DB Advantages

Answer 21

• efficient relationship handling like recommendation handler
• flexible schema by allowing dynamic changes without rigid table structures
• fast queries on connected data

Question 22

Popular Graph DBs

Answer 22

Amazon Neptune

Question 23

When to use a graph DB

Answer 23

• friend recommendations
• analyzing social interactions
• finding influencers in a network
• fraud detection

Question 24

Composite Table

Answer 24

Store data usually FKs to represent complex relationships and the ability to uniquely identify a row

Question 25

flyway.baselineOnMigrate

Answer 25

If set to true means if flyway detects an existing DB that hasn't been managed by flyway it will create a baseline version for it. This allows flyway to start managing the DB without trying to reapply old migrations. Simple terms: If the DB already exists, treat it as if it starts from a specific version so we don't mess up what's already there. For example, if your database is already at version 30, Flyway will skip versions 1–30 and only apply migrations starting from version 31.

Question 26

flyway.table

Answer 26

Tells flyway what table name to use to keep tack of the migrations it has By default it uses flyway_schema_history. Flyway allows you to configure different tables for tracking migrations. Use Case: If you have multiple projects or modules (e.g., policy_data and base_policy_data) sharing the same database, you can use separate migration history tables to track their migrations independently.