Question

Use MYSQL to create the set of database tables of the relational database model and complete the associated queries given.

Procedure: 1) Write all the SQL statements, necessary to create all tables and relationships, with Primary & Foreign keys.

2) Execute each statement in the correct order to create the relational database in MYSQL.

3)Insert some data into each table.

4) Use all your SQL create and Insert statements (from MS Word) to execute in the MYSQL WorkBench

5) Write in MS Word and execute in MYSQL WorkBench the statements necessary to; i. display all tables, ii. identify sales total for each item iii. identify delivery confirmation of sold items iv. identify marketing level for sold items

(Tables that have to be created)

• Sales details

• Marketing details

• Customer details

• Production details

• Delivery details

• Management details

Answer 1

/* CREATE DATABASE */

CREATE DATABASE SampleProduction

/* CREATE TABLE : ProductionDetails */

CREATE TABLE ProductionDetails (

ProductID int auto_increment not null primary key,

ProductName varchar(255),

Manufacturer Varchar(255),

ManufacturingDate DateTime,

ExpiryDate DateTime,

UnitPrice Money,

Quantity int

);   

/* CREATE TABLE : SalesDetails */

CREATE TABLE SalesDetails (

SalesID int auto_increment not null primary key,

ProductId int,

SaleDate DateTime,

Quantity int,

TotalBill money,

MarketingLevel Int,

Foreign key(ProductId) references ProductionDetails(ProductId)

);   

/* CREATE TABLE : MarketingDetails */

CREATE TABLE MarketingDetails (

MarketingID int auto_increment not null primary key,

ProductId int,

LaunchDate DateTime,

MarketingExecutive Varchar(255),

UnitPrice money,

Foreign key(ProductId) references ProductionDetails(ProductId)

);   

/* CREATE TABLE : CustomerDetails */

CREATE TABLE CustomerDetails (

CustomerID int auto_increment not null primary key,

FirstName varchar(255),

LastName Varchar(255),

DateOfBirth DateTime,

Address Varchar(255)

);   

/* CREATE TABLE : DeliveryDetails */

CREATE TABLE DeliveryDetails (

DeliveryID Int auto_increment not null primary key,

CustomerId int,

ProductID int,

OrderQuantity Int,

OrderDate DateTime,

DeliveryDate DateTime,

DeliveryStatus Bit,

Foreign key(ProductId) references   ProductionDetails (ProductId),

Foreign key (Customerid) references CustomerDetails    (CustomerId)

);   

/* CREATE TABLE : ManagementDetails */

CREATE TABLE ManagementDetails (

ManagementID int auto_increment not null primary key,

OfficeAddress Varchar(255),

TotalEmployees Int );  

/* Insert Data in ProductionDetails */

Insert into ProductionDetails (ProductName, Manufacturer, ManufacturingDate,   ExpiryDate, UnitPrice, Quantity)

Values (‘Hanging Pathos in Glass container’, ‘ABC Gardening’, ’01-sep-2019’, ’01-sep-2020’, 599, 10)

Insert into ProductionDetails (ProductName, Manufacturer, ManufacturingDate,   ExpiryDate, UnitPrice, Quantity)

Values (‘Peace Lily Red’, ‘ABC Gardening’, ’01-sep-2019’, ’01-sep-2020’, 999, 10)

/* Insert Data in SalesDetails   */

Insert into SalesDetails (ProductId, SaleDate, Quantity, TotalBill, MarketingLevel)

Values (1, ’05-sep-2019’, 1, 599, 1)

Insert into SalesDetails (ProductId, SaleDate, Quantity, TotalBill, MarketingLevel)

Values (2, ’12-sep-2019’, 1, 999, 1)

/* Insert Data in MarketingDetails */

Insert into MarketingDetails (ProductId, LaunchDate, MarketingExecutive, UnitPrice)

Values (1, ’02-sep-2019’, ‘Mr George’, 599)

Insert into MarketingDetails (ProductId, LaunchDate, MarketingExecutive, UnitPrice)

Values (2, ’02-sep-2019’, ‘Mr George’, 999)

/* Insert Data in CustomerDetails */

Insert into CustomerDetails (FirstName, LastName, DateOfBirth, Address)

Values (‘Namrata’, ‘Nikhare’, ’04-Jan-1979’, ‘Besa Nagpur, Maharashtra, India’)

Insert into CustomerDetails (FirstName, LastName, DateOfBirth, ‘Address’)

Values (‘Julie’, ‘Fernandis’, ’17-Apr-2000’, ‘100, street park, New York’)

/* Insert Data in DeliveryDetails */

Insert into DeliveryDetails (CustomerId, ProductID, OrderQuantity, OrderDate, DeliveryDate, DeliveryStatus)

Values (1, 1, 2, ’10-sep-2019’, ’11-sep-2019’, 1)

Insert into DeliveryDetails (CustomerId, ProductID, OrderQuantity, OrderDate, DeliveryDate, DeliveryStatus)

Values (2, 2, 1, ’20-sep-2019’, null, 0)

/* Insert Data in ManagementDetails */

Insert into ManagementDetails (OfficeAddress, TotalEmployees)

Values (‘109, Ozone park, India’, 102)

/* SELECT QUERIES*/

/* Display all tables */

/* We can use MySQL command line client to display all the tables */

/* Below are the commands to display all tables */

/* use operating system command line to connect to MySQL */

$ mysql –u root –p     

/* Connect to database */

Mysql> use SampleProduction;

/* Command to List Tables from current database */

Mysql> show tables;

/* Query to identify sales total for all items */

SELECT SD.ProductID, PD.ProductName, sum(TotalBill) FROM SalesDetails SD

INNER JOIN ProductionDetails PD on SD.ProductID = PD.ProductID

GROUP BY ProductID

/* Identify Delivery confirmation of sold items */

SELECT ProductID, DeliveryStatus FROM DeliveryDetails

/* Identify Marketing level for sold items */

SELECT SD.ProductId, PD.ProductName, SD.Marketinglevel

FROM SalesDetails SD

INNER JOIN ProductionDetails PD on SD.ProductID = PD.ProductID