Databases

Question 1

What is a database?

Answer 1

An easier way to store data for humans to read.

They are used by organisations to CRUD:

Create
Retrieve
Update
Delete

Question 2

Why do organisations use them?

Answer 2

Security - manage who has access & what type

Size/scalability

Accuracy - create rules so incorrect data / data formats cannot be entered

Redundancy - having multiple copies

No clashing - can lock others out so two people can’t edit the same thing at the same time (reduce risk of error)

Question 3

What is a relational database

Answer 3

A type of database that maps data via the relationships between different tables.

Question 4

What are used to map this data?

Answer 4

Primary keys & Foreign keys

Question 5

What is the benefit of using a combination of relational & non relational databases

Answer 5

Non relational are better at ingesting a lot of data fast (hot data). Therefore companies can use this type of database to capture a lot of data at once, then move it to a relational database later on to do something with that data that is better for analytics

Question 6

What is a non-relational database?

Answer 6

Good for key value pairs.

E.G. given x ID give me that value.

Question 7

What is ACID compliance?

Answer 7

A set of properties that guarantees database transactions are processed RELIABLY.

Guarantees validity even in the event of errors, power failures etc.

Atomicity - the “all or nothing” rule — the transaction either happens completely or doesn’t happen at all

Consistency - data is consistent before and after a transaction without any missing steps. e.g. if you don’t have 10 inserts for the 10 columns, the query will fail.

Isolation - multiple transactions can happen at the same time creating incorrect data in the database

Durability - transactional success is robust to system failure, e.g. if a database turned off and then got turned off a day later, all logs would still be there.

Question 8

What is an ERD / what does it stand for?

Answer 8

Entity Relationship Diagram.

A way of drawing out the structures in a database and the relationships between the tables.

Question 9

What are the different types of database relationships?

Answer 9

One-to-One

One-to-Many (most common)
- e.g. one user has many tasks (to do app)

Many-to-Many

Question 10

How did the one to many ERD look for your database?

Answer 10

Table: User
userID (PK)
username

Table: Tasks
taskID (PK)
description 
completed
userId (FK) - this is what links the two database tables

Question 11

What are primary & foreign keys?

Answer 11

Primary Key = the unique id for a row within a database table (think uuid)
- every row in a relational database will have a primary key and it MUST be unique

The primary key constraints are used to enforce entity integrity.

Foreign Key = what relates the tables eg. user id in user & task

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values

Question 12

What is meant by a transaction?

Answer 12

A sequence of database operations that satisfies the ACID properties

Question 13

Why SQL?

Answer 13

Pros:
Great with relational data
Has a table structure
Been around for longer - reliable

Cons:
Inflexible
Requires a schema

Question 14

What is a database schema?

Answer 14

The structure of the database.

This is usually done ahead of creating the database, as SQL works best when you have a solid idea of what your tables will l
look like & the relationships between them.

Question 15

Why NoSQL? E.G. MongoDB

Answer 15

Pros:
More flexible to change
Scales horizontally
Has a document structure

Cons:
Not great with complex relationships
It’s new so is subject to lots of changes

Question 16

How do you insert data into your table?

Answer 16

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3)

Note: if you are entering data for all columns, you don’t need to specify them in the first line

Question 17

How do you create a table?

Answer 17

CREATE TABLE  products (
id INT NOT NULL,
name STRING,
price MONEY,
PRIMARY KEY (id)
)

Question 18

What does SELECT * FROM ‘products’ mean?

Answer 18

It would show you all data in your table.

Question 19

What does the WHERE keyword do?

Answer 19

Allow us to select the pieces of data we need by defining a criteria

E.G. WHERE id = 1