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.