Database_Structure

DROP DATABASE IF EXISTS  riversData;

-- create a new database as riverData--
CREATE DATABASE riversData;

-- set this database as current working database
USE riversData;

-- initilly we are asumming that there are only two rivers


--  table to store the id's of the various sensors of river1
CREATE TABLE river1Nodes(
 nodeId INTEGER PRIMARY KEY
 location varchar(255),
 );


-- table to store the id's of the various sensors of river2
CREATE TABLE river2Nodes(
 nodeId INTEGER PRIMARY KEY,
location varchar(255),
);

-- table to store the sensor's data situated various location in river 1 at a timestamp
CREATE TABLE river1Data(
 pressure FLOAT,
 moisture FLOAT,
 atTime TIMESTAMP,
 nodeId INTEGER,
 FOREIGN KEY (nodeId) REFERENCES river1Nodes(nodeId)
 );

-- table to store the sensor's data situated various location in river 2 at a timestamp
CREATE TABLE river2Data(
 pressure FLOAT,
 moisture FLOAT,
 atTime TIMESTAMP,
 nodeId INTEGER,
 FOREIGN KEY (nodeId) REFERENCES river2Nodes(nodeId)
 );