db sql query

CREATE DATABASE techinfo_db;
USE techinfo_db;

-- Processors Table
CREATE TABLE processors (
processor_id INT PRIMARY KEY AUTO_INCREMENT,
processor_name VARCHAR(100),
brand VARCHAR(50),
socket_type VARCHAR(50),
tdp_wattage INT,
base_clock_speed DECIMAL(4, 2),
max_clock_speed DECIMAL(4, 2)
);

-- Motherboards Table
CREATE TABLE motherboards (
motherboard_id INT PRIMARY KEY AUTO_INCREMENT,
motherboard_name VARCHAR(100),
brand VARCHAR(50),
socket_type VARCHAR(50),
ram_type VARCHAR(50),
max_ram_slots INT,
gpu_interface VARCHAR(50), -- e.g., PCIe 4.0
form_factor VARCHAR(50)
);

-- RAM Table
CREATE TABLE ram (
ram_id INT PRIMARY KEY AUTO_INCREMENT,
ram_name VARCHAR(100),
brand VARCHAR(50),
ram_type VARCHAR(50), -- DDR4, DDR5, etc.
ram_capacity_gb INT,
ram_speed_mhz INT
);

-- GPUs Table
CREATE TABLE gpu (
gpu_id INT PRIMARY KEY AUTO_INCREMENT,
gpu_name VARCHAR(100),
brand VARCHAR(50),
interface_type VARCHAR(50), -- e.g., PCIe 4.0
tdp_wattage INT,
gpu_length_mm INT
);

-- Power Supply Units (PSUs) Table
CREATE TABLE power_supply_units (
psu_id INT PRIMARY KEY AUTO_INCREMENT,
psu_name VARCHAR(100),
brand VARCHAR(50),
wattage INT,
efficiency_rating VARCHAR(10)
);

-- SSDs Table
CREATE TABLE ssd (
ssd_id INT PRIMARY KEY AUTO_INCREMENT,
ssd_name VARCHAR(100),
brand VARCHAR(50),
interface_type VARCHAR(50), -- SATA, NVMe, etc.
capacity_gb INT
);

-- HDDs Table
CREATE TABLE hdd (
hdd_id INT PRIMARY KEY AUTO_INCREMENT,
hdd_name VARCHAR(100),
brand VARCHAR(50),
interface_type VARCHAR(50),
capacity_gb INT
);

-- Computer Cases Table
CREATE TABLE computer_cases (
case_id INT PRIMARY KEY AUTO_INCREMENT,
case_name VARCHAR(100),
brand VARCHAR(50),
form_factor_supported VARCHAR(50),
max_gpu_length_mm INT
);

-- CPU Coolers Table
CREATE TABLE cpu_coolers (
cooler_id INT PRIMARY KEY AUTO_INCREMENT,
cooler_name VARCHAR(100),
brand VARCHAR(50),
socket_type_supported VARCHAR(50), -- For processors
max_cooler_height_mm INT
);

CREATE TABLE compatibility (
compatibility_id INT PRIMARY KEY AUTO_INCREMENT,
processor_id INT,
motherboard_id INT,
ram_id INT,
gpu_id INT,
psu_id INT,
case_id INT,
cooler_id INT,
FOREIGN KEY (processor_id) REFERENCES processors(processor_id),
FOREIGN KEY (motherboard_id) REFERENCES motherboards(motherboard_id),
FOREIGN KEY (ram_id) REFERENCES ram(ram_id),
FOREIGN KEY (gpu_id) REFERENCES gpu(gpu_id),
FOREIGN KEY (psu_id) REFERENCES power_supply_units(psu_id),
FOREIGN KEY (case_id) REFERENCES computer_cases(case_id),
FOREIGN KEY (cooler_id) REFERENCES cpu_coolers(cooler_id)
);

-- Insert Example Processors
INSERT INTO processors (processor_name, brand, socket_type, tdp_wattage, base_clock_speed, max_clock_speed)
VALUES
('Intel i3-10100', 'Intel', 'LGA1200', 65, 3.6, 4.3),
('Intel i7-10700K', 'Intel', 'LGA1200', 125, 3.8, 5.1);

-- Insert Example Motherboards
INSERT INTO motherboards (motherboard_name, brand, socket_type, ram_type, max_ram_slots, gpu_interface, form_factor)
VALUES
('MSI Z490-A Pro', 'MSI', 'LGA1200', 'DDR4', 4, 'PCIe 4.0', 'ATX'),
('ASUS Prime Z490-A', 'ASUS', 'LGA1200', 'DDR4', 4, 'PCIe 4.0', 'ATX');

-- Insert Example RAM
INSERT INTO ram (ram_name, brand, ram_type, ram_capacity_gb, ram_speed_mhz)
VALUES
('Corsair Vengeance DDR4 16GB', 'Corsair', 'DDR4', 16, 3200),
('G.SKILL Trident Z DDR4 32GB', 'G.SKILL', 'DDR4', 32, 3600);

-- Insert Example GPUs
INSERT INTO gpu (gpu_name, brand, interface_type, tdp_wattage, gpu_length_mm)
VALUES
('NVIDIA RTX 3080', 'NVIDIA', 'PCIe 4.0', 320, 285),
('AMD RX 6800 XT', 'AMD', 'PCIe 4.0', 300, 267);

-- Insert Example Power Supply Units
INSERT INTO power_supply_units (psu_name, brand, wattage, efficiency_rating)
VALUES
('Corsair RM750', 'Corsair', 750, '80+ Gold'),
('EVGA 850 G5', 'EVGA', 850, '80+ Gold');

-- Insert Example Computer Cases
INSERT INTO computer_cases (case_name, brand, form_factor_supported, max_gpu_length_mm)
VALUES
('NZXT H510', 'NZXT', 'ATX', 310),
('Cooler Master MasterBox', 'Cooler Master', 'ATX', 300);

-- Insert Example CPU Coolers
INSERT INTO cpu_coolers (cooler_name, brand, socket_type_supported, max_cooler_height_mm)
VALUES
('Cooler Master Hyper 212', 'Cooler Master', 'LGA1200', 158),
('Noctua NH-D15', 'Noctua', 'LGA1200', 165);

-- Insert Example Compatible Configurations
INSERT INTO compatibility (processor_id, motherboard_id, ram_id, gpu_id, psu_id, case_id, cooler_id)
VALUES
(1, 1, 1, 1, 1, 1, 1), -- Intel i3-10100 with MSI Z490-A Pro, Corsair Vengeance RAM, RTX 3080, etc.
(2, 2, 2, 2, 2, 2, 2); -- Intel i7-10700K with ASUS Prime Z490-A, G.SKILL RAM, RX 6800 XT, etc.