HW#5 --- last modified December 07 2023 04:43:15.

Solution set.

Due date: Dec 9

Files to be submitted:
  Hw5.zip

Purpose: To gain experience with more advanced SQL queries. To gain experience using triggers, views, and JDBC.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO6 -- Define and use constraints and triggers in SQL

CLO7 -- Describe the concept of transactions

CLO8 -- Build a simple database application in a high-level programming language (e.g., Java and Python) that interacts with a relational database system at the back-end

Specification:

This homework consists of two parts: the culmination of the Hinder/Honor database application you've have been developing since HW3 and an exercise where you experiment with semi-structured data in Postgresql.

As with the previous homeworks, your Hinder/Honor database application should be developed using MySQL. For this assignment, we are going to create a finished working application. This isn't a class where we learn web (CS174), mobile (CS175), or GUI (CS 151) user interfaces, so to keep things simple to build your application, you can use System.out to print anything you want the end user to see and use System.in to receive inputs from the user. Common navigational tricks would be list a numbered sequence of options and then get the user to type the number of the particular option they want. You can also prompt the user for a sequence of values, one-by-one, if you need the user to perform some kind of form activity (For example, "Enter First Name:", "Enter Last Name", etc). To test your application, the grader will run the commands in a file create_and_populate.sql that should be in your HW5 folder. This file should have the code to create and initially populate your database that you made in HW4. Then the grader will look for a app.properties file in your Hw5 folder. This file should contain information that is read into a java.util.Properties object about the name of the database your project uses, the user needed to access the database, and any other related details. Your app should read from this file this information! The grader will modify this file according to their set-up. Next the grader will compile your app by typing:

javac RecognizeApp.java

and then will run your application by typing the command:

java Recognize

Your program when run should initially print:

Welcome to NAME-OF-APP

Here are our five recent honor/hinder recipients:
  (A) RECIPIENT_A_NAME
  ...
  (E) RECIPIENT_E_NAME

What would you like to do:
  (1) ...
  (2) ...
  ...
  (n) ...
Enter your Option:

Don't be too literal -- replace NAME-OF-APP with what you want to call your app. You should use a separate database table which maintains the list of the five most recent honoree/hinderee's using a trigger (defined in create_and_populate.sql). Your application should also non-trivially use a view somewhere to maintain information that is shown to the end user. Your application should use JDBC to communicate with the database and should implement each of the transaction you had from HW4 problem 2. I.e., the options listed on the start screen should take you to various activities. At least one of these transactions should be carried out by invoking a stored procedure in MySQL (You should tell the grader where to look for each of these gradeable items in your readme.txt file). The interface structure of your application is otherwise left to you. The grader though will grade for the overall usability and finish.

For the semi-structured data experiments with Postgresql, first, I'd like you to create a simple DTD for Notes. A Notes should have a string Theme, an Author, and a collection of 0 or more Note document fragments. A Note document fragment should have Title string, a Creation date, and a string Body fragment. Save your DTD in a file Notes.dtd which you include with your homework. Next in the file semi_structured.sql I want you to include the DDL to create a PostgreSQL table with a XML column type. Include also the DML to insert at least three Notes documents each with at least two Note fragments. Include as well the SQL query needed to retrieve all the Note fragments whose Notes has author Chris Pollett and which were written after December 25, 2022. Next convert each part of the what you did instead using PostgreSQL's JSON type as the semi-structured content model.