Question

This is third time im posting this Question
COURSE; introduction to database
Please, No handwriting
thanks for your efforts
Design the database by following
. Execute the sample requested queries.
. ER Diagram.
. All schemas before and after normalization.
. All SQL statements of:
-Creating tables.
- Inserting data in tables.
-Queries.
5. Screenshots from MySQL (or any other software you use)

Question is

- 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

ER Diagram

Tables before normalization

Stock(Id, Name, Quantity)

Customer(Id, Name, Address)

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

Tables after normalization

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

Stock(Id, Name, Quantity)

Customer(Id, Name, Address)

Buyer(BuyerId, StockId, DueDate, PaymentStauts)

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');

SQL Queries

--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;