Foreign key in DBMS

A foreign key in DBMS (Database Management System) is a field (or a set of fields) in one table that refers to the PRIMARY KEY of another table.
It is used to establish a relationship between two tables and ensures referential integrity—meaning the database prevents actions that would leave broken links between related tables.

✅ 1. Create BRAND Table

CREATE TABLE BRAND (
    BID INT PRIMARY KEY,
    BName VARCHAR(50),
    BType VARCHAR(50)
);

✅ 2. Insert Records into BRAND

INSERT INTO BRAND (BID, BName, BType) VALUES
(10, 'Dove', 'Soap'),
(11, 'Lux', 'Soap'),
(12, 'Ariel', 'Detergent'),
(13, 'Medimix', 'Soap');

✅ 3. Create PRODUCT Table

CREATE TABLE PRODUCT (
    PID INT PRIMARY KEY,
    PName VARCHAR(50),
    BID INT,
    Stock INT,
    UPrice INT,
    Rating INT,
    FOREIGN KEY (BID) REFERENCES BRAND(BID)
);

✅ 4. Insert Records into PRODUCT

INSERT INTO PRODUCT (PID, PName, BID, Stock, UPrice, Rating) VALUES
(1, 'Detergent Bar', 12,  60, 28, 3),
(2, 'Beauty Bar',    11, 143, 26, 4),
(3, 'Soap Bar',      10,  78, 20, 4),
(4, 'Soap Bar',      13,  55, 25, 5),
(5, 'Liquid Soap',   13,  70, 34, 3);

---

Answer the questions based on the following tables:

Table: Product

PCode	PName	UPrice	Rating	BID
P01	Shampoo	120	6	M03
P02	Toothpaste	54	8	M02
P03	Soap	25	7	M03
P04	Toothpaste	65	4	M04
P05	Soap	38	5	M05
P06	Shampoo	245	6	M05

Table: Brand

BID	BName
M02	Dant Kanti
M03	Medimix
M04	Pepsodent
M05	Dove

(i) Display product name and brand name from the tables PRODUCT and BRAND
(ii) Display the structure of the table PRODUCT
(iii) Display the average rating of Medimix and Dove brands
(iv) Display the name, price, and rating of products in descending order of rating

(i) Display product name and brand name
SELECT PName, BName
FROM PRODUCT, BRAND
WHERE PRODUCT.BID = BRAND.BID;

(ii) Structure of PRODUCT table
DESC PRODUCT;

(iii) Display the average rating of Medimix and Dove brands
SELECT BName, AVG(Rating) AS AverageRating
FROM PRODUCT, BRAND
WHERE PRODUCT.BID = BRAND.BID
AND BName IN (‘Medimix’, ‘Dove’)
GROUP BY BName;

(iv) Display name, price, and rating in descending order of rating
SELECT PName, UPrice, Rating
FROM PRODUCT
ORDER BY Rating DESC;

Using NATURAL JOIN

(i) Product name and brand name

SELECT PName, BName
FROM PRODUCT NATURAL JOIN BRAND;

(iii) Average rating of Medimix and Dove**

SELECT BName, AVG(Rating) AS AverageRating
FROM PRODUCT NATURAL JOIN BRAND
WHERE BName IN ('Medimix', 'Dove')
GROUP BY BName;