What’s a relational DB
hint - what does it say about its data (2 bullet points)
- allows us to identify and access datain relationto another piece of data in the database, e.g. foreign key constraints
- designed to store structure data, usually organized as a set of tables with columns and rows
What are 4 important aspects of a relational DB
- supports SQL
- data integrity
- supports transactions
- ACID compliance
What’s ACID compliance (what does ACID stands for)
- atomicity
- consistency
- isolation
- durability
What’s data integrity (referring to one aspect of a relational DB, 4 bullet points)
Data integrity
- overall completeness, accuracy and consistency of data - use a set of constraints to enforce data integrity - These include primary Keys, Foreign Keys, ‘Not NULL’ constraint, ‘Unique’ constraint, ‘Default’ constraint and ‘Check’ constraints - most relation databases also allow custom code to be embedded in triggers that execute based on an action on the database
Name a few popular relational DB (no need to name all)
- MySQL
- PostgresSQL
- Oracle
- SQL server (microsoft)
- SQLite - popular open source SQL DB
- snowflake
- Amazon Aurora
Explain the “A” in ACID
Atomicity - a transaction is either all or nothing
All parts of a transaction are executed completely and successfully, or else the entire transaction fails.
Explain the “C” in ACID
Consistency
- consistency refers to data integrity, data written as a part of the transaction must adhere to all defined rules.
- Restrictions include constraints, cascades, and triggers
Explain the “I” in ACID
Isolation
- each transaction is independent of all other transactions
- critical to achieve concurrency control
Explain the “D” in ACID
Durability
- All changes made in the transaction are permanently stored even if the DB fails
Why is DynamoDB noSQL
DynamoDB is NoSQL in that it does not support SQL. Instead, it uses a proprietary API based on JSON.
What’s a noSQL DB
- doesn’t store data in tables but instead in whatever format is best for the type of data being stored
- designed to contain unstructured data
Give some examples of data that are better to be stored in noSQL DB
Loosely defined data like
- email
- videos
- images
- business documents
What are several ways to create noSQL DB
hint - what are some ways other than “key value pairs” (4 bullets including key/value)
- key-value stores
- column-family data stores
- graph databases
- document databases
Name some common noSQL DB
MongoDB
IBM Cloundant
Amazon DynamoDB
Apache Cassandra
When to use SQL vs noSQL DB
- structured or unstructured data? - noSQL can work with both
- how much data are you storing - noSQL are better for larger vol of data than SQL DB
- who’s managing your data - non professionals find SQL DB easier to work with (because they are more familiar with using SQL queries?)
Advantages of using DDB (7 bullets)
- single digit msec performance
- nearly unlimited throughput and storage
- automatic multi region replication
- encryption at rest
- SLA of 5 9s availability
- fully managed, auto scale up and down
- integrated with AWS
Optimistic vs pessimistic locking - How do they lock
Optimistic locking
When you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn’t changed before you write the record back.
Pessimistic locking
Lock the record for your exclusive use until you have finished with it.
What fields we can do optimistic locking on
Normally use the version field, but can use other fields such as timestamp, checksum, etc
Pros and cons for pessimistic locking
- Con - could run into deadlocks
- Pro - better integrity than optimistic locking
When do we use optimistic locking
Used in three-tiered architectures where application does not maintain DB locks as connections are taken from a pool.
When do we use pessimistic locking
Either need a direct connection to DB or or an externally available transaction ID that can be used independently of the connection.
