Relational Schema Good Design

Relational Schema Diagnosis

  • Usability: How does a choice of design impact productivity?
    • Ease of expressing queries
    • Ease of expressing data revision
    • Metadata Transparency
  • Resource requirements: What are the allowed instances of the schema?

Change Anomalies

Change Anomalies

Inconsistencies that result from an operation like update, insertion, or deletion

Example

Pasted image 20231212113731.png
No clear problem with expressing queries
Problems with data revisions

  • Update
    • Change name of supplier S1 to ACME
    • Need to change all S1
  • Insert
    • Add item I4 with name Washer
    • What about the first 3 columns?
  • Delete
    • Supplier “Budd” no longer supplies screws
    • Do we delete Budd or just the screws?

Fix

No more ambiguity

More example

Information is lost

Diagnosis and Repair