Assignment 1 CS440 Solved

45.00 $ 22.50 $

Category:
Click Category Button to View Your Next Assignment | Homework

You'll get a download link with a: . zip solution files instantly, after Payment

Description

5/5 - (2 votes)

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

 

  • assignment_1-pd1keo.zip