[Solved] Assignment 1 CS440

The assignment is to be turned in before Midnight (by 11:59pm) on January 19th. You should turn in the solutions to this assignment as a PDF file through Canvas. The solutions should be produced using editing software programs, such as LaTeX or Word, otherwise they will not be graded. The assignment should be done in groups of two students. Each group must submit only one file that contains the full name, OSU email, and ONID of every member of the group.

1: Relational Model and SQL (10 points)

Consider the following relational schema:

Emp(eid:integer, ename:string, age:integer, salary:real)

Works(eid:integer, did:integer, pc time:integer)

Dept(did:integer, dname:string, budget:real, managerid:integer)

The underlined attributes are keys for their relations. Note that a manager is an employee as well and their manager id and employee id are the same. An employee can work in more than one department. The pct time field of the Works relation shows the percentage of time that a given employee works in a given department and is always greater than zero.

dname

Business Development

A database sample (sample db.sql) is provided with this assignment and the output of the correct queries over this sample database is given in each part of this question. We have created an account for each student on the MySQL server of our department. The access guide to the MySQL server (database access guide.txt) is also posted with this assignment. You can import the sample database to your account on the MySQL server and use it to ensure that your queries are correct. You do not need to submit any .sql file in your assignment submission and must write your final SQL query in the single PDF file that you submit for all questions in this assignment.

Write the following queries in SQL. You should not submit any .sql file in your assignment submission and must write your final SQL queries in the single PDF file that you submit for all questions in this assignment.

• Return the did and dname of the departments with at least one half-time (50%) employee. (1 point)

The answer on the sample database is:

did	dname
8	Hardware

• Print the names of every employee who works in Hardware, Software, and Research departments. (1 point)

The answer on the sample database is:

ename

Shirish Ossenbruggen

• Return the names of every department without any employee. (1 point) The answer on the sample database is:
• Print the managerids of managers who manage only departments with budgets greater than $5 million. (1 point)

The answer on the sample database is:

managerid

110511

• Print the name of employees whose salary is less than or equal to the salary of every employee. (1 point)

The answer on the sample database is:

ename

Antonio Lavante

• Print the enames of managers who manage the departments with the largest budget. (1 point) The answer on the sample database is:

ename

Tonny Butterworth

• Print the name of every department and the average salary of the employees of that department. The department must have a budget more than or equal to $ (1 point) The answer on the sample database is:

dname	average employee salary
Software	48291
Human Resources	717092.5
Research	490439.6666666667
Hardware	61842.125
Customer Service	40000

• Print the managerids of managers who control the largest amount of total budget. As an example, if a manager manages two departments, the amount of total budget for him will be the sum of budgets of two departments. We want to find managers that have max total budget. (1

point)

managerid

111692

• Print the name of every employee whose salary is less than or equal to the average salary of all employees in his/her departments. (1 point) The answer on the sample database is:

ename

Alex Dalas

Antonio Lavante

Tonny Conner

Shirish Ossenbruggen

DeForest Hagimont

Tonny Butterworth

Shigehito Kropatsch

• Print the name of every employee who works only in Hardware department. (1 point) The answer on the sample database is:

ename

Alex Dalas

Sergio Ravarez

Antonio Lavante

Tonny Conner

Gladys Cooper

Rodney Ferreri

Arie Staelin