Introduction to databases and Access 2000

A relational database is a collection of related tables. A table is a kind of organised file where data about a certain type is organised into records (rows) and fields (columns). Each field in a table constitute a property of the data to be stored in the table.

Example 1: The Sample Bookstore Database

ü      Books  Table
 (ISBN, TITLE, AUTHOR, YEAR, LISTPRICE, PUBLISHERID)

ü      Publishers Table
 (PUBLISHERID, PUBLISHERNAME, ADDRESS, CITY, STATE, ZIPCODE, PHONE)

ü      Orders Table
 (ORDERID, DATE, PUBLISHERID)

ü      Order Details Table
 (ORDERID, ISBN, QUANTITY)

ISBN: The International Standard Book Number. First part indicates the publisher. For instance, 0-13 is for Prentice Hall.

The key of a table is a combination of its fields that uniquely identify each record in that table. The underlined field(s) above are the keys of the corresponding tables.

The relation between the tables may be either one-to-many (1-M) or many-to-many (M-M). For instance, a publisher will most probably publish more than one book. So, the relation between the Publishers and Books tables ‘s 1-M. On the other hand, there may be more than one book in a specific order and a specific book may be ordered several times, that is in several orders. So, the relation between the Order Details and Books tables is M-M.

Example 2: Personnel Database, Employee Table

Microsoft Access is software that enables us to create and manage such databases. A Microsoft Access file has the extension mdb (Microsoft data base).

When you start Microsoft Access, it will ask you if you want to create a new database or open an existing database (See figure 1).If you choose to open an existing database, the database window will be displayed on the screen. The database window has 7 objects:

1.      Tables (open an existing table for editing, design a table, create a table)

2.      Queries (a query is a question about the database written in pre-defined forms)

3.      Forms (a form is a formatted window for managing data in a database)

4.      Reports (a report is a formatted print-out that outputs information about a database; a report may be based on a table or a query)

5.      Pages

6.      Macros (kind of computer program to be executed automatically one after another; is used to automate repetitive tasks)

7.      Modules (programming in Visual Basic for Applications)

Figure 2

Opening a table activates the Datasheet Window which is used to add, delete, or update (edit) records in that table. Figure 3 demonstrates the Datasheet Window for the Books Table.

Figure 3

Navigation in the Datasheet Window:

·         Record Navigation

¡       Use the record navigator buttons on the status bar:

Note: When you close the Datasheet Window and re-open the table, the record you have inserted will be located in the order of the primary key of the table.

¡       Use the arrow keys/Home key/End key

¡       Use the mouse

Click the grey left hand side portion of the record you want to move to, which in turn will locate the record selector 4to that record. Also note that * indicates the end of the table where you can insert another record. As you are typing a new record, the indicator will turn into a pencil.

¡       Edit Menu; Go to command

·         Field Navigation

¡       Use the arrow keys/Home key/End key

          Note: If you are at the first field of a record then pressing the left arrow will move you to the last field of the previous record. If you are at the last field of a record then pressing the right arrow will move you to the first field of the next record. 

¡       Use the mouse

Just click to the field required

Inserting a new record

·         The record navigator                4*

·         The Table Datasheet Toolbar     4*

·         Insert Menu; New Record Command

IMPORTANT NOTE:

After typing the new record and pressing the Enter key, the record is saved immediately. Actually, saving occurs as soon as you move to the next  record or close the table. That is, you don’t have to execute the Save command explicitly to save the date in the table.

Deleting a record

1.      Navigate to that record

2.      Use the         button on the toolbar, or press the Delete key, or right-click and select Delete Record.

3.      Answer Yes to the question asked to accept the delete operation.

AutoCorrect Feature

This feature is also available in Access. For instance try typing “teh” and observe that it converts to “the”.

You should issue the Tools Menu; AutoCorrect Command to add any extra text to convert automatically.

Sample: Add PH for Prentice Hall.

Printing the table

File Menu ; Page setup

File Menu ; Print

IMPORTANT NOTE:  

You can only undo the most recent command.

Exiting Access

1.      Close the table(s)           File Menu; Close

2.      Close the database         File Menu; Close

3.      Exit Access                             File Menu; Exit

Do not , in any case, take your floppy disks out of the floppy drives before exiting microsoft access.

Find and Replace Commands

Edit Menu ; Find/Replace Command . See figure 4 for a demonstration.

Figure 4

If the Match Case check box is checked then ph is different than PH.

The search is performed over the specified field/table in the Look In list box.

In the Match list box, selecting Whole Field identifies that Davis is different than Davison.

The replacement may either be selective or automatic. Selective replacement is a loop of find next and replace/find next combination whereas automatic replacement means clicking the replace all button.

Data Validation

The ability to check for errors prior to saving data. Some data validation is automatically performed by Microsoft itself. For instance, it will not allow you to enter letters into a numeric field. You might want to add extra validation yourself. For instance, consider the employee table. You may add a validation to the Employment Date field so that it will be a date between the foundation of the company and the day of data entry. Considering the books table, you may prefer to reject any record that omits the title.

Sample: Try inserting XXX in the price field for a new record.

Filters and Sorting

A filter  displays a subset of records from the table according to specified criteria. A sort lists those records in a specific sequence such as alphabetically by last name or by social security number.

Figure 5

Using Forms

To open an existing form

1.      Click on the Forms tab in the database window

2.      Double click the appropriate form icon, or

Click the icon and press the enter key, or

Click the icon and click the Open button.

To add a new record in a form:

Click the 4* button on the record navigator or click the Add Record command button, if available.

To advance to next field in a form ,  use the tab key.

If a drop-down arrow exists for a field, then click on it to display the list of available values for that field and select the required value from the list. Sample: Publisher

To switch to datasheet window, use the View Menu; Datasheet command or the corresponding button on the toolbar.

See Figure 6 for details.

Figure 6

Using Queries

To open an existing query

1.      Click on the Queries tab in the database window

2.      Double click the appropriate query icon, or

Click the icon and press the enter key, or

Click the icon and click the Open button.

3.   Enter any parameter value and click OK button. See Figure 7.

Figure 7

IMPORTANT NOTE:

The results of the query are displayed in the Datasheet View on which you can do editing, insertion and deletion. See Figure 8.

Figure 8

Using Reports

To open an existing report

1.      Click on the Reports tab in the database window

2.      Double click the appropriate report icon, or

Click the icon and press the enter key, or

Click the icon and click the Preview button. See Figures 9 and 10.

Figure 9

Figure 10

Here select Fit in the Zoom box so that the page to be printed is displayed to fit on the window. To avoid unnecessary pages you should re-design the report.

Click Print  button to print your report.

Relationship between tables

The tables in an Access database are created independently, then related to one another through the Relationships window.

To create the relations between tables:

1.      Activate the Relations window:
pull down the Tools menu and click the Relations command.

2.      Display the tables:
pull down the Relationships menu and click the Show Table command; double-click (or select the table and click the Add button) the required tables and close.

3.      Arrange the tables and make all fields visible by dragging from the borders.

4.      Create the relationships:
click and drag the related field of one table to the related field of the other table. In the Edit Relationships dialog box, check the box to enforce Referential Integrity. Click the Create button to create the relationship.
A line shows that a relationship is created. The number 1 or the infinity symbol (¥) appear at the ends of the line to indicate the nature of the relationship (1-1, 1-M, M-1, or M-M). Note that the infinity symbol identifies many.

5.      Save and exit:
Click the Save button on the Relationship toolbar to save the Relationships window, then close the Relationships window.

Referential Integrity

The tables in a database must be consistent with one another, a concept known as referential integrity. Thus, Access automatically implements certain types of data validation.

Designing a new table

The order of the fields is not significant. Neither are the specific field names. What is important is that the table contain all necessary fields so that the system can perform as intended.

Guidelines for table design

1.      Include all of the necessary data (design according  to reports that may be required; think of  future: eg/ Number of inflating 0’s in TL)

2.      Store data in its smallest parts (surname and name separately, address, city, state, zip, country separately)

3.      Do not use calculated fields(keep birth date instead of age)

Creating a table in Access

q       Either by Table Wizard, that is, using one of the available pre-defined tables in Microsoft Access, or

q       By defining every field one by one yourself  (Design View)

For each field, you define:

q       Field Name (up to 64 characters including letters, numbers, space)

q       Data type

Number (digits, decimal point, plus or minus sign)

Text (up to 255 characters)

Memo (up to 64,000 characters)

Date/time

Currency

Yes/No (Boolean/Logical) or True/False

OLE (field created by another application)

AutoNumber (1,2, ... assigns the next number when you add a record)

Hyperlink (Web address-URL)

·         If there is no apparent primary key for a table, than add a new field of type AutoNumber.

·         The Default data type is Text.

·         In the Design View, just press the first letter of a data type to switch to that data type.

A table has two views:

1.      Datasheet View (add, edit, delete records)

2.      Design View ( create and modify a table)

In the design view of a table the following information on each field is displayed:

1.     Field Name

2.     Data Type

3.     Properties

Plus the primary field(s)is indicated by a key before the field name.

A property is a characteristic or attribute of an object that determines how the object looks and behaves. Every access object (tables, forms, queries, reports) has a set of properties. Properties are displayed and/or changed in a property sheet.

IMPORTANT NOTE:

First select the field in design view whose property you want to display.

Each field has its own set of properties that determine how the data in the field are stored & displayed. The properties are set to default values according to data type, but can be modified as necessary. The field properties displayed in the design view are:

·         Field Size (adjusts the size of a text field or limits the allowable value in a number field)

·         Format (changes the way a field is displayed)

·         Input Mask (data validation: the data entered by the user must match the input mask)

·         Caption (label other than the field name to be used in reports and forms)

·         Default Value

·         Validation Rule (rejects data that does not conform to the validation rule)

·         Validation Text (error message to be displayed when data does not conform to the validation rule)

·         Required (must value)

·         Allow Zero Length (null text or memo fields)

·         Indexed (for efficient search purposes each table may be indexed on various fields, e.g. surname; the table is indexed on the primary key by default)

In order to add/delete a field in the design view select (point to) the corresponding field(s), right click and select add/delete rows as appropriate.

In order to change the primary key, point to the row where the new primary key field resides, right click and select the primary key command.

If the primary key is composed of more than one field, select all the components by pressing the Ctrl key upon selection and then issue the Primary Key command.

Deleting a table from a database

Select its icon in the Tables tab of the Database Window and press the Delete key.