Chris Pollett> Old Classses >
CS157a

( Print View )

Student Corner:
[Submit Sec4]
[Grades Sec4]

[Lecture Notes]
[Discussion Board]

Course Info:
[Texts & Links]
[Description]
[Course Outcomes]
[Outcomes Matrix]
[Course Schedule]
[Grading]
[Requirements/HW/Quizzes]
[Class Protocols]
[Exam Info]
[Regrades]
[University Policies]
[Announcements]

HW Assignments:
[Hw1] [Hw2] [Hw3]
[Hw4] [Hw5] [Quizzes]

Practice Exams:
[Midterm] [Final]

HW#5 --- last modified December 09 2019 15:56:34.

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:

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:

  1. Exercise 6.3.1 out of the book.
  2. Give a relational schema consisting of at least two relations and instance of these relations with at least three rows each. Give SQL queries involving CROSS JOIN, NATURAL JOIN and LEFT OUTER JOIN for your relations and show what they would compute on your instance.
  3. Exercise 6.4.6 out of the book, but everywhere you see the word average replace it with maximum, and everywhere you see maximum replace it with average.
  4. Exercise 6.4.8 out of the book.

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:

  1. Create the two tables: Employee(id, name, address, departmentName, supervisorID, salary) and Project(id, name, budget, managerID). Employee.supervisorID should have a foreign key constraint which references Employee.id, Project.managerId should have a foreign key constraint which references Employee.id.
  2. A view PublicEmployee(name, departmentName, supervisorName) which might be used by public users to see the name, department name, and supervisor's name for any employee.
  3. A trigger which is executed before a row is inserted into the Employee table and a trigger which is executed before an Employee row is updated. These triggers should set an employee's salary to be the maximum of the salary inserted or updated and 200000 times the number of projects they are manager for. I.e., if someone is the manager of three projects their salary will by at least 600000, but might be higher (if the value was higher in the original insert or update).

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.

Point Breakdown
Problems (each problem 1pt - 1 fully correct, 0.5 any defect or incomplete, 0 didn't do or was wrong)4pts
Employee.sql is as specified above, each item 1pt3pts
Employee.csv, Project.csv ,and Transcript.txt are as specified above (including trigger test).1pt
WhichEmployee.java works as described (1pt compiles and connects to DB, 1pt produces correct output).2pts
Total10pts