Notes

-- Create the database and switch to it
CREATE DATABASE db_exam;
USE db_exam;

-- Create the Customer table
CREATE TABLE tbl_Customer (
customerID INT PRIMARY KEY,
cs_name VARCHAR(100),
cs_dob DATE,
cs_mobile VARCHAR(15),
cs_category VARCHAR(50)
);

-- Create the Product table
CREATE TABLE tbl_Product (
productID INT PRIMARY KEY,
pr_name VARCHAR(100),
pr_price DECIMAL(10, 2)
);

-- Create the Orders table
CREATE TABLE tbl_Orders (
orderID INT PRIMARY KEY,
or_quantity INT,
or_date DATE,
customerID INT,
productID INT,
FOREIGN KEY (customerID) REFERENCES tbl_Customer(customerID),
FOREIGN KEY (productID) REFERENCES tbl_Product(productID)
);

-- Add foreign key constraints (Optional if already added in CREATE TABLE)
ALTER TABLE tbl_Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customerID) REFERENCES tbl_Customer(customerID);

ALTER TABLE tbl_Orders
ADD CONSTRAINT fk_product
FOREIGN KEY (productID) REFERENCES tbl_Product(productID);

-- Verify the tables (for debugging or validation purposes)
SHOW TABLES;

-- Check the structure of each table
DESCRIBE tbl_Customer;
DESCRIBE tbl_Product;
DESCRIBE tbl_Orders;