Question $1)(10$ points$)$

In this question, you will delve deeper into the principles of database normalization by working with a more complex denormalized table. Your task is to transform the given denormalized table into relations that adhere to the first, second, and third normal forms $(1$NF$,2$NF$,$ and $3$NF respectively$).$

The following table represents information about employees, their departments, projects they are assigned to$,$ and the tasks associated with those projects:

Employee ID Employee Name Department ID Department Name Project ID Project Name Task ID Task Description Task Start Date

$101$ Alice $1$ Engineering $001$ Project A $078$ Design UI $01/01/24$

$102$ Bob $2$ Marketing $002$ Project B $089$ Market Research $01/04/24$

$101$ Alice $1$ Engineering $001$ Project A $089$ Market Research $01/06/24$

$103$ Charlie $1$ Engineering $003$ Project C $096$ Testing $02/01/24$

$104$ Mohsen $1$ Engineering $001$ Project A $078$ Design UI $01/02/24$

We have the following functional dependencies present in this table:

$($Employee ID$)($Employee Name, Department ID$)$

$($Department ID$)($Department Name$)$

$($Project ID$)($Project Name$)$

$($Task ID$)($Task Description$)$

$($Employee ID$,$ Project ID$,$ Task ID$)($Task Start Date$)$

Your tasks:

$1.$ If the table is not in First Normal Form $(1$NF$),$ convert it into $1$NF$.$ What is the primary key in the $1$NF table.

$3.$ Convert the $1$NF table into Second Normal Form $(2$NF$).$

$4.$ Convert the $2$NF table into Third Normal Form $(3$NF$).$

Solution Submission Guidelines for Question $(1)$:

Submit your assignment containing the following:

$-$ Normalized tables for $1$NF$,2$NF$,$ and $3$NF$.$

$-$ A brief explanation of the normalization process you followed for each normal form.

$-$ You can complete this question in an Excel file and submit the Excel file.

Question $2)(10$ points$)$

In this question, you will explore the principles of database design by creating an Entity$-$Relationship Diagram $($ERD$)$ based on a provided scenario and requirements. Your task is to visualize the relationships between entities for the given business context and represent them using an ERD.

Scenario and Requirements:

You are tasked with designing a database system for a fictional e$-$commerce platform named "EcoGoods." EcoGoods specializes in selling eco$-$friendly products across various categories such as home goods, clothing, accessories, and more. The company needs to manage information about customers, products, orders, suppliers, and shipments. Here are the requirements:

$1.$ Customers: Each customer has a unique customer ID$,$ name, email address, and shipping address. Customers can place multiple orders.

$2.$ Products: Each product has a unique product ID$,$ name, description, price, and category. Products are supplied by different suppliers. Different suppliers price a product differently.

$3.$ Suppliers: Suppliers have a unique supplier ID$,$ name, email address, and location. A supplier can supply multiple products.

$4.$ Orders: Each order is associated with a customer and includes a unique order ID$,$ order date, and total amount. An order can contain multiple products that each can be supplied from multiple suppliers. An order can be shipped through one or multiple shipments.

$5.$ Shipments: Each shipment is associated with an order and includes a unique shipment ID$,$ shipping date, and shipping status.

$($Hint: you may need a table to track price of a product based on its supplier, and a table to track which order contains which products from which suppliers$)$

Your task:

$1.$ Based on the provided scenario and requirements, create an Entity$-$Relationship Diagram $($ERD$)$ representing the entities and their relationships.

$2.$ Be specific with the entities, attributes, keys, relationships, and cardinality between entities.

Solution Submission Guidelines for Question $(2)$:

To create the ERD, you MUST use Lucidchart or Visio, otherwise you will lose half of the points in this question.

Submit your assignment containing the following:

$-$ An Entity$-$Relationship Diagram $($ERD$)$ representing the relationships between the entities in the database.

$-$ Your ERD should include entities, attributes, keys, relationships, and cardinalities.

$-$ Copy your ERD in a word file or in the Excel file of Question $(1).$