sql_commands

CREATE DATABASE solarspace;

CREATE TABLE solarspace.USER (
    username VARCHAR(30) PRIMARY KEY,
    first_name VARCHAR(15) NOT NULL,
    last_name VARCHAR(15) NOT NULL,
    h_no INT,
    locality VARCHAR(20),
    city VARCHAR(20),
    pincode INT,
    password VARCHAR(30)
);

CREATE TABLE solarspace.user_contact (
    username VARCHAR(30) NOT NULL,
    contact BIGINT NOT NULL,
    FOREIGN KEY (username) REFERENCES solarspace.USER (username)
);

CREATE TABLE solarspace.appliance (
    aid INT PRIMARY KEY,
    name VARCHAR(20),
    POWER DECIMAL(5,2)
);

CREATE TABLE solarspace.appliance_user (
    username VARCHAR(30) NOT NULL,
    aid INT NOT NULL,
    quantity INT,
    FOREIGN KEY (username) REFERENCES solarspace.USER (username),
    FOREIGN KEY (aid) REFERENCES solarspace.appliance (aid)
);

CREATE TABLE solarspace.MONTH (
    mname VARCHAR(15) PRIMARY KEY,
    days INT NOT NULL
);

CREATE TABLE solarspace.location (
    l_name VARCHAR(20) PRIMARY KEY,
    latitude DECIMAL(4,4),
    longitude DECIMAL(4,4)
);

CREATE TABLE solarspace.loc_month (
    l_name VARCHAR(20) NOT NULL,
    m_name VARCHAR(15) NOT NULL,
    sunrays_recd DECIMAL(10,5),
    ideal_power DECIMAL(5,2),
    FOREIGN KEY (l_name) REFERENCES solarspace.location (l_name),
    FOREIGN KEY (m_name) REFERENCES solarspace.MONTH (mname)
);

CREATE TABLE solarspace.solar_panel (
    sid INT PRIMARY KEY,
    owner VARCHAR(30) NOT NULL,
    loc_name VARCHAR(20) NOT NULL,
    model VARCHAR(30),
    SIZE DECIMAL(5,2),
    FOREIGN KEY (owner) REFERENCES solarspace.USER (username),
    FOREIGN KEY (loc_name) REFERENCES solarspace.location (l_name)
);

CREATE TABLE solarspace.panel_month (
    pid INT NOT NULL,
    MONTH VARCHAR (15) NOT NULL,
    actual_power DECIMAL(5,2),
    FOREIGN KEY (pid) REFERENCES solarspace.solar_panel (sid),
    FOREIGN KEY (MONTH) REFERENCES solarspace.MONTH (mname)
);

CREATE TABLE solarspace.repair_person (
    r_id INT PRIMARY KEY,
    l_id VARCHAR(20) NOT NULL,
    name VARCHAR(20),
    rating INT,
    shop VARCHAR(30),
    FOREIGN KEY (l_id) REFERENCES solarspace.location (l_name)
);

CREATE TABLE solarspace.repair_contact (
    r_id INT NOT NULL,
    contact INT,
    FOREIGN KEY (r_id) REFERENCES solarspace.repair_person (r_id)
);

CREATE TABLE solarspace.repair_skills (
    r_id INT NOT NULL,
    skill VARCHAR(50),
    FOREIGN KEY (r_id) REFERENCES solarspace.repair_person (r_id)
);