Based on the below tables (Entity-Relationship Model), develop a Structured Query Language using MySQL for Walmart that contains the following items:

 

1. Provide the scripts needed to create the above tables.

a. Data Manipulation (DML) Scripts:

• Insert at least 10 records into each table.
• Delete a record from any table using its unique identifier (primary key) field.
• Update a record in any table using its unique identifier (primary key) field.
• Add 2 to 3 of your own DML scripts to manipulate data in your database.

2. Include scripts with the following queries for reporting:

• Total number of orders by customer.
• Total number of orders by product.
• Provide 2 to 3 aggregate function scripts to show use of AVG, MAX, MIN, COUNT or SUM.

3. Give a narrative of how this design fulfills the mission and goals of Walmart.

 

Transcribed Image Text:

Entities (Tables) Store Employees Products Suppliers Transaction Customers Departments Walmart Database Tables Attributes (Rows) StoreID (PK) INT NOT NULL Location VARCHAR (500) NOT NULL Size VARCHAR (500) NULL OpeningDate DATE NOT NULL EmployeeID (PK) INT NOT NULL FirstName VARCHAR (500) NOT NULL LastName VARCHAR (500) NOT NULL Position VARCHAR (150) NULL Salary DECIMAL NOT NULL HireDate DATE NOT NULL StoreID INT NOT NULL DepartmentID INT NOT NULL ProductID (PK) INT NOT NULL Name VARCHAR (150) NOT NULL Category VARCHAR (50) NULL Price DECIMAL (10, 2) NOT NULL Stock Quantity INT NOT NULL StoreID INT NOT NULL SupplierID INT NOT NULL SupplierID (PK) INT NOT NULL Name VARCHAR (250) NOT NULL Contact Person VARCHAR (250) NOT NULL ContactEmail VARCHAR (200) NOT NULL ContactPhone VARCHAR (200) NOT NULL TransactionID (PK) INT NOT NULL Date DATE NOT NULL TotalAmount DECIMAL (10, 2) NOT NULL StoreID INT NOT NULL CustomerID (PK) (int) NOT NULL Name VARCHAR NOT NULL Address VARCHAR (150) NOT NULL Email VARCHAR (150) NOT NULL Phone VARCHAR NOT NULL DepartmentID INT NOT NULL Name VARCHAR (500) NOT NULL EmployeeID VARCHAR (500) NOT NULL Relationships PRIMARY KEY PRIMARY KEY PRIMARY KEY PRIMARY KEY PRIMARY KEY PRIMARY KEY PRIMARY KEY Cardinality Constraints 1:1 1:1 1:Many 1:1 1:1 Many:Many 1:Many 1:1 1:1 1:1 1:1 1:Many 1:1 Entities (Tables) Store Employees Products Suppliers Transaction Customers Departments Walmart Database Tables Attributes (Rows) StoreID (PK) INT NOT NULL Location VARCHAR (500) NOT NULL Size VARCHAR (500) NULL OpeningDate DATE NOT NULL EmployeeID (PK) INT NOT NULL FirstName VARCHAR (500) NOT NULL LastName VARCHAR (500) NOT NULL Position VARCHAR (150) NULL Salary DECIMAL NOT NULL HireDate DATE NOT NULL StoreID INT NOT NULL DepartmentID INT NOT NULL ProductID (PK) INT NOT NULL Name VARCHAR (150) NOT NULL Category VARCHAR (50) NULL Price DECIMAL (10, 2) NOT NULL Stock Quantity INT NOT NULL StoreID INT NOT NULL SupplierID INT NOT NULL SupplierID (PK) INT NOT NULL Name VARCHAR (250) NOT NULL Contact Person VARCHAR (250) NOT NULL ContactEmail VARCHAR (200) NOT NULL ContactPhone VARCHAR (200) NOT NULL TransactionID (PK) INT NOT NULL Date DATE NOT NULL TotalAmount DECIMAL (10, 2) NOT NULL StoreID INT NOT NULL CustomerID (PK) (int) NOT NULL Name VARCHAR NOT NULL Address VARCHAR (150) NOT NULL Email VARCHAR (150) NOT NULL Phone VARCHAR NOT NULL DepartmentID INT NOT NULL Name VARCHAR (500) NOT NULL EmployeeID VARCHAR (500) NOT NULL Relationships PRIMARY KEY PRIMARY KEY PRIMARY KEY PRIMARY KEY PRIMARY KEY PRIMARY KEY PRIMARY KEY Cardinality Constraints 1:1 1:1 1:Many 1:1 1:1 Many:Many 1:Many 1:1 1:1 1:1 1:1 1:Many 1:1