Managing Data

A database is a structured collection of data, organised for easy access, management, and updating. Think of it as a highly organised electronic filing cabinet. To create a useful database, we first build a data model. A data model is a digital representation of a real-life scenario, breaking it down into individual attributes (data items) that a computer can understand and process. For example, to model a 'student', we would identify attributes like 'student ID', 'name', and 'date of birth'. This process helps us decide what data we need to store and how to structure it.

Databases are organised into tables. Each table represents an 'entity', which is a person, place, thing, or concept we want to store data about (e.g., 'Student', 'Book', 'Order'). Each table is made up of columns called 'fields' and rows called 'records'. A field represents a single piece of information about the entity (e.g., 'FirstName'). A record contains all the data for one instance of the entity (e.g., all the information for a single student). To uniquely identify each record, we use a 'primary key'. A primary key is a field containing a value that is unique for every record in the table, like a Student ID number.

A data dictionary is a central source of information about the data in a database. It's like a detailed blueprint that defines the structure of the database tables. For each field, the data dictionary specifies its name, the type of data it will hold (e.g., Text, Number, Date/Time), its size or length (e.g., max 50 characters), any validation rules, and whether it's a primary key. Creating a data dictionary is a key step in database design as it ensures consistency and helps developers understand the data they are working with.

Data validation is the process of using automatic checks to ensure that the data entered into a database is sensible, reasonable, and complete. It helps to improve data quality by catching errors at the point of entry. It does not check if the data is correct (this is verification), but only if it is plausible. Common validation checks include: Presence check (ensures a field is not left empty), Type check (ensures the data is of the right type, e.g., a number in a 'Quantity' field), Range check (ensures a number is within a specific range, e.g., age between 0 and 120), Format check (ensures data matches a specific pattern, e.g., a postcode like 'SW1A 0AA'), and Length check (ensures data is a certain number of characters long).

While you can enter data directly into a database table, it's not very user-friendly and can lead to errors. A data capture form is a screen or window with an organised layout designed for easy and accurate data entry. It presents the fields from a table in a clear, logical order, often with helpful labels and instructions. Forms can use features like drop-down lists (from lookup validation), tick boxes, and calendars to make data entry faster and more accurate. When a user fills in the form and clicks 'Save' or 'Submit', the data is automatically placed into the correct fields in the underlying database table.