Q: Normalize the below set of attributes upto BCNF.

Cust_no, cust_name, cust_gender, prod_id, prod_name, qty_ordered, cust_city, qty_available,

STEP 1: Identify functional dependencies

Custno-> cust_name,cust_gender, cust_City

Prod_id-> prod_name, qty_available, price_per_unit

Bill_no->date_of_order,qty_ordered, cust_no, prod_id,delivery_zipcode

Delivery_zipcode->city

Step 2: All columns should have atomic values

Custno order_no prod_id

C1 o1,o2,o3 p1,p2

As one customer can place order for no. of times as well as for no. of products. So this relation is not
in 1NF.

Custno order_no prod_id

C1 o1 p1

C1 o2 p2

C1 o3 p1

C1 o3 p2

So, now this relation is in 1NF as all columns have one value at a time.

Step 2: 2NF : remove partial dependency

Here we have prime attributes : custno, prod_id, bill_no

We already have identified FDs in first step, and all non prime attributes are not directly dependent
on all prime attributes. So this relation is not in 2NF.
Customer_details : custno(PK), cust_name, cust_gender, cust_zipcode, cust_city

Product_details : prod_id(PK), prod_name, qty_available, price_per_unit

Order_details : billno(PK), custno(FK), prod_id(FK), qty_ordered, date_of_ordered

So, now this relation is in 2NF

Step 4: 3NF: in 2Nf

2. Remove transitive dependency

As we don’t have any transitive FD, This relation is already in 3NF

Custid->zipcode

Zipcode->city

So custid->city is a transitive fd

Zip_city: zipcode(PK), city

Customer_details: customer_id(PK),name, zipcode(FK)

Step 5: BCNF

In 3NF

If any non-trivial FD exists, X->Y

Then X must be a super key.

All 3 relations have strong primary key

Custno, prod_id and billno

Which are super keys of respective FDs

So , this relation is in BCNF.

use scmhrd2;

SELECT * FROM DEPARTMENT;

UPDATE DEPARTMENT

SET DEPT_NAME='PURCHASE'

WHERE DEPT_ID='D4';

SET SQL_SAFE_UPDATES=0;

SELECT DEPT_ID FROM EMP;

# DISPLAY ALL DEPARTMENT NAMES HAVING NO. OF EMPS WORKING IN IT

SELECT D.DEPT_NAME, COUNT(E.EMP_ID)

FROM DEPARTMENT D LEFT OUTER JOIN EMP E

ON D.DEPT_ID=E.DEPT_ID

GROUP BY D.DEPT_NAME;

SELECT D.DEPT_NAME, COUNT(E.EMP_ID)

FROM EMP E RIGHT OUTER JOIN DEPARTMENT D

ON D.DEPT_ID=E.DEPT_ID

GROUP BY D.DEPT_NAME;
# FULL OUTER JOIN

#SELECT E.EMP_ID, [Link], D.DEPT_ID, D.DEPT_NAME

#FROM EMP E FULL outer JOIN DEPARTMENT D

#ON E.DEPT_ID=D.DEPT_ID;

use scmhrd;

# List out all the tables in the DB

SHOW TABLES;

# DISPLAY THE STRUCTURE OF PRODUCT TABLE

DESC PRODUCT_MASTER;

# DISPLAY THE DETAILS OF CUSTOMERS

SELECT * FROM CUSTOMER_MASTER;

# DISPLAY THE CUSTOMERS FROM HYDERABAD CITY

SELECT * FROM CUSTOMER_MASTER

WHERE CITY='HYDERABAD';

# DISPLAY THE CUSTOMERS FROM OOTY OR PUNE

SELECT * FROM CUSTOMER_MASTER

WHERE CITY IN ('PUNE', 'OOTY');

SELECT CNAME FROM CUSTOMER_MASTER

WHERE CNAME LIKE 'AB%';

#DISPLAY THE UNIQUE CITY NAMES

SELECT DISTINCT CITY FROM CUSTOMER_MASTER;

# DISPLAY THE DETAILS OF CUSTOMERS WHO IS HAVING EXACTLY 6 CHARS

# IN THEIR NAMES

SELECT * FROM CUSTOMER_MASTER

WHERE LENGTH(CNAME)=6;

# WHOSE NAME IS HAVING I ANYWHERE

SELECT * FROM CUSTOMER_MASTER

WHERE CNAME LIKE '%I%';

# DISPLAY CUSTOMER DETAILS THOSE WHO ARE NOT FROM PUNE OR CHENNAI

SELECT * FROM CUSTOMER_MASTER

WHERE CITY NOT IN('PUNE','CHENNAI');

# DISPLAY THE PRODUCTS HAVING PRICE IN THE RANGE OF 500 TO 1000

SELECT * FROM PRODUCT_MASTER

WHERE PRICE_PER_UNIT BETWEEN 500 AND 1000;

# DISPLAY THE DETAILS OF PRODUCT HAVING NAME ENDS WTIH "SHIRT"

SELECT * FROM PRODUCT_MASTER

WHERE PNAME LIKE '%SHIRT';

# DISPLAY THE DETAILS OF ORDERS WHICH HAVE BEEN PLACED IN YEAR 2022

SELECT * FROM ORDER_MASTER

WHERE YEAR(DOO)=2022;

# DISPLAY THE DETAILS OF ORDERS HAVE PLACED IN 2022 AND APRIL MONTH

SELECT * FROM ORDER_MASTER

WHERE MONTHNAME(DOO)='APRIL' AND YEAR(DOO)=2022;

# DISPLAY THE DETAILS OF ORDERS WHICH HAVE BEEN PLACE BEFORE 2022

SELECT * FROM ORDER_MASTER

WHERE YEAR(DOO)<2022;

# DISPLAY THE ORDER HAVING MINIMUM VALUE OF QTY_ORDERED

SELECT * FROM ORDER_MASTER

WHERE QTY_ORDERED IN(SELECT MIN(QTY_ORDERED) FROM ORDER_MASTER);

# DISPLAY THE NON-MOVING PRODUCTS

SELECT * FROM PRODUCT_MASTER

WHERE PID NOT IN(SELECT PID FROM ORDER_DETAILS);

SELECT PM.* FROM

PRODUCT_MASTER PM LEFT OUTER JOIN ORDER_DETAILS OD

ON [Link]=[Link];
SELECT E.*, D.DEPT_NAME

FROM EMP E NATURAL JOIN DEPARTPMENT D;

SELECT E.*, D.DEPT_NAME "NAME OF DEPARTMENT"

FROM EMP E INNER JOIN DEPARTPMENT D

ON E.DEPT_ID=D.DEPT_ID;

SELECT CM.*, [Link] FROM

CUSTOMER_MASTER CM NATURAL JOIN PRODUCT_MASTER PM;

SELECT COUNT(*) FROM EMP;

SELECT COUNT(*) FROM DEPARTPMENT;

SELECT [Link], D.DEPT_ID, D.DEPT_NAME

FROM EMP E CROSS JOIN DEPARTPMENT D;

SELECT * FROM DEPARTPMENT;

INSERT INTO DEPARTPMENT VALUES('D3','SALES'),

('D5','FINANCE');

SELECT DEPT_ID FROM EMP;

SELECT D.DEPT_NAME , COUNT([Link])

FROM DEPARTPMENT D LEFT OUTER JOIN EMP E

ON D.DEPT_ID = E.DEPT_ID

GROUP BY D.DEPT_NAME;

SELECT D.DEPT_NAME , COUNT([Link])

FROM EMP E RIGHT OUTER JOIN DEPARTPMENT D

ON D.DEPT_ID = E.DEPT_ID
GROUP BY D.DEPT_NAME;

#DISPLAY THE LIST OF TABLES

SHOW TABLES;

# DISPLAY THE SCHEMA OF CUSTOMER TABLE

DESC CUSTOMER_MASTER;

# DISPLAY ALL THE DETAILS OF PRODUCT

SELECT * FROM PRODUCT_MASTER;

# DISPLAY THE NAMES OF CUSTOMERS HAVING 'I' ANYWHERE

SELECT CNAME FROM CUSTOMER_MASTER

WHERE CNAME LIKE '%I%';

# DISPLAY THE NAMES OF CUSTOMERS HAVING EXACTLY 6 CHARACTERS

SELECT CNAME FROM CUSTOMER_MASTER

WHERE LENGTH(CNAME)=6;

#DISPLAY THE PRODUCT DETAILS HAVING PRICE BETWEEN 500 TO 1000

SELECT * FROM PRODUCT_MASTER

WHERE PRICE_PER_UNIT BETWEEN 500 AND 1000;

# DISPLAY THE ORDERS WHICH HAVE BEEN PLACED IN APRIL , 2022

SELECT * FROM ORDER_MASTER

WHERE MONTHNAME(DOO)='APRIL' AND YEAR(DOO)=2022;

# DISPLAY THE ORDERS PLACED BEFORE 2022

WHERE YEAR(DOO)<2022;