Lab 1. Normalization and
SQL
CS422,
Summer 2005
[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 Students(student_id,
student_name, quarter, course, course_grade, assignment,
assignment_grade, club_membership), which record the information
about students,
the courses they take, the grades they get, and their student club
memberships. The following table
shows some sample data for this relation:
| student_id |
student_name |
quarter |
course |
course_grade |
assignment |
assignment_grade |
club_membership |
| 1001 |
Joe |
F05 |
CS422 |
D |
HW1 |
10 |
ACM |
| 1001 |
Joe |
F05 |
CS422 |
D |
HW2 |
34 |
Chess |
| 1001 |
Joe |
X05 |
CS422 |
B |
HW1 |
80 |
ACM |
| 1002 |
Sue |
F05 |
CS122 |
A |
HW1 |
90 |
ACM |
| 1002 |
Sue |
F05 |
CS122 |
A |
HW2 |
100 |
ACM |
Note that a student may take the same course multiple times, and a
course may have several assignments.
1. Identify the nontrivial FD(s) and MVD(s) in the Students relation.
2. Identify the key(s) of the Students
relation
3. Is Students 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 the online turnin server.
The script should include all the queries you complete for this
assignment. Note that file
uploading will be disabled automatically after 7:40PM, 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 are ordered by last name in ascending order. A sample output of the
queries is available here.
Q1. List the names of the employees whose salary (excluding commisions)
is above 10,000.
Q2. List the names of the employees who have worked for the company for
more than 10 years.
Q3. List the names of the employees who are managers, e.g. their job
title has the word "Manager" in it.
Q4. List the names of the employees who have worked more than one job
in the company.
Q5. List the names of the employees whose salary is above the average
for their job title. Note that the average salary for a particular job
title can be calculated by averaging the min_salary and max_salary listed in the Jobs relation.
Q6. List the names of the departments that are located in Europe.
Q7. List the names of the departments that have more than 10 employees.
Q8. List the names of the top 3 departments that have the highest
average employee salary.
Q9. List the average employee salary in each region. Output -1 for a
region if there are no employees working in that region.