Chapters 1&2 Knowledge Testers

Question 1

Edgar Codd?

Answer 1

Introduced Data Independence -> revolutionized data storage
did work on relational algebra

Question 2

Data Independence

Answer 2

Seperation of physical and logical representation of data<br></br>Make physical simple and clear for human understanding

Question 3

Data Shapes

Answer 3

trees, cubes, tables, vectors (text), graphs

Question 4

Data Model

Answer 4

What data looks like and what you can do with it
How much data? What shape? How data is organized?

Question 5

Table Synonyms

Answer 5

Collection, Relation, Relational Table

Question 6

Row Synonyms

Answer 6

Business Object, Item, Entity, Document, Record, Tuple

Question 7

Attribute Synonyms

Answer 7

Column, Field, Property, Key

Question 8

Primary Key Synonyms

Answer 8

Row ID, Name, Key

Question 9

Value Synonyms

Answer 9

Scalar, Cell, Characteristic, Fact

Question 10

Relational Tables have

Answer 10

set of attributes (schema) and set/bag/list of tuples

Question 11

Atomic Integrity

Answer 11

All values are atomic (string, number), NOT ARRAY

Question 12

Relational Integrity

Answer 12

all its records have identical
support. All elements have all attributes

Question 13

Sketch the history of databases (ancient and
modern) to a colleague in a few minutes?

Answer 13

DNA - first data storage
Brain - First human controlled data storage
Humans told stories->ISSUE: not reliable, story changes over time
Writing - clay tablets - tables -> ISSUE: how to make copies??
Printing Press-> easily make copies and mass produce/distribute
Computers

Question 14

Difference between data, information and knowledge

Answer 14

Data -> numbers<br></br>Information -> Meaning from data, processed data<br></br>Knowledge -> meaning from information, interpreting information

Question 15

How can structured data can be characterized?

Answer 15

Order and organization

Question 16

Do you know the standard prefixes of the International System of
Units (when the exponent in base 10 is a positive multiple of 3)?

Answer 16

Karl Marx gave the proletariat eleven zeppelins, yo
Kilo, Mega, Giga, Tera, Peta, Exa, Zeta, Yotta, Ronna, Quetta

Question 17

4 technologies commonly referred to as
NoSQL

Answer 17

key-value, document, column family, graph

Question 18

3 Vs

Answer 18

Volume - Amount of data
Velocity - Capacity, latency, throughput
Variety - Shapes

Question 19

Define capacity, throughput and latency with units

Answer 19

Capacity: how much data per volume (bytes)
Latency: Wait time to read data (miliseconds)
Throughput: Data read per time (byte/sec)(Not sure what is standard units)

Question 20

Can you explain why and how the evolution of capacity, throughput
and latency over the last few decades has influenced the design of
modern database systems?

Answer 20

Capacity expanded a lot more than the other 2. Need to use parallelization and batch processing to improve latency and throughput (scale out)

Question 21

Scale out vs Scale up

Answer 21

scale out - more machines
scale up - more powerful machines

Question 22

Name a few big players in the industry that accumulate and
analyze massive amounts of data?

Answer 22

S3, Azure

Question 23

bit vs byte

Answer 23

a bit is 0 or 1, a byte is a collection of 8 bits

Question 24

Name a few concrete examples that illustrate the various
orders of magnitude of amounts of data?

Answer 24

Files Kb, Movies Gb

Question 25

Why is it important to consider whether a use case is read-intensive, or write-intensive, or in-between?

Answer 25

Guess:Read intensive -> benefit from denormalized data to reduce query complexity and latency Write intensive -> benefit from normalized data to avoid redundancy (redundant writes) and maintain data integrity

Question 26

Why normal forms are important?

Answer 26

Can prevent deletion, insertion and update errors

Question 27

first normal form in simple terms?

Answer 27

Atomic integrity -> all values are atomic (simple) -> no nesting

Question 28

Describe in simple terms how higher normal forms (like Boyce-Codd) are related to joins?

Answer 28

NF are like opposite of joins - seperate big table into small tables

Question 29

Why is it common, for large amounts of data, to drop several levels of normal form, and denormalize data instead?

Answer 29

GUESSING: preventing expensive joins, simpler queries

Question 30

Declarative language

Answer 30

User specifies what they want - not how to compute it - up to system to figure out how to execute the query

Question 31

Functional language

Answer 31

Nesting - expressions can nest in each other Queries are like lego - building blocks - you can change order and such But changing order can change outcome

Question 32

Why design query languages that are declarative and functional?

Answer 32

GUESS: declarative: focus on what rather than how (easy for users, leave how to machine) functional: modularity, can move around pieces

Question 33

Describe the major relational algebra operators: select, project, aggregate, sort, Cartesian product, join?

Answer 33

Select -> Choose rows Project -> Choose columns Aggregate -> Combine - group by cols -> aggregate other columns Sort -> order by a specified column value Cartesian Product -> multiply tables (all rows) Join -> multiply tables based on certain matching values eg A=B

Question 34

The names of the basic components of the tabular shape at an abstract level (table, row, column, primary key) as well as the names of the most common corresponding counterparts in the NoSQL world?

Answer 34

Relation/Collection, Record, Attribute/Field, Id

Question 35

ACID

Answer 35

Atomicity, Consistency, Isolated, Dependable A - either an update (called a transaction if it consists of several updates) is applied to the database completely, or not at all; C - before and after the transactions, the data is in a consistent state (e.g., some values sum to another value, another value is positive, etc); I - the system “feels like” the user is the only one using the system, where in fact maybe thousands of people are using it as well concurrently; D - any data written to the database is durably stored and will not be lost (e.g., if there is an electricity shortage or a disk crash).

Question 36

Describe the following SQL terms: SELECT, FROM, WHERE, GROUP BY, HAVING, JOIN, ORDER BY, LIMIT, OFFSET

Answer 36

Select - projection - selecting columns from - table where - selection of certain rows group by - aggregate information having - selection join - combine 2 tables order by - sorting limit - number of rows to display eg. 10 rows offset - start from nth row