Topics: SQL, Database, Relational database Pages: 6 (1168 words) Published: October 30, 2013
Access teaching materials

1. Introduction

Access is a computerized Database Management System (DBMS) that enables you to store, retrieve, analyze, and print information. It is a system for managing large amounts of data. Companies use databases for many purposes: to manage customer files, to track orders and inventories, and for marketing purposes. An individual might set up a data base to track household expenses or manage a list of family, friends, and business addresses. Teachers often set up a database to track students’ grades ad other class information. A database enables the user to access and manage thousands of pieces of data in an organized, efficient, and accurate manner.

To begin using Access, there are a number of terms that you need to understand. An Access database consists of tables, queries, forms, reports, etc, which are all generally known as objects. These objects work together to store, search, input, report, and automate the data. The following gives you a brief explanation of the purpose of the major objects.

Tables are the foundation of the database because they store the data in the database. Each table stores a set of related data.

Queries are used to sort, search, and limit the data to just those records that you need to see.

Forms are generally used to input or edit data, or to view one record at a time.

Reports are used to summarize information for printing and presentation of the data.

Notes: Access 2007/2010 file ends up with . accdb instead of. mdb

2. Primary Key/Relationship

Primary key:

Definition: A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number or personal identification number. A relational database must always have one and only one primary key. Primary keys typically appear as columns in relational database tables.

Example: Imagine we have a STUDENTS table that contains a record for each student at a university. The student's unique student ID number would be a good choice for a primary key in the STUDENTS table. The student's first and last name would not be a good choice, as there is always the chance that more than one student might have the same name. The choice of a primary key in a relational database often depends on the preference of the administrator. It is possible to change the primary key for a given database when the specific needs of the users changes. For example, the people in a town might be uniquely identified according to their driver license numbers in one application, but in another situation it might be more convenient to identify them according to their telephone numbers.

Relationship: A relationship works by matching data in key fields - usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table.

Example: A complicated database of a manufacturing company with several tables.

3. Query

3.1 Queries with two tables connected together

3.1.1 Start

Table used: Teacher and Course

Step 1: Go to “Create” –“Other”—“Query design”. Your query is created under design view.

Step 2: Select the Course and Teacher tables and click the Add button. The field list for the course and teacher tables are added to the Query Design window. Save the query by using the name: Query in Course and Teacher tables.

Step 3: Click the Close button to close the Show Table dialog box. Double-click the fields of Course ID, Course Name, Teacher ID in Course table, and Teacher Name in Teacher table.

Step 4: Click the View button to switch to the Datasheet View. All of the records...
Continue Reading

Please join StudyMode to read the full document

Become a StudyMode Member

Sign Up - It's Free