Database Field Properties, Relationships, and Queries
Field Properties
Field Size: The maximum number of characters allowed in the field (Integer, Long Integer, Single).
Format: Customize data presentation on-screen or in reports.
Input Mask: Control user input values.
Decimal Places: Specify the number of decimal places for a data type.
Title: Customize the field header’s appearance.
Default: Automatically entered value if no other is provided.
Validation Rule: Control data entry based on specific criteria.
Text Validation: Display text when input fails validation.
Required: Enforce field completion.
Allow Zero Length: For text fields, permit empty or zero-character values.
Indexing: Create an index for a single field to improve search speed.
Relationships
Relational databases use multiple tables to avoid data duplication, saving memory and disk space, increasing execution speed, and simplifying data management. Tables are related through common fields, with one field serving as the primary key in one table.
- One-to-One: A record in one table relates to only one record in another table.
- One-to-Many: A record in one table can relate to multiple records in another table, but a record in the second table relates to only one record in the first.
- Many-to-Many: A record in one table can relate to multiple records in another table, and vice-versa.
Creating Relationships
Use the ‘Database Tools’ tab > ‘Relationships’.
– In the ‘Show Table’ dialog, select the tables for the relationship and click ‘Add’ and ‘Close’.
– Drag the related field from the main table to the corresponding field in the other table.
– In the ‘Edit Relationships’ window, specify the relationship type and create it.
Queries
Queries retrieve, modify, and store data from database tables.
Creating Queries
– Open the database and click ‘Query Design’ (Create tab).
– Select the tables to retrieve data from and click ‘Add’ and ‘Close’.
– In the design view, drag data fields and set search criteria.
– Click ‘Run’ in the design tab.
Q&A
Show Direct Relationships button will show: All relationships of a given table.
Is it necessary for relating fields to have the same name? No.
From what view can we modify the definition of a table? Design View and Datasheet View.
From what view can we insert, delete, and modify data in the table? Datasheet View.
A client informs us that their middle name is misspelled in letters. How do we fix it? Use the ‘Find and Replace’ feature on the Customers table.
The context menu option of a table: Opens it in Datasheet View.
The Home tab > Records group contains buttons for: Deleting the current record or field.
Can you create a new record from the scroll bar? Yes.
If we are entering data into a table and press the right arrow: The next record field will be selected.
How can we place ourselves in record 17 directly? By writing 17 in the scroll bar.
What kind of index is not recommended for tables? Extensive tables likely to be updated continuously.
The Find and Replace tool, “distinguishes between uppercase and lowercase text to find?” By default, no, unless otherwise indicated.
What is the advantage of applying an index to a field in a table? Inquiries will run faster, and sorting the table by this field will be faster.
What does it mean if a field is required? It cannot contain a null value.
What property is most appropriate to specify that the year of birth of a client cannot be earlier than 1900? The Validation Rule.
Why should we define a default value in a field? To make data entry more convenient. For example, if most clients are from Valencia, set Valencia as the default.
What role does the Title property have? To display a more user-friendly header than the actual field name, preferably without spaces or special characters.
The Field Size property refers to: The maximum number of characters a field can contain.
Consultations help us to exploit the data in tables, enabling us to: Select data to display based on criteria, show sorted results, combine data from multiple tables, and work on combined data.
What is the purpose of changing a column header? To change the column name to an alias.
The order in which columns are displayed depends on: The order in which fields have been entered in the QBE grid.
The order in which records are displayed depends on: The order specified in the query through the field order clause.
Can a field of a query be filtered by a selection criterion? Yes, each field can have its own criteria, and it can also be specified at the time of query execution. Management can be ascending or descending.
We have a field called f_nac and we want to show it as “Date of Birth”. What is the proper syntax to enter in the QBE grid? Date of birth: f_nac
Can a query contain calculated fields? Yes, but not defined in the source tables.
Where do we find the Query Design button that allows us to include further consultation? In the Create tab.
Autonumbering fields are of type: Long Integer.
If you want to force the user of the database to enter 8 digits and a letter to define a person’s NIF, which property do you use? Input Mask.
The validation text allows us to display a message if: You try to enter data that does not meet the field’s validation rule.