Ms-access 2003 Brief Data-base Introduction

Document Sample
Ms-access 2003 Brief Data-base Introduction Powered By Docstoc
					Introduction to Databases


By the end of this lesson, learners should be able to:
     Understand the usefulness of a database
     Define a relational database
     Appreciate databases using real world examples



Organizing Our World
 The world generates an enormous amount of data from virtually every imaginable
 aspect of living -- credit cards, store merchandise, telephone systems, web sites, etc.
 Years ago this was a manual effort to track and report against this information. Today,
 computer systems and databases manage this information. Databases simplify our
 lives.

 Think of an address book you might buy in a pharmacy. In it, you will write all your
 important contacts -- friends, family, relatives, companies, recruiters, and other
 people in your network. The address book contains all the data you need to contact
 anyone at any time.




Organizing Our World (continued)
 Now what if you needed to pull out those contacts that represent family members?
 Perhaps another family member might want this information. You would have to flip
 through all the pages of the address book and write down the exact same information
 onto another piece of paper, or perhaps even another address book. This is time-
 consuming. Contact records are duplicated. A change to one phone number means it
 has to be changed in two or more places: in the address book and anywhere else
 where you recorded the information.




 Over time, your address book will become old and worn. The records in it will be
 crossed out and changed as people move from one place to another, or change their
 phone number. You need a new address book. And then you need to rewrite all that
 information again in a brand new book.

 It's a lot of work.

 What you need is a database.




What is a relational database?
 A database maintains order and structure in our lives. It is easy to maintain. It
 manages information and then shares it with other tables and databases.

 Many entries in your address book will undoubtedly contain identical information --
 name, address, city, state, zip code, and phone number. This information might be
 written into a table called the Contacts table.
 You may want to structure your address book so that family members are separated
 from companies called during an employment search, for example. These categories
 -- family, friend, relative, company -- might appear in a second table called Contact
 Types.
 Maybe you want write notes for certain calls. This is useful during an employment
 search. Information pertaining to a telephone call -- date, time, and notes, for example
 -- might be saved to a third table called Calls.
What is a relational database? (continued)
 The information contained in any one table might in and of itself tell us very little.
 The Contact Types table, for example, provides relatively little information that stands
 on its own: Family, Friends, Relatives, Companies, Recruiters and Network.
 However, if you could relate this table to the Contacts table -- name, address, and
 phone -- then you could separate contacts by category. The Contact Types table
 becomes useful, therefore, when related to another table.




 Similarly, by relating the Calls table with the Contacts table you can relate specific
 phone calls to specific people.




 The power of a relational database is twofold: 1) information is managed in separate
 tables to make maintenance easier, 2) data can be combined by relating different
 tables.
Real world examples of database applications
 Databases play an enormous role in nearly every aspect of our lives. Think of the
 amount of credit card transactions that take place during a given day, from receiving
 authorization at the point-of-sale, to applying the purchase to your credit card, to the
 store receiving payment from the credit card company. There are many stores, many
 banks, and many credit cards involved. Databases are all around you. You only need
 to learn how to view the world in terms of databases.

 Think of your favorite department store. Chances are the one you shop at is just one
 of several in the chain. And these stores are probably located in different states.
 Databases manage this information.

 Inside the store there are different departments: Ladies', Men's, and Electronics, to
 name a few. Within Electronics, there are different categories called classes and
 these might be represented by radios, televisions, CDs, and DVDs. More databases.

 And within any given class -- CDs, for example -- there are subclasses like jazz, rock,
 country, and classical. Within rock there are bands and within bands there are
 recordings.

 Get the idea? Even more databases.

 This structure categorizes different kinds of information at nearly every level of the
 store. It is essential to maintaining order and accuracy.




Real world examples of database applications
(continued)
 How do these databases work together? Well, everything in retail begins with the
 product or item. Each item has a barcode that uniquely identifies it. These items are
 defined in an Item database and associated with a specific department defined in
 a Department database.
 When a buyer decides to purchase some products for sale in a store, he or she
 creates an order in a Purchase Order database, pulling the individual items to buy
 from the Item database. The order might then be transferred to Communications
 databases that electronically transmit the order to the vendor, and also to
 an Accounts Payable database to pay for the merchandise.
 Trucks deliver merchandise to the store (don't forget the vendor has their own
 databases that tells what they shipped), where the products received are input into
 a Receiving database. The received quantities are then matched against the
 original Order database to make sure that all products ordered were received.
 An Inventory database might tell the sales clerk which items to move to the selling
 floor because the shelves are bare, and what other items might be stored in the
 stockroom. For items moved to the selling floor, a Shelf Planning database might tell
 the clerk exactly where to place the product on the shelf.
 When you shop and take items to the checkout counter, the cash register will lookup
 the price of each item in thePrice Lookup database. Want to pay by check or credit
 card? Databases are used to ensure that sufficient funds are available in the account.
 Every item sold in the store needs to be removed from inventory so that it can be re-
 ordered. This adjustment might be made in the Inventory table to alert the buyer that
 a certain amount of items need to be ordered to refresh the store's inventory.
 Many things happen and many databases are used to make sure the products you
 want make their way from the manufacturer to the stores and then into your homes.




Introduction

By the end of this lesson, learners should be able to:
     Understand the relationship of database components
     Understand the specific roles of tables, forms, queries, and records



What is a Database?
 A database is a collection of information organized and presented to serve a specific
 purpose. Database programs are created using a program like Microsoft Access
 2003.

 A Microsoft Access database is made up of several components including:

     Tables
     Forms
     Queries
     Reports
 These components are called database objects. One or more of these objects are
 formed when a database is created. These components are stored in a single
 database file.




What is a Table?
 Data is stored in one or more tables. Separate tables are usually created for specific
 topics, such as products or suppliers. Tables can be related to one another to access
 the different types of information. Because data is stored only once -- you probably
 would not save the same information in two different tables -- your database becomes
 more efficient.
 The columns and rows in an Access table resemble an Excel spreadsheet. Tables
 organize data into columns (called fields) and rows (called records). A record is
 comprised of one or more fields, depending on the number of fields defined to the
 table.
 Individual fields in an address book table might consist of name, street address, city,
 state, zip code, and phone number. A single record is an entry that uses all these
 fields, such as your brother or sister's contact information.




What is a Query?
 A query lets you find and retrieve information from one or more tables based on a set
 of search conditions you define (e.g., certain fields in one or more tables). The results
 can be displayed in a manner of your choosing. Queries can be created using a
 wizard or developed from scratch in the Query Design view.
 An online form can be created to view, input or change information in one or more
 tables. In this course, we will see how forms are used as both menus and as data
 entry forms to database tables. Forms can retrieve data from one or more tables, and
 display the output on the screen.




What is a Report?
 A report is an effective way to analyze and present data in a printed format using a
 specific layout. You have control over the size and appearance of information printed
 on the report, similar to formatting you perform in a Microsoft Word document.
Introduction

By the end of this lesson, learners should be able to:
     Identify the parts of the main Access window
     Identify the parts of the database window
     Understand the role of the design windows



Launching Microsoft Access from the Windows
Desktop
 Microsoft Access is launched from the Windows desktop in a manner similar to that
 used to launch Microsoft Word, Excel, or PowerPoint. You can launch Microsoft
 Access either from the desktop shortcut or from the Start program.

To open Microsoft Access using the desktop shortcut:
     Double-click the Microsoft Access shortcut icon on the Windows desktop.




To open Microsoft Access using the Start program:
     Click the Start button located in the lower left corner of the Windows screen.
     Click the Programs option on the Start menu.
     Click the Microsoft Access selection.

      (If Access does not appear, then click the double down areas immediately below
      the Microsoft PowerPointentry and locate Access from a complete list of software
      installed on your computer.
The Main Access Window
 When you open Microsoft Access, many items you see are standard in most
 Microsoft software programs like Word, Excel, and PowerPoint.
Menu bar



 The Menu bar displays all the menus available for use in Access 2003. The contents
 of any menu can be displayed by clicking on the menu with the left mouse button.




The Main Access Window (continued)
Toolbar
 The pictured buttons in the toolbar are quick and easy shortcuts to specific actions.
 For example, if you want to save a spreadsheet using the menus then you would first
 click File and then click Save. Using the toolbar to perform this save operation
 actually saves you a click. Click the save button once to save the spreadsheet.



 Some commands in the menus have pictures or icons associated with them. These
 pictures may also appear as shortcuts in the Toolbar.
The Database window
Database Toolbar



 The Database toolbar presents operations that can be performed against different
 database objects, including buttons to open an object, design an object, create a new
 object and delete an object.
Left Pane




Database objects are created and opened by choosing any of the buttons listed in the
left pane of the database window.

Right Pane




Any of the first four options -- Tables, Queries, Forms, or Reports -- opens additional
choices related to that selection in the right pane.

Choosing the Tables button, for example, displays at least three options in the right
pane: 1) Create a table in Design view, 2) Create a table by using wizard, and 3)
Create table by entering data. It also shows any objects that you created in the
database.
Introduction

 By the end of this lesson, learners should be able to:
        Understand datasheet basics
        Understand field properties
        Understand table relationships
        Understand the role of the primary key



Datasheet basics
  The core component of a database is a table. Data is defined and stored in a table.
  Multiple tables -- each consisting of different types of data -- can be created in a
  database.




  Each row in the database is called a record. The entry for John Smith is called a
  record. The entry for Martha Tompkins is also a record. Each row or record is made
  up of columns or fields -- L.Name, F.Name, Phone, Address, City, State, Zip -- which
  contain a particular piece of information.
L.Name       F.Name     Phone          Address                 City       State     Zip

Smith        John       919.555.6320 100 Paramount Parkway     Morrisville NC       27560
Tompkins   Martha    919.555.6427 97 Hummingbird Court         Cary       NC        27513

  In a Contact Management database, a list of names -- those contacts to whom you
  have sent resumes or have met through your personal network -- might be
  maintained in a table, along with address, phone number, and other personal
  information.




Field Properties
  Every table contains a number of columns called fields or datatypes. Fields are
  unique pieces of information that make up the information in a table. Tables usually
  contain multiple fields.
  In a previous example we mentioned that a table might consist of the fields: Last
  Name, First Name, Phone, Address, City, State, and Zip. Each field has unique
  properties. Some contain characters. Others contain numbers. These Field Properties
  are defined when the table is created.




Understanding Table Relationships
  Databases can be simple -- consisting of a single table -- or made up of many
  different tables. If you were to convert your resume into a database, for example, you
  might have a table that contains your name and personal mailing address. We might
  call this the Contact_Information table.
 Your work experience is a different kind of information. Instead of identifying who you
 are or where you live, it identifies the companies you worked for, their addresses,
 your job title, and responsibilities. Because this set of information is independent from
 the contact information, we might instead create a second table called
 theWork_Experience table.
 The same is true of your educational background. It has no direct bearing on your
 contact information or the companies where you worked. A third table might be
 created called the Education table to save this kind of data.
 The database contains three tables, each independent of the other, and all containing
 different types of information. The database needs a way to connect these three
 tables.




Primary Keys
 Every table in Microsoft Access must have at least one field that uniquely identifies
 each record in the table. This field is known as a primary key. This primary
 key essentially opens the door to the table and allows you to retrieve information from
 the table.
 The primary key is the mechanism by which you relate different tables and combine
 information for viewing (query) or printing (report).
Introduction

By the end of this lesson, learners should be able to:
     Download a database from the Web
     Open a database in Microsoft Access 2003
     Understand the tables, forms, and reports in the Contact Management database



Download the Contact Management Database
 The Contact Management database used in this course can be downloaded from the
 GCFLearnFree.org® Web site and installed on your computer.

   GCFLearnFree.org's Access 2003 lessons all use examples from the Contact
 Management Database. However, if you'd rather work with another existing database,
 you should have little problem following our lessons.

To Download the Contact Management Database:
     Click the link, and download and save the Contact Management database (992k).

      (Note, you must have an open connection to the Internet in order to download the
      database).
     The following dialog box appears:




     Click the Save button.
     You are prompted to save the database in some location on your computer. You will
      want to save the database in the c:\My Documents folder.




     Click the Save button to download the database



Open the Contact Management database
 Now that you've downloaded the Contact Management database, let us open and
 become familiar with it. We will briefly discuss how it was created and look at some of
 its database components.

 If properly downloaded, the Contact Management database will be present in
 the c:\My Documents folder on your computer. It is recommended that you do not
move the database from this location until after you have completed the Access 2003
course.
To Open the Contact Management database:
    Open Microsoft Access by selecting its associated icon on the Windows desktop.
    Choose File     Open from the menu bar.




    The Look In box in Microsoft Access 2003 defaults to the c:\My Documents when the
     application is first opened.

     If the My Documents folder does not appear in the Look In box, then click in the Look
     In drop-down box, and locate and select the My Documents folder.
     Select the Contact Management.mdb file.




     Click the Open button to open the Contact Management database.
 The main Contact Management database screen appears on your computer screen:




Reviewing the Contact Management Tables
 Tables are found in the database Object called Tables. The Contact Management
 database contains four tables: Calls, Contact Types, Contacts, and Switchboard
 Items.
Reviewing the Contact Management Forms
 Forms are found in the database Object called Forms. The Contact Management
 database has seven forms that are used as both menus and data entry screens.
 These forms are Call Details Subform, Call Listing Subform, Calls, Contact Types,
 Contacts, Report Date Range, and Switchboard. You can define additional forms to
 meet your data entry needs.
Reviewing the Contact Management Reports
 Reports are found in the database Object called Reports. The Contact Management
 database has two reports defined to it: Alphabetical Contact Listing and Weekly Call
 Summary reports. You can define additional reports in the database depending on
 your reporting needs.
Introduction

By the end of this lesson, learners should be able to:
     Launch the database wizard
     Create a database using the database wizard tool



Launching the Database Wizard
 The Contact Management database was created using the Microsoft
 Access Database Wizard. We will briefly review the steps involved in creating this
 database.
 Microsoft Access 2003 is equipped with a database wizard that can be used to create
 any of ten simple databases: Asset Tracking, Contact Management, Event
 Management, Expenses, Inventory Control, Ledger, Order Entry, Resource
 Scheduling, Service Call Management, and Time and Billing.

 In this lesson we will briefly review the steps involved in creating the Contact
 Management database.
How the Contact Management Database was Created Using the Database
Wizard:
    Choose File New from the menu bar.
    Click once on the Databases tab near the top of the New window.




    If the Templates window does not show, you may need to select On my computer from
     the task pane on the right side of your screen.
   Click once to select on the Contact Management icon.
   Click the OK button to launch the wizard.




   Specify the location where the database will be saved.
Verifying the Intent of the Contact Management
Database
 The first screen of the wizard verifies the type of information that will be stored in the
 Contact Management database: Contact information and Call information.




     Click the Next button to continue.



Selecting the Tables and Fields to be included in
the Contact Management Database
 The second screen of the wizard identifies the tables to be created. Three tables were
 created: Contact information, Call information, and Contact Types.

 Click on any table to display the fields contained to it in the Fields in the
 table column. Click on a different table and other fields used by that table will replace
 the ones displayed for the table selected.
     Accept all other default field selections by leaving those checkboxes marked by a check.
     Click the Next button to continue.



Defining the Format of the Screen Display
 The third screen asks the question, What style would you like for screen
 displays? Microsoft Access provides ten different screen displays for your use. A
 thumbnail picture is also provided for each format listed.
     Click through the different format options displayed on the screen -- Blends, Blueprint,
      Expedition, etc. -- to display a picture of each format on the left side of the wizard
      screen. Highlight the desired format -- the Contact Management database used the
      Standard format -- to be used.
     Click the Next button to continue.



Defining the Format of the Report Display
 The third screen asks the question, What style would you like for printed
 reports? Microsoft Access provides six different report displays for your use. A
 thumbnail picture is also provided for each format listed.
     Click through the different format options displayed on the screen -- Bold, Casual,
      Compact, etc. -- to display a picture of each format on the left side of the wizard screen.
      Highlight the desired format -- the Contact Management database used the Corporate
      format -- to be used.
     Click the Next button to continue.



Naming the Database
 The next step is to assign a name to the database.
     Assign a name to the database by typing a file name in the What would you like the
      title of the database to be field.
     Click the Next button to continue.




Finishing the Wizard
 The final step asks whether the database being created is to be opened after it is built
 by the wizard? It has no bearing whatsoever on the actual building of the database. If
 you don't want to open the new database at this point then you can always return to it
 later and open it in Microsoft Access.
   Leave or remove the checkmark that appears in the checkbox associated with the Yes,
    start the databaseprompt.
   Click the Finish button to initiate the database build.

				
DOCUMENT INFO
Description: This document covers the following topics. Introduction to Databases Organizing Our World What is a relational database? Understand the relationship of database components Understand the specific roles of tables, forms, queries, and records Identify the parts of the main Access window Identify the parts of the database window Understand the role of the design windows Understand datasheet basics Understand field properties Understand table relationships Understand the role of the primary key Launch the database wizard Create a database using the database wizard tool