C SC 100 Lecture Notes

C SC 100 Lecture Notes
Spring 2008
Pete Sanderson

[ previous | schedule | next ]

major resource: Tomorrow's Technology and You (Complete), Eighth Edition, Beekman and Quinn, Pearson Prentice Hall, 2008

Chapter 7, Databases

What is a database?

a collection of information stored in an organized form on a computer ("electronic filing cabinet")
methods for organizing, storing, retrieving, communicating and managing information

What are databases used for -- what would your life be like without databases?

have you used an ATM recently?
have you used a credit card recently?
have you opened your Otterweb page recently?
have you had your Cardinal Card scanned recently?
have you used a Kroger/Giant Eagle/whatever card recently?
have you purchased anything online recently?
have you flown on a commercial airline recently?
have you called anyone on your cell phone recently?

If you can answer yes to ANY of these questions, you have interacted with databases that have information about you.

Basic concepts and terminology

database is the collection, typically stored as a file
- like a spreadsheet, it can be visualized as one or more 2-dimensional grids
table is one 2-d grid of related information

analogous to one worksheet in a spreadsheet workbook

record is the information relating to one person, other entity, or event.
- a record can be visualized as one row in the table
field is a discrete piece of information in a record
- a database field can be visualized as one column in the table
- a field in one specific record can be visualized as one cell in the table
- each field has a specfic type and format: numeric, textual, date, picture, computed (like spreadsheet formulas)
key field is a field designated as being unique for each record
- for student records, key value used to be social security number, now unique ID number assigned
- each record must have different value in key field
- the database is ordered (sorted) based on key field value, to make searches faster
- sometimes called an index field
- imagine if Columbus White Pages listed names randomly instead of alphabetically?
- imagine if Columbus White Pages were listed in order based on phone number instead of name?
- interesting fact: Iceland (population about 300,000) has one telephone book, and people are listed alphabetically by their first name. Their last name is formed by taking their father's first name and appending "son" if a boy and "dottir" if a girl. Women do not change names upon marriage.
browsing is a method for retrieving information from a very small database - just peruse it.
query is a method for asking for specific information from database.
- every time you submit a search request to a web search engine, you are making a query
- there are many query techniques and languages, more on this later
views are different ways of presenting database information
data mining refers to sophisticated new techniques for discovering patterns and trends in huge databases
relational databases consist of related database files which point to each other's fields, to minimize duplication of information. More on this later.
database management system (DBMS) is software to manage complex databases consisting of many related files.

Query techniques

Web searches as queries

You explored this topic in Project 1.
Every Web search engine has an "advanced search" feature.
Fine-tuning your search results in fewer but better "hits".
All allow the use of Boolean logic.
- join two terms with AND to get hits only if both terms appear (most search engines insert an implied AND between terms)
- join two terms with OR to gets hits if either or both appear
- these can be combined, e.g. database AND (query OR browse)
use of quotes "" around a phrase to match the exact phrase, e.g. "database query techniques"
use of asterisk * to match character sequence within a word. e.g. trai* matches any word starting with trai, such as trail, trailer, train, training, trainer, trainee, etc.
ability to limit search to a specifc server or web site -- Google feature that allows you to specify search term(s) then "site:" followed by site to search. So to search for the term "registration" only at otterbein's site the search would be: registration site:www.otterbein.edu
How can web searches be completed so quickly??? Exact details are tightly held secrets, but basically the search sites have software that automatically "surfs" from page to page and stores words from each page into highly-indexed databases.

SQL: a language for forming database queries

Structured Query Language, pronounced "S-Q-L" or "sequel"
has become the lingua franca of database management products (lingua franca: a common language used by speakers of different languages)
SQL contains a few specific commands for filtering information contained in a database, yet queries are somewhat English-like in appearance
- SELECT command specifies which fields to display from matching records
- FROM command specfies which database table to search
- WHERE command specifies search criteria to filter records from the database
think of database as mathematical set of records and the WHERE command defines subsets whose intersections (AND) and unions (OR) determine matches
Some SQL examples for database table StudentList that has fields called FirstName, LastName, Age, Credits (and more):

SELECT FirstName FROM StudentList WHERE LastName = "Jones"
SELECT FirstName, LastName FROM StudentList WHERE (Age > 20 AND Credits < 12)

Relational databases

A database may consist of many related tables.
The example in the textbook is a student database for a university.
There is a table containing transcript information, another table containing financial aid information, another table containing class rosters.
Some information is required by all three: student name, ID, class rank, and major.
If this common info is replicated in all three, then any change to it (say, student changes major) requires all three tables to be updated.
In complex systems, this becomes very error-prone.
Solution: place common information into a separate table, where other tables can point to it. Change only needs to be made in one place!
Since tables point to each other, we say they are related and call it a relational database.
In this example, "pointing to" is accomplished by storing the student's ID in all the tables.

Microsoft Access as illustration

Access is the DBMS (DataBase Management System) part of Microsoft Office suite.
Widely used for "small" databases and easy to use
Comes with sample databases.
Illustrate with the Northwind Traders sample.
some terminology:
the entire database is stored in one file, identified by ".mdb" filename extension
the database consists of 7 different types of objects:
- a table is one grid of information. Each row is a record, each column is a field.
- a query is a stored query specification (e.g. an SQL command).
- a form is a nicely structured data entry form so you can enter data more easily (instead of directly into the grid)
- a report is a nicely structured data display form so you can view data more easily
- a page is a web page which can be generated from database contents
- a macro is a stored command sequence, so you can easily automate repetitious tasks
- a module is a computer program you can write using Visual Basic language to perform special processing.

Here is a view of the Northwind database, with the list of tables shown. You can view a table by double-clicking on its name or icon.

You can get an idea of the nature of Northwind just by reading the names of the various tables. This is a relational database, and the relationships are shown in this illustration. The list of identifiers in each box is the list of fields in that table, and the lines represent relationships.

Databases and privacy

The textbook briefly describes a plethora of database-related privacy horror-stores, most caused by criminals creating or stealing an "identity" or mistakes made by credit bureaus.

What are some of the privacy concerns related with databases?

Did you know that the U.S. Constitution does not explicitly address the right to privacy?

Do you feel the greater privacy threat is government, or the private sector?

Sometimes it is possible to uniquely identify you and reveal information about you by combining different pieces of information gleaned from multiple unrelated databases. This is called record matching. Geneologists have long depended on this technique to learn about their ancestors. The government depends on this technique to identify potential terrorists.

Sometimes even summaries of anonymous survey results can uniquely identify you. For example, my previous university compiles and publishes an annual report based on faculty survey information. One of the summary categories is average salary by age group (20-29, 30-39, ..., 70-above). There is one professor in the "70 - above" age group. The survey is not anonymous for him.

[ Pete Sanderson | Math Sciences server | Math Sciences home page | Otterbein ]

Last updated:
Pete Sanderson (PSanderson@otterbein.edu)