• To go through a complete design (ER Model) and development cycle for a data-intensive application.
• To build a system with multi-users: (DB) administrator, clients (or users) with different roles and access privileges.
• To exercise your knowledge on SQL interface to Oracle database and application interaction through ODBC.

A document is the name given to the collection of data your application interacts with. Usually we think of "document" as something textual in nature, it need not be. It can be a graphic drawing for a "paint" application. It is a table for a spreadsheet application and so on. In Computer Science department and in many other agencies in order to create a paperless workflow we are increasingly witnessing the use of online submission of documents. In most cases the documents are stored as many flat files in a regular directory structure. Instead of this, we desire to create a database for the documents with all the details associated with the document. The users of this system will be the person(s) who submits the documents and the person(s) who reviews the document and registers his/her response. As a case study we will use project submissions similar to CSE462. Person who submits the document will be the student and the reviewer will be the instructor(s) who will assign a grade. The student can then retrieve grade information.

We can approach this problem from the user point of view or from the data point of view. We will use the latter approach since our emphasis in this course is on database design. The database to suit the requirements above has to be designed and deployed by the administrator (SQL Client). Administrator will add (INSERT) the data pertaining to the user "instructor". Such details will include name, user name and password. The user "instructor" will interact with the database through an application (InstructorApp). Of course, the instructors have username and password to authenticate them. Instructors will enter the details of registered students’ identification number, name, username and password, and the project numbers and their respective due dates. All the above activities typically take place only once at the beginning of the semester assuming that there will be no changes to the students enrolled in the class, to the project due date and to the information about the instructors. Instructors are also the graders or reviewers of the project. After the due date the projects will be accessed by them, graded and a grade entered into the database.

Students will interact with the database through an application (StudentApp). Students will go through username and password authorization process. Students will submit their projects, which are assumed to be simple text. If a submission is on or before the due date it will be accepted and a confirmation number will be returned to the student. If it is beyond due date the submission will be rejected. Students may use the confirmation number to request grades. They may also look at the grade by providing other details such as project number. (Which is more efficient?)

After using the system all the users should gracefully exit the system by logging out. This is one of the options offered by the user interface.

1. Model the storage requirements of the system using ER Model. Design the relational tables based on this model.
2. Using SQL statements define the tables and insert necessary information into the tables. The information includes the details of the instructors. There should be information on at least two instructors.
3. Design a common user interface for both instructors and students, which will in turn invoke the student interface or the instructor interface.
4. Requests from the instructor’s interface will be handled by the InstructorApp (class) and those from the student interface by the StudentApp (class). This is only a suggestion. You are allowed to use any other reasonable object-oriented design.
5. Format of a document submitted can be a simple variable length string. Open up a simple editor window for the user to type in the document. Save the content and insert it into the database to simulate submission.
6. Some of the scenarios to be included:

-- Logs in. a) Login accepted. b) Login rejected.

-- Check if grade is ready. If it is, obtain grade for a project.

-- Log out.

1. ER Model of the database design. The schemas representing the tables.
2. Object-oriented design of the application (For example: DocumentManager class, DocumentUI class, InstructorApp class and StudentApp class).
3. SQL file that defines the database.
4. Sample runs illustrating all the scenarios.