CS 330
Oracle CBT assignments

Oracle Computer Based Training

Rationale

In lieu of homework assignments, for the next 2-3 weeks you will have to learn Oracle on your own. The Computer Based Training units that came with the software, plus your own familiarity at this point with SQL, should allow you to do this relatively smoothly. However, since this is the first time we are using the software and many unexpected things could happen, I will ask you to:

Agree to exercise caution, especially given that you are allowed, for the first week or so, to have full database administrator power.
Create separate accounts for you as soon as you learn how to (may have two or three accounts, depending on whether you work with administrator or plain user privileges), and do all the work, whenever possible, from these accounts. I will ask you to email me the names and passwords of all the accounts you create on Oracle (so that I will be able to assist you with problems and debugging, should they occur). I also ask you NOT to modify any of the already existing accounts and passwords.
At the end of each week, email me a 1-2 page status report: what you have covered from the CBT units, questions about what you have learned, what modifications you have created to the database.

What to do

I have left the first three sets of CBT diskettes in McConnell 209, by the server. They are already installed on skor, but if the workstation is already used by someone else, you should install them on some machine downstairs, in McConnell 104.

The first ones are two "courses" on Database Administration and one on SQL. You should start with database administration. You should become familiar with the software installed on skor (available from the Start menu) and figure out which component does what. Some of the components of Oracle are invoked in different ways from those described in the CBT (e.g for administration), but you should be able to figure that out. To verify that you can apply the concepts detailed in the CBTs, I will ask you to:

Create "personal" accounts for you (both admin and user accounts).
Test that you can access the starter database, as described in the user manuals I left in McConnell 209 (Please DO NOT remove the booklets from this room!).
Create a sample database for, say, the banking databse used in the textbook, and practice some of the sql queries from the book.

Next week I will install the other units (dealing with forms, reports, etc.) on the server and make the disketets available to you for installation on the clients.

You should email me when things don't work, or when you cannot figure out things, but only after talking with your classmates. I would like to start a Frequently Asked Questions file for these problems, and therefore I ask you to send me email as soon as you figure out how to do something that was particularly challenging to figure out. These contributions will count for your grade.

Other than this, the only thing you should probably know is how to use Oracle from the clients in McConnell 104 (on the server it is straighforward).

Logon the machines as dbuser.
to continue..

What to turn in each week

A status report, by email.
This should include the number of hours spent per week on Oracle.

A reminder: this is a 4-credit course, and you are expected to spend about 8 hours per week, on average, with class preparation. I expect the work to be a bit more for the first week, then slow down, then increase again as you start working on your projects and then it should be realatively steady to the end. Do NOT postpone the CBT courses and starting on the project until the very last minute. Although I will not grade your work for the next couple of weeks, I will keep track of what you are doing, and take that into account for the final grade.

More precisely: if you do not turn in satisfactory status reports each week, but eventually will do A-level work for the project, there will be no penalty. But if you do have problems while debugging the project and are in danger of getting a less-than-perfect project, and if I am aware that you started early and did the Oracle training satisfactorily, then I will take that into account and assist you with the final debugging and possibly grant a 1-2 day extension. If you didn't do the training in time, and started late on your project and did poorly, you will not be granted any extension or special assistance, and will be graded accordingly.

Hints, and what to do the first time

Use the booklet Oracle 7 Server Release 7.3.7 for Windows-NT, Getting Started.
Read Chapter 3 about the Starter Database and what it includes.
Windows-NT account with full privileges (written in the book): dbuser/dbpass.
Oracle accounts, page 3-3.
To find out what user accounts are currently been created and what tables they can access, use:

Start/Programs/Oracle Enterprise manager/Data Manager

Examples: scott/tiger has 4 tables for test purposes: bonus, dept, emp and salgrade.
Do simple queries on these tables, logging on SQL*PLUS as scott/tiger.
To logon sql*plus: Start/Programs/Oracle for Windows-NT/SQL-Plus 3.3
For user System/manager, notice the tables account and branch. I have created them when been logged on sqlplus as system/manager. They are part of the banking database from the textbook examples. Try some queries on them (make sure you are logged on as the appropriate user).
Look around to the other administration tools. Bring up the Oracle administrator toolbar. Each time you want to use a tool, log on as system/manager. Later on, make yourself an account with the same privileges as system/manager and work from there.
To create user accounts, use the Security Manager from Oracle Enterprise Manager.
Create an simple (user) account for you. Write down the password and email it to me.
Try to do something using this account. See what privileges you need. For instance, I have not been able to create a table in my user account before I went back into Security Manager as system/manager, and granted myself (as user ileana) permissions to create table and unlimited tablespace!
Try the same!
Using the SQL textbook available in McC 209 (next to skor) as a reference (your textbook MIGHT be useful, too, but not for precise syntax), create a table, insert some rows into it, then perform a simple select * query.
Learn to use the editor in SQL*PLUS, whenever you run into errors.