Database Management Best Practices

Definition: Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life cycle. In simple terms, it means keeping your data correct, complete, and trustworthy!

The "GIGO" Principle

Garbage In, Garbage Out — if the input data is wrong, your reports and decisions will be wrong. This is why data validation is so important!

Integrity Constraints

• Entity Integrity: Every table must have a Primary Key, and it cannot be null. Each record must be uniquely identifiable.
• Referential Integrity: Relationships between tables must remain synchronized. If a record is deleted, related records must be handled properly.
• Domain Integrity: Values in each column must follow specific rules (e.g., age cannot be negative, dates must be valid).

Protecting your database is just as important as designing it properly. Unauthorized access can lead to data theft, manipulation, or complete loss.

Access Control

Different users need different levels of access. A student should only view their own grades, while a teacher can enter and modify grades, and an administrator can manage the entire system.

Data Encryption

Sensitive data like passwords and personal information should be encrypted so that even if someone accesses the database, they cannot read the actual values.

Passwords and Authentication

Strong passwords and proper authentication methods are your first line of defense against unauthorized access.

The Problem with Redundancy

Redundancy means storing the same data in multiple places. This leads to:

• Wasted storage space: The same information takes up room in multiple records
• Update anomalies: When data changes, you must remember to update it everywhere — easy to forget!
• Data inconsistency: Different copies might have different values after updates

Normalization Simplified

Normalization is the process of organizing data into multiple related tables to minimize duplication. The goal is to store each piece of information in exactly one place!

Normal Forms (Key Concepts)

• 1NF (First Normal Form): Each column contains atomic (indivisible) values, no repeating groups
• 2NF (Second Normal Form): No partial dependencies — all non-key attributes depend on the whole primary key
• 3NF (Third Normal Form): No transitive dependencies — non-key values don't depend on other non-key values

Why Backups Matter

Hardware failures, natural disasters (especially relevant in the Caribbean context with hurricanes), software bugs, or accidental deletion can result in catastrophic data loss. Regular backups are your safety net!

Types of Backups

• Full Backup: Everything in the database is copied. Most complete but takes the longest and uses the most storage.
• Incremental Backup: Only changes made since the last backup (of any type) are saved. Fast and storage-efficient, but recovery requires multiple backup files.
• Differential Backup: Changes since the last full backup are saved. A middle ground between full and incremental.

Off-site Storage

Storing backups in a different physical location or in the Cloud protects against local disasters. A backup stored in the same building won't help if the building is damaged!

Data Dictionary

A data dictionary is a document that describes the structure of your database. It typically includes:

• Field Names: The actual names used in tables
• Data Types: What kind of data each field holds (Text, Number, Date, etc.)
• Field Sizes: Maximum length or storage allocation
• Descriptions: What each field represents in business terms
• Validation Rules: Any constraints or rules that apply

Why Document?

If the original database creator leaves the organization, a good data dictionary ensures others can understand, maintain, and modify the system without guesswork or costly mistakes.

Privacy Issues

When handling personal data (medical records, student grades, financial information), database administrators have legal and ethical responsibilities to:

• Keep data confidential and accessible only to authorized personnel
• Use data only for its intended purpose
• Protect data from unauthorized access or breaches
• Dispose of data properly when it's no longer needed

Key Ethical Principles

Test your knowledge across all topics from this series — Tables, Keys, Forms, Queries, Reports, and Best Practices!

Congratulations on completing this comprehensive database series! Let's review what you've learned:

Ready for your CSEC SBA? You now have the knowledge to design, build, and manage a complete database system!