Decomposition to Third Normal Form (3NF)

The assignment is to decompose the 'base' table into its Third Normal Form (3NF) by applying functional dependencies. The given view 'base' contains attributes from the employee database, and the required outputs are three separate files in a tarball, each containing the contents of one of the decomposed tables.

Decomposing to Third Normal Form
Delivery Method
You will hand in output files contained in a tarball. The files will include the contents of
the tables you have generated as a result of your decomposition to Third Normal Form
(3NF).
Description
This project makes use of the database you installed for the first project. If you have not
modified the data or schemas associated with the original tables that were installed when
you imported the database, you should be able to begin with the existing database.
Otherwise, reinstall the employee database as described in Project 1.
Procedure
You will be given a table derived from the employee database and a set of functional
dependencies. You should complete the following steps
1. Create the database table to be decomposed. Execute the following query:
create view base as select dept_manager.emp_no,
departments.dept_no,
dept_name,
dept_manager.from_date as dept_mgr_from_date,
dept_manager.to_date as dept_mgr_to_date,
title,
titles.from_date as title_from_date,
titles.to_date as title_to_date
from departments, dept_manager, titles
where departments.dept_no = dept_manager.dept_no and
dept_manager.emp_no = titles.emp_no;
This query yields a view called base that you will need to decompose.
2. What should be the key for this table?
(emp_no, dept_no)
3. You have as inputs the attributes of the base table, the key you just determined,
and the following functional dependencies:
a. If you know the department number, you know the department name.
dept_no -> dept_name
b. There can only be one department manager at the same time.
dept_no dept_mgr_from_date -> dept_mgr_to_date
c. Each employee can only hold one title at the same time.
emp_no title_from_date -> title_to_date title

4. Derive the 3NF form for this table. Write the proper queries to generate the
decomposed tables as views. You should be able to explain during the grading
interview how you applied the 3NF Synthesis algorithm.
(emp_no, dept_no)
(emp_no, title_from_date, title_to_date, title)
(dept_no, dept_name)
(dept_no, dept_mgr_from_date, dept_mgr_to_date)
5. Provide the output of each table in a separate file, tar these files together and
submit them to。
There is ambiguity in this specification intentionally. The intent of the assignment is to
gauge your ability to execute this process with fuzzy specs, which is what will happen in
the real world. As a result, there will be variations in the final answers. Grading will be
done based on how you derive the 3NF and how well you execute each of the individual
steps.