CSC 101 CompLit, Fall 1996
Merrie Bergmann
Ileana Streinu
Dominique Thiébaut

Reading: Chapter 13, textbook.

Software to be introduced: Microsoft Access, from Microsoft Office.

Also, some of the material (video clips) shown in previous classes will be referred to, as well as references to topics previously discussed in the "Algorithms" lecture.

Overview:

1. What is a database?
2. Problems: creating, maintaining, querying the database.
3. Example: creating and exploring an Access database.
4. Searching and sorting, fundamental underlying algorithms.
5. Security issues.

What is a database?

Data organized so that specific items are easy to find. Usually organized around a topic or a key, which is an identifier or a special number (such as a SSN for a person or a call number for a book). The key makes individual data items and the information associated with them easy to locate.

Non-electronic databases: library catalogues ( a bit of history, maybe a video clip).

Modern database example: a library catalog database. E.g. the Five Colleges, at:

telnet fclibr.library.umass.edu

(from a PC, Mac or sophia - can be done from home, too!)

Based on the options offered for searching the library catalog, what do you think is the relevant data stored with each item in the database? What is the key uniquely identifying an item in the database? What information can be used as search keys in the database? (author, title, call number, etc.): but author, title, etc. may not be unique.

Example: a search by author may produce several authors with the same (last) name, and several books by the same author. A search by title may produce several books with the same title. Only a search by call number guarantees uniqueness (unless the library has several copies of the same book).

Creating a Database:

• What information to store.
• What is the key that identifies the data without ambiguity
• Using Forms to enter the data.

• Need to specify:
• 1. Structure of the database
• tables (files) containing the information in the database.
• fields for each table
• 2. Information in the database: records in tables (record = one row of a table). Using FORMS to enter the data.

Querying a Database:

• Designing Queries.
• Creating Reports.

Need to specify:

• table(s) where the information is stored.
• which fields to consider in the query
• matching criteria (e.g. name to search for, etc.)

• Examples of queries for a library database:
• find all the authors in a library database.
• find all the books written by author Shakespeare.
• find all copies of the book "Ulysses" available in the library.

• Examples of queries for a student grades database:
• Make a list of all the students in the class.
• Make a list of all assigned class accounts.
• Find the students with max grade for Hw1.

Maintaining a Database:

• Adding new items.
• Deleting old items.
• Updating information for some items.
  

• Other Problems: Security (who has permissions to do what):

• querying the database (everybody can log on to the Umass library database and search).
• updating the database (only certain people should have the permissions to do this).

• How to create electronic databases?
  • ad hoc methods (software custom written for a specific database); some library systems in use have been created in this way, so two different library databases may look completely different.
  • simple database-like facilities may be created using spreadsheets (e.g. Excel). Example: a database for maintaining the class grades.
  • Using a DBMS: DATABASE MANAGEMENT SYSTEM: software specially designed to facilitate easy database creation, maintaining and query design. Examples: Microsoft Access (available on PCs in Burton basement labs, under W-95); Paradox (available on PCs on Bass under DOS); Oracle, etc.

• EXAMPLE:
  • Creating the Student/Grades "database" in Excel. Discuss problems and why the "solution" is not satisfactory.
  • Creating a database with Microsoft Access: show how to create a good Student/Grades database. Using forms to enter data. Using Reports to find your grades. Packaging everything into an application.
  • If time allows: a Personal library database.

Searching:

• Given:
  • a database organized around a certain key
  • a possible value of the key (a name or id number)
• Find the key and its associated information in the database, if it exists.

Sorting.

Given: a database organized around a certain key.

Want: to arrange it in (say) increasing order of that key for faster access.

Grand finale(surprise): my favorite example of a non-electronic database.