CS422 Lab1

Lab 1. Normalization and SQL
CS422, Summer 2006

[Normalization] (5pt) Please write down your answers to the following questions on a piece of paper, and show it to the instructor before you proceed to the SQL section.

Consider the relation NoTown(musician_id, musician_name, album_id, album_title, producer_id, song_id, song_title, author_id), which is a simplified version of the NoTown Records Database described in Homework 1. As in Homework 1, we assume that

Each album has a number of songs on it, but no song may appear on more than one album.
Each song recorded at Notown has a title and an author.
Each song is performed by one or more musicians, and a musician may perform a number of songs.
Each album has exactly one musician who acts as its producer. A musician may produce several albums.

1. Identify the nontrivial FD(s) and MVD(s) in the NoTown relation.

2. Identify the key(s) of the NoTown relation

3. Is NoTown in 3NF? If not, decompose it into 3NF.

4. Are the relations you get in (3) in BCNF? For the ones that are not, decompose them into BCNF.

5. Are the relations you get in (4) in 4NF? For the ones that are not, decompose them into 4NF.

[SQL] (5pt) Please upload an SQL script lab1.sql using CSNS. The script should include all the queries you complete for this assignment. Note that file uploading will be disabled automatically after 7:50PM, so please turn in your work on time.

Use the Oracle Human Resource (HR) sample schema to complete the following queries. For Q1 to Q5, the employee names should be in the form of "last name, first name", and for Q1 to Q4, the names should be ordered by last name in ascending order.

Q1. List the names of the employees whose salary (excluding commissions) is below 3,000.

Q2. List the names of the employees who were hired in 1999 or 2000.

Q3. List the names of the employees who are department managers.

Q4. List the names of the employees who have worked for more than one department.

Q5. List the names of the top 10 employees ranked by years of service. You don't need to worry about months and days when calculating years of service. For example, if an employee was hired in December last year, his or her years of service is still 1.

Q6. List the average employee salary in each department. The output should include department name and average salary, and ordered by average salary in descending order.

Q7. List the average employee salary in each region. The output should include region name and average salary, and ordered by average salary in descending order.

Q8. List the average employee salary by years of service. The output should include years of service and average salary, and ordered by average salary in descending order.

Q9. Find the number of employees who are managed (directly or indirectly) by Neena Kochhar.

Q10 (+1pt). Find the employees who (directly or indirectly) manage more than 5 other employees. The output should include the names of the employees and the number of employees they manage, order by the number of employees managed in descending order.

[NOTES] Since all the tables are in the HR schema, you will need to qualify the table names with the schema name, for example:

select * from
hr.employees;

Alternatively, you may create synonyms in your own schema so you don't have to specify the HR schema name every time. For example:

create or
replace synonym employees for hr.employees;


select * from employees;

And once you complete the lab, you may drop the synonyms using the drop synonym statement. For example:

drop synonym
employees;