Databases

Question 1

What is a database?

Answer 1

An organized collection of structured information or data, stored electronically

Question 2

What was the first kind of database?

Answer 2

Navigational Database

Question 3

What is the query language for navigational databases?

Answer 3

CODASYL (Conference/Committee on Data Systems Languages)

Question 4

What is the primary benefit of a navigational database over reading data sequentially?

Answer 4

Navigational databases allow to navigate directly from record to record following relations

Question 5

Who invented relational databases?

Answer 5

Dr. Edgar Codd

Question 6

What is the entity-relationship model used for?

Answer 6

To model data in databases using relationships and to help navigating a database

Question 7

What are the three subcomponents of a relationship in ER models?

Answer 7

Entities, Atributes and Relationships

Question 8

How many cardinality types an entity-relationship can have?

Answer 8

4:
Zero or one
Exactly One
Zero or More
One or More

Question 9

What is the sign of a mandatory one (exactly one) cardinality type in crow’s foot notation?

Answer 9

||

Question 10

What is the sign of an optional one (zero or one) cardinality type in crow’s foot notation?

Answer 10

o|

Question 11

What is the sign of a mandatory many (one or more) cardinality type in crow’s foot notation?

Answer 11

|{ or }|

Question 12

What is the sign of an optional many (zero or more) cardinality type in crow’s foot notation?

Answer 12

o{ or }o

Question 13

What is the data type for missing data in a relational database?

Answer 13

null

Question 14

What is the query language used for relational databases?

Answer 14

SQL

Question 15

What does DDL stand for?

Answer 15

Data Definition Language

Question 16

What does DML stand for?

Answer 16

Data Manipulation Language

Question 17

What does DCL stand for?

Answer 17

Data Control Language

Question 18

How do you create a table in SQL?

Answer 18

CREATE TABLE a (a INTEGER);

Question 19

How do you read data from a table in SQL?

Answer 19

SELECT * FROM a

Question 20

How do you add data to an SQL table?

Answer 20

INSERT INTO a (a)
VALUES
(1),
(2);

Question 21

How do you delete data from an SQL table?

Answer 21

DELETE FROM a WHERE a=1

Question 22

How do you modify data in an SQL table?

Answer 22

UPDATE a SET a=”new” WHERE a=1

Question 23

How do you investigate what a query actually does in SQL?

Answer 23

EXPLAIN QUERY PLAN SELECT * FROM a

Question 24

What is an attribute in SQL?

Answer 24

An attribute is whats commonly refered to as a column in a table and stores one single datapoint per entry in the Database

Question 25

What is a tuple in SQL?

Answer 25

A tuple is whats commonly refered to a row in a table, it is an individual entry in the Database

Question 26

What is a primary key in SQL?

Answer 26

A Primary key is a unique identifier with which an entry can be identified

Question 27

What is a composite key in SQL?

Answer 27

A composite key is a combination of two or more Primary and Foreign keys in a joining table used to secure the uniqueness of entries into the joining table

Question 28

What is a candidate key in SQL?

Answer 28

A candidate key is a natural, domain or business key. The Candidate key is unique, might be primary and can be null, which is why it cant be a primary key

Question 29

What are the two criteria of the first normal form?

Answer 29

- Contains only atomic values | - There are no repeating groups

Question 30

How many join types the ANSI SQL standard have?

Answer 30

5: | INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS

Question 31

What does a CROSS JOIN of two tables return in set theory terms?

Answer 31

A Carthesian Product

Question 32

What does an INNER JOIN of two tables return?

Answer 32

Returns the set of records that match in both tables

Question 33

What does a FULL OUTER JOIN of two tables return?

Answer 33

Returns every record from all tables, if no matching record can be found to a record it will be matched with a null value

Question 34

What concepts a simple key-value store has to store data?

Answer 34

what? (maybe key, value)

Question 35

What are the three concepts of a graph database?

Answer 35

Nodes, Edges and Properties

Question 36

What does OLTP stand for?

Answer 36

Online Transaction Processing

Question 37

What does OLAP stand for?

Answer 37

Online Analytical Processing

Question 38

What does ACID stand for?

Answer 38

Atomicity, Consistency, Isolation, Durability

Question 39

What are the two most popular data storage options?

Answer 39

Row oriented and Collumnar oriented Data Storage

Question 40

What are the advantages of row-oriented data storage?

Answer 40

- Easy to insert a new row - Transactions are concerned about a single row - Row level security can be used with this design

Question 41

What are the disadvantages of row-oriented data storage?

Answer 41

- Reading an entire row while only needing a single collumn

Question 42

What are the advantages of columnar-oriented data storage?

Answer 42

- Reading a single column results in reading the minimum amount of data - Advanced techniques can be applied using the columnar aproach

Question 43

What are the disadvantages of columnar-oriented data storage?

Answer 43

- Hard to update | - Hard to insert into

Question 44

Which data storage is better suited for OLTP use cases?

Answer 44

Row Data Storage

Question 45

Which data storage is better suited for OLAP use cases?

Answer 45

Collumnar Data Storage

Question 46

What are the three letters stand for in CAP theorem?

Answer 46

Consistency, Availability, Partition Tolerance

Question 47

What does ETL stand for?

Answer 47

Extract, Transform, Load