Decomposition to Third Normal Form: Employee Database Analysis

This assignment focuses on decomposing a database to Third Normal Form (3NF). The task begins with a provided SQL query to create a base table from an employee database, followed by identifying the primary key. Students must then apply the 3NF synthesis algorithm, using given functional dependencies to decompose the table into smaller, normalized tables. The functional dependencies include department number determining department name, department number and start date determining the end date of a manager's tenure, and employee number and title start date determining the title end date and title. The assignment requires creating views for each decomposed table and providing the output of each table in separate files, which are then to be tarred and submitted. The grading emphasizes the correct application of the 3NF process and the execution of each step, reflecting real-world database design challenges. The aim is to assess the student's ability to handle ambiguous specifications and apply database normalization principles effectively.

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.