*In order for this assignment to be completed the expert from Order ID 77701/
78023needs to do it. This assignment is basically a continuation from the first and second assignment the expert did.
File Name "Assigment3.png" is the new assignment I need help with.
#view 1 CREATE VIEW EmployeeDepartment AS SELECT employee.name as EmployeeName, contact, designation, Department.name As DepartmentName FROM employee INNER JOIN department on department.id = employee.Department_id; #view 2 CREATE VIEW ProductSales AS SELECT products.name as ProductName, brand, sales_description, customer_feedback FROM products INNER JOIN products_has_sales on Products_idProducts =idProducts INNER JOIN Sales on idSales = Products_idProducts ; #question 3 Select customers.name as CustName, customers.contact_no, address, products.name as ProductName, brand from customers inner join products on products.idProducts = customers.Products_idProducts where quantity = 10; #union 1 SELECT address FROM Customers UNION SELECT address FROM employee; #union 2 SELECT products.idProducts FROM products UNION DISTINCT SELECT Products_idProducts FROM products_has_sales; #user defined function 1 DELIMITER // CREATE FUNCTION EmployeeDepartment(empid int) RETURNS int DETERMINISTIC BEGIN DECLARE deptId int; Select Department_id into deptId where id = empid; RETURN deptId; END // DELIMITER ; ecommerce.sql -- MySQL Script generated by MySQL Workbench -- Mon Mar 15 15:33:28 2021 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- ----------------------------------------------------- -- Schema Ecommerce -- ----------------------------------------------------- DROP SCHEMA IF EXISTS `Ecommerce` ; -- ----------------------------------------------------- -- Schema Ecommerce -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `Ecommerce` DEFAULT CHARACTER SET utf8 ; SHOW WARNINGS; USE `Ecommerce` ; -- ----------------------------------------------------- -- Table `Customers` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Customers` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Customers` ( `c_id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `contact_no` VARCHAR(45) NOT NULL, `address` VARCHAR(45) NOT NULL, `Products_idProducts` INT NOT NULL, PRIMARY KEY (`c_id`, `Products_idProducts`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Department` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Department` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Department` ( `id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `deartmentDescription` VARCHAR(100) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Products` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Products` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Products` ( `idProducts` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `brand` VARCHAR(45) NOT NULL, `quantity` VARCHAR(45) NULL, PRIMARY KEY (`idProducts`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Products_has_Sales` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Products_has_Sales` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Products_has_Sales` ( `Products_idProducts` INT NOT NULL, `Sales_idSales` INT NOT NULL, PRIMARY KEY (`Products_idProducts`, `Sales_idSales`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Sales` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Sales` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Sales` ( `idSales` INT NOT NULL, `sales_description` VARCHAR(45) NOT NULL, `customer_feedback` VARCHAR(45) NULL, `Department_id` INT NOT NULL, PRIMARY KEY (`idSales`, `Department_id`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `employee` -- ----------------------------------------------------- DROP TABLE IF EXISTS `employee` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `employee` ( `id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `contact` VARCHAR(45) NOT NULL, `address` VARCHAR(45) NOT NULL, `designation` VARCHAR(45) NOT NULL, `Department_id` INT NOT NULL, PRIMARY KEY (`id`, `Department_id`)) ENGINE = InnoDB; SHOW WARNINGS; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; ER diagram.png project-report-cspt5eiy-5wvpj2vs.docx Ecommerce store Introduction The tradition of sale and purchase of the products are getting change day by day. So, based on the customer and shopkeeper’s demand we are going to develop an easy, efficient and cost-effective database design and development for the shopkeeper. So that customer can choose the required product on demand in very less moment of time and shopkeeper can also show the products and at a time large number of people can see the product and purchase and based on that shopkeeper can order the inventory and productivity to the manufacturing unit. ER diagram for the e-commerce company The required E-R diagram is given below, Table information and use Based on the demand and relation with the data, the tables of the above ER diagram, we have created the tables and its field. Customer: The main purpose of this table is to store the customer’s data such as name, contact number, address. So that if any new product going to deliver or any special discount offer are going release in future then our marketing team can approach and explain about the product. Products In this table we will store the all the information about the product such name, brands, availability or quantitate such that we can be ready for the inventory, take new order based that stored data etc. It will help a lot to make and manage the store. Employee: In this table we will store the employee information such that we can make the track of the employee and product sold by the employee such that, if any dispute or any other on demand requirement can full fill and also can make the track of the customer and company relationship maintain and also can check the performance of the employee. Department: This table store the information related to the management, by which we are managing the centralized department such as employee, marketing, hiring etc. can be manage.\ Sales: The sales department can make the track of the customer and check the customer’s demand and track the customer satisfaction, based on that information new products and order can be introduce and also promote the product marketing such as any new offer or occasional and festive offer can show to customer to increase the profitability of the company. The required SQL queries are given below- -- MySQL Script generated by MySQL Workbench -- Mon Mar 15 15:33:28 2021 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- ----------------------------------------------------- -- Schema Ecommerce -- ----------------------------------------------------- DROP SCHEMA IF EXISTS `Ecommerce` ; -- ----------------------------------------------------- -- Schema Ecommerce -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `Ecommerce` DEFAULT CHARACTER SET utf8 ; SHOW WARNINGS; USE `Ecommerce` ; -- ----------------------------------------------------- -- Table `Customers` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Customers` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Customers` ( `c_id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `contact_no` VARCHAR(45) NOT NULL, `address` VARCHAR(45) NOT NULL, `Products_idProducts` INT NOT NULL, PRIMARY KEY (`c_id`, `Products_idProducts`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Department` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Department` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Department` ( `id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `deartmentDescription` VARCHAR(100) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Products` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Products` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Products` ( `idProducts` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `brand` VARCHAR(45) NOT NULL, `quantity` VARCHAR(45) NULL, PRIMARY KEY (`idProducts`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Products_has_Sales` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Products_has_Sales` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Products_has_Sales` ( `Products_idProducts` INT NOT NULL, `Sales_idSales` INT NOT NULL, PRIMARY KEY (`Products_idProducts`, `Sales_idSales`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Sales` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Sales` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Sales` ( `idSales` INT NOT NULL, `sales_description` VARCHAR(45) NOT NULL, `customer_feedback` VARCHAR(45) NULL, `Department_id` INT NOT NULL, PRIMARY KEY (`idSales`, `Department_id`)) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `employee` -- ----------------------------------------------------- DROP TABLE IF EXISTS `employee` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `employee` ( `id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `contact` VARCHAR(45) NOT NULL, `address` VARCHAR(45) NOT NULL, `designation` VARCHAR(45) NOT NULL, `Department_id` INT NOT NULL, PRIMARY KEY (`id`, `Department_id`)) ENGINE = InnoDB; SHOW WARNINGS; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Conclusion From the above information we can conclude that the ER diagram and normalized data can increase the efficiency and productivity of the company and save lots of time to manage the records and also can track the all the required information related to the marketing and the sales and the customer track.