Lecture 6

Question 1

Data

Answer 1

Raw facts and figures without any deeper meaning

Question 2

Information

Answer 2

Data shaped into meaningful form through context

Question 3

Knowledge

Answer 3

Results from experience and education

Question 4

What does a relational database do?

Answer 4

It organises data into one or more related tables

Question 5

What does a unique key do?

Answer 5

It identifies each row, and can be used to link different tables.

Question 6

Data redundancy

Answer 6

unnecessary repetiiton

Question 7

Why should redundancy be reduced? (2)

Answer 7

1. it increases the amount of storage required

2. It is a major source of data inconsistencies

Question 8

What is one benefit of redundancy?

Answer 8

It can improve database performance since there is not needs for joint operations

Question 9

What does database normalization do?

Answer 9

It manages the tradeoff between redundancy and performance (ease of use)

Question 10

Requirements for 1NF?

Answer 10

1. The domain of each attribute contains only atomic values

2. The values of each attribute contain only one value from that domain

Question 11

Requirement for 2NF?

Answer 11

1. Must satisfy 1NF
2. In the case of a composite key, there are no partial dependencies. ie. non-key attributes are dependent on all key attributes and not just a subset

Question 12

Requirement for 3NF?

Answer 12

1. Must satisfy 2NF
2. There should be no transitive dependencies. Attributes should not be functionally dependent on other non-key attributes.

Question 13

What is Entity relationship modelling (ERM)?

Answer 13

describes the relationship between entities (things of interest)

Question 14

Entity relationship diagrams are built on what four elemental concepts?

Answer 14

1. Entities
2. Attributes
3. Relationships
4. Cardinalities

Question 15

What are entities?

Answer 15

They are things of interest to an organisation

Question 16

What are attributes?

Answer 16

They describe the entity on question

Question 17

What are relationships?

Answer 17

They express that entities are linked together

Question 18

What are cardinalities?

Answer 18

They represent the number of occurrences within one entity that are associated with occurrences in another.

Question 19

What are the 3 types of cardinalities?

Answer 19

1. One-to-one: exactly one occurrence in entity A is associated with exactly one occurrence in entity B
2. One-to-many: Exactly one occurrence in entity A is associated with many occurrences in entity B
3. Many-to-many: Many occurrences in entity A are associated with many occurrences in entity B

Question 20

What is optionality (lower cardinality) and upper cardinality?

Answer 20

The minimum amount of occurrences, and the maximum amount of occurences

Question 21

What is an entity set and a relationship set?

Answer 21

It contains all entities of a similar kind / all relationships of a similar kind.

Question 22

What are candidate keys and a primary key?

Answer 22

There are often multiple possible keys, and the one post suited as the main reference is the primary key. The primary key is chosen from all candidate keys

Question 23

3 steps or constructing a data model and what do they entail?

Answer 23

1. Conceptual model: Identity all entities and relationships
2. Logical model: Identify the attributes of all entities and determine keys
3. Physical model: a database structure than can be implemented of a specific platform. All M:N relationships can been resolved through associative tables and normalisation has occurred.

Question 24

SQL: Data definition language (DDL) meaning?

Answer 24

creating, deleting, and altering, databases and tables (STRUCTURE). Important when working with ERD.

Question 25

SQL: Data manipulation language (DML) meaning?

Answer 25

retrieving, inserting, changing, deleting tuples/records (CONTENT)

Question 26

SQL: Data control language (DCL) meaning?

Answer 26

user and permission management (ACCESS)