Contents
Normalization:..............................................................................................3
Un normalized Form:................................................................................3
First Normalized Form:.............................................................................3
Second Normalization Form:....................................................................4
Third Normalization Form:........................................................................5
Textual Analysis:..........................................................................................6
Figure 1 Textual Analysis Figure..................................................................7
Figure 2 Erd of the case scenario.................................................................8
Normalization:
The process of breaking down the complex entities into simpler form in
process of eliminating redundancies and ensuring there is integrity existing
inside the data.
Un normalized Form:
User- (User_ID, User_Name, User_Email, User_Phone{Project_ID,
Project_Name, Project_SDate, Project_EDate{Task_ID, Task_Name,
Task_SDate, Task_EDate, Status}})
This data here is in unnormalized form which means that there are existing
repeating groups and a lot of redundancies and the primary keys aren’t
described properly.
So for removing the repeating groups and redundancies we will proceed to
first normalized form.
First Normalized Form:
User-1: User_ID (Primary Key), User_Name, User_Email, User_Phone
Project-1: Project_ID (Primary Key), User_ID(Foreign Key), Project_Name,
Project_SDate, Project_EDate
Task-1: TaskID (Primary Key), Project_ID (Foreign Key), User_ID (Foreign
Key) TaskName, TaskStartDate, TaskDueDate, TaskStatus
In this form of normalization all of the repeating groups are separated to
different tables and assigned a primary key.
The attributes of projects and tasks are separated from the main user table.
The project attributes are separated from the user table and then user id is
assigned as a foreign key to the project table.
The task attributes are separated from the project table and the project id
and the user id are assigned as the foreign keys to the task table.
After the completion of 1NF, the process to converting the hence achieved
data into 2NF will be started. Here the data will remove all the partial
dependencies amongst themselves which is when a non key attribute is
dependent on a composite primary key.
Second Normalization Form:
User-2: User_ID (Primary Key), User_Name, User_Email, User_Phone
Project-2: Project_ID (Primary Key), Project_Name, Project_SDate,
Project_EDate
Project-User-2: Project_ID (Primary Key), User_ID(Foreign Key)
Task-2: TaskID (Primary Key), TaskName, TaskStartDate, TaskDueDate,
TaskStatus
Task-Project-User-2: TaskID (Primary Key), Project_ID (Foreign Key),
User_ID (Foreign Key)
Here the removing of partial dependency has been done on the project
table to ensure that there is fully functional dependency amongst all the
attributes in the table which also ensures that all the non key attributes
depend only on the primary key of the table.
Moving forward to the Third Normalization form there will be elimination of
transitive dependencies.
Third Normalization Form:
User-3: User_ID (Primary Key), User_Name, User_Email, User_Phone
-No changes done because no transitive dependency exists in this table.
Project-3: Project_ID (Primary Key), Project_Name, Project_SDate,
Project_EDate
-No changes done because no transitive dependency exists in this table.
Project-User-3: Project_ID (Primary Key), User_ID(Foreign Key)
-No changes done because no transitive dependency exists in this table.
Task-3: TaskID (Primary Key), TaskName, TaskStartDate, TaskDueDate,
TaskStatus
-No changes done because no transitive dependency exists in this table.
Task-Project-User-3: TaskID (Primary Key), Project_ID (Foreign Key),
User_ID (Foreign Key)
-No changes done because no transitive dependency exists in this table.
Textual Analysis:
User - Project: A User can be part of multiple Projects, and each Project
can have multiple Users (Many-to-Many relationship). This will be managed
through a join table
User - Task: A User can be assigned to multiple Tasks, and each Task can
have multiple Users (Many-to-Many relationship).
Task - Subtask: A Task can have multiple Subtasks, but each Subtask is
linked to only one Task (One-to-Many relationship).
User - Comment: A User can make multiple Comments on a Task (One-to-
Many relationship). Each Comment belongs to one User and one Task.
Task - Resource: Each Task can require multiple Resources and each
Resource can be linked to only one Task (One-to-Many relationship).
Project - Milestone: A Project is broken down into multiple Milestones, and
each Milestone belongs to only one Project (One-to-Many relationship).
(ASSUMPTION)
Milestone - Task: Tasks can be associated with specific Milestones, and
each Milestone can have multiple Tasks (One-to-Many relationship).
Figure 1 Textual Analysis Figure
Figure 2 Erd of the case scenario
