Module 6

Question 1

Data

Answer 1

Raw facts and figures without any meaning.

Question 2

Information

Answer 2

Data shaped into a meaningful form through context

Question 3

Knowledge

Answer 3

Results from experience and education. The info collected by an entity and the ability to locate additional info

Question 4

DNA of a Tweet

Answer 4

• data on hashtags used and users mentioned
• geo-data
• data on videos and pictures
• user-data

Question 5

Relational Database

Answer 5

Database organises data into one or more tables. Tables consist of columns called attributes or fields and rows called tuples or records.

Question 6

Keys

Answer 6

Unique Primary Key identify each row. Keys can be used to link different tables.

Question 7

SQL

Answer 7

Standard Query Language is the defacto standard language used to communicate with database systems. We can create, delete and manipulate databases using SQL.

Question 8

Categories of commands in SQL

Answer 8

• DDL data definition language: creating, deleting, altering databases and tables
• DML data manipulation language: inserting, deleting, changing, retreiving tuples
• DCL data control language: access management

Question 9

SQLite

Answer 9

SQLite stores the whole database in a single file. Once you have access to the file, you have access to the database. DCL is not part of SQLite

Question 10

Creating tables

Answer 10

CREATE TABLE Table Name (Column1 DataType (optional length), Column2 DataType (optional length),… PRIMARY KEY (KeyColumn1, KeyColumn2)

Creates a table and declares a composite primary key across multiple columns.
You can’t have spaces in table name and column names unless you put [].

Question 11

Data Types

Answer 11

TEXT: character string
INTEGER/INT: signed integer
BLOB: binary large object, videos?photos stored in binary language
REAL: floating point numbers
NUMERIC/NUM: anything not covered by the rest, includes BOOLEAN, DATE, DATETIME

Question 12

Redundancy

Answer 12

Redundancy=repetition should be avoided in databases since it:
- Increases the required amount of storage
- Is a source for data inconsistencies
BUT: it can improve databases since the join operator becomes uneccessary

Question 13

Data Normalization

Answer 13

Normalization is a technique that manages the trade-off between redundancy and ease of use/speed of database. It is a standardized technique that transforms a database iteriatively accordingly to a strict set of rules.
A database satisfies a normal form, if all tables in that database satisfy the normal form.

Question 14

First Normal Form 1NF

Answer 14

1. The domain (space from wich attributes take their value from) of each attribute contains only atomic values. Atomic values: values that can’t be split into multiple logically distinct parts
2. Each attribute contains only one value from the domain.
   After first normalization redundancies remain

Question 15

How to solve when there is multiple players with the same name?

Answer 15

a) create a composite key (player, date of birth)
b) create a unique player ID

Question 16

Second Normal Form

Answer 16

1. It satisfies 1NF
2. In case there is a composite key there are no partial dependencies (a non-key attribute always depends on all components of the key and not just a subset.
   [place depends on tournament, but not on year, it only depends on part of the key, 2NF not satisfied]

Question 17

How to solve partial dependencies?

Answer 17

a) we can create a new column that provides an artificial key to the table such as ID (the primary key consists of a single value so 2NF is satisfied)-> doesn’t reduce redundancy
b) we split the table into 2 (with the second table containing the tournament information)-> reduces redundancy

Question 18

Third Normal Form

Answer 18

• It satisfies 2NF
• There are no transitive dependencies (attributes may not be functionally dependant on other non-key attributes)
  E.g: Country and date of birth are both dependant on player, which is not a primary key. They are only linked to the primary key because the player is linked to it. (this is called transitive dependency)

Question 19

How to solve transitive dependencies?

Answer 19

By splitting the table (with the second table containing player info)

Question 20

Results of Normalization

Answer 20

• Less suceptible to human error
• More complex SQL queries to join the tables to produce a complete statement
  Normalization also resolves many-to-many relationships between entity sets

Question 21

Entity Relationship Models

Answer 21

Entity Relationship Models describe the relationship between things of interest. They are visualized as Entity Relationship Diagrams. ERD

Question 22

Components of ERD

Answer 22

• Entities are things of interest to the organization, may be physical or immaterial concepts.
• Attributes describe an entity (chracteristics of a person may be gender, age, name…)
• An entity set corresponds to the table notion, while an entity corresponds to a row,

Question 23

Relationships and Cardinalities

Answer 23

Relationships explain the link between two entities. Cardinalities refer to the number or entities in one entity set that are related to entities in another entity set.

Question 24

Types of Cardinalities

Answer 24

1. One-to-one (1:1): Exactly one entity in set A is linked to one in set B. These are very rare as they usually get combined in one entity set.
2. One-to-many (1:M): One entity in set A is linked to many in set B
3. Many-to-many (M:N): Many entities in set A are linked to many (not always the same nr.) entities in set B. These are eliminated through normalization.

Question 25

Optional Relationship

Answer 25

One or both ends of the relationship can have zero entities. Optionality refers to the minimum nr.of cardinalities (1 or 0)

Question 26

Upper cardinality

Answer 26

Or multiplicities are about the maximum number of cardinalities.
E.g: Not all employees are given a car.
Not all players have won a tournament. (but every tournament needs a champion, so this is not optional).

Question 27

Sets & Keys

Answer 27

• an entity set contains all entities of a similar kind
• a relationship set contains all relationships of a similar kind
  To uniquely identify each entity in an entity set we need a key.
• A key is an attribute that uniquely identifies each entity in an entity set, respectively each record

Question 28

Phases of an ERD

Answer 28

• Conceptual model: identify the things of interest (entities)
• Logical model: identify the attributes and keys, decide the data type for each entity
• Physical model: database proof, no many to many relationships, implementable

Question 29

How to solve M:N relationships?