6. Database integrity
By analysing what could go wrong, we can design a database system that guarantees some error patterns won’t happen.
Let’s look at the planets and moons database again. Let’s assume we have this entry:
MoonName: Deimos PlanetName: Mars Area: 495
What could go wrong?
- PlanetName could be mistyped as Mers or set to NULL. This planet doesn’t exist, so our queries will produce erroneous results.
- Area should never negative, so we should disallow negative values.
Some of these problems that can arise are detectable and preventable if we design the database for that.
Integrity Constraints can help us prevent errors. Here are some examples:
- Join fields must match – We should use a FOREIGN KEY. A subsequent INSERT with wrong value will fail.
- One some values of a field are valid – Use CHECK column constraint. eg Area INT CHECK (Area > 0) to prevent negative areas.
- Tables values should not be inconsistent – Avoid repeating information in a database. We can do this by using the PRIMARY KEY which guarantees uniqueness.
Also, avoid storing calculated values. eg Rather than a field listing the number of moons, just do a COUNT.
- Changes should not cause inconsistency – Use FOREIGN KEY rules to enforce correct behaviour.
eg FOREIGN KEY (PlanetName) REFERENCES Planets(PlanetName) ON DELETE CASCADE
When a parent entry in Planets is deleted, this will remove related moons.
- Table values should not be inconsistent – Remove functional dependencies.
Tuesday 2 November 2021, 279 views
Next post: 7. Data integrity and security
Previous post: 5. Joins in SQL
- 26. A very good guide to linked data
- 25. Information Retrieval
- 24. Triplestores and SPARQL
- 23. Ontologies – RDF Schema and OWL
- 22. RDF – Remote Description Framework
- 21. Linked Data – an introduction
- 20. Transforming XML databases
- 19. Semantic databases
- 18. Document databases and MongoDB
- 17. Key/Value databases and MapReduce
- 16. Distributed databases and alternative database models
- 15. Query efficiency and denormalisation
- 14. Connecting to SQL in other JS and PHP
- 13. Grouping data in SQL
- 12. SQL refresher
- 11. Malice and accidental damage
- 10. ACID: Guaranteeing a DBMS against errors
- 9. Normalization example
- 8. Database normalization
- 7. Data integrity and security
- 6. Database integrity
- 5. Joins in SQL
- 4. Introduction to SQL
- 3. Relational Databases
- 2. What shape is your data?
- 1. Sources of data