COMS W4111 - Introduction to Databases - Spring 2013

Project 2: Object-Relational Schema Design

Due Date : Thursday April 25 at 5:00pm

Teams, Submission Instructions, Grading

Project Description

In this project, you will learn about some of the object-relational features of the Oracle 10g database management system. You will incorporate some of these features in your Project 1 database schema - you will not need to expand your front-end even if you followed the Web Front-End Option for Project 1. 

Oracle 10g provides several object-relational (OR) features that we ask you to explore in this project. You can find supporting documentation on these features in the Oracle Database Application Developer's Guide--Object-Relational Features. Start by reading the "Introduction to Oracle Objects" section of the Oracle manual to get a general view of the key features supported and get familiar with the terminology. Then proceed to the "Basic Components of Oracle Objects" section. The document describes OR features such as object types (also known as row types, see Section 23.2 of the textbook), object references (OIDs, REFs; see Section 23.6 of the textbook), varrays (see Section 23.2 of the textbook), and how you define methods (Section 23.4.1 of the textbook) and inheritance (Section 23.5 of the textbook).

In particular, the following features are of importance to this assignment:

• Object Types and Objects
• Object Methods
• Type Inheritance
• References (REF, DEREF)
• VARRAY
• TABLE
• VALUE

For Project 2, we ask you to modify your Project 1.3 schema so that your new schema includes the following:

1. At least one object type and at least one new relation of the object type. 
2. Your new relation(s) should make use of at least one additional OR feature from the list above (methods, inheritance, references, etc.).
3. You should populate your new relation(s) with data, no fewer than 10 tuples
4. You should prepare and submit at least 3 meaningful queries involving your new relation(s) and the OR feature(s) you implemented. 

If you have chosen the web front-end option,  you will not demonstrate or expand the web front-end; rather you will modify your database schema and demonstrate the use of OR feature with the 3 queries mentioned in Item 4 above.  

Your grade will depend on whether you use the OR features in a natural and meaningful way. We suggest that you do not dramatically change your original relational schema. Simple (but meaningful) changes will do the job. Also, while you are designing an object-relational schema for this project, the design guidelines that you learned earlier in the course still apply: for example, you should include the appropriate primary keys from your Project 1.3 and enforce referential integrity as well as other constraints as much as possible. We encourage you to discuss your modifications to your original schema with your mentor. 

We encourage you not to delete your Project 1 schema from your Oracle account in order to do Project 2, in case we need to revisit it for whatever reason later in the semester. For your Project 2 submission, however, please make sure that you only export the schema tables, etc. corresponding to Project 2 and not those for Project 1. Specifically, when you run the exp export utility, you should only list the Project 2 tables in the command line (see item 1 in the "What to Submit" section, below).

Notes on Oracle:

If you choose to implement object methods, you are not required to know PL/SQL (Oracle's server-based procedural extension to SQL) in order to complete this assignment. The object types and methods that you implement for this project will generally fit the following template, but you will most likely have to return more complex SQL expressions, of course; in particular, note that attr_1 + attr_2 in the return statement may be replaced by any valid expression that may appear in a SELECT clause (e.g., the result of an arithmetic operation, a call to an aggregate function, a SQL case statement, etc.).

create type my_typ as object (
  attr_1 number,
  attr_2 number,
  member function My_Function return number
);
/

create or replace type body my_typ as
  member function My_Function return number is
  begin
    return attr_1 + attr_2;
  end;
end;
/

To populate your object-relational database, you may either load your data directly into to your tables or, it may be more convenient to read your data from your existing tables from Project 1 and insert them into your object-relational schema using SQL insert statements. Alternatively, if your data reside in a file in a comma-delimited format, the easiest way to load the file into your database is to use the Oracle SQL*Loader utility that we mentioned in Part 2 of Project 1. 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.

Teams

You will carry out this project in teams of two. Since this project expands on the database schema of your Project 1, the simplest way to carry out this project is to work with the same teammate and mentor as for Project 1. Both students in the team will receive the same grade. 

However, you are free to switch teammates if there is a problem with your existing teammate that makes it impossible to continue working as a team (e.g., you teammate dropped the course). In this case, you and your new teammate will need to decide whether to expand your schema or your new teammate's schema. In addition, you will need to inform right away your old teammate (if still in class) as well as the mentors involved telling them your decision on which schema your new team will be working on. To make any change on your team composition, you need to get explicit permission from your mentor by Wednesday April 17 at 5:00pm. 

Submission Instructions

What to Submit

1. A transcript of your schema. For this, generate the dump file expdat.dmp using Oracle’s export utility exp. 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/Mac, 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:
   • For the Oracle server on w4111b.cs.columbia.edu:
     exp userid=<userid>/<password>@ADB TABLES=table-1,table-2,…,table-n ROWS=Y
     where table-1, table-2, …, table-n are the names of all the tables in your database; note that you do not need to include any auxiliary/temporary tables that you only created to populate the object tables.
   • For the Oracle server on w4111c.cs.columbia.edu substitute ADB above with ADB2.
   • For the Oracle server on w4111f.cs.columbia.edu substitute ADB above with ADB3.
   • For the Oracle server on w4111g.cs.columbia.edu substitute ADB above with ADB4.
     
     
2. A plain-text file named queries.txt.
   • This file contains at least three "interesting" SQL queries over your database, with a sentence or two per query explaining what the query is supposed to do and what features of your object-relational database is demonstrating. You will be graded on these queries. They have to work and they have to show the use of object-relational features in your application. There must be execution examples (use copy and paste). Please make sure you have tested the queries on your exact database that you submitted.
     
3. A README file containing the following information:
   • Your and your partner's name/UNI.
   • A list of all the files that you are submitting.
   • Your Oracle account name (not the password), so that we can check your schema implementation. Please do not modify anything in the database after submitting the project.
   • A clear description of your schema design decisions, listing explicitly which object type you added, the relation using the new type, and what additional object-relational features of Oracle you used, where, and why.
   • Any additional information that you consider significant.

How to Submit

1. Create a directory named <your-UNI>-proj2, 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-proj2).
2. Copy all the files described in the "What to Submit" section above into the <your-UNI>-proj2 directory.
3. (Unix option only) Tar and gzip the <your-UNI>-proj2 directory, to generate a single file <your-UNI>-proj2.tar.gz (containing the files in the "What to Submit" section above), which is the file that you will submit.
4. (Windows/Mac option only) Zip the <your-UNI>-proj2 directory, to generate a single file <your-UNI>-proj2.zip (containing the files in the "What to Submit" section above), which is the file that you will submit.
5. Login to Courseworks at https://courseworks.columbia.edu/ and select the site for our class.
6. From the left pane, select "Assignments" and then "Project 2".
7. Go to the "Submission" section and attach your <your-UNI>-proj2.tar.gz file (from Unix) or your <your-UNI>-proj2.zip file (from Windows/Mac).
8. Hit the "Submit" button.

Grading

Your grade will be based on the following factors:

1. Quality of your new schema design as you described it in the README file. We are going to look at if and how you have incorporated into your design the OR features as described in Items 1 through 3 listed in the Project Description section above. This is 60% of your grade.
2. Execution of the queries in the queries.txt file (Item 4). This is 40% of your grade.