What Is a Database?

In the simplest sense, a database is a collection of records and files that are organized for a particular purpose. On your computer system, you might keep the names and ? addresses of all your friends or customers. Perhaps you collect all the letters you write and organize them by recipient. You might have another set of files in which you keep all your financial data-accounts payable and accounts receivable or your checkbook entries and balances. The word processor documents that you organize by topic are, in the broadest sense, one type of database. The spreadsheet files that you organize according to their uses are another type of database. Shortcuts to all your programs on your Windows Start menu are a kind of database. Internet shortcuts organized in your Favorites folder are a database.

If you’re very organized, you can probably manage several hundred spreadsheets or shortcuts by using folders and subfolders. When you do this, you’re the database manager. But what do you do when the problems you’re trying to solve get too big? How can you easily collect information about all customers and their orders when the data might be stored in several document and spreadsheet files? How can you maintain links between the files when you enter new information? How do you ensure that data is being entered correctly? What if you need to share your information with many people but don’t want two people to try updating the same data at the same time? How do you keep duplicate copies of data from proliferating when people can’t share the same data at the same time? Faced with these challenges, you need a database management system (DBMS).

Relational Databases

Nearly all modern database management systems store and handle information using the relational database management model. In a relational database management system, sometimes called an RDBMS, the system manages all data in tables. Tables store information about a single subject (such as customers or products) and have columns (or fields) that contain the different kinds of information about the subject (for example, customers’ addresses or phone numbers) and rows (or records) that describe all the attributes of a single instance of the subject (for example, data on a specific customer or product). Even when you query the database (fetch information from one or more tables), the result is always something that looks like another table.

The term relational stems from the fact that each table in the database contains information related to a single subject and only that subject. If you study the relational database management model, you’ll find the term relation applied to a set of rows (a table) about a single subject. Also, you can manipulate data about two classes of information (such as customers and orders) as a single entity based on related data values. For example, it would be redundant to store customer name and address information with every order that the customer places. In a relational database management system, the information about orders contains a field that stores data, such as a customer number, which can be used to connect each order with the appropriate customer information.

You can also join information on related values from multiple tables or queries. For example, you can join company information with contact information to find out the contacts for a particular company. You can join employee information with department information to find out the department in which an employee works.

Some Relational Database Terminology

Relation.   Information about a single subject such as customers, orders, employees, products, or companies. A relation is usually stored as a table in a relational database management system.

Attribute.   A specific piece of information about a subject, such as the address for a customer or the dollar amount of an order. An attribute is normally stored as a data column, or field, in a table.

Instance.   A particular member of a relation-an individual customer or product An instance is usually stored in a table as a record, or row.

Relationship.   The way information in one relation is related to information in another relation. For example, customers have a one-to-many relationship with orders because one customer can place many orders, but any order belongs to only one customer. Companies might have a many-to-many relationship with contacts because there might be multiple contacts for a company, and a contact might be associated with more than one company.

Join.   The process of linking tables or queries on tables via their related data values. For example, customers might be joined to orders by matching customer ID in a customers table and an orders table.

Database Capabilities

An RDBMS gives you complete control over how you define your data, work with it, and share it with others. The system also provides sophisticated features that make it easy to catalog and manage large amounts of data in many tables. An RDBMS has three main types of capabilities: data definition, data manipulation, and data control.

• Data definition.   You can define what data is stored in your database, the type of data (for example, numbers or characters), and how the data is related. In some cases, you can also define how the data should be formatted and how it should be validated.

• Data manipulation.   You can work with the data in many ways. You can select which data fields you want, filter the data, and sort it. You can join data with related information and summarize (total) the data. You can select a set of information and ask the RDBMS to update it, delete it, copy it to another table, or create a new table containing the data.

• Data control.   You can take advantage of features that help ensure that the right type of data goes into the correct places. In many cases, you can also define how “data can be shared and updated by multiple users using the database.

All this functionality is contained in the powerful features of Access. Let’s take a look at how Access implements these capabilities and compare them to what you can do with spreadsheet or word processing programs.