本次数据库代写是用Oracle数据库系统设计一个员工系统，并使用Java的JDBC完成一系列的操作

CS 450 – Database Concepts

Project Assignment

Assigned: Wednesday, October 9, 2019

Due on Blackboard Assignment Submission Page on:

Friday, November 8, 2019 at 11:59PM.

This project is designed to explore the SQL Programming Language and the use of Java and the JDBC interface and function libraries to access relational databases.  In this case you will use the Oracle DBMS.

We will use the COMPANY database that was provided in HW 2.  Please use this database schema and database instances to run your client/server queries.

Part 1

For problems 1) and 2) use JDBC with Java as the host language:

1) Write a program segment that retrieves the employees who work in the Research department and print the employee’s last name and their SSN.

2) Write a program segment that retrieves the employees who work in departments located in Houston and work on the project ‘ProductZ’.  List their last name, SSN, and the number of hours that the employee works on that project.

Part 2

Write a JDBC program that will allow a department manager to add a new employee to the COMPANY database.

The program should first prompt the manager for their SSN, and then check if the employee is really a manager.  If not, prompt the user that they are not a manager, and stop.

If the user is indeed a manager, then present a screen that will allow the manager to provide the employee SSN, last name, first name, and the other attributes associated with the employee.

Next provide a screen that allows the manager to assign the new employee to one or more projects.  The total time allocated to all projects should not exceed 40 hours per week.  You may check this at runtime and provide feedback to the manager so as to allocate the hours to projects while satisfying the 40 hours total.

If the new employee has dependents, (yes or no checkbox) then enter the new dependents according to the DEPENDENT table attributes.  Please write code to handle both cases.

When the data entry is completed, print a report that can be presented to the new employee.

Deliverables for Part 2:

For Part 2 of the project, the GUI may be written in JavaScript or HTML and at the client side must provide:

1. A)  If students choose to implement the GUI using Java, the deliverable should be java file(s).
2. B)  If students choose to implement the GUI as a web page, the deliverable should include every file that page uses (JSP or html, js file, Java logic classes, lib files, and the deployment description), or a link (URL) to the web page that the student has deployed.

The GUI, on the client, must provide:

1) A way for a “manager” (student) to enter the SSN of a manager (like Borg, or any other manager), and the SQL Query would check if indeed that SSN matches a manager’s SSN. If the test is OK, then the manager can input the employee information. If the SSN does not match a manager’s SSN, then print that the SSN does not correspond to a manager and then stop.

2) Next the manager must be able to insert a new employee into the COMPANY database.

3) For the new employee, the manager should be asked to enter the employee information according to the EMPLOYEE table attributes, and then to enter one or more projects to which the employee is assigned, and to verify that the total number of hours does not exceed 40 hours total. Ideally, the GUI should interact with the manager to ensure that the employee does not work more than 40 hours over all the projects assigned. The employee can work up to but should not exceed 40 hours for all the assigned projects.

4) Use a two-box checkbox, if possible, to have the manager enter whether the new employee has dependents. If NO is checked, then no dependents are entered, and if YES is checked, the manager is to be prompted to enter the attributes associated with one or more dependents.

5) Finally, once the new employee information, including projects and dependents, has been entered, the database tables are updated, and then a report is printed and presented to the employee. The students should print the report as part of their project submission.

Screen-shots of the interaction should be included.

NOTE:  In order to facilitate grading your project submissions please make a MOVIE of your GUI and show the steps for parts 1 through 5 of Part 2.

Extra Credit

You are asked to implement an application program that accesses the COMPANY database using Java and JDBC to test for and enforce “business rules” that cannot be done using referential integrity constraints.

Here are the rules you are to implement:

1) An employee may not work on more than two projects managed by his/her department.

2) An employee must work on at least one project controlled by his/her department.

Here are the situations that may arise:

1) Perform a scan of the database to verify that no employees violate the above rules;

2) When inserting a new employee, ask for the department to which they will be assigned, and the project(s) to which they are assigned;

3) When removing an employee from a project, check if any constraints are violated, and if so reject the update and explain why it was rejected.

4) When deleting a project, the employees will also be deleted, and check for constraint violations, and explain why a project may not be deleted.  Suggest alternatives to the user.

Implementation Details

Here is the Oracle page at the Volgenau School:

https://labs.vse.gmu.edu/index.php/Services/Oracle

This assignment is to be done on your own subject to the Mason- and CS-Department Honor Codes.

You may contact our GTA, Mr. Mansur, for assistance.

When you are working with the Oracle database, you need to include the JDBC drivers to connect with the server.
To work on the JDBC related questions this is the only jar (OJDBC14.jar) you need to include.
How to add jar files:
If you are working on Eclipse add the jar files to your library – right-click on your project name /build path/configure build path/lib/add jars or else you can create a folder called libraries under your project and can add the jar files into it.

Included with the assignment is a PowerPoint presentation that should help you in using Eclipse.