Databases:

Flat-File Databases:

• One table

• No foreign keys

• Less secure

  • No User-Access Control (UAC)

• Harder to change data

• Easier to code (simple)

• Easier to implement

• Slower

  • All data has to be loaded at once

• More likely to have duplicates

Relational Databases:

A type of database with 2 or more tables linked via relational data (such as primary and foreign keys)

• Multiple tables

• Foreign keys

• More secure

  • UAC

• Easier to change data

• Harder to code

• Harder to implement

• Quicker

  • Only loads data when it is needed

• Less likely to have duplicates

Keys:

• Primary key: a unique identifier

• Foreign key: a primary key from another table

• Secondary key: a non-unique key from another table

Normalisation:

• The process of organising data by -

  • Eliminating redundancy

  • Ensuring data is stored in the correct table(s)

  • Eliminating the need to restructure a database when data is added

• Database does not have redundant data, so is smaller

• Faster to run a query (less data to search through)

• Less likely to have mistakes - less duplicates

• One update applies for linked tables, so no repeat updates needed

• Making data atomic can be counter-productive

• Can cause more tables than an unnormalised database

• As a database has more tables and becomes more complex, it takes longer to run queries

• More relationships are needed to interact with larger numbers of tables

• Queries become more complex as more tables are added

First Normal Form (1NF):

• No repeating or duplicate fields

• Each cell has a single value

• Each record is unique

  • Identified by a primary key

• Each field has a unique name

• Atomic - a data item that cannot be broken down any further

• Compound primary key - 2 or more fields together to create a primary key

Second Normal Form (2NF):

• The table must already be in 1NF

• All non-key fields must be dependent on all fields of the primary key

  • Guaranteed when the primary key is a single field

Third Normal Form (3NF):

• The table must already be in 2NF

• No non-key fields depend on each other

  • All non-key fields depend only on the item key