Schema design for relational databases
In terms of structured relational databases, the paramount considerations are making sure your data is reliable, and everything runs efficiently. Two foundational principles drive this focus:
- Avoiding data anomalies
- Reducing data redundancy
In the context of a relational database management system (RDBMS), a data anomaly is an inconsistency in the dataset resulting from a write operation, such as insert, delete, or update. For example, a university stores student information such as email, phone numbers, and addresses in multiple tables or columns. Over time, a student's phone number changes, and the university administration updates the phone number field in one of the tables or columns but forgets to update the others. As a result, the system now has conflicting information for the same student's phone number. Such a situation creates a data anomaly known as an update anomaly.
Data redundancy refers to the unnecessary...
