You will build your project incrementally, throughout the semester. Unless otherwise noted, each of these project deliverables is due at the normal assignment due time, one week after it is assigned. Be sure to upgrade previous submissions as you proceed through the semester.

1. Vision — Write a one-to-two-paragraph description of a non-trivial information system project. The domain of application is up to you. You don’t need to give a full description of the database structure at this point, but you should describe the stakeholders for your database (give a short list of potential users) and your domain should be complex enough to support the following requirements.

   • The information requirements of your domain should be compatible with the database approach discussed in the text, Sections 1.3 and 1.8.
   • The project, when fully developed, should require at least 6-8 tables with a variety of inter-table relationships and integrity constraints. Most practical, information-based domains will be compatible with this requirement.
   • The domain should be different from the domains discussed in the text and in class. Also, be prepared to compare and contrast it with other similar information systems already out there.

   Talk to me if you have any questions about whether your idea fits these requirements or if you need suggestions on where to start.

   Submit your vision as a text file in vision.txt by the end of unit 2. See the policies page for submission details. We’ll grade on the quality and novelty of your idea and on the level of domain details you give.

2. Design — Design an ERD for your domain and then translate it into a relational database.

   • Your schema should have the size and complexity discussed in the previous project assignment.
   • Prove that your schema satisfies the requirements for an appropriate normal form, and justify the normal form you chose.
   • Implement your database in Oracle.
   • Add at least a few dozen representative records to each table as appropriate. You can find or generate data using any of the following approaches.
     • Extract real data from existing databases or applications.
     • Scrape some real data from web sources.
     • Use an online record generator to help create random sample records, e.g., generatedata.com.

   Talk to me if you have any questions about how complex your database needs to be or how much data you need to generate. Submit the following materials in project/* by the end of unit 5.

   • erd.* - your ERD model, potentially a powerpoint file or a scanned version of a hand-drawn model (e.g., erd.pptx or erd.png)
   • create.sql
     • a script that creates a new DB user and loads the database. Load the database using a loading script (load.sql) that includes calls to rebuild the schema (drop.sql & schema.sql), load sample data (data.sql) and add constraints as needed (constraints.sql).
     You may distribute these commands into more sub-command files provided that you continue to use a centralized create.sql and load.sql scripts. For examples, see the readme overview and the CPDB and GRDB command files in https://github.com/cs342/code/databases.
   • data.txt
     • a one-paragraph discussion of where you got your data.
     • a one-paragraph discussion identifying/justifying your chosen normal form.
     • a demonstration that your schema satisfies your chosen normal form.

   We’ll grade on the quality and completeness of your model and implementation, and on the correctness of your analysis.

3. Queries — Build a set of queries for your system that would serve the users/stakeholders you identified in your vision statement and test those queries on your representative data. The nature and complexity of your queries is up to you, but they should satisfy the following requirements.

   • Include at least three significant queries, with comments on what the query returns (one sentence) and who would care about the results (another sentence).
   • Include at least one view, with comments on what the view provides (one sentence) and who would use it (another sentence). Implement it as either a materialized or non-materialized view and justify your choice (one sentence).
   • Demonstrate your ability to use the following SQL features somewhere in your queries/view.
     • a join of at least four tables
     • proper comparisons of NULL values
     • a self-join using tuple variables
     • a combination of inner and outer joins
     • a nested select statement
     • aggregate statistics on grouped data
   • If appropriate, list the other ways you might have designed each query and justify why your chose your approach (one to two sentences per query).

   You may need to modify your schema (and ERD) or your database instance to support these queries. The information “value” of your queries matters here, so don’t just pick simple queries; pick queries that would matter to your stakeholders. Talk to me if you have any questions about the appropriate nature and/or complexity of your queries.

   Submit your solution as a commented SQL command file in queries.sql by the end of unit 8. We’ll grade on the utility, complexity and correctness of your queries.

4. Application — Add appropriate stored procedures, triggers, tuning features and transaction processing.

   • Create an appropriate stored procedure for your database that implements a multi-step transaction. Store this in procedures.sql.
   • Create an appropriate trigger for your database that enforces an integrity constraint (or constraints) that can’t easily be implemented with table constraints and checks. Store this in triggers.sql.
   • Analyze one of your more complicated queries from project 3 with respect to physical DB and SQL optimizations. Make a copy of the query, show the execution plan, explain why it’s appropriate for this query on your database, discussing the use of indexes and SQL optimizations that you’ve made. Store this in optimizations.txt.
   • Provide a datapump loader for your full database. You should be able to load your database and run your queries from project 3 and your stored procedures from this project. Create your database dump and par files in

     C:\Users\yourCalvinLoginId\Documents\project\

     This is important because DataPump is sensitive to the location in which you created the *.dmp file. It’s also important that this be on the C: drive in the lab because the Oracle user doesn’t have write permission to your P: drive. For simple instructions on Datapump, see how it is used for the imdbLarge database (in the imdbLarge readme file). For more details, see Oracle Data Pump Quick Start. Store the script for building a DBMS user and defining the dump directory in create.sql and the Datapump parameters settings in databaseName.par and the actual database dump in databaseName.dmp.

     Commit your par file and create script, and, for the purposes of grading, the LOG file as well.

   Submit your solution as specified above by the end of unit 11. We’ll grade on the utility, complexity and correctness of your upgrades.

5. Extensions — Add an appropriate enterprise web service and a separate NoSQL version for selected parts of your system.

   • Create an appropriate JPA-based, RESTful web service for your project application that serves up useful information in JSON format. Let your service be similar in scope and architecture to the service built in lab 12 (i.e., add JPA and RESTful support for one table plus a many-to-one and a many-to-many relationship). Explain what the information is and why it would be useful to application developers using your webservice. Store your solution as a separate IDE application in your project repo under project/j2ee/.
   • Re-implement information from two or perhaps three tables from your application in KVLite. Design the key-value structure so that is supports the following queries.
     • a useful, two-table join
     • a useful, ordered list
     This should be a separate implementation designed to demostrate the value of noSQL technologies for your project application. Include the loading/querying programs, explain why the information they provide is appropriate for a noSQL solution and evaluate whether or not Oracle's KVLite key-value system is the best type of noSQL database for your application. Store your solution in your project repo under project/nosql/.

   Submit your code by the end of unit 14. We’ll grade on the completeness and soundness of your new implementations.

6. Final Deliverable — There are no additional features to add for this deliverable. Address all the comments made on earlier project deliverables, modify your proposal so that it can serve as a vision statement for the project, modify the ERD so that it accurate reflects the table structure and include a readme.txt file that explains how to use the scripts.

   This final deliverable is due by the end of the day of the final exam. We’ll grade on the overall quality of your app, including its vision, design and implementation.