Question

Please help me to solve

Please, No handwriting

COURSE; introduction to database

Q- write a query SQL by Using the info below

A. Normalize the Tables (in 3NF at least)
B. Create the Normalized Tables and Populate them with at least 5 Rows

C. Write the Wholesale Management System requested Queries & Execute them

VERY IMPORTANT
Screenshots from MySQL (or any other software you use) of all the tables after queries result.

- Database system for a Wholesale Management System

Consider a database system for a Wholesale Management System. The data requirements are summarized as follows:

o Maintain the details of stock like their id, name, quantity.

o Maintain the details of buyers from which manager has to buy the stock like buyer id, name, address, stock id to be bought.

o Details of customers i.e. name, address, id.

o Defaulters list of customers who have not paid their pending amount after the due date So List of payment paid or pending.

o The stock that is to buy if quantity goes less than a particular amount.

o Profit calculation for a month.

o Quantity cannot be sold to a customer if the required amount is not present in stock and date of delivery should be maintained up to which stock can be provided.

SQL Queries:

1. List of payment paid or pending customers.

2. Find the Defaulters list of customers who have not paid their pending amount.

3. Find the details of customers name, address, id.

4. Find Query to get information of employee where employee is not assigned to the department.

5. List the stock that is to buy if quantity goes less than a particular amount.

Answer 1

SQL Queries : -

• stock table

CREATE TABLE stock

(
id       VARCHAR(10)   PRIMARY KEY,
name   VARCHAR(30),
quantity   NUMBER

)

• customer table

CREATE TABLE customer
(
id       VARCHAR(10)   PRIMARY KEY,
name   VARCHAR(30),
address   VARCHAR(30)
)

--

CREATE TABLE buyer
(
buyerId   VARCHAR(10)   REFERENCES customer(id),
name   VARCHAR(30),
address   VARCHAR(30),
stockId   VARCHAR(10)   REFERENCES stock(id),
paymentStatus   VARCHAR(10),
dueDate   DATE,
PRIMARY KEY(buyerId,stockId)
)

• insert data into tables

INSERT INTO customer VALUES('C01', 'John', 'Chicago');
INSERT INTO customer VALUES('C11', 'Smith', 'Chicago');
INSERT INTO customer VALUES('C6', 'Steve', 'Los Angeles');
INSERT INTO customer VALUES('C51', 'Smith and Jones', 'California');

INSERT INTO stock VALUES('E01', 'Charger', 50);
INSERT INTO stock VALUES('P02', 'iPhone X', 10);
INSERT INTO stock VALUES('E07', 'USB cables-C type', 15);

INSERT INTO buyer VALUES('C01', 'John', 'Chicago', 'P02', 'Due', '15-APR-2019');
INSERT INTO buyer VALUES('C6', 'Steve', 'Los Angeles', 'E01', 'Paid', '15-MAR-2019');

• list of paid buyers

SELECT * FROM buyer WHERE paymentStatus LIKE 'Paid';

-- list of pending payment of buyers

SELECT * FROM buyer WHERE paymentStatus LIKE 'Due';

• Details of customers

SELECT * FROM customer;

• stock that is to be bought where quantity goes down a specific amount
• assuming that the minimum quantity is 20, if goes down 20 that product is to be bought

SELECT * FROM stock WHERE quantity <= 20;

ER Diagram : -

Name Name Buyerld Quantity ID Address Buyer STOCK H Stockld ayment dueDate PaymentStatus Details Customer ID Address Name

Tables

Before Normalization

Stock(Id, Name, Quantity)

Customer(Id, Name, Address)

Buyer(BuyerId, StockId, Name, Address, DueDate, PaymentStauts)

After normalization

As the name and address of buyer is already in customer table, it is not required in buyer table again.

Stock(Id, Name, Quantity)

Customer(Id, Name, Address)

Buyer(BuyerId, StockId, DueDate, PaymentStauts)