Your company is a multi$-$department retail organization that operates both online and offline. The company has several departments, products, employees, customers, suppliers, inventory, and sales transactions. Your task is to design a database that efficiently organizes this information, supports various business operations, provides meaningful insights through complex queries, and includes mechanisms for data integrity and business logic enforcement.

Project Requirements

$1.$ Database Design

$1.$ Entity Identification:

$-$ Identify and define all necessary entities for the company. These should include, but are not limited to:

$-$ Departments

$-$ Products

$-$ Employees

$-$ Customers

$-$ Suppliers

$-$ Inventory

$-$ Sales Transactions

$-$ Online Orders

$-$ Store Locations

$2.$ Attributes:

$-$ Identify and define attributes for each entity. For example, for the $`$Employees$`$ entity, attributes might include $`$EmployeeID$`,`$FirstName$`,`$LastName$`,`$DOB$`,`$DepartmentID$`,`$Salary$`,`$HireDate$`,$ etc.

$3.$ Relationships:

$-$ Define relationships between entities. For example, a department can have many employees, and a product can be supplied by multiple suppliers but managed in a single inventory.

$4.$ Normalization

$1.$ Normalization Process:

$-$ Normalize your database to at least the Third Normal Form $(3$NF$).$ This involves removing redundant data and ensuring dependencies are logically placed.

$2.$ Normalization Documentation:

$-$ Provide a detailed explanation of how you achieved normalization. Include the initial unnormalized design and the steps you took to reach $3$NF$.$

$3.$ Database Schema

$1.$ ER Diagram:

$-$ Create an Entity$-$Relationship $($ER$)$ diagram to visually represent the database schema. This diagram should include all entities, attributes, and relationships.

$2.$ SQL Script:

$-$ Write SQL scripts to create the database schema. This should include:

$-`$CREATE DATABASE$`$ statement

$-`$CREATE TABLE$`$ statements for each entity

$-$ Appropriate data types and constraints $($e$.$g$.,$ primary keys, foreign keys, unique constraints, and not null constraints$).$

$5.$ Data Population

$1.$ Sample Data:

$-$ Populate each table with sample data. Ensure there is enough data to test the functionality of your queries effectively.

$6.$ Advanced Database Features$($You need to have some search about this part $)$

$1.$ Stored Procedures: You have to search a

$-$ Write at least three stored procedures. For example:

$-$ A procedure to process a sale transaction, updating inventory and recording the sale.

$-$ A procedure to add a new product and its supplier information.

$-$ A procedure to calculate and distribute bonuses to employees based on sales performance.

$2.$ Triggers:

$-$ Create at least two triggers. For example:

$-$ A trigger to automatically update inventory levels when a sale is made.

$-$ A trigger to log changes to employee salary information.

$3.$ Views:

$-$ Create at least three views to simplify complex queries. For example:

$-$ A view to display total sales per department.

$-$ A view to show customer purchase history.

$-$ A view to summarize inventory levels and reorder points.

$7.$ Complex Queries

$1.$ Advanced Queries:

$-$ Write SQL queries to:

$-$ Retrieve all products sold by a specific department and their sales statistics.

$-$ Find the total sales amount for each customer and their frequent purchase locations.

$-$ List employees who have been with the company for more than five years and their performance metrics.

$-$ Identify the most popular product by sales volume and revenue.

$-$ Calculate the average salary of employees in each department and the variance.

$2.$ Reports:

$-$ Write SQL queries to generate complex reports. For example:

$-$ Monthly and yearly sales reports for each department, broken down by product category.

$-$ Comprehensive customer purchase history, including online and offline transactions.

$-$ Detailed employee performance metrics based on sales transactions and customer feedback.