BCI 3913 Muscat College Muscat Mobiles Company Entity Relationship Diagram Project Program: Oracl for sql All requirements in the file attached C Departmen

BCI 3913 Muscat College Muscat Mobiles Company Entity Relationship Diagram Project Program: Oracl for sql
All requirements in the file attached C
Department of Computing
FINAL ASSIGNMENT
COURSE
:
STRUCTURED QUERY LANGUAGE
COURSE CODE
:
BCI3913
LECTURER
:
PRASEEDA MANOJ
DATE
:
07th JUNE 2020
TOTAL MARKS
:
100
SEMESTER /SESSION
:
SEMESTER 2 / 2019-20
PROGRAMME
:
BCN/BCS
The distribution of marks among the parts of the question is indicated.
INSTRUCTIONS TO CANDIDATE
Please upload your answers to the provided “Final Exam /Assignment” space on Moodle. You
may either type your answers in Word or handwrite and scan / photograph them.
It is essential that you write your student number at the top of your answers and, if
handwriting, on every sheet you use.
If you have written or drawn anything on paper, please keep your copy so further photographs
can be sent if needed during marking.
THIS IS AN OPEN-BOOK EXAM / ASSIGNMENT AND YOU MAY CONSULT YOUR
NOTES, BUT YOU MAY NOT CONSULT ANY ONLINE RESOURCE.
YOU MUST COMPLETE THE EXAM / ASSIGNMENT UNDER EXAM CONDITIONS. YOU
MUST TALK TO NO ONE AND MUST WORK ALONE.
INCLUDE THE FOLLOWING DECLARATION AT THE END OF YOUR SUBMISSION:
“I DECLARE THAT THIS IS MY OWN WORK”
DO NOT SIGN THIS DECLARATION.
This exam / assignment question paper consists of five [05] printed pages including the front page.
Final Assignment
The following Entity Relationship Diagram illustrates the database diagram of Human Resource
Management System for the Muscat Mobiles Company dealing with the distribution of major mobile
brands.
From the E-R diagram and you are asked to do the following tasks:
Task A: Prepare a report using E-R modelling
[25 Marks]
Outline the aims of your system. In your written report, fully document the design components: this
should include details about entities and their relationships, that model the information requirements of
the Human Resource System. Your report should contain details about the relations, show all attributes
of each entity and identify the primary key and any foreign keys used to form relationships.
Task B: Create a new relation and adding records
[10 Marks]
We want to keep track of the dependents for each employee. Each dependent has a dependentid,name,
birthdate and relationship with the employee. Create a new table Dependents in your ORACLE HR
database by executing CREATE statements. Choose appropriate data types for your attributes and set
the primary and foreign keys that will be needed.
BCI3913
Page 2 of 5
Final Assignment
Task C: Querying your database
[65 Marks]
Write and execute single SQL statements to provide answers to each of the following queries. Show
your SQL and the query results in your report:
1. Write a query to help H R Director to display unique job id of all employees.
[1 Marks]
2. The HR department has requested a report of all employees and their job IDs . Display the last
name concatenated with the job ID (separated by a comma) and name the column Employee
and Title.
[2 Marks]
Sample output
Employee and Title
King , AD_PRES
Ernst, IT_PROG
3. The HR department needs a report to display the employee number, last_name, and salary for
all employees in departments 100 or 120 in ascending alphabetical order by last_name. Label these
columns as EMPLOYEE#, NAME,MONTHLY SALARY.
[4 Marks]
4. Display the last names and hire-date of all employees who were hired in 1994 and have “u” in
their last name.
[4 Marks]
5. Write a query to display the number of people working with the same Manager. [4 Marks]
6. Following query is written by an IT staff to show the total salary of employees in each
department. Is there any error in the following query? Justify your answer. Rewrite the query,
if any mistake found.
SELECT department_id,TOTAL(salary)
FROM employees
WHERE TOTAL(salary)>20000
GROUP BY employee_id;
BCI3913
[5 Marks]
Page 3 of 5
Final Assignment
7. Create a report that displays manager number and average salary of its employees. Exclude any
group that the average salary is lower than $5000. Make sure you round the average to avoid
decimal value. Sort the output in descending order of salary.
[5 Marks]
Output example:
MANAGER_ID
LOWEST SALARY
102
9000
202
8400
155
7500
8. Create a join query that displays the employee first name, employee last name, jobid,
departmentid, department name and salary for all employees whose department name is
IT_PROGRAMMING. Sort the output in descending order of department_name. [5 Marks]
9. Find the Manager id and Manager name of all employees.
[5 Marks]
(Hint : sample output)
Employee
Firstname
Employee
lastname
Manager#
Manager
Firstname
Manager
Lastname
Ahmed
Al Zadjali
103
Dr.Malik
Said
10. Write a statement that prompts the user to input a manager ID, and afterwards will display
employee_ ID, first_name, department_id, and salary for those employee who worked under
that manager, provided the employees’s salary is more than 7000. Make sure that display
order is sorted based on the employee_id.
[5 Marks]
11. Write an SQL statement that display the first name and last name, department id, job id and
salary, of employees who works as the same job id as the employee whose employee id is
201.
[5 Marks]
BCI3913
Page 4 of 5
Final Assignment
12. Write a statement that display the names (First and Last Name) and hire dates for all
employees who were hired before their managers, together with their managers’ last_names
and hire date. Label the First Name and Last Names as “ Employee Name”, and Manager’s
Last Name as “Manager”.
[5 Marks]
13. The Oracle server uses constraints to prevent invalid data entry into tables. Constraints enforce rules
on the data in a table whenever a row is inserted, updated, or deleted from that table.
Explain the different type of constraints with appropriate examples.
[15 Marks]
END OF QUESTION PAPER
BCI3913
Page 5 of 5

Purchase answer to see full
attachment