[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.

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:

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

The answer on the sample database is:

• 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:

• 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:

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

• 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:

• 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)

• 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

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

ename