Week 1

Question 1

What are the two types of data?

Answer 1

Unstructured, and structured, although much of the data is something in between

Question 2

What is structured data?

Answer 2

Data that can be represented as a collection of tables, with each column describing a different attribute of the data.

Question 3

What is the conceptual definition of a database?

Answer 3

A database is a collection of information/data that exists over a (very) long period of time

Question 4

What is a DBMS?

Answer 4

A Database Management System is a tool for creating an managing large amounts of data

Question 5

Wat is isolation?

Answer 5

No unexpected interactions between actions of different users

Question 6

What is atomicity?

Answer 6

An action is completed or not, not half completed

Question 7

What are the types of users of a DBMS?

Answer 7

1. Conventional users, ask for data and/or modify it

2. Database administrators, responsible for the structure of the data

Question 8

What is a query compiler?

Answer 8

It translates and optimizes the query into something i.e. a set of commands that can be executed

Question 9

What is a transaction manager?

Answer 9

It groups queries or other DML actions into a transaction and makes sure the transaction is either:

1. Completed in full, or not at all
2. Executed in a way that it would be executed when no other transaction would be executed at the same time
3. Logged properly, so the effect of the transaction is never lost (durability)

Question 10

What is ACID?

Answer 10

A = atomicity
C = consistency
I = isolation
D = durability

Question 11

What does the data model consists of?

Answer 11

1. Structure (numbers, strings, arrays, …)
2. Operations on data: any operations that can be programmed
3. Constraints on data: e.g. must be positive, must be multiple choice.

Question 12

What are tuples?

Answer 12

• Can be considered as a row in a table
• Relations are sets of tuples, as the order of tuples is irrelevant
• A tuple has one component for each attribute of the relation

Question 13

What is a domain?

Answer 13

• Each component must be atomic (thus a integer, or a string, cannot be broken up in smaller components)
• Each attribute has a domain: a particular elementary type (a year is an integer)

Question 14

What is a Schema?

Answer 14

• Defines name of relation R and its attributes A1, A2, …, An as R(A1, A2, …, An)
  E.g. Movies(title, year, length, genre)
  You can also define a domain:
  Movies(title:string, year:integer, length:integer, genre:string)
• Attributes form a set, the order is irrelevant
• However we follow the order of the schema

Question 15

What are keys?

Answer 15

A key of a relation is a set of attributes of that relation for which no two tuples are allowed to have the same components across all attributes in the key, usually this is an ID

Question 16

What are the 3 different relations in SQL?

Answer 16

Table, views, temporary tables

Question 17

How to create a table in SQL?

Answer 17

CREATE TABLE TableName (
    string CHAR(10),
    integer INT,
    bool BOOLEAN,
    float FLOAT,
    double DOUBLE,
    date DATE,
    time TIME
);

Question 18

How to delete a table in SQL?

Answer 18

DROP TABLE TableName;

Question 19

How to alter a table

Answer 19

ALTER TABLE TableName DROP RowToDelete;

ALTER TABLE TableName ADD RowToAdd CHAR(10);

Question 20

How to set default value in SQL?

Answer 20

i.e.
CREATE TABLE TableName(
default CHAR(100) DEFAULT ‘default’
);

Question 21

How to declare a key?

Answer 21

Add PRIMARY KEY to the end i.e.
name CHAR(30) PRIMARY KEY

or add it at the end
PRIMARY  KEY (name, otherValueIfYouWant)

You can also set a UNQIUE

Question 22

What is the difference between PRIMARY KEY and UNIQUE?

Answer 22

Primary key does not allow NULL.