Download a PDF of this page here.
Download the Spanish version here.
Essential Concepts
Database Basics
A database is a structured collection of data. Key concepts include data integrity, data independence, and reducing redundancy. Databases are managed by DBMS (Database Management Systems) that control access, ensure security, and allow for data manipulation. Constraints like primary keys and foreign keys maintain the integrity of relationships within the database.
Flat-file databases consist of a single table, leading to redundancy and inefficiency. Normalized databases spread data across multiple related tables, reducing redundancy and improving data integrity by organizing information more effectively.
Three main table relationships exist:
- one-to-one, where each record in one table corresponds to one record in another
- one-to-many, where one record in a table relates to multiple records in another
- many-to-many, managed via junction tables, where multiple records in both tables relate to each other.
These relationships are crucial for structuring a relational database effectively.
Working with Tables
Datasheets in Access display data in a grid format, similar to a spreadsheet. This view is used to enter, view, and modify data in tables or queries. It’s a primary interface for interacting with records, allowing you to easily add, edit, delete, and sort data.
Entering data in Access is straightforward; you can add new records directly into Datasheet View. Each row represents a record, and each column corresponds to a field. As you type, data is automatically saved.
To change values in Access, click on the cell containing the data you want to modify, type the new value, and press Enter. Access automatically updates the record. This simple process allows you to correct errors or update information without needing to re-enter entire records.
Working with Datasheets
Adding new records in Datasheet View involves entering data into the blank row at the bottom of the datasheet. Access automatically saves the new record and assigns it a unique identifier if an AutoNumber field is used. This process is essential for populating your database with information.
To delete a record in Datasheet View, select the row by clicking its record selector, then right-click and choose “Delete Record.” This action permanently removes the record from the database, so use it with caution.
Sorting and Filtering Data
Datasheet View allows you to display and interact with records in a tabular format. You can navigate, sort, and filter records to focus on specific data, making it easier to manage large datasets.
- Sorting Data: Sorting data in Datasheet View helps you organize records based on specific fields, either in ascending or descending order. This is useful for quickly finding information or analyzing trends.
- Aggregating Data: Aggregate functions, like SUM, AVG, and COUNT, allow you to perform calculations on data sets, summarizing important metrics. Add a total row to the bottom of your datasheet, then choose the aggregate function for the desired field
- Printing Records: To print records, select the datasheet or report, adjust print settings, and output the data to a printer or PDF. This feature is essential for creating hard copies or sharing data outside Access.
Glossary
access privilege
permissions granted to users to view or modify database data.
aggregate
functions that perform calculations on a set of values, such as sum or count.
concurrency control strategies
methods to manage simultaneous data access to prevent conflicts.
data independence
the ability to change the database schema without altering application programs.
data redundancy
unnecessary duplication of data within a database.
database
a structured collection of data stored and accessed electronically.
database constraints
rules that ensure data integrity, such as primary keys or unique constraints.
DBMS (Database Management System)
software that manages and controls access to the database.
datasheet
a grid format in Access where data is displayed, similar to a spreadsheet.
flat file
a database that contains a single table with no relations to other tables.
integration
combining data from different sources into a single, unified database.
many-to-many
a relationship where multiple records in one table relate to multiple records in another table, typically managed with a junction table.
metadata
data that describes other data, such as the structure and constraints of a database.
normalized data
data organized into multiple related tables to reduce redundancy.
one-to-many
a relationship where one record in a table relates to multiple records in another table.
one-to-one
a relationship where one record in a table relates to exactly one record in another table.
read-only privilege
permissions that allow a user to view but not modify data in a database.
record
a complete set of related data fields, representing a single entity in a table.
self-describing
a characteristic of a database that includes metadata, making it self-explanatory to the system or users.