Database concepts

Question 1

What’s a relational DB

hint - what does it say about its data (2 bullet points)

Answer 1

1. allows us to identify and access datain relationto another piece of data in the database, e.g. foreign key constraints
2. designed to store structure data, usually organized as a set of tables with columns and rows

Question 2

What are 4 important aspects of a relational DB

Answer 2

• supports SQL
• data integrity
• supports transactions
• ACID compliance

Question 3

What’s ACID compliance (what does ACID stands for)

Answer 3

• atomicity
  
  • consistency
  • isolation
  • durability

Question 4

What’s data integrity (referring to one aspect of a relational DB, 4 bullet points)

Answer 4

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

Question 5

Name a few popular relational DB (no need to name all)

Answer 5

• MySQL
• PostgresSQL
• Oracle
• SQL server (microsoft)
• SQLite - popular open source SQL DB
• snowflake
• Amazon Aurora

Question 6

Explain the “A” in ACID

Answer 6

Atomicity - a transaction is either all or nothing

All parts of a transaction are executed completely and successfully, or else the entire transaction fails.

Question 7

Explain the “C” in ACID

Answer 7

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

Question 8

Explain the “I” in ACID

Answer 8

Isolation

• each transaction is independent of all other transactions
• critical to achieve concurrency control

Question 9

Explain the “D” in ACID

Answer 9

Durability
- All changes made in the transaction are permanently stored even if the DB fails

Question 10

Why is DynamoDB noSQL

Answer 10

DynamoDB is NoSQL in that it does not support SQL. Instead, it uses a proprietary API based on JSON.

Question 11

What’s a noSQL DB

Answer 11

• 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

Question 12

Give some examples of data that are better to be stored in noSQL DB

Answer 12

Loosely defined data like
- email
- videos
- images
- business documents

Question 13

What are several ways to create noSQL DB

hint - what are some ways other than “key value pairs” (4 bullets including key/value)

Answer 13

• key-value stores
• column-family data stores
• graph databases
• document databases

Question 14

Name some common noSQL DB

Answer 14

MongoDB
IBM Cloundant
Amazon DynamoDB
Apache Cassandra

Question 15

When to use SQL vs noSQL DB

Answer 15

1. structured or unstructured data? - noSQL can work with both
2. how much data are you storing - noSQL are better for larger vol of data than SQL DB
3. who’s managing your data - non professionals find SQL DB easier to work with (because they are more familiar with using SQL queries?)

Question 16

Advantages of using DDB (7 bullets)

Answer 16

• 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

Question 17

Optimistic vs pessimistic locking - How do they lock

Answer 17

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.

Question 18

What fields we can do optimistic locking on

Answer 18

Normally use the version field, but can use other fields such as timestamp, checksum, etc

Question 19

Pros and cons for pessimistic locking

Answer 19

• Con - could run into deadlocks
• Pro - better integrity than optimistic locking

Question 20

When do we use optimistic locking

Answer 20

Used in three-tiered architectures where application does not maintain DB locks as connections are taken from a pool.

Question 21

When do we use pessimistic locking

Answer 21

Either need a direct connection to DB or or an externally available transaction ID that can be used independently of the connection.