Here is the simple definition of a database: a database is an organized list of related information.
As I said in the introduction, you probably use some sort of database every day. It may not be on a computer, but if it is an organized list of related data, then it is a database. A phone book is one example of a database. A phone book holds a list of names, addresses, and phone numbers. The data is
related because you are storing the same type of information for each person (i.e. name, address, and phone number). Many databases are kept on computers for the flexibility it gives you to sort, filter, and organize data. As you already know, Access is a popular database application, and many people just beginning to delve into the world of databases start with Access, because it is relatively easy to use, but also provides flexibility.
- Everyday Databases
Access is a tool for managing databases which are carefully structured catalogs of information (or data). Databases can store just about any type of information, including numbers, pages of text, and pictures. Databases also range wildly in size since they can handle everything from your list of family phone numbers to a product catalog for Distributed Networks. Using Microsoft Access you can design complete databases, maintain them, search for valuable nuggets of information, and build attractive forms for quick and easy data entry.
The following series of images shows some examples of databases that you may use every day.
Access is a
relational database management system. Access data is stored in related tables, where data in one table (such as Customers) is related to data in another table (such as Orders). Access maintains the relationships between related tables, making it easy to extract a customer and all the customer’s orders, without losing any data or pulling order records not owned by the customer. Multiple tables simplify data entry and reporting by decreasing the input of redundant
data. By defining two tables for an application that uses customer information, for example, you don’t need to store the customer’s name and address every time the customer purchases an item. After you have created the tables, they need to be related to each other.
For example, if you have a Customer's table and a "Sales" table, you can relate the two tables using a common field between them. In this case, Customer Number would be a good field to have in both tables. This will allow you to see sales in the "Sales table" where the Customer Number matches the Customers table.
The benefit of this model is that you don’t have to repeat key attributes about a customer (like 1) customer name, 2) address, 3) city, 4) state, 5) zip) each time you add a new record to the Sales table. All you need is the customer number. When a customer changes address, for example, the address changes only in one record in the Customers table.
Separating data into multiple tables within a database makes a system easier to maintain because all records of a given type are within the same table. By taking the time to properly segment data into multiple tables, you experience a significant reduction in design and
work time. This process is known as normalization.
Access stores data in tables that look much the same as worksheets but are designed for complex querying in relation to data stored in other tables and locations. If your data needs to be stored in more than one table, then you need a relational database. Each table is basically a description of a type of data (such as orders for a customer). If you require a relational database, you have identified a one-to-many relationship in your data. For example, if you have a customer order database, one table will contain customer names and another will contain their orders. A single customer can have many orders. Additionally, you might want to have another table for order details since each order can have multiple line items. Relational data is best stored in Access. Do you notice this direct correlation between the size of your data and the challenge you have in organizing it effectively? The more data you have, the more likely you are to store it in multiple tables in Access. To help manage your data and keep it accurate, Access and Excel provide you with unique identifiers. In Access, a primary key (an icon in the shape of a key visible in Design view of your table) uniquely identifies each record.
In the next lesson, learn about relational databases.