Chris Pollett>
Old Classses > |
HW#5 --- last modified December 09 2019 15:56:34.Due date: Dec 9 Files to be submitted: 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: CLO3 -- Use a database management system's bulk loader to populate a database. CLO4 -- Write simple transactions using JDBC and ODBC, or similar programmer interfaces in other languages. Specification: As with previous assignment this homework consists of two parts, a written part and a coding/experiment part. Do the following problems and write them up in the file Hw5.pdf that you include in your Hw5.zip file your submit:
For the coding part of the homework, I want you to use the Mysql DBMS. First, I want you to make a file Employee.sql (also turned in you Hw5.zip) containing the SQL DML commands to do the following:
Next I want you to make a CSV files Employee.csv and Project.csv each containing at least 10 rows of data. Some of your rows for employees should contain project manager's earning less than the minimum amount so as to test your trigger. You can make these files in a spreadsheet like Excel by just saving the sheet in CSV format. Put these two files in your Hw5.zip file. Also, put in your Hw5.zip the transcript of you issuing a mysqlimport command to read the data in these files into the tables you created above. Put this in Transcript.txt. For the last part of the homework I want you to write a program WhichEmployee.java. It will be compiled by the grader from the command line using the syntax: javac WhichEmployee.java This program will then be tested based on the grader's version of Employee.csv and Project.csv to populate the two tables described above. It will be run from the command line using a syntax like: java WhichEmployee public_or_private part_of_name For example, java WhichEmployee public Bob java WhichEmployee private Doe When run, this program will make a JDBC connection to the database hw5 running on localhost which should contain the tables and views created earlier. The user, password, and connection string data should be defined as constants towards the top of the WhichEmployee class for the grader to modify. After connecting to the database, your program should execute a query that does a SELECT * FROM either PublicEmployee or Employee depending on whether the first argument was public or private that returns all row information for employees that manage projects whose names contain the second argument. For example, "java WhichEmployee private Bob" would use the Employee table to return the id, name, address, departmentName, supervisorID, salary of all employees whose name contains "Bob" that manage at least one project. You should order the output in increasing alphabetical order of Employee name.
|