Module 12 - Data Storage Design

Question 1

What are the 2 basic types of data storage?

Answer 1

Files
Databases

Question 2

What are files used for?

Answer 2

Optimizing a specific transaction:
Master files
look-up files
transaction files
audit files
history files

Question 3

What are databases for?

Answer 3

Databases are for storing a collection of files

Question 4

What is a relational database?

Answer 4

A database that uses primary keys and foreign keys. It is a collection of tables.

SQL is used for CRUD

The DMBS ensures referential integrity i.e. all foreign key elements cannot be null.

Question 5

What is a multidimensional database?

Answer 5

Used in data warehousing (collecting data from a wide range of sources)

Allows facts to be aggregated quickly on various dimensions.

Question 6

What is a NoSQL Database?

Answer 6

Think of firebase, collections -> documents

Wide column stores

Question 7

File strengths and weaknesses:

Answer 7

Good performance
Good for short term data storage
Redundant data; data must be updated using programs

Question 8

What is an advantage and disadvantage of Legacy databases?

Answer 8

Very mature products
Outdated, not efficient, limited future

Ouch.

Question 9

Relational database pros and cons

Answer 9

Leader in database market.
Uses SQL.
Lower level database with flexibility, the opposite of a no SQL database that is more abstracted.

Cannot handle complex data

Question 10

Object databases pros and cons

Answer 10

Can handle complex data.
Limited market

Question 11

Multidimensional database pros and cons

Answer 11

Can provide business facts quickly.
Highly specialized use.

Question 12

NoSQL database pros and cons

Answer 12

Good for huge varied datasets.
New in the market
Specialized use

Question 13

Entities –> tables

Answer 13

Each row represents a single entity instance

Each column represents an attribute

All values in a column have the same data format.

Each column has a range of values knows as the attribute domain

The order of rows and columns do not matter

Each table must have a primary key

Question 14

What is a primary key?

Answer 14

An identifier composed of one or more attributes.

A table can have multiple candidates for a primary key.

Question 15

1:M Relationship using PKs and FKs

Answer 15

Put the PK of the 1: in the table of the :M as a foreign key on the far left

You can do this because every instance of the many will refer to the one and only entity.

Question 16

What is a foreign key?

Answer 16

The primary key of an outside table to create a COMMON ATTRIBUTE (the FK).

Question 17

Referential Integrity

Answer 17

Every reference to an entity instance through an FK is technically valid, it can be either null or a valid value.

Null values should be avoided as much as possible

This can happen in relationships where there can be one or no entities.

Question 18

1:1 Relationship with PKs and FKs

Answer 18

We add the FK in the middle of the table where it makes sense

Question 19

M:N Relationship with PKs and FKs

Answer 19

Relationships are implemented through common attributes, what common attributes should we define for M:N?

We create an intersection or bridge entity, then we add the FKs to it on the far left.

I think this effectively creates two 1:M relationships

Question 20

Why should null values in a database be avoided?

Answer 20

Because they threaten data integrity, think of comp 1800 and missing images.

Question 21

What is the best way to optimize data storage for efficiency?

Answer 21

Normalization

Question 22

What is denormalization?

Answer 22

Adding back redundant attributes to avoid aggregation (combing two tables together to access values from multiple tables)

Question 23

What is indexing

Answer 23

Similar to a library catalogue, index keys POINT to table rows.

Indexing requires overhead, they need to be updated whenever data is inserted, deleted, or changed.