Creating Energy Management System (EMS)Database Project.
This project aims to design and implement a database system for Energy Management (EMS) for an imaginary estate that has three buildings having a total of eight apartments. The database system will store and manage energy data from various devices and electric meters in the estate. The database system will also provide functions to monitor and optimize energy consumption and efficiency in the estate. The database system consists of four relational tables: Building, Devices, Electric meter, and Energy Data.
The goal of this project is to develop a comprehensive system that can collect, store and analyze energy data from various sources within the estate. The system will also provide intelligent recommendations and solutions to optimize energy consumption and efficiency across the estate. The system will consist of a central database, a web-based interface and a network of sensors and smart devices.
DDL AND DML
Using DDL and DML commands, the EMS database was created and populated the tables with data. DDL stands for Data Definition Language, which allows us to define the structure and schema of the database. DML stands for Data Manipulation Language, which allows us to insert, update, delete, and query the data in the database. The EMS database consists of several tables that store information about Building, Devices, Energy Table, and Energy Data.
create database EMS;
use EMS;
create table Devices (
DeviceID int auto_increment,
BuildingID int, DeviceName varchar(50),
DeviceType varchar (50), Location varchar (50), Manufacturer varchar (50),
primary key (DeviceID), key (BuildingID));
insert into Devices values (1, 1,'Smart Thermostat', 'Thermostat', 'Living Room', 'Samsung');
insert into Devices values (2, 2, 'Smart Bulb', 'Lighting', 'Bedroom ', 'Philips');
insert into Devices values( 3, 3, 'Air Conditioner', 'HVAC', 'Living Room', 'Sony');
insert into Devices values (4, 5, 'Water Heater', 'Heater', 'Bathroom', 'Maxi');
insert into Devices values (5, 4, 'Smart Speaker', 'Speaker', 'Living Room' , 'Samsung');
insert into Devices values (6, 7, 'Smart TV', 'TV','Living Room', 'Samsung');
insert into Devices values (7, 6, 'Smart Fridge', 'Appliance', 'Kitchen', 'LG');
insert into Devices values (8, 8, 'Electric kettle', 'Kettle','Kitchen', 'Hisense');
insert into Devices values (9, 2, 'Fridge', 'Appliance', 'Kitchen', 'LG');
insert into Devices values (10, 3, 'Microwave', 'Appliance', 'Kitchen', 'LG');
insert into Devices values (11, 1, 'Pressing Iron', 'Appliance', 'Lundry', 'Philips');
insert into Devices values (12, 5, 'Washing Machine', 'Appliance','Lundry', 'Hisense');
insert into Devices values (13, 4, 'Smart TV', 'TV', 'Living Room', 'LG');
insert into Devices values (14, 7, 'Microwave', 'Appliance', 'Kitchen', 'LG');
insert into Devices values (15, 8, 'Electric kettle', 'Kettle','Kitchen', 'Sony');
insert into Devices values (16, 6, 'Pressing Iron', 'Appliance', 'Lundry', 'Philips');
insert into Devices values (17, 1, 'Washing Machine', 'Appliance','Lundry', 'Hisense');
insert into Devices values (18, 3, 'Smart TV', 'TV', 'Living Room', 'LG');
insert into Devices values (19, 5, 'Microwave', 'Appliance', 'Kitchen', 'LG');
insert into Devices values (20, 4, 'Electric kettle', 'Kettle','Kitchen', 'Sony');
create table Electric_Meters (
MeterID int auto_increment, MeterType varchar (50),
BuildingID varchar (50) not null, Manufacturer varchar (50),
SerialNumber varchar (50) not null, VoltageRange varchar (50) not null,
primary key (MeterID), key (BuildingID) );
insert into Electric_Meters values ( 1, 'Smart Meter', '1', 'ABC Corp', 'ABC100-0001', '220-240V');
insert into Electric_Meters values (2,'Electric Meter','3', 'XYZ Inc', 'XYZ200-0002', '200-240V');
insert into Electric_Meters values (3, 'Energy Meter', '4', 'EFG Ltd', 'EFG300-0003', '220-230V');
insert into Electric_Meters values (4, 'Smart Meter', '2', 'EFG Ltd', 'EFG400-0004','210-240V');
insert into Electric_Meters values (5, 'Electric Meter', '5', 'XYZ Inc', 'XYZ500-0005', '200-240V');
insert into Electric_Meters values (6, 'Energy Meter', '8', 'EFG Ltd', 'EFG600-0006', '220-230V');
insert into Electric_Meters values (7, 'Smart Meter', '7', 'ABC Crop', 'ABC700-0007','210-240V');
insert into Electric_Meters values (8, 'Electric Meter', '6', 'XYZ Inc', 'XYZ800-0008', '200-240V');
create table Energy_Data ( ReadingID int auto_increment,
DeviceID int not null, MeterID int not null,
Energyvalue varchar (50) not null,
primary key (ReadingID), Key (DeviceID, MeterID) );
insert into Energy_Data values (1, 1, 1, '0.9');
insert into Energy_Data values (2, 2, 2, '0.4');
insert into Energy_Data values (3, 3, 3, '1.9');
insert into Energy_Data values (4, 4, 4, '1.8');
insert into Energy_Data values(5, 5, 5, '0.5');
insert into Energy_Data values (6, 6, 6, '0.5');
insert into Energy_Data values (7, 7, 7, '1.3');
insert into Energy_Data values (8, 8, 8,'1.5');
insert into Energy_Data values (9, 9, 4, '2.2');
insert into Energy_Data values (10, 10, 6, '1.3');
insert into Energy_Data values (11, 11, 2,'1.8');
insert into Energy_Data values (12, 12, 7,'0.5');
insert into Energy_Data values (13, 13, 8,'2.2');
insert into Energy_Data values (14, 14, 3, '1.3');
insert into Energy_Data values (15, 15, 1, '1.9');
insert into Energy_Data values (16, 16, 2, '1.8');
insert into Energy_Data values (17, 17, 7, '0.5');
insert into Energy_Data values (18, 18, 8, '2.2');
insert into Energy_Data values (19, 19, 3, '1.3');
insert into Energy_Data values (20, 20, 1, '1.9');
create table Building( BuildingID int not null,
Buildingname varchar (50),
BuildingparentID varchar (50),
primary key (BuildingID) );
insert into Building values (1, 'Whole Building', 'Building1' );
insert into Building values (2, 'Groundfloor', 'Building2');
insert into Building values (3, '1st Floor', 'Building2');
insert into Building values (4, '2nd Floor', 'Building2');
insert into Building values (5, 'Groundfloor', 'Building3');
insert into Building values (6, '1st Floor', 'Building3');
insert into Building values (7, '2nd Floor', 'Building3');
insert into Building values (8, '3rd Floor', 'Building3');Data Analysis
- Find out the common home device used ?
-- what is the common home device used
select devicename, count(devicename) 'common device'
from devices
group by DeviceName
order by count(devicename) desc;
2. Find the list of buildings and the devices in them
-- Find the list of buildings and the devices in them
select b.buildingname, b.buildingparentID,
d.DeviceName, d.DeviceType
from building b
join devices d
using (BuildingID);
3. find the device that consumes the most energy
-- find the device that consume the most energy
select devicetype, DeviceName,
(select max(energyvalue)
from energy_data ed
where ed.DeviceID = d.DeviceID) as max_energy
from devices d
order by max_energy desc;
4.
-- What is the most used meter type
select metertype, count(MeterType)
from electric_meters
group by MeterType
order by count(MeterType);
-- find the count of meter Manufacturer
select Manufacturer, count(Manufacturer)
from electric_meters
group by Manufacturer
order by count(Manufacturer) desc;
5.
-- correlate meter types with building type?
select buildingname, BuildingparentID,
( select metertype
from electric_meters em
where em.BuildingID = b.BuildingID) as metertype
from building b;
7. find the total energy consumption for each of the buildings.
-- find the total energy consumption for each of the buildings?
SELECT buildingname, BuildingparentID,
(select sum(energyvalue)
from energy_data
where DeviceID in
( select DeviceID
from devices d
where d.BuildingID = b.buildingID ) )as total_energy
from building b; 
8. Which devices have the highest energy consumption within a particular building?
-- Which devices have the highest energy consumption within a particular building?
select b.BuildingparentID, b.Buildingname,
d.DeviceName, d.Location,
max(ed.energyvalue) as Max_energy
from building b
join devices d on b.buildingID = d.BuildingID
JOIN energy_data ed on ed.DeviceID = d.DeviceID
group by Buildingname; 
9. Determine the devices in a specific location, such as the “Kitchen” or “Living Room.”
-- Determine the devices in a specific location, such as the "Kitchen" or "Living Room."
select devicename,
devicetype,
location
from devices
where Location = 'Kitchen' or location = 'Living Room'
order by location;
10. Analyze the energy consumption patterns of different devices across buildings.
-- Analyze the energy consumption patterns of different devices across buildings
SELECT b.Buildingname,
b.buildingparentID,
d.DeviceType,
SUM(ed.Energyvalue) AS TotalEnergyConsumption
FROM Devices d
JOIN Energy_Data ed ON d.DeviceID = ed.DeviceID
JOIN Building b ON d.BuildingID = b.BuildingID
GROUP BY d.DeviceType, b.BuildingparentID
ORDER BY d.DeviceType, b.BuildingparentID;
11. Performance of Meters from Different Manufacturers
-- Performance of Meters from Different Manufacturers
SELECT em.Manufacturer,
AVG(ed.Energyvalue) AS MeanEnergy,
STDDEV(ed.Energyvalue) AS StdDevEnergy
FROM Electric_Meters em
JOIN Energy_Data ed ON em.MeterID = ed.MeterID
GROUP BY em.Manufacturer;
Conclusion
In conclusion, my journey through the creation and analysis of the Energy Management System (EMS) database project has been an illuminating one.
In this database, the potential for optimizing energy management is vast. Haven highlighted how different devices consume energy across buildings and the performance of meters from various manufacturers.
This project showcases the power of a well-structured database in transforming data into actionable insights. As I move forward, I’m excited to continue refining this system and exploring more ways to enhance energy management and efficiency.
Stay tuned for further developments as I embark on this exciting journey.
THANKS FOR READING
