Databases

Table Records and Fields

Records - Rows

Fields - Columns

What are tables typically used for?

storing contact details, small product database, maintaining a game or music collection

Disadvantages of Tables

• lots of repeated data

• takes up a lot of space

• slow to query

• difficult to maintain

• better to split into multiple tables (relational database)

Primary Key

a field in a record that is guaranteed to be unique

Foreign Key

when a table has a relational link to another table

Secondary key fields

used to index fields that are often used as seach criteria

normalisation

splitting tables in a database and arranging the data to move it from different normal forms

how to get to first normal form

• all field names must be unique

• values in fields should be from the same domain

• values in fields should be atomic

• no two records can be identical

• each table needs a primary key

how to get to second normal form

• the data is already in 1NF

• any partial dependencies have been removed

how to fix a many-to-many relationship

• create a linking table

• assign the primary keys from the two initial tables as the composite key for the new linking table

• flip the M:M crows feet relationship to become two separate 1:M relationships joined by the new table

how to get to third normal form

• the data is already in 2NF

• any transitive dependencies have been removed

non key dependency

the value of a field is determined by the value of another field that is not part of the primary key