Database Fundamentals and Table Relationships

Posted on Dec 10, 2024 in Computers

Database Fundamentals

DATABASE: A collection of information stored in an organized manner. There are different classes of databases:

  • Documentary Database: Also called a simple file, it contains information in a single table. Common data across multiple records must be repeated for each of them.
  • Relational Database: These use related or linked tables. Thus, you can enter information so that data are tied to one another.

Key Database Components

  • TABLES: A data set collected in rows and columns, which are the foundation of the database, since this is the information to be used for work.
  • FORMS: A presentation 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 and 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 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.
  • WIZARD SEARCH: An application that creates a field for selecting a value from another table or a 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, showing the name of each field, the type of data to be entered, and even a brief description of the content.

Primary Key

The PRIMARY KEY is a feature added to a field in an Access table to recognize it as the key field. A primary key can be one or more fields that meet these requirements:

  • It is not repeated in any of the records.
  • It does not acquire a null value.

Field Properties

  • 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: Specifies the number of decimal places when the data type is Number, Currency, or AutoNumber.
  • INPUT MASK: Controls how you enter information, forcing data entry into a standard format and reducing 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 the values for the field.
  • VALIDATION TEXT: The message that appears when an invalid value is introduced, i.e., one that does not meet the validation rule.
  • REQUIRED: When set to Yes, Access requires mandatory data entry in that field, ensuring it is never empty.
  • ALLOW ZERO LENGTH: Allows saving zero-length strings.
  • INDEXED: Speeds up searching, sorting, and querying data in the field, although it requires more storage space.
  • UNICODE COMPRESSION: A global standard for communication between computers. If set to Yes, data is stored in a compressed format.

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: IDs of people. Each person has one ID, and each ID belongs to one person.

One-to-Many

When each element in Table 1 relates to multiple elements in Table 2, but each element in Table 2 relates to only 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

When each element in Table 1 is related to multiple 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 may star in multiple movies.

Filters

A FILTER is a restriction on a table to display only a specific set of records, facilitating the reading and evaluation of certain information. When setting a filter, you decide which criteria should determine whether data is displayed.

Purpose of Filters

  • To identify records containing specific information.
  • To work with a particular record.
  • To print only the records of interest.