• Field: "basic fact or data element"
• Record: a set of fields
• Table: a set of records
• DATABASE: one or more tables -- but more!
  • Special attention to preservation of data integrity
  • Can be queried -- can ask questions about the data in the tables
  • Can relate fields to other fields to link tables and data together -- more later in Access

• Make tables to arrange data and insure its validity
• Make forms for data entry
• Make queries to get questions answered about data
• Make reports to summarize and present data
• Make Web pages
• Make macros
• Make modules, which are more complex programs for Access
• NOTE: all of these can be held within ONE database (e.g., one Access .mdb file)

• Databases are designed to have as many features as possible that preserve data integrity. For example, when you create a new database, Access will ask you to give it a file name and save it immediately, so that a copy exists on non-volatile media. Also, you won't find a Save As... option for the entire database, because having two copies of the same database floating around can cause data integrity problems.
• While you are working on a database, DO NOT move the file. Your database saves itself constantly (every time you finish editing a record) and moving a file while it is being saved is dangerous. Your file WILL corrupt if you are not careful.
• Help is available within Access in the same way as in the rest of the Office suite. Simply choose Help or press F1.

• You will need the Bookstore Database file from the data disk. Do you have it?
• Open Access. Start>Programs>Office2000>Microsoft Access.
• Open the file. Click Open an existing file>more files....
• Open the Books table within the database and examine how to navigate the window.
• Add these new records at the end of the table:

  0-13-011190-0	Exploring Microsoft Office 2000 Vol I	Grauer/Barber	1999	$45.00	Macmillan Publishing
  0-07-054048-9	Ace teh Technical Interview	Rothstein	1998	$24.95	McGraw Hill

• AutoCorrect should have handled the "teh" typo above.
• Save... but wait! The save option isn't there! Remember that databases save data automatically when you are finished editing a record (i.e., when you hit Enter or choose another record). If you do not wish to save the changes that you have made to a record, press Escape.

• You can find records by using the Edit>Find command. Find Exploring Windows 95.
• Change this book's list price to $28.95 by using the tab key to move to that field and typing the new data. You can use the Undo button to change the price back.
• You can select entire records by either clicking the gray area next to the record or clicking inside the record and choosing Edit>Select Record.
• Delete this record by pressing the Delete key. You will be warned about this, as you will not be able to bring this record back. Keep this in mind when you do other database projects: you cannot undelete records. Click Yes to delete the record.
• Also note that because of the table's design, you are restricted from typing in nonsensical data. Try to type a text value into the List Price field.
• Close the table and open the Books form. You can see that now you are able to enter data in a slightly more friendly format. The real benefit to forms is this: you can have people enter data in a form without allowing them access to your able design (which protects data integrity). Here is another record to enter:

  1-56694-229-1

  HTML Publishing on the Internet

  Heslop/Budrick

  1995

  $49.99

  Ventana Press.

• Note the drop-down box, another measure to preserve the integrity of the data.
• Use the View menu to switch to Datasheet view. You can still have the tabular data entry format without allowing users to edit your table design. Use Edit>Replace to replace all entries of Prentice-Hall in the Publisher field with Prentice Hall (no space).
• Lastly, check out the Report in this database. Type Prentice Hall to see all books in the database published by them.

• From the data disk, open the Employees database in the Access folder.
• Add yourself to the database...

  [SSN]

  [last name]

  [first name]

  Miami

  Account Rep

  $32,000

  [gender]

  Good

• Click on the Title of any Account Rep and click the the Filter by Selection button to see only Account Reps. Do it again with the filtered data to see only those Account Reps rated "Good" in Performance.
• Try using the Filter by Form button to see only employees making above $30,000 and located in Chicago. The advantage of this button is flexibility -- you could enter "Chicago OR Miami" and can use "greater than $30,000" rather than "equals $30,000."
• Remove the filter.
• Use the Sort buttons to sort the table by Last Name, ascending and Descending. If you select two fields, you can sort by both -- fields further left in the table are sorted first, and then "ties" are sorted by the second field.

You can relate data in one table to data in another table if those two tables share a common field (or "attribute"). For example, John Doe is both a student at Hopkins and a former military man. Does Hopkins need to know everything about his military history? No -- it's unlikely that all Hopkins students have that information. Do his former commanding officers need to know what courses he's taking? No. It would be easier to match John's info from the Hopkins database to info in a military database if you needed to know something about his military history. You would use some bit of information about John that would be in each database to link the two together. This relieves data redundancy and can help keep data valid (by checking it from multiple tables).

There are several kinds of relationships. A one-to-one relationship indicates that one record in a table can match one and only one from another table. In this case, you would use a piece of data that is unique for each record in a table (often something called a primary key, which is a unique identifier for each record) and link those two. Example: John Doe's SSN from one table to his SSN in another.

A one-to-many relationship means that one record in one table can have many related records in another, but records from the other table can only relate back to one record in the first table. Example: Cars and car owners. One person can own many cars, but for insurance, each car is owned by only one person.

In a many-to-many relationship, a record in either table can relate to many records in the other table. Example: A table of student information and a table of courses at Hopkins. Each course can be taken by many students, and each student can take many courses. How does one relate the two tables in this case? Ultimately you want to link a unique identifier for students, SSN, to a unique identifier for courses, the course number. However, relational algebra, which drives relational databases, has no way of representing a many-to-many relationship mathematically. The way around this? Create an intermediate table, in which the course numbers and SSNs are associated (an enrollment table), and use one-to-many relationships into that table to effectively obtain that many-to-many relationship. This will become a bit more apparent as we do this in Access.

• Open the Look Ahead database and choose Tools>Relationships. We will relate the Locations and Employees table by the LocationID attribute. What kind of relationship is this? Well, one employee can only work at one location. However, one location can certainly have many employees -- thus it is one-to-many. The Locations table is the "one" table and the Employees table the "many."
• Click and drag LocationID from one table to LocationID in the other (see how you must link attributes that have data that match?). Choose Enforce Refential Integrity -- now, when you enter data in the "many" table, the "one" table will be checked to see if there is a matching value. If not, you can't make the record, so that you cannot fabricate locations (good for data integrity!). Try it by entering a false location in the Employees table:

  [SSN]

  [last name]

  [first name]

  L44

  T01

  $32,000

  [gender]

  Good

• Now change the LocationID to L04 (the Miami code) and your record should be saved.
• You can also enter data using the form, and because your Relationship exists, you won't need to remember that L04 means Miami or T01 means Account Rep.

  888-88-8888

  Grauer

  Bob

  Miami

  Account Rep

  $150,000

  M

  Excellent

• Open the Locations Table and press the small plus next to the Miami record (L04). You should see the related records (including you and Bob).
• You can alter this record to reflect a new location:

  L04

  Orlando

  1000 Kirkman Road

  FL

  32801

  (407) 555-5555