COMS 4111, Spring 2013
Project 1, Part 2
(worth 25% of overall Project 1 grade)

 

FAQs

Your team and "project mentor"

You will do Part 2 of Project 1 with the same team as for Part 1. If your team partner dropped the class and you did not submit a contingency plan for this with your Part 1 submission, then unfortunately you will still have to complete the whole project by yourself. If you team partner dropped the class and you did submit a contingency plan for this with your Part 1 submission, then you are welcome to switch to this reduced version of your project.

For continuity, the TA who grades your Part 1 will grade the two remaining parts of your project. (In many but not all cases, this is the TA with whom you met to discuss Part 1.) This TA will be your "project mentor" for the remainder of your project. You are welcome to contact other members of the class staff (including the instructor) about your project, but your project mentor should be your main contact, for continuity, since your mentor will be grading all parts of the project.

As mentioned earlier, both students in a team will receive the same grade for Project 1. Team partners are expected to fully collaborate with each other on solving the project. However, communication about project details with somebody other than your partner is not permitted, and is considered cheating. If in doubt about what kinds of consultations are allowed, check with the instructor, or see the Computer Science Department's policies and procedures on academic honesty. Questions of a general nature that may be of interest to the whole class should be posted to the CourseWorks discussion board.

Grading of Part 1

Teams can pick up their graded Part 1 in class on Wednesday February 27; if you could not attend the class, please contact the TAs. Once you have received your graded Part 1, please modify your database according to your mentor's instructions. Meanwhile, please start getting familiarized with the Oracle Database Management System.

Computer accounts

You can choose to do this project on your Windows/Mac laptop or on Unix.

You will not need a CS account if you choose Windows for the project. For Mac users, if at least one team member has access to a Windows machine, then you do not need a CS account for the project; if both team members plan on working on Macs, then you need a CS account to setup a Windows environment on your Macs.

Windows and Mac users will need to install software on their computers (see “Software installation instructions,” below).

If you choose Unix for the project, then at least one of the team members should have a CS account, but you will not need to install any software for this part of the project. If neither of you have a CS account, please open one.

Late policy for projects

Please check the Project Lateness Policy.

Overview of Part 2 of Project 1

For Part 2 of this project, which you should submit electronically, you should implement your relations of Part 1 over an Oracle Database server and load your tables with some real or realistic data, as you outlined in Part 1.

What you need to do for Part 2

  1. Familiarize yourself with the Oracle DBMS by reading the materials available at http://www.oracle.com/technology/documentation/index.html . Our servers are running Oracle 10gr2. The documentation for Oracle 10gr2 can be found at http://www.oracle.com/pls/db102/homepage and http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14200%2Ftoc.htm&remark=portal+%28Getting+Started%29. Please check these materials carefully before sending email to the class staff with questions on syntax or supported features.
  2. Set up your Oracle database and change your Oracle account password (see below). You only need to set up one Oracle account/database per team. You don't need to turn anything in for this part.
  3. Make all suggested changes to the overall design in general, and to the SQL schema in particular, once you receive your graded Part 1. You are likely to have extensive comments from your project mentor in your graded Part 1. Your Part 2 grade will be based in part on how well you have incorporated your project mentor's comments. If you have any questions about these comments once you received them, please contact your project mentor as soon as possible to clarify.
  4. Create all the SQL tables in your revised SQL schema on your Oracle account, including all constraints that you could specify in the table declarations.
  5. Add any additional attribute-based CHECK constraints and tuple-based CHECK constraints (as discussed in class) that you need so as to express any real-world constraints of your application that are missing from your SQL schema. Note that Oracle does not support general assertions, and that triggers are slightly different from our class description. You do not need to use triggers for this project, and you can ignore any real-world constraints that you could not capture with either good-style (as discussed in class) attribute- or tuple-based CHECK constraints, or with PRIMARY KEY, UNIQUE, FOREIGN KEY, and NOT NULL constructs. (Hint: "Good-style" attribute- or tuple-based CHECK constraints tend to refer only to the table in which they are defined, not to other tables.)
  6. Insert into each table in your database, on average, at least 10 tuples of real or "realistic" data, as you described in your Part 1 project description. This data will help you test and play with your database. Of course, issue queries of your choice to make sure that everything works as you intend.

Setting up your Oracle database

  1. You should have received an email from Abhas with your Oracle database username and password. This email was sent to your @columbia.edu email address. If you have not received such an email within a day after this project is posted, please contact Abhas at ab3599@columbia.edu immediately.
    Note:     You only need to set up one single Oracle account/database per team, even though your team was assigned one Oracle account per team member. Pick just one of the two accounts arbitrarily and work on it together with your team-mate.
  2. For this class, we have four identical Oracle database servers:

    w4111b.cs.columbia.edu
    w4111c.cs.columbia.edu
    w4111f.cs.columbia.edu
    w4111g.cs.columbia.edu

    You may use any server, as we have created independent accounts for you on all of them. (Your 4 accounts are not linked in any way.) There are four servers to help balance load and provide some redundancy. If one server goes down, you may continue development on others. It is important to note that the four machines, though identical, are independent. That is, none of your data on one machine will be replicated on the other machine.     You should use the exp command frequently so you can restore your data on the other machine if your development machine crashes; you should use the imp command to automatically recreate your schema and data on the other machine. See the “Important note on backups” section below for details about using both the exp and imp commands.

    CRF has asked us to request that students in cs4111 not call or send emergency tickets to CRF regarding a crashed database server (or any other such problem) outside of CRF's working hours. If you have any server status related issues, please contact Abhas (ab3599@columbia.edu).
  1. Connect to the Oracle server using SQL*Plus (check our useful SQL*Plus tips):  

To connect to the Oracle server on w4111b.cs.columbia.edu: sqlplus <username>@ADB

To connect to the Oracle server on w4111c.cs.columbia.edu: sqlplus <username>@ADB2

To connect to the Oracle server on w4111f.cs.columbia.edu: sqlplus <username>@ADB3

To connect to the Oracle server on w4111g.cs.columbia.edu: sqlplus <username>@ADB4

At the Password: prompt, enter the password that was emailed to you.

  1. Change your password at the Oracle prompt by typing:

ALTER USER <username> IDENTIFIED BY <your new password>;

Note that all SQL commands issued from within the SQL*Plus client need to end in ";" (or carriage-return followed by "/") to be sent to the server. Pressing <enter> without a semicolon will continue the same command on the next line.  

  1. Exit your SQL*Plus session by typing exit;.

What to submit and when

You will submit this part of the project electronically on CourseWorks directly. The deadline is Monday March 25 at 5 p.m. EST. Here is how you should proceed:

  1. Create a directory named <your-UNI>-proj1part2, where you should replace <your-UNI> with the Columbia UNI of one teammate (for example, if the teammate's UNI is abc123, then the directory should be named abc123-proj1part2).
  2. Generate the dump file expdat.dmp using Oracle’s export utility exp and place it inside the <your-UNI>-proj1part2 directory. The export utility lets you store your database objects in an external file. Note that the exp command must be executed from your shell if you are using Unix, or from the command prompt if you are using Windows, not from within SQL*Plus. The exp command creates a file called expdat.dmp in the directory where it is executed. Here is how you should run exp:
  1. Generate the following plain-text file inside the <your-UNI>-proj1part2 directory:

      File queries.txt: This plain-text file must have three "interesting" SQL queries over your database, with a sentence or two per query explaining what the query is supposed to compute. The goal of these queries is to help us better understand your application. You will not be graded on these queries, but we strongly suggest that you submit well-formed queries that run without problems, so please make sure that you have tested your queries by running them on your database exactly as submitted (use copy and paste).

  1. (Unix option only) Tar and gzip the <your-UNI>-proj1part2 directory, to generate a single file <your-UNI>-proj1part2.tar.gz (containing the expdat.dmp and queries.txt files), which is the file that you will submit.
  2. (Windows/Mac option only) Zip the <your-UNI>-proj1part2 directory, to generate a single file <your-UNI>-proj1part2.zip (containing the expdat.dmp and queries.txt files), which is the file that you will submit.
  3. Login to Courseworks at https://courseworks.columbia.edu/ and select the site for our class.
  4. From the left pane, select "Assignments" and then "Project 1.2".
  5. Go to the "Submission" section and attach your <your-UNI>-proj1part2.tar.gz file (from Unix) or your <your-UNI>-proj1part2.zip file (from Windows).
  6. Enter the names of the members of the team and their UNI in the text box.
  7. Hit the "Submit" button.

Grading for Part 2

Your grade for Part 2 of Project 1 will be split as follows:

  1. Quality of final SQL schema and implementation on Oracle: 10 points.
    We will evaluate the overall quality of your final SQL schema on Oracle, especially in terms of how thoroughly you incorporated any revisions suggested by your project mentor in the grading of Part 1 of your project.
  2. Quality of constraint handling: 10 points.
    We will evaluate how well you managed to capture real-world constraints through primary key, foreign key, unique, and attribute- and tuple-based CHECK constraints.
  3. Quality of the real-world (or at least realistic) data that you loaded into the database: 5 points.

 

Notes

Important note on backups

To avoid losing any data, we strongly suggest that you periodically use the Oracle export utility, exp, as explained above to generate a file with all your schema definitions and with the data that you uploaded to your database. You should then save this file in a safe place. For a variety of technical reasons that are not under our control, we cannot provide backups of the contents of your Oracle account, so do back up your data frequently.

 

Important instructions on Oracle timeouts and limit on the number of connections

At any given time, each user can have a maximum of 5 open connections to each Oracle server. Also, a connection will be automatically closed by Oracle if the connection has been idle for 20 minutes. (In this case, you will receive the following error message: “ERROR at line 1: ORA-02396: exceeded maximum idle time, please connect again.”) These limits are in place to make the Oracle servers more stable and avoid overload-originated server crashes as much as possible. If you find out that your SQL*Plus session has ended “automatically” and/or you are unable to connect to the Oracle servers using SQL*Plus, please check the following before emailing the TA's or posting a question on CourseWorks:

 

Data Importation

To populate your object-relational database, you may load your data directly into to your tables using SQL insert statements. Alternatively, if your data reside in a file in a comma-delimited format, a convenient way to load the file into your database is to use the Oracle SQL*Loader utility; invoke the loader on the command line as follows:

 

sqlldr userid=user/pass@ADB control=sample.ctl data=dfile.dat

 

where user and pass are your Oracle login credentials, sample.ctl is the name of your SQL*Loader control file, and dfile specifies the name of the datafile containing the data to be loaded. If you invoke SQL*Loader without specifying any parameters, SQL*Loader displays a help screen with the available parameters and their default values. Please read the documentation for details.

 

You may use any of these options to populate part or all of your schema. In any case, you will not be graded on the method you used to populate your schema, and you do not need to submit your load scripts with the project.

Strings

We suggest that for variable-length string attributes such as names, use VARCHAR2 as the domain instead of a fixed-length CHAR domain. VARCHAR2 will simplify your handling of such attributes in Part 3, particularly if you are following the Web Front-End Option. A CHAR column automatically fills white spaces until the fixed length which means that in a CHAR(10) column "test" will be "test          ". This may be a problem, because "test" is not equal to "test          " . 

Commit

By default, SQL*Plus does not generate commits automatically. You will need to issue a “commit” at the end of your updates. Example:

 

SQL> update employees set salary = 0 where ssn = 355282907;

1 row updated.

SQL> commit;

Commit complete.

A word about Oracle errors

You can show precise schema error information by typing "sho err" after getting error message like "Warning: Type created with compilation errors."

 

Software Installation for Windows/Mac/Unix

Regardless of the machine you will be using, you will need to connect to one of the Oracle servers by accessing the Oracle SQL*Plus client. The following sections explain to how to install the client software depending on the machine you are using.

Software installation instructions for Windows users only

If you choose to do your project on your Windows computer, then you will need to install the Oracle XE client software, which is freely available from Oracle, as explained next. (Note that your database—both the schema and the data—will reside on one of the Oracle servers that we provide. You will connect to the remote Oracle servers from your computer using the client software that you will install on your Windows computer.)

  1. Download the Oracle XE client for Windows from http://www.cs.columbia.edu/~biliris/Oracle10gR2XE.zip.
  2. Click on the downloaded executable to install Oracle XE.
  3. Configure your Oracle client by going to the directory in your Oracle installation that contains file tnsnames.ora. For example, if you installed Oracle XE in your C: drive, this file will be in directory C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN. Edit file tnsnames.ora by adding the following lines at the end of the file. These lines specify the Oracle servers for the class with which your client will be able to communicate:

ADB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = w4111b.cs.columbia.edu)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ADB)

    )

  )

 

ADB2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = w4111c.cs.columbia.edu)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ADB)

    )

  )

ADB3 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = w4111f.cs.columbia.edu)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ADB)

    )

  )

ADB4 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = w4111g.cs.columbia.edu)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ADB)

    )

  )

 

To connect to one of our Oracle servers, you will need to open a command prompt to access the Oracle SQL*Plus client. Then you will be able to set up your database as discussed in the section “Setting up your Oracle database” above.

Software installation instructions for Mac users only

Follow the instructions below for installing SQLPlus on a Mac which will let you connect to the class Oracle servers from your OS X terminal:

  1. Download Oracle Instant Client here (make sure you get the right version for you mac: 32 or 64 bit): http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
    To get SQLPlus working you need to download "Instant Client Package - Basic" and "Instant Client Package - SQLPlus". You'll have to register a free account with Oracle before downloading.
  2. Expand these downloaded files and put the contents of both into the same folder. Let's say you put yours in: /Applications/instantclient_10_2. Put the following in your .bash_profile file:

    export ORACLE_BASE=/Applications/instantclient_10_2
    export ORACLE_HOME=$ORACLE_BASE
    export ORACLE_SID=ADB
    export EDITOR=vi

    export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc14.jar:$ORACLE_HOME/jlib/orai18n.jar:$CLASSPATH
    export DYLD_LIBRARY_PATH=$ORACLE_HOME
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
    export PATH=$ORACLE_HOME:$ORACLE_HOME/bin:$PATH
  3. Create the file tnsnames.ora in /etc and add the following to the file:

    ADB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = w4111b.cs.columbia.edu)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ADB)
        )
      )

    ADB2 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = w4111c.cs.columbia.edu)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ADB)
        )
      )

    ADB3 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = w4111f.cs.columbia.edu)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ADB)
        )
      )

    ADB4 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = w4111g.cs.columbia.edu)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ADB)
        )
      )
  4. You should now be able to connect to the oracle servers as discussed in the section “Setting up your Oracle database” above.

 

Important note for teams using only Mac computers:

A Windows/Unix machine is necessary for running imp/exp commands on the Oracle client. These commands are necessary for backup and project submission purposes. The Oracle client for Mac does not support imp and exp commands. If both members of the team work on Macs only, you will have to work with a Windows virtual machine on your Mac for backup and submission. The instructions for creating a Windows virtual machine on your Mac can be found here: Running Windows 7 using VMware Fusion for Mac
Once you run a Windows virtual machine on your Mac, setup Oracle on the Windows virtual machine by following the Oracle setup instructions for Windows users (above section).



Unix users only

No software installation is needed. Follow the instructions below to set up your environment.

  1. Get a CS account if neither you nor your team-mate has one. 
  2. Use ssh to clic.cs.columbia.edu to access Linux machines in the CLIC cluster.
  3. Add the following lines to your .bashrc file or equivalent:

export ORACLE_BASE=/usr/lib/oracle/xe/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/server
export EDITOR=vi

export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc14.jar:$ORACLE_HOME/jlib/orai18n.jar:$CLASSPATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH

After editing your .bashrc file, please logout and login again, so that the new version of the file is loaded.

 

Frequently-asked questions