Computer Assignment 4 and final Project

CS-315 - Assignment IV (10 points)

Task Description: Consider the following relations for a company:

Department (Did: integer, Dname: string, location: string)

Employee (Eid: integer, DepartmentID, integer, Ename: string, Erank: integer, salary: real)

Project (Pid: integer, DepartmentID: integer, Pname: string, budget: real, start year: integer)

The “DepartmentID” fields in both Employee and Project are foreign keys referencing the “Did” field of the Department table.

Task1 (2 points) - Through MySQL workbench, use SQL to create a new database (named MyCompany) then use it to build the relations (tables) mentioned above. Place the constraints (e.g., default values) you find appropriate. Then use SQL, insert (random but meaningful) data records in the above tables.

• Create 2 different departments (named: Marketing and Human Resources) • Create at least 3 employees per department (6 employees in total). A rank of an employee is an

integer value between 1 and 3. • Create at least 4 different projects per department (8 projects in total). The start year is between

2018 and 2020.

Task2 (8 points – 2 point/query): Using SQL, develop and execute the following queries:

1- Display the average salary of employees working in the “Marketing” department. Give appropriate name to the output.

2- Display the rank value and the average salary of employees per rank.

3- Display names and ids of employees from the “Human Resources” department with salaries >= average salary of employees from the “Marketing” department.

4- Display employees by name working in the “research” project with salary between 70,000 and 100,000. Sort the results in descending order with respect to the employees’ rank.

Submission:

1. For task 1, take clear screenshots to the queries you developed to create the tables.

2. For task 1, after inserting the records, take a clear screenshot for the content of each table – as follows:

SELECT * FROM theTableName;

No need to take screenshot to the developed “insert” queries.

3. For task 2, for each query, take a clear screenshot for both the query you developed as well as the output.

4. This is an individual assignment -- Cheating/plagiarism will be checked and will receive zero.

5. Submit ONE PDF file directly to the folder titled Assignment 4 under the D2L Assignments tab (other formats will not be accepted). Don’t submit .zip, .ppt, .pptx, .doc, .docx file.

6. The assignment is due 10:00pm – November 14th. You can submit your assignment, within 24 hours after this due date, to be graded out of 50% of the assignment’s grade. After this grace period your late submission will not be accepted.