Lecture 4: Database Design

Question 1

What can speed up retrieval of data?

Answer 1

Keys and indexes

Question 2

What to consider designing a database?

Answer 2

– Inclusion of all information
– Selection of correct datatypes
– Creation of keys / indexes

Question 3

What is normalisation?

Answer 3

Normalisation is the process of efficiently organising data in a database.

Question 4

Goals of normalisation:

Answer 4

– eliminating redundant data (storing the same
data in multiple tables)
– ensuring data dependencies make sense (only
storing related data in a table).

Makes updating DB more efficient

Question 5

How many NF are there and which is most common?

Answer 5

0-5 + BCNF
3NF

Question 6

Explain 1NF

Answer 6

• Eliminate duplicate fields from the same table
• Create separate tables for each group of
  related data
• Identify each row with a unique value (primary
  key)

Question 7

Explain 2NF

Answer 7

Removes more duplicate information
– There must not be any partial dependency of any column on primary key (bijv. the criminal data only depends on CaseID and not ID)

• The guidelines are:
  – Meet 1NF requirements
  – Remove subsets of data that apply to multiple rows of
  a table and place them in separate tables.
  – Create relationships between these new tables and
  their predecessors (foreign keys)

Question 8

3NF requirements

Answer 8

– Meet all 2NF requirements
– Remove fields that are not dependent upon
the primary key (bijv. street and city are dependent on zip code)

Often of third normal form it is said that
the data should “ … depend on the key,
the whole key, and nothing but the
key.”