SQL, NOSQL databases

Question 1

What are 7 advantages of using SQL

Answer 1

• Relational data model (tables of data that are organised and related based on PK and FK relationships, nomalisation is used for tables 1NF, 2NF, 3NF, 4NF)
• Strong data integrity (constraints on the data means it is consistent, ACID)
• Retrieve and manipulate data in different ways
• Widely used and supported - many resources available
• Can be scaled up to handle large data sets and designed to work well in high-performance environments
• Long history of development, deployment and security so it is robust and secure

Question 2

What are 6 disadvantages of SQL

Answer 2

• Can be expensive to implement, maintain and scale (extra hardware)
• Performance declines as data grows
• Rigid data structure makes it difficult to accommodate changing requirements
• Queries can be complex and difficult to write
• Doesn’t support unstructured data ie., graphs, images, videos
• Security concerns ie., injection attacks

Question 3

What are the advantages of NOSQL

Answer 3

• Scalable and able to process large amounts of unstructured or semi-structured data
• Flexible because it can be easily changed without having to change the underlying schema
• Fast read and write performance
• Typically cheaper than relational databases because don’t require complex set up and hardware

Question 4

What are the cons of NOSQL?

Answer 4

• Unstructured making it difficult design, implement and maintain consistency across data
• Can be complex to scale
• Doesn’t support transactions so difficult to guarantee consistency
• No structures like joins and foreign keys can make it difficult to work with complex data structures and queries

Question 5

What is a database

Answer 5

A database is permanent storage

Question 6

What is a cache?

Answer 6

A cache remembers the result of an expensive operation to speed up rears

Question 7

What is batch processing?

Answer 7

Batch processing is periodically crunching a large amount of data

Question 8

Give an example of a NOSQL database

Answer 8

MongoDB is a no sql database that supports scaling out across regions and across multiple servers

Question 9

Describe 3 features of NOSQL databases

Answer 9

Flexible schemas
Unstructured
Fast queries due to the data model

Question 10

What are 2 main types of NOSQL databases?

Answer 10

Document databases
Graph databases

Question 11

What is a document database?

Answer 11

A document database stores objects similar to JSON objects.
Each document contains pairs of fields and values.

Question 12

What are graph databases?

Answer 12

A graph database stores data in nodes and edges

Question 13

What is a DDL?

Answer 13

Data definition language (DDL) describes the portion of SQL that creates, alters, and deletes database objects.

Question 14

What is a DML?

Answer 14

A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.

Question 15

What are database design principles?

Answer 15

ACID

atomicity - if one part fails, all fails
consistency - data integrity
isolation - not changed by another transaction
durability - changes persisted permenantly