D210930 Assignment 2: Application Design and Query Language Solution

Objectives
The purpose of this activity is to practice the query writing skills and how to design front and middle tier of a Database Application.
Submission Requirements
• Please submit your results/output in a PDF file. There must be only one report file for all the questions. Submit your code in a text (.txt) file(s) as directed.
• The name of the files must be your student number such as 100131001.pdf or 100131001 .pdf.
• File must be uploaded to the A02 submission link provided. The name of submission on the link must also be your student number.
• Your diagrams can be hand drawn or digital. In both cases, the images must be clear.
• If the files do not open properly or the content is not clear, then you will be awarded zero.
• This is an individual assignment.
Note: In database design, variations are always possible. If the business requirements are taken care of in a proper logical way as per the data modeling guidelines, the answer will be considered correct.
General Instructions
• Make sure that images/screenshots are clear. If the image is big then split it into multiple parts. Clearly write their purpose. You can add multiple images even if the question statement doesn’t say so to make sure that your answer is easy to comprehend.
Question 1: Getting the Database Ready [3 Marks]
Use the SQL scripts (a02SchemeScript.sql and a02DataScript.sql) provided with the assignment to prepare the database schema and snapshot. Before you begin, you must make sure that you don’t have schema objects with the same names already in your database. Create a schema with the name “a02schema”.
You are required to:
• Run the two scripts on your MySQL server and add the screenshots. Your screenshots must:
o Show the list of tables (there should be none) before the script is executed; use some suitable technique to do that.
o Show the list of tables after the script is executed.
o Show the count of rows in the “Event”, “People” and “ResearchTopic” tables using some suitable technique.
o The whole scripts must execute successfully.
Question 2: Query the database [1+3+3 Marks]
Write the following two queries and execute them on the database snapshot produced in Question 1:
a) Display the count of each event type grouped based on the calendar months i.e., how many events of each type happening in the months for which the data is present in the database. You need to show the output only for those months which are present in the database. For EventConference we will consider the EvDate field while for the other two event types, Journal and Book, it will be the ActivityDate of the earliest/lowest activity (from ActivityHappens). You can show this query as three different queries for each event type. Here is a sample output:


You are required to:
• Submit all the code in a script file. Name this file as yourID_q2.sql.txt.
• Run each query separately on MySQL server and add the screenshots for each query. Your screenshots must:
o Show the statement and the result of the statement.
NOTE: You are not allowed to change the schema in any way. CFPCount table is not part of schema.
Question 3: Application Design [3 Marks]
Use the three java files: A02Main.java, A02FrontEnd.java and A02MiddleTier.java.
• The class A02Main is to launch the application and GUI; you do not need to do much in this class.
• The class A02FrontEnd creates the GUI components. In this class you will be writing the suitable action code for the “Submit” button to invoke some suitable function for A02MiddleTier class.
• The class A02MiddleTier is the one where you will be doing most of the work. In this class you will write to code to establish connection with the database and process the query.
• This assignment requires installation of Java and the “Connector/J” plugin from MySQL.
Complete the class A02MiddleTier to fulfill the following tasks:
• Establish connection with the database. Please make sure that server port and authentication parameters are default values: (IP: 127.0.01, Port: 3306, User: root, Password: no password)
• For the “Submit” button clicked event, write suitable code to query the database and then display the result of the query in the given text area. The query output will contain all the columns of the “Event” table. You need to follow the restrictions given below:
o A02FrontEnd must not have any code to connect/query database. It must only be in A02MiddleTier.
o You can’t change the GUI. You can extend/add suitable functions/listeners if required. o A02MiddleTier can’t directly change the properties of GUI component in A02FronEnd. It must be done using some appropriate indirect approach, either in A02MiddleTier or A02FrontEnd. o If you feel necessary, you can extend the A02Main to add suitable functionality. o You must make sure that your submission has only three files as mentioned above. Your code must be able compile from the command line using “javac” from any folder where “javac” is accessible. Your code must be executable from the command line using “java” from any folder where “java” is accessible. See the images below:

How to compile and run the code
You are required to:
• Submit the code for the three java files. Do not change the names of the files. In each file, on the top add your ID and Name as comment.
Question 4: Testing and Reporting [8 Marks]
In your PDF document, test your application for all possible use cases. With 3 checkboxes and two radio buttons there are in total 8 variations possible.
You are required to:
• Add at least one screenshot showing each of the use cases. Add necessary explanation wherever you feel the reader could be lost.