Lecture 9

Question 1

alias

Answer 1

An alias is created in SQL to give a column or a table a temporary name during a query (good for code readability)

SELECT column_name AS alias_name

Question 2

two main issues normalisation reduces

Answer 2

Redundant data

Anomalies (delete, insert and update irregularity, that generates the inconsistent data)

Question 3

When normalising a database you should achieve four goals:

Answer 3

• Arranging data into logical groups (such that each group describes a small part of the whole)
• Minimizing the amount of duplicated data
• Access and manipulate the data quickly and efficiently (without compromising the integrity of the data storage)
• Organising the data such that, when you modify it, you make the changes in only one place

NOTE: Database designers refer to these goals in terms such as data integrity, referential integrity, or keyed data access

Question 4

Un-normalised data

Answer 4

repeating groups, inconsistent data, delete and insert anomalies

Question 5

Can you have an M relationship in relational database?

Answer 5

Short answer: NO, you need to break it down => normalised

Question 6

4 types of NoSQL databases

Answer 6

• Key-value databases
• Column oriented databases
• Document oriented databases
• Graphs databases

Question 7

Key-value DB

Answer 7

Amazon

store data in a simple <key> : <value> format</value></key>

where key is used to retrieve the value from the table

The table contains many key spaces and each key space can have many identifiers to store key value pairs

Question 8

Column-oriented DB

Answer 8

Google

It extends the previous model by allowing adding more columns to have wider table

Since the table is going to be very broad, it supports to group the column with a family name, call it “Column Family” or “Super Column”

Question 9

Document-oriented DB

Answer 9

supports storing semi-structured data

The unit of data is called a “Document”
The table which contains a group of documents is called as a “Collection”

Each document might contain a XML or even a Word Document

Document databases are suitable for web-based applications

Question 10

Graph DB

Answer 10

highlighting the connection between the data

storing and performing data manipulation operations on nodes, relations and attributes of nodes and relations

the more relations the better

no foreign keys needed

Advantages

• Better Performance: if data volume increases in a relationship DB the data increases (e.g. more foreign keys), while in the graph DB the connections stay the same

Flexibility: no complete model needed, you can add as you go along

Agility: aligns well with agile software development as they are incremental

Question 11

pros of NoSQL

Answer 11

highly scalable,

provide better performance,

designed to store and process a significant amount of unstructured data at a speed 10 times faster than SQL one,

high availability and strong fail over capabilities

Question 12

cons of NoSQL

Answer 12

There is no standard with the API that comes with the DB and each vendor has its own (while SQL is more stable)

Not all NoSQL DB support JOIN operations, these are core commands only in SQL DB (e.g. MongoDB does not have JOIN)

Weak security - this is an issue in any type of DB, however NoSQL security is very weak, Authentication and Encryption are almost non-existant, or weakly implemented

Question 13

scalability: NoSQL vs. SQL

Answer 13

SQL vertically scalable – performances can be increased on a single server by adding better hardware power, e.g. better CPU, more RAM

NoSQL horizontally scalable: need more servers to handle traffic

Question 14

Choosing SQL or NoSQL:

• matury
• compatibilty
• cost-effective
• flexibility

Answer 14