MOAC Access Chapter 1Database EssentialsGuided Notes

Name: ______Date: ______Period: ______

Key Questions -- As you read Chapter 1, answer the following questions to help you gain a better understanding of the material in this chapter.

  1. What is a database? ______

______

Give an example: ______

  1. As a database management system (DBMS), Access enables you to ______

This type of database allows you to store, ______, and ______your data, no matter how complex it is, and then retrieve and ______it in various formats and ______.

  1. In Access, you also have the Backstage View where you can create blank databases, databases with a template or open a recently saved database.
  2. By default, the Navigation Pane appears on the ______side of the Access screen each time you create or open a database. The Navigation Pane will let you open, ______, and delete tables and other database ______. It also shows a list of what? ______including tables, queries, forms, and reports. Define each of these objects:
  • A table is -- ______
  • A query is -- ______
  • A form is -- ______
  • A report is -- ______

The Navigation Pane divides your database objects into ______that contain groups. The default category is Tables and Related Views , which groups the objects in a database by the ______to which they are related. You can change the category to Object Type which groups database objects by their type – tables, forms, reports, and so on.

  1. When you create a database in Access, all the objects in that database—including forms, tables,reports, and queries—are displayed in a single window separated by ______. Tabs help keep openobjects visible and accessible. To ______among the open objects, click a tab. To close a tab,click its Close button. You can also right-click a tab to display the ______menu where youcan save, close, close all, or switch views.
  2. Each database object can be viewed several different ways. What are the two ways tables can be

viewed? ______

Which of these views is typically used the most often and why? ______

______

What is a datasheet? ______

What is a query? ______

  1. The Access Ribbon is divided into ______tabs, or areas of activity. Just as in Excel, each tab has groups of related commands. Some groups also have a dialog box launcher with additional options or information. The Ribbon can be customized with items you use most often. You can also use and customize the Quick Access Toolbar with the commands that you use most often.

DEFINING DATA NEEDS AND TYPES

  1. To create a database that achieves your goals and provides you with up-to-date, accurate information, you need to spend time ______and ______it.

What is the first step you should do when planning a database? ______

______Once you have decided how the information will be used, the next step is to categorize the information by dividing it into subjects such as Products or Order, which become the ______in your database. Each table should only contain the information that relates to that ______. If you are tempted to add new information to a table, don’t. Just create a new table.

  1. How is data stored in a database table? ______

Each row in a table is called a ______. Each column in a table is called a ______.

To create the columns within a table, you need to determine what information you want to store in the table and break it down into the smallest useful part. For example, use First Name and Last Name instead of just Name if you want to sort, search, calculate, or report using the separate pieces of information, such as last name or email address. For each table you will choose a primary key. What is that? ______

Defining and Modifying Data Types for Fields

  1. When designing the database, you set a data type for each field (column) that you create to match the information it will store. What is a data type? ______
  2. It is important to define the data as specifically as possible. For example, if you are using a number, you should determine whether you need to use the Currency or Number data type. Or, if you need to store large amounts of text, you may need to use the Memo data type instead of Text.

When you create a new field in a database and enter data in it, Access will automatically try to detect the appropriate data type for the new column. For example, if you key aprice, such as $10, Access recognizes the data as a price, and sets the data type for the field toCurrency. If Access doesn’t have enough information from what you enter to guess the data type,the data type is set to Text.

A new data type in Access 2010 is a calculated data type. What can that data type do? ______

For example, you can easily create a calculated field named FullName that concatenates the First Name and Last Name fields into one string. Then, the FullName field can easily be added to a form, query, or report without having to create a new expression within the object.

Microsoft Access 2010 will also let you create fields that can hold ______values.

Access provides ______different data types, each with its own purpose.

This Table describes the types of data that each field can be set to store.

Data Type / Example / Description
Text / Last Name: Zimmerman
Street: 6789 Walker St. / The most common data typefor fields. Can store up to 255characters of text, numbers, or acombination of both.
Memo / Comments: Student will make monthly pmts on the 15th of each month of $247. / Stores large amounts of text—upto 64,000 characters of text,numbers, or a combination(although if you use that muchspace, your database will runslowly).
Number / Age: 19 / Stores numeric data that can beused in mathematical calculations.
Date/Time / Birthday: December 1, 1987 / Stores date and/or time data.
Currency / Registration Fee: $50.00 / Stores monetary data withprecision to four decimal places.Use this data type to storefinancial data and when you don’t want Access to round values.
AutoNumber / Student ID: 56 / Unique values created byAccess when you create a newrecord. Tables often contain anAutoNumber field used as theprimary key.
Yes/No / Insurance: Yes / Stores Boolean (true or false)data. Access uses 1 for all Yesvalues and 0 for all No values.
OLE Object / Photo / Stores images, documents, graphs, and other objects from Office and Windows-based programs.
Hyperlink / Web addresses / Stores links to websites, sites orfiles on an intranet or Local AreaNetwork (LAN), and sites or fi leson your computer.
Attachment / Any supported type of file / You can attach images,spreadsheet files, documents,charts, and other types ofsupported fi les to the recordsin your database, much like youattach files to email messages.
Calculated / FullName: John Derenzo / Stores an expression based ontwo or more fields within thesame table.

****IMPORTANT TO NOTE: The Number data type should only be used if the numbers will be used in mathematical ______. For numbers such as phone numbers, use the Text data type.

Defining Database Tables

  1. ______are the most basic organizational element of a database. Not only is it important to plan the tables to hold the type of data you need, but also to plan how the tables and information will be connected.

In a ______database, you might only have ______table, but most databases will have more. The tables you include in a database will be based on the data available. For example, a database ofstudents might have a table for contact information, one for grades, and one for tuition and fees.

In database applications like Access, you can create a relational database. What is a relational database? ______

______

***You learn more about table relationships in Lesson 3.

  1. An important principle to consider when planning a database is to try to record each piece of information only ______. Duplicate information, or ______data, wastes space and increases the likelihood of errors. Relationships among database tables help ensure ______and reduce ______data entry.

As you create each table, keep in mind how the data in the tables are ______to each other.

Enter ______data and then add fields to tables or create new tables as necessary to ______the database. The last step is to apply data normalization rules to see if your tables are structured correctly and make adjustments as needed. What is normalization? ______

______

Database design principles include standards and guidelines that can be used to determine if your database is structured correctly. These are referred to as ______forms. There are ______normal forms, but typically only the first three are applied, because that is usually all that is required. The following is a summary of the first three normal forms:

• First Normal Form (1NF): Break each field down into the smallest meaningful value, remove repeating groups of data, and create a separate table for each set of related data.

• Second Normal Form (2NF): Each nonkey column should be fully dependent on the entire primary key. Create new tables for data that applies to more than one record in a table and add a related field to the table.

• Third Normal Form (3NF): Remove fields that do not relate to, or provide a fact about, the primary key.

Data can be brought into an Access database in a number of ways, including ______and ______. When defining tables, you have to decide whether data should be linked to or imported from ______sources. When you import data, Access creates a copy of the data or objects in the ______database without altering the source. Linking lets you ______to data from another source without importing it, so that you can view and modify the latest data in both the source and destination databases without creating and maintaining ______copies of the same data. Any changes you make to the data in the ______are reflected in the linked table in the destination database, and vice versa.

***** You learn more about importing data and linking to an external data source in Lesson 14.