Database Design

Question 1

WHat is meant by business data being:
Valuable
Persistent
Protected

Answer 1

Valuable; forms the core of main business operations
often provides commercial edge

PErsistent; must survuve many executions of program
must be accessed by many programs

Protected; must be kept safe from system failure
must be kept from unauthorised access

Question 2

How do we store programs in memory? data structure? issues?

Answer 2

arbitrary connected graph
may be extended dynamically
navigation by object reference

objects may have ‘rich’ data type

Question 3

How do we store database files on a disk? data structure

Answer 3

Simple data types
fixed strucutre
data tables have fixed width
navigation by key values

Question 4

What is the original purpose of relational databases?

Answer 4

to eliminate redundancy

to minimize dependancy between data items

Question 5

In what way do we navigate through database tables?

Why?

Answer 5

from many (foreign key) to one (primary key)

otherwise we would have to change the size of the foreign key field

Question 6

Briefly describe relational data analysis

Answer 6

Set of rules for transforming arbitrary data tables into normal form based on analysis of attribute dependency

Question 7

Brielfy describe entity relationship modelling for data normalization

Answer 7

diagram based technqiue based on simplfying relationships and multiplicities

Question 8

Breifly describe event driven design

Answer 8

constructs data model in 3nf normal form

event table & graph building technique linking according to existence dependency

Question 9

When minimizing data dependency, what are our design goals?

Answer 9

+Remove duplicated data
+remove redundant paths
+optimise table structure for inserting, updating and deleting single data items

Question 10

When minimizing data dependency, what is the minimization technique?

Answer 10

+ merge 1:1 tables
+ link 1:M tables from many to one
+ introduce linker tables for M:N relationships
+ find redundant search paths and remove the shorter path

Question 11

What is an involuted association? How is it normalized?

Answer 11

association linking a table to itself

involuted linker; only place where we allow a 1:1 relationship to still exist

Question 12

What is a ternary assocation? How is it normalized?

Answer 12

Highr order association linking 3 tables

need a ternary linker; don’t require all three primary keys to become pfk
- usually want to avoid, better to replace with 3 binary associations adn remove redundant path

Question 13

What is the traditional method of data normalization?

Answer 13

• break up lists by replicating atomic data in many rows
• split into tables to remove replicated data
• make sure non-key attributes depend fully on the key

Question 14

What is required for 1NF?
2NF?
3NF?

Answer 14

1NF:
atomicity rule
every row has a primary key

2NF:
nonkey attributes has full functional dependence on the primary key

3NF:
attributes dont depend transitively on the primary key

Question 15

What to check when looking at database design?

Answer 15

Insertion anomaly: cant insert X until we have inserted Y

Update anomaly: update affects some but not all rows

Deletion anomaly: delete X has also deleted Y

Question 16

Why might we want to denormalize data?

Answer 16

to increase the efficiency of searches

to reduce the fragmentation of data

Question 17

What is the multiplicity of a generalisation?

Answer 17

0:1 to 1
when 0:1 is subclass
              1 is superclass

Question 18

When looking at denormalization; what are the costs of reducing the number of tables?

Answer 18

wasted space through null values (generalisations)

replicate part data many times (compositions)

Question 19

When looking at denormalizations, what are the costs of optimzing access paths?

Answer 19

(retain redundant paths) additional foreign keys, risk of inconsistency