Database Fundamentals: Terms, Concepts, and Operations

Posted on Dec 21, 2024 in Computers

Database: Key Terms and Concepts

A database is a collection of information stored in an organized manner. There are different types of databases:

Classes Database/Database Documentary: Also called a simple file, it contains information in a single table. Common data across multiple records must be repeated for each record.
Relational Database: These databases use related or linked tables. This allows you to enter information so that data is tied to one another.

Database Components

Tables: A data set collected in rows and columns, which are the foundation of the database, as this is the information to be used for work.
Forms: A presentation used to manage data on the screen.
Reports: A summary of data from tables or queries. Used to print information in a readable and attractive format.
Queries: Used to search data using specific questions. They retrieve information stored in tables.
Macros: Small programs that perform a repetitive sequence in one step.
Modules: Applications written in VBA that automate tasks in a database.

Data Types

AutoNumber: A unique numeric value that is inserted automatically when creating a new record. By default, it increases one by one. It is often used for primary key fields.
Memo: A data set similar to text but with greater storage capacity.
Numeric: A set of numerical data with which to perform mathematical operations.
Date/Time: A structure used to enter dates and times in the records.
Money: A type assigned to monetary values.
Yes/No: A structure used for affirmative or negative values, true or false, etc.
OLE Object: An object linked or embedded in a Microsoft Access table. It may contain information that comes from other applications.
Hyperlink: An alphanumeric combination stored as text and used as a hyperlink address.
Lookup Wizard: An application that creates a field for selecting a value from another table or list of values, using a list box or combo box.
Attachments: A structure designed to store all types of documents in the database without significantly increasing its size.

Creating Tables in Design View

Design View is used to create tables by showing the name of each field, the type of data to be entered, and even a brief description of the content.

Key Field Properties

Primary Key: The feature that is added to a field in an Access table to recognize it as the key field. A primary key must meet these requirements:
- It is not repeated in any of the records.
- It does not acquire a null value.
Size: The maximum number of characters that can be inserted (between 0 and 255).
Format: Determines the field format (font type, size, color, number format, decimal, etc.) to establish how data appears on the screen or when printed.
Decimal Places: Choose the number of decimal places when the data type is Number, Currency, or AutoNumber.
Input Mask: Controls how you enter information. Forces data entry into a standard format and reduces the possibility of making a mistake.
Caption: A label that will appear in forms and reports.
Default Value: The value that the field takes by default.
Validation Rule: An expression that limits values for the field.
Validation Text: The message that appears when an invalid value is introduced, i.e., it does not meet the validation rule.
Required: When set to Yes, Access will require mandatory data entry in that field, ensuring it is never empty.
Allow Zero Length: Saves zero-length strings.
Indexed: Speeds up searching and sorting of data and queries made in the field, although it requires more storage space.
Unicode Compression: A global standard for communication between computers. If the Yes option is enabled, data is stored compressed.

Relationships Between Tables

One-to-One: When an element in Table 1 is related to another element in Table 2 and vice versa.

Example: Table 1: Names of people. Table 2: ID of the people. Each person has one ID, and vice versa.

One-to-Many: When each element of Table 1 relates to various elements in Table 2, but each element in Table 2 is only related to one element in Table 1.

Example: Table 1: Football teams. Table 2: Football players. Each team has several players, but each player belongs to only one team.

Many-to-Many: In this case, each element in Table 1 is related to several elements in Table 2 and vice versa.

Example: Table 1: Movie titles. Table 2: Names of actors. Each film stars several actors, while each actor plays more than one movie.

Filters

A filter is a restriction on a table to display only a specific set of records and to facilitate reading and evaluation of certain information. When setting a filter, criteria must be established that allow data to be displayed or not.

Purpose of Filters

To know which records contain a piece of information.
To work with a particular record.
To print only the records that interest you.

Queries

A query is the result obtained after selecting records that meet certain conditions imposed by the user.

Differences Between Queries and Filters

With filters, you can only use fields from a single table.
With queries, you can combine fields from multiple tables.

Purpose of Queries

View, modify, or analyze data.
Serve as the record source for forms, reports, etc.

Types of Queries

Selection: Display data, analyze, or even edit them. Additionally, you can automatically combine fields from two or more tables or queries using specified criteria. The results are shown in the desired order. These are the most common.
Crosstab: Organize information according to different clustering values. Calculate the sum, mean, or other grouping totals for some data on the left side and others at the top.
Action: Used to make changes to a particular group of selected records using specified parameters. Queries of this kind can be for deletion, updating, creating tables, and appending (adding to existing records in another table).
Union: Combine fields from one or more tables into one.
Find Duplicates: Determine whether there are duplicate records in a table or which records share the same value.
Find Unmatched: Help locate records in one table that have no related records in another.
Parameter: Offer the possibility for the user to specify search criteria when running them.
Totals: Based on the use of aggregate functions (sum, count, max, etc.).

Forms

A form is a tool used to enter, modify, and submit information stored in one or more tables in a database.

Parts of a Form

Header: Includes information identifying the form.
Body: Exposes all the relevant information.
Footer: Includes guidance elements, such as page number, date, etc.

Options for Creating Forms

Split Forms: Standard forms.
Form Wizard: Allows the creation of subforms.
Forms in Design View: The user creates the form without the help of the wizard, allowing for greater creativity.
Advanced Forms: Incorporate controls.

Reports

Reports get their information from the fields of the tables in the database and present an organized and coherent summary. They are suitable for working when hard copies are needed. Also, if data in tables is modified, reports are updated automatically.

Ways to Create a Report

With the Report Wizard.
In Design View.
AutoReport: Simple reports.

SQL (Structured Query Language)

SQL is a structured query language that allows you to perform management tasks and control a database, as well as perform searches and queries.

An SQL statement is a set of English words that indicates what is desired and where to look. It always starts with a verb and is followed by several clauses, mandatory or not. Some statements include: SELECT, ALL, DISTINCT, FROM, WHERE.

Names Used in SQL

Simple Name: Consists of just the name of the column.
Qualified Name: Consists of the name of the table, a period, and the name of the column. If the name has spaces, it should be written in brackets.
Alias: A second name assigned to the table or column, which is shown upon completion of the task.