Data Dictionary and Validation

Data Dictionary and Validation

What is a Data Dictionary?

A data dictionary is a document (often a table) that describes every aspect of a database's structure. It answers: What tables exist? What columns does each table have? What type of data does each column hold? What rules prevent bad data from being entered?

A data dictionary is created during the Design phase and updated as the database evolves. It serves as:

• Documentation: Future developers understand the database without reading code.
• Specification: Database developers know exactly what to build.
• Quality assurance: Testers know what validation rules to test.
• Audit trail: If data looks wrong, the data dictionary shows what rules should have prevented it.

Components of a Data Dictionary

A complete data dictionary includes columns for:

• Entity: The table name (e.g., Student, Course, Enrollment).
• Attribute: The field/column name (e.g., StudentID, FirstName, DateOfBirth).
• Key Type: Is this a Primary Key (PK), Foreign Key (FK), or neither?
• Data Type: Text, Integer, Decimal, Date, Boolean, etc.
• Size: For text, max length (e.g., VARCHAR(50)). For numbers, precision (Integer 0–32,767).
• Validation Rule: What constraints apply? (e.g., "Presence check," "Range 0–100," "Restricted to A–F").

The Five Validation Types

1. Presence Check

A presence check ensures a field is not left blank. If a field is mandatory, the database rejects any record where that field is empty.

Example: StudentID is a primary key, so it must have a value. If someone tries to add a student without an ID, the database rejects the entry.

• Rule: StudentID must not be NULL
• Error: "Student ID is required"

2. Restricted Choice

A restricted choice validation limits entries to a predefined set of allowed values. The field only accepts values from a list.

Example: A Grade field should only accept A, B, C, D, E, or F. Entering "X" is rejected.

• Rule: Grade must be one of {A, B, C, D, E, F}
• Error: "Grade must be A, B, C, D, E, or F"

3. Field Length Validation

Field length validation limits how many characters can be entered into a text field. This prevents data that's too long for the field.

Example: A FirstName field is defined as VARCHAR(30), meaning up to 30 characters. A 9-character name is accepted. A 50-character name is rejected.

• Rule: FirstName must be ≤ 30 characters
• Error: "First name cannot exceed 30 characters"

4. Range Validation

Range validation ensures numeric or date values fall within acceptable limits. Test scores must be 0–100, dates must be between 2020 and 2030, ages must be 0–120.

Example: A TestScore field should only accept 0–100. Entering –5 or 150 is rejected.

• Rule: TestScore must be ≥ 0 AND ≤ 100
• Error: "Test score must be between 0 and 100"

5. Foreign Key Validation

Foreign key validation is the most powerful. It ensures a value in one table exists as a primary key in another table. This enforces referential integrity.

Example: An Enrollment table has a StudentID field referencing the Student table. Before allowing enrollment for StudentID=55, the database checks: does StudentID 55 exist in Student? If not, enrollment is rejected.

• Rule: StudentID in Enrollment must match a StudentID in Student
• Error: "That student does not exist"

Why Validation Matters

Validation prevents garbage-in-garbage-out (GIGO). Without validation:

• Users might leave required fields blank, creating incomplete records.
• Typos might be entered (e.g., Grade="X" instead of "A").
• Out-of-range values might be stored (e.g., Age=–5).
• Orphaned records might exist (e.g., enrollment for a deleted student).
• Reports and analysis become unreliable because data is inconsistent.

Validation catches errors before they're stored, ensuring data quality. This is much cheaper than fixing bad data later.