General

1. The coursework is due on Tuesday 16 March 2010.
    
2. Courseworks should be handed in before noon in the usual manner in the box outside the department. Your coursework should clearly indicate your full name and the course. Late coursework submitted after the deadline but before Thursday 18 March 2010 will incur a penalty of 50%. A sample answer sheet can be found here. Please use and sign the Coursework Cover Sheet in the Course Booklet.

 
3. Create the tables defined below and populate each one of them with a reasonable number of rows so that each query below produces a non-trivial result. You should hand in a listing of all the tables you have created. You do not need to submit an electronic copy of your tables, but you should keep a copy ready in case you are asked to submit it, which may happen under special circumstances.

 
4. For Part I you should hand in a listing of the SQL queries and their results. Write a sentence or two on each query describing what you did.

 
5. Accompany your submission with an Entity Relationship Diagram containing all relevant entities in the schema. Make sure elements in your diagram are clearly labelled.

 
6. Part II demands you to do some independent work; you can use the Web as an information source, but I encourage you to use other material.

 
7. Part II should NOT exceed two sides of A4, point size 11, 1 line spacing and should be done on a word processor. Make sure to reference all your information sources.

Tables Definitions

The following tables model Human Resources database for a company. The company is divided into a number of departments. Each department has a number of employees. Each employee belongs to only one department, but can work on more than one project. The projects can span multiple departments. In addition, the department manager (DMGR) and project manager (PMGR) are also employees. Therefore, they should also appear in the employee table as an ENUM.

Note: The following table definitions were written in Oracle and data types were defined using NUMBER() and CHAR(). In Microsoft ACCESS, data types are defined using different symbols, i.e. Number and Text.

employee

 Name                            Null?    Type			Comments
 ------------------------------- -------- ----			------------------------------------
 ENUM                            NOT NULL NUMBER(4)		ENUM should not exceed a length of 4.
 ENAME                                    CHAR(15)		 
 ADDRESS                                  CHAR(25)		ADDRESS should not exceed 25 characters.
 SALARY                                   NUMBER(5)		
 OFFICE                                   CHAR(4)
 DNUM                            NOT NULL NUMBER(4)		Department which this employee belongs to

department

 Name                            Null?    Type			Comments
 ------------------------------- -------- ----			-------------------------------------
 DNUM                            NOT NULL NUMBER(4)
 DMGR                            NOT NULL NUMBER(4)		Department manager
 DNAME                           NOT NULL CHAR(15)

project

 Name                            Null?    Type			Comments
 ------------------------------- -------- ----			-------------------------------------
 PNUM                            NOT NULL NUMBER(4)
 PMGR                            NOT NULL NUMBER(4)		Project manager
 PTITLE                          NOT NULL CHAR(15)

emp_proj

 Name                            Null?    Type
 ------------------------------- -------- ----
 PNUM                            NOT NULL NUMBER(4)
 ENUM                            NOT NULL NUMBER(4)
 

Part I: Answer the following questions

 
1. Output a relation of employees' names and their salaries earned. Order the resutl office-wise and within each office in the ascending order of salary.

 
2. Output a relation of projects' title and the project manager's name.

 
3. Find the projects headed by project manager "Jones".

 
4. Find the projects and the office of employees who work in the "CS" department.

 
5. Find the names of employees who earn the highest salary.

 
6. Find the names of employees who earn the 3rd lowest salary.

 
7. Find the projects headed by whoever heads the "Music" project.

 
8. Find the total salary of all employees who work on the "BT" project.

 
9. Find the names and department of employees who work on all projects headed by project manager "Cage".

 
10. Find the names of employees, the project they work on and the department they belong to, for employees who work on more than one project and less than five projects.

 
11. Find the names and salaries of employees who work on the most number of projects.

 
12. Find the names of employees who do share an office.

 
13. Find the names of employees who do not share an office but work on the same project, and have different salaries..

 
14. Find, in each department, the names and salaries of employee who earns within 2000 of the lowest salary.
     
15. Find, for each department, the average employees' salary and its standard deviation.

 

Part II: Null Values in SQL

1. Comment on the pros and cons of using NULL values as opposed to default values. Illustrate your points with examples and their answers, using the database you have created for this assignment.



2. Briefly discuss the semantics of the Boolean and comparison operators in the context of three-valued-logic.



3. State the salient features of the aggregate functions (Max, Min, Sum, Avg and Count) when NULL values are involved. Illustrate the usage of these aggregates function in the presence of NULLs with example queries and their answers, using the database you have created for this assignment.