information systems technology ross malaga b copyright © 2005 prentice hall, inc. b-1 working with...

Copyright © 2005 Prentice Hall, Inc.

B-1

Information Systems TechnologyRoss Malaga

B WORKING WITHDATABASES


B-2

LEARNING GOALS

• Design a database.• Build the database tables using Microsoft Access®.• Establish relationships between tables.• Design and use forms.• Perform basic database queries.• Use Microsoft Word® to perform a mail merge with the

database.

http://office.microsoft.com/home/office.aspx?assetid=FX01085791


B-3

Database Design

• Prior to building a database, we must consider the overall logical design.

• The design includes:– The database tables

• Each attribute in a table• The primary key for the table

– Relationships between tables• Foreign keys in any tables

– An Entity-Relationship diagram provides a graphical representation of the logical database design.


B-4

Building a Table• Open Microsoft Access®

– Click on the blank Access database button– Give database a name– Create table in design view

• For each attribute that is to be in the table, you enter:– The attribute/column name– The data type

» If the first attribute is to be the primary key (e.g. Person_Number) then choose Auto Number as data type

• Assign the primary key– Save the table

• File/Save menu item• Save icon on the tool bar looks like floppy disk

– Repeat “create table” steps for other tables


B-5

Establishing Relationships

• Use the Tools|Relationships menu items to open the Relationships window

• Select the tables for which you wish to establish relationships

• Define the relationship– Point to the foreign key field in the relationship– Hold down the left mouse button and drag the foreign key

field to the primary key in the relationship

• Determine Join type• Check option to “Enforce Referential Integrity”


B-6


B-7


B-8

Designing Forms

• Select the Forms object.

• Choose the form wizard.– Select the table for this form.– Add fields you want on the form.– Pick a form layout.– Pick a style (Standard is probably best).– Enter a descriptive title for the form.


B-9

Combining Forms

• Use a “Subform” if you wish to enter data into two or more tables at the same time.

• The Subform tool puts a table containing a foreign key on the same form with the table to which the foreign key links.


B-10

Queries

• Click on the Query object.

• Choose the Create by Query Wizard option.

• Choose the table(s) to be in the report.

• Move any/all fields to the report.

• Click Next.

• Choose “Modify the Query Design” to allow entry of query criteria.


B-11


B-12

Query Criteria

• Open an existing query in “Design” view.• For each attribute included in the selection criteria

– Enter the value/condition for including records in the query output

– =“VA” under the attribute for state selects only the records that are for people from Virginia

– =“32504” under the attribute for zip_code selects only the records that are for people from that zip code

• To run the query, double click on the query name.


B-13


B-14

Mail Merge

• Open Microsoft Word and activate the Mail Merge Wizard– Tools|Mail Merge


B-15


B-16

Mail Merge (continued)

• Choose or create a main document.– Some standard document options are provided.

– You may create main document in the active window or create linked documents (very advanced topic).

• Choose data source.– Open Data Source.

– Change type to Microsoft Access.

– Find your database table.

– Click Open.


B-17

Mail Merge (continued)

• Edit the Main Document.– This is where you will create the document prototype.– Create the format for your main document.– Use the Insert Merge Field option to insert a field from

the database into the document prototype.– Repeat for all other fields from the database.– Type other text in document prototype just as if you were

doing a regular Word document.

• Merge the data from your database with the document prototype.– “Merge to Printer” to print documents.

information systems technology ross malaga b copyright © 2005 prentice hall, inc. b-1 working with...

Documents