Create a database structure Revision Notes

When developing a database, data is often imported from existing files such as.csv (comma-separated values) or.txt (plain text) files. These files contain raw data structured in rows and columns, where each column corresponds to a field and each row to a record.

To create a table from imported data, the field names must be clearly identified and used as column headers in the database table. For example, importing a.csv file with columns named 'StudentID', 'Name', 'DateOfBirth', and 'Grade' will create a table with these fields.

It is important to verify that the imported data matches the intended field names and that no data is lost or misaligned during import. This ensures the database structure accurately reflects the data source.

• Use.csv or.txt files as common sources for importing data.
• Ensure field names in the source file match the database table fields.
• Check data alignment and completeness after import.

Each field in a database table must have an appropriate data type to define the kind of data it stores. Common data types include:

Text: for words or alphanumeric characters, e.g., names or addresses.

Numeric: for numbers, subdivided into integers (whole numbers), decimals (numbers with fractional parts), and currency (monetary values). For example, 'Age' might be an integer, while 'Price' could be currency with two decimal places to represent cents or pence. Numeric fields can also specify sub-types such as percentages, which are stored as decimals but displayed with a % sign, and the number of decimal places can be set to control precision (e.g., 2 decimal places for 12.34%).

• Text fields store letters, numbers, and symbols as strings.
• Numeric fields include integer, decimal, currency, and percentage sub-types.
• Decimal places control the precision of numeric data.
• Currency fields format numbers with currency symbols and fixed decimal places.

Boolean or logical fields represent true/false values and can be displayed in various formats to improve user understanding and data entry accuracy. Common display formats include:

Yes/No: showing the words 'Yes' or 'No'. For example, a field 'IsMember' might show 'Yes' if true.

True/False: displaying the logical values explicitly as 'True' or 'False'. This is often used in programming contexts but less user-friendly for general users.

• Checkboxes provide a visual way to select true (checked) or false (unchecked).
• Date/time fields store dates and times and can be formatted to show different styles, such as 'DD/MM/YYYY', 'Month Day, Year', or 24-hour time.
• Choosing the correct display format helps users enter data consistently and reduces errors.

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. For example, 'StudentID' can be a primary key in a student table because each student has a unique ID.

A foreign key is a field in one table that refers to the primary key in another table, establishing a link between the two tables. For example, an 'Enrollment' table might have a 'StudentID' foreign key linking to the 'Student' table's primary key.

These keys enable relational databases to connect data across multiple tables, improving data integrity and reducing redundancy. Relationships can be one-to-one, one-to-many, or many-to-many depending on how keys are used and tables are linked.

• Primary keys uniquely identify records within a table.
• Foreign keys link records between tables to establish relationships.
• Relationships enable complex data structures and queries in relational databases.

Flat file databases store all data in a single table, similar to a spreadsheet. This simplicity makes them easy to create and understand but can lead to data duplication and inconsistencies when handling complex data.

Relational databases organise data into multiple related tables connected by keys. This structure reduces redundancy, improves data integrity, and supports more complex queries and reporting.

Advantages of flat file databases include simplicity and ease of use for small datasets. Disadvantages include poor scalability and difficulty maintaining data consistency. Relational databases are more complex to design but offer greater flexibility and efficiency for larger or more complex datasets.

• Flat file: single table, simple but prone to redundancy.
• Relational: multiple linked tables, reduces duplication, supports complex queries.
• Choose database type based on data complexity and application needs.

Data entry forms provide a user-friendly interface for entering and editing database records. Good form design improves data accuracy and user experience.

Key considerations include: selecting only the necessary fields to avoid clutter; using readable font styles and sizes to enhance legibility; spacing fields adequately to prevent confusion; managing character spacing within fields for clarity; and using white space effectively to separate sections and reduce visual overload.

Input controls such as radio buttons (for selecting one option from many), check boxes (for selecting multiple options), and drop-down menus (for choosing from a list) help standardise input and reduce errors. For example, a 'Gender' field might use radio buttons for 'Male', 'Female', and 'Other'.

• Include only required fields to simplify the form.
• Use clear fonts and appropriate sizes for readability.
• Space fields and sections to avoid clutter.
• Use radio buttons, check boxes, and drop-down menus to guide input and reduce errors.