Notes

Outline

Database Concepts

Introduction Frequently, individuals and organizations need to store and retrieve data of similar types. Whether it is customer information, product information, inventory, payroll or sales results, the timely storage and efficient retrieval and analysis of this data can mean the success or failure of the venture.!

Data is Stored in Databases A Database is an store of information of a particular type. This could be lists, inventories, invoices, patients in a hospital, staff of a business, etc. This data is stored, retrieved and analyzed with Database Software. Database Software can be “Flat File” or Relational in nature. Relational Databases are more efficient and more powerful.

One the Internet We also have something called a “DATA STORE” A Data Store can be a collection of data of different types.  This can include Databases Text Documents Web Pages Other types of textual information

Microsoft Access We will be using Microsoft Access as our Database program or database manager. Access puts all the related information for one project in a Database. An Access Databases Consist of Tables, Reports, Views, Queries and other information related to this particular set of Data.

Access Can Have Many Databases If you have several businesses you would have a Databases for each. I could have a database for ABC Company, and Once for XYZ Company I could use Access to Manage Each separately. Access Databases end in mdb, so one database could be in a file called XYZCO.MDB, and the other could be ABCCO.MDB

Tables Access Uses the Concept of a Table to organize Data A Table Could be a List of Clients and Addresses. One table could be your companies employees and their address Another table could be the customers and their addresses A Third table could be your inventory. Since they all belonged to your company they could be in the same access database.

Records Tables consist of Records In a database all the information about one person or thing is a record. Each record has the same structure for a given record type All the Inventory items have a description, location, and cost.  In the Client Table, each record has a name, address and phone number: Josephine Carr 448 34th St 565-333-0998

Fields Each separate part of the record structure is a field. For example each record in our inventory had fields called name, location, and cost In our client list we had fields called Name,  Address, and Telephone What would the fields be in this table? #223    Fred Smith Co.   $345   May 8, 1999 We could call them Number, Company, amount, and Date!

Keys Records are identified from other records by a primary key. The primary key must be unique, only one record can have it! In a list of invoices, the invoice number would be the primary key

Types of Data As you can see from the last example, the data, or information in the fields, can be of different types. Common Data Types are: Numbers:  $34.45   100,000   .0034 Dates and Times:  Feb 5, 2000, 10:01 AM Text:   Fred,  123-344-4566, 00345, Zebra Logical:  Yes, No;  True, False; Memo: Now is the time to come to my party, the date is Nov 23, 2001, and the...

Text Data TEXT FIELDS are fields with the letters A-Z, or numbers with no value.  For instance a phone number entered as 743-9123 should be entered as a text field, not a numeric field. Likewise a Zip code, such as 00456 must be entered as text field, otherwise the leading zeros mean nothing and they will be dropped! Numeric Fields are of several types, but whenever we need to keep value or do math on a field it must be entered as some type of numeric field

DATE FIELDS DATE FIELDS contain numbers in a date format of some type.   Usually these dates can be used in certain operations such as determining elapsed time between two dates. Mar 6, 1999  or 10/05/2000 are examples of dates in a data field. How they are displayed is irrelevant to their actual value.  The program can display the same date many different ways!

LOGICAL FIELDS LOGICAL FIELDS store information as one of two values, such as True or False or Yes or No. T   or True or Y or Yes would all be different displays of the same logical field.

MEMO Fields Some programs lump large amounts of character data in field types called MEMO Fields. This allows the user to store documents, notes, and other large amounts of data with the other fields which are usually shorter. This is a memo field it can be 64K long, which means thousands of characters in one long field! Memo Fields do not usually have all the lookups and other options of the other types of fields.

Field Sizes and Attributes Set the length of text fields except memo, date, and certain numbers when you first create your table, but then it is fixed unless the structure of the table is modified. Set up fields long enough for your longest entry. For example, many zips are 5 digit, but some require 10 digits, thus the field zip in a fixed length database must be at least 10 characters long Shortening fields after data is entered may result in loss of data. Typical Problem: try to enter 00785-0045 as zip, but  5 Character ZIP field only allows first 5 characters!

Deciding What Fields to Use Data can be searched by entries in a field For example you can look up the name Jones in the Last Name field of your customers table and show all the records with Jones in the Last Name Field if it is entered that way in your table.  Last Name  First Name           Jones         Howard But not if you have first and last name together in one field called name, unless you put in Jones, Howard when entered! For searches to work effectively the data you want to search by should be in its own field.

Sorting Data can be arranged or sorted by any of the fields. In order to do this the field must be indexed or sorted We can frequently sort by one field first, and then by another. For example we could sort by state, and then by city or zip: Alaska, Fairbanks, 55555; Ohio, Dayton, 22244; Ohio, Dayton, 22335; … If we want to find or look up something frequently it should be in a unique field.  Otherwise searches are slow and/or impossible. For example if we store first and last name in one field a search for a last name would be difficult.  If we store them in two separate fields called FirstName and LastName we could easily search the first name field for John, and the last name field for Smith.