Week 1 - ER Model

Question 1

What is a database?

Answer 1

Stores large amounts of data.

Question 2

From a software perspective, what can databases do?

Answer 2

It’s an expensive piece of software that provides the following functionalities:
To model data (relational data modelling, object oriented data modelling), access data, analyse data, store data and secure data

Question 3

From a software perspective, what can databases do?

6 things.

Answer 3

1) model data
2) access data
3) store (physically) data
4) secure data
5) maintain data consistency
6) optimize data access

Question 4

From a user perspective, what can databases do?

Answer 4

Provides a black box for users so that they can perform:

1) data modelling
2) work with SQL (a declarative programming language) to manage and query data. Declarative means we tell the DB what to do, not how to do it.

Question 5

If a user is using SQL to communicate with the database, what thing bridges the user to the rest of the system?

Answer 5

The application

Question 6

What does the parser component do?

Answer 6

checks for any syntax errors

Question 7

What does the optimiser component do?

Answer 7

Tries to find the best sequence of the underlying algorithms and how it’s going to solve the request from the user.

Question 8

What happens after the optimiser can found the best optimisation sequence to use?

Answer 8

After finding the best optimization sequence of the processing algorithms, then the system invokes all these processing algorithms with access to the computational resources: the CPU, the memory, the data files and so on so forth. Then with an interaction with the OS, our query is executed.

Question 9

What are the six steps that happen when a user writes a SQL query?

Answer 9

1) Application
2) parser
3) optimiser
4) processing algorithms
5) CPU/memory/data files/OS
6) Code generator

Question 10

What are the three families of data?

Answer 10

1) Structured data
2) Unstructured data (there is no meta-data to interpret the data)
3) Semi-structured data (XML/JSON)

Question 11

What are the two approaches to conceptual data modelling?

Answer 11

1) Entity Relationship (E-R) modelling

2) Relational Modelling (purely maths driven)

Question 12

What is a SQL schema?

Answer 12

A SQL schema is made when we take the relational model and write down specific statements written in SQL. Once the schema is created, we can start querying in SQL.

Question 13

What are the components of the ER model?

Answer 13

1) entities
2) attributes
3) relationships

Question 14

How do you identify an entity in the ER model?

Answer 14

Entities are represented in a box with rounded corners

Question 15

What is the instance of an entity?

Answer 15

It materialises an entity, e.g. “Chris” is an employee

Question 16

How do you represent an attribute in the ER model?

Answer 16

Attributes are represented in an oval shape.

Question 17

What instantiates an attribute in the ER model?

Answer 17

A value.

e.g. name = “Chris”

Question 18

After we have instantiated an attribute by assigning it a value in the ER model, what do we get?

Answer 18

A tuple. A bunch of values where each value corresponds to a specific attribute.

Question 19

How are relationships in the Entity Relationship model represented?

Answer 19

We represent relationships between entities in diamonds. Roles are optional and are the noun for the verb relationship. They’re written on the line.

Question 20

What does relationship cardinality mean?

Answer 20

It’s the number of instances that an entity participates in a relationship.

E.g. if entity A = manager
if entity B = project
The relationship cardinality is 1 to 1, because a manager can only manage one project.

Question 21

What are four types of relationship cardinality?

Answer 21

1:M one to many
M:1 many to one
1:1 one to one
M:N many to many

Question 22

What happens when we split a many to many relationship?

Answer 22

N:M relationship always splits into two one-to-many (1:N and M:1) relationships, by transforming the original one into a new Relationship Entity.