Back to 321 Main Page

Mobius strip

Assignment 1

The objective of this assignment is to get you ready to use Oracle by creating a couple empty tables.

Due: 11 Feb 2003

Please do the following:

*Install Oracle 9i Personal Edition as documented at (or find a lab with it installed).

* Start up Oracle, log in, and create a user account for yourself with DBA and Resource privileges. Be sure that your password is the same as the username you give yourself.

* Create both a CAPTAIN and an ITEM table.

  • CAPTAIN should include the fields: CAPTAIN_ID, CAPTAIN_NAME, PHONE, STREET, CITY, STATE, and ZIP.
  • ITEM should include the fields: ITEM_ID, DESCRIPTION, DATEOUT, DATEIN, CAPTAIN_ID. (If you prefer to place DESCRIPTION in its own table with a key to it, as discussed in class, that is also acceptable.)
  • CAPTAIN_ID in the CAPTAIN table and ITEM_ID in the ITEM table should be constrained as primary keys.
  • Note that you're only creating the tables; you don't need to input any data into them.

* Create an index for ZIP from the CAPTAIN table and for one column of your choice from the ITEM table. (Think about it though--which attribute do you think having an index would be most helpful for? Which attribute is likely to be involved in the most queries?)

* Export your database into a [your username here].dmp file. It should only be a few kb. Email it to me at

Read this document for the step-by-step details of this assignment.

Let me know if you have any questions.

Questions & Answers

Which datatypes should I be using?
For this assignment, you really only need to use VARCHAR2, NUMBER, and DATE. You can learn more about some of the Oracle datatypes here:
Does the SYSTEM password make a difference?
The system password should not make a difference. Just remember what you set it to so you can log in to Oracle. (You can tell it to save it locally so you don't need to enter the password each time.) I will need to know your user password though, so you should make it the same as your user ID. You can change passwords by clicking on a particular user under ORCL->Security->Users.
How do I check if my dump really worked?
Create a second user account, remembering to give it DBA and Resource privileges. Export the completed database from your first account using "exp", as per the assignment instructions. Then use "imp" to import the file into your second account to see if it worked. Give your second account's username and password to imp. Go with the default (just hit enter) for most of the questions. When asked for the username half-way through, it wants the user of the exported database (your first account.)


This assignment is worth 10 points. This is how it breaks down:

  • 3 points: If I can import your database (with "imp") and access it.
    (If I can't get past this step, you'll get a 0, since I won't be able to grade it.)
  • 3 points: If you have the two tables with reasonable data types and sizes for each of the requested attributes.
  • 2 points: If you have the primary key of each table properly constrained as such.
  • 2 points: You have two indices other than the primary key indices.

~ztomasze Index : TA Details: ICS321: Assignment 1
Last Edited: 04 Feb 2003
©2002 by Z. Tomaszewski.