exer9

BU509 IT in Orgs
Exercise #9 Updated 24 Oct 2000

If you enjoyed the password exercise, you'll probably enjoy this one, too. If you didn't, well....

At PSC we have a recent version of the Oracle database management system (DBMS). Oracle happens to be the leading DBMS for large corporate applications. (Whereas Microsoft Access is probably the leading DBMS for PC applications). They are both based on Ted Codd's Relational Data Model* circa 1970. The Relational Model has a structure which requires that all data be expressed in rows and columns, similar to a spreadsheet, although a database is NOT a spreadsheet. They just look the same in many respects.

The rows and columns go by other names. Rows are comparable to "records". Codd called rows "tuples" (a hold over from relational algebra and calculus on which this model is based). Column headings are comparable to "fields", or "attributes", or "vectors" in relational vocabulary. The rows and columns, taken as a unit, are "tables". Every table has a table name and one or more field names, and at least one of the fields must be a unique identifier. For example, "Social Security No." is the unique identifier on PSC's "student" table. The table might look like this:

The Structured Query Language (SQL) is one of two popular query languages [the other is Query By Example (QBE)] that has been adapted to most DBMSs. SQL allows the user to construct queries that read existing tables for the input data to answer questions. An example of the use of SQL might be a Sales Manager who wants to know "What are the names and telephone numbers of all customers who have purchased over $1000 worth of stuff since January". If a user can speak in SQL terms then he/she is more likely to be able to retrieve the information required to be more productive. In reality, many user won't be writing their own SQL queries, but will be communicating their queries to systems analysts who will actually compose the queries and run them.

The BU509 SQL exercise simulates creating a query. PSC's ITS has set up 30 user accounts for the 28 BU509 students on a Unix server called "Rabbit". Students can telnet to Rabbit from the main "BU509 Contents" page (item #12) or go directly using the URL telnet://rabbit.plymouth.edu

Our username/password is different from your oz account. This semester ITS set all BU509 students up using the following model (from a message from ITS):

> I have made all the oracle accounts... The scheme is as
> follows:
> Unix names: bu50900 bu50901 ... all the way to bu50930
> Unix Passwords: busdept00 busdept01 and so on
> Oracle Accounts: bu00 bu01 and so on.
> Oracle passwords are the same as the unix ones.

NOTE: BU509 students should go to the BU509 roster page and get their BU509 student number to the left of their respective names. This two digit student number should replace the last two digits in the instructions above. P.S. Ned is assuming the 00 account.

Assuming that you get into Rabbit, next get into Oracle's SQL module using "rabbit.plymouth.edu> sqlplus". You have yet another [last one] username/password hurdle which is constructed using the model noted above.

Assuming that you are now in SQL, the prompt has changed to "SQL>" and only SQL commands work. Everybody should already have a USPRES table (US presidents) in their SQL accounts (we put them there), so that you can run queries on that table. Apparently there is also a VPRES table and a PRES table that I have played with yet. The structured format for an SQL command is:

SQL> select attributes [ where attributes = fields, a variable of your choice. "*" = wildcard, or "all fields"]
     2> from table [ where table is a variable. In this case "uspres"]
     3> where conditions ; [ where conditions is a Boolean expression using fields and values]
          Note: All SQL commands end with a semi-colon, where ";" means "end of command, run it."
          Also, you'll keep getting numbered lines after each "Enter" until you type ";".

Give the following a try:
SQL> select fname
2> from uspres
3> where yrborn>1900;

You should get 8 names from John to Bill.

SQL> describe uspres [will show you the actual field names in uspres]
SQL> select * from uspres; [will dump the whole table]
SQL> quit [gets you out of SQL]
% logout [gets you out of Rabbit]

The undergraduate SQL exercise is also available for your perusal. Some of the parts don't apply to this BU509 exercise, but some are similar and you might pick up a trick or two. If you are able to get "John...Bill" try a few variations (who died while still in office?) I will then add a few twists that will allow you to create "save-able" output that can be sent to me, or linked from your homepages.

Good luck, and enjoy.