Tasks:

Write Java or Python programs to answer the following questions using the database that you created in

Project $$ Part $1.$

$$ Please put all the code into one source file $($one java file or one python file$).$

$$ Language version

o Clearly denote your Java or Python version.

o Java version should be $11$ or above.

o Python version should be Python $3\mathrm{.}5$ or above.

$$ You can hardcode the database connection parameters $($host name, user id$,$ database name, and

the database connection password$)$ in your program, or put them in a configuration file.

$$ Your program should contain a main function

o The main function should have question # as the first parameter.

o The main function should also have other necessary parameters to answer individual

questions.

$(5$ points$)$ Create a README.txt to write down the work allocation of the different team members.

In this file, also write down the full name of the team representative who submit the team project.

$(5$ points$)$ Each team member needs to create a PeerEvaluation$\_.$txt to include a

peer evaluation to your team members for this part of the project. Your peer evaluation should

include a score $(1$ to $5,$ with $1$ being poorest and $5$ being best$)$ and a justification for your score.

The justification does not need to be long.

Note:

$$ If your program cannot be compiled correctly, or cannot be run correctly, this part of your project

will be graded as ZERO even though your SQL can be run correctly through the interactive

interface of any DBMS$.$

$(10$ points$)$ The correctness of the program $($compiling$,$ taking parameters, connecting to

databases, etc.$)$ counts for $10$ points.

o For the database connection, please use hostname $$localhost$,$ database name

$$cs$482502,$ username $$dbuser$,$ and password $`$Iwilldowell$.$ Your TA will create a

$2$

database with the given name, create a user $($with the given username password$)$ in their

MySQL database server, and have all the tables to test your program.

$(80$ points$)$ Each of the following questions counts for $10$ points.

Queries:

$1.(10$ points$)$ Find the sites that are on a given street $($i$.$e$.,$ the address contains the street name

$($case insensitive$)).$ Show the detailed information of each site. To get the answer to this question,

the command to run is

$./$proj $1($for Java$)$

python proj.py $1($for Python$)$

$2.(10$ points$)$ Find the digital displays with a given scheduler system. Show their serial nos, model

nos, and the names of technical supports who specialize their models. The scheduler system

should be a parameter input through the main program. To get the answer to this question, the

command to run is

$./$proj $2($for Java$)$

python proj.py $2($for Python$)$

$3.(10$ points$)$ List the distinct names of all salesmen and the number of salesmen with that name.

The output should be in the ascending order of the salesmen name. If multiple salesmen have the

same name, show all the attribute values for those salesmen. For instance, if the Salesman

relation contains the following $4$ records

$(1,$Peter$,$M$),(2,$ Mary, $$F$),(3,$John$,$M$),(4,$ Mary, $$F$).$

The output should be:

Name cnt

$------------------$

John $1$

Mary $2(2,$Mary,$$F$),(4,$Mary,$$F$)$

Peter $1$

To get the answer of this question, the command to run is

$./$proj $3($for Java$)$

python proj.py $3($for python$)$

$4.(10$ points$)$ Find the clients with a given phone no$.$ The phone no should be a parameter input

through the main program. To get the answer to this question, the command to run is

$./$proj $4($for Java$)$

python proj.py $4($for Python$)$

$5.(10$ points$)$ Find the total working hours of each administrator. Display the administrators$$

employee ids, names, and total working hours in ascending order of the total working hours. To

get the answer to this question, the command to run is

$./$proj $5($for Java$)$

python proj.py $5($for Python$)$

$6.(10$ points$)$ Find the technical supports that specialize a specified model. Display the names of

those technical supports. The specified model no should be a parameter input through the main

program. To get the answer to this question, the command to run is

$./$proj $6($for Java$)$

python proj.py $6($for Python$)$

$7.(10$ points$)$ Order the salesmen with descending order of their average commission rates. Display

each salesman$$s name and the average commission rate. To get the answer of this question, the

command to run is

$./$proj $7($for Java$)$

$3$

python proj.py $7($for Python$)$

$8.(10$ points$)$ Calculate the number of administrators, salesmen, and technical supports. Display the

results in the following format.

Role cnt

$------------------$

Administrator $10$

Salesmen $40$

Technicians $20$

To get the answer to this question, the command to run is

$./$proj $8($for Java$)$

python proj.py $8($for Python