What is good database design?

• Spreadsheets - bad database design?
  • Relationships between data are hard to map and keep track of
  • Data can be duplicated - DRY - data redundancy
  • Hard to validate (but not impossible)
  • Fields are not atomic

Data Integrity

• Entity Integrity - all records have a valid unique identifier
• Referential Integrity - all foreign keys in a table must refer to a valid record in the corresponding table
• Domain Integrity - values in fields are valid and accurate

ER Diagrams

Modelling Entities

Let's model our Student and Program entities from before using Chen notation.

Modelling Relationships

• 1:1 - foreign key in table A references one primary key in table B
• 1:M - multiple foreign keys in table A reference one primary key in table B
• N:M - need to resolve this - otherwise data fields become non-atomic 
  • Use a junction table to resolve to a series of 1:M relationships