Databases Assignment 2

Scope
Preparation of this assignment requires implementation of the conceptual modeling tasks and implementation of the SQL statements included in laboratory experiments and exercises of week 1, 2, 3, 4, 5 and 6.
Objectives
The objective of this assignment is:
to modify and to extend the structures of a sample relational database,
to implement the modifications of database contents,
to implement the simple and complex queries.

Tasks
Download a2create.sql and a2drop.sql from the elearning space, execute the script file a2create.sql to create tables before task 1 implemented, and execute the script file a2drop.sql at the end of this assignment to save your space.
Task 1: Data definitions, modifications and manipulations (4 marks)
Implement SQL script in a file a2task1.sql that performs the following tasks. Add the tasks’ number in the comment lines for each sub-task. For example,
/* Task 1.1 */
1. Add a new constraint in the table Department so that the value of manager# should refer to the column e# in the table Employee. Add a new department record of which number is 6, name is “Research Centre”, manager# is “00271”, he will start the job from “01/06/2016”. The manager’s name is “Garret”, who was born on “10/04/1980”, live at “13 Hindmarsh Ave, North Wollongong, NSW 2500|”. His salary will be $125.2 K per year and his supervisor is “Albert”. The new department is located at “3 Renovation campus, North Wollongong, NSW 2500”.
(0.8 marks)
2. Add a constraint that an employee cannot work on a project for more than 30 hours per week.
(0.2 marks)
3. Change the length of project’s title to VARCHAR2(50). (0.1 marks)
4. Add a new column total_staff_number in the table Department. Set the correct values for the new column. (Note: The correct values should be got from the corresponded table by using one SQL statement). (0.6 mark)
5. The department “SPORTS” will be merged into the department “GAMES”. The
manager of the department “GAMES” is still the manager of that department. The
employees that worked in the department “SPORTS” stay in the same locations.
Update correspondent rows in the other related tables (Don’t forget to update
total_staff_number in the table Department).
(0.8 mark)
6. Modify the check constraint in the table Dependent so that all values of relationship could be “SON”, “DAUGHTER”, “SPOUSE”, “MOTHER”, “FATHER”, and “OTHER”.
(0.5 marks)
7. Implement a parameterised SQL script that prompts a department number, then display the department name and total number of project that the department has. (0.5 mark)
8. Implement a parameterised SQL script that prompts about full information describing a project and inserts a new row into the table Project.
(0.5 mark)
Task 2: Data retrieval operations (4 marks – 0.4 marks each)
Implement the following data retrieval operations using SELECT statement (one
statement for each question) of SQL in a file a2task2.sql.
Add the tasks’ number in the comment lines for each sub-task. For example,
/* Task 2.1 */
1. Find all the departments’ numbers and names if they have office located in
“Wollongong”.
2. Find all the departments’ numbers, names and managers’ numbers that have no
project.
3. Find all the departments’ numbers, names and total project budgets for each of them.
(Display 0 (zero) if a department has no project). The title of the column of total
project budgets should be “TOTAL BUDGETS”.
4. Find all employees’ numbers, names, department numbers, and total hours per week
that the employees work on the projects (display 0 (zero) if an employee has no
project) for each of them. The title of total hours per week should be “TOTAL
HOURS/W”.
5. Find the employees’ number and names that directly supervised by “Alvin”.
6. Find the employees’ number and names that have no dependent by using NOT
EXISTS.
7. Find the employees’ numbers and names who work on the projects with budgets
more than $20000.
8. Find sponsors’ names and total budgets that each sponsor provided.
9. Find the employees’ numbers and names who work on the projects sponsored by
“Education committee”.
10. Find the average age of employees (Note: You may use MONTHS_BETWEEN() to
compute how many months of the age).
Deliverables
Task 1
Submit a file a2task1.lst which contains the execution results of the script file
a2task1.sql. Remember to put SQL*Plus command SET ECHO ON in the front of
the script file.
Task 5
Submit a file a2task2.lst which contains the execution results of the script file
a2task2.sql. Remember to put SQL*Plus command SET ECHO ON in the front of
the script file.
Submissions
This assignment is due by 11.55 pm (sharp) on Saturday, 16 April 2016.
This Assignment is to be submitted on Moodle.
Zip the files task1.lst and task2.lst obtained as the solutions of tasks 1 and 2 into a file
assignment2.zip and submit the file through Moodle in the following way:
(1) Connect to Moodle.
(2) Navigate to a folder ASSIGNMENT SUBMISSIONS
(3) Click at Assignment 2, Submit your solution here link.
(4) Click at Add Attachments button.
(5) Navigate to a location where a file assignment2.zip has been saved.
(6) Select the file and click at Open button.
(7) Click at Submit button.
(8) Click at OK button to return to Home Page.
A policy regarding late submissions is included in the course outline.
Only one submission of the first assignment is allowed.
The assignment must be submitted as soft copy only.
The first assignment is an individual assignment and it is expected that all its tasks will
be solved individually without any cooperation with the other students. If you have
any doubts, questions, etc. please consult your lecturer or tutor during lab classes or
office hours. Plagiarism will result in a FAIL grade being recorded for that assessment
task.
Late submissions do not have to be requested. Late submissions will be allowed for a few
days after close of scheduled submission (up to 3 days). Late submissions attract a mark
penalty; this penalty may be waived if an appropriate request for academic consideration
(for medical or similar problem) is made via the university SOLS system before the Due
date. No work can be submitted after the late submission time.
Marks and comments on the assignments will be returned to the students in two weeks
after the submission on Moodle.
End of specification