Jump to content
Guy_Smith

Engine Exhaust Emissions Database ERD is Circular!

Recommended Posts

Guy_Smith    0

I am trying to set up a database to calculate and report exhaust emissions from diesel engines and am stumped at Step #1:  The infamous Entity Relationship Diagram!

I am looking at a few thousand engines and need to calculate how much of what kind of pollutants they kick out into the air.  I have spreadsheets that list the Year of Manufacture, the horsepower, the serial number, how long the engine ran during a given year and how much fuel it consumed during that year.  I also have some spreadsheets that list what the Emission Factors, usually expressed in pounds per hour operated or pounds per gallon of fuel consumed, for each of the two dozen chemical compounds I’m interested in tracking.

Those emissions are relatively simple to calculate:  Multiply the Emission Factor times the number of hours operated (or times the gallons of fuel consumed) and we’re done.  Except for the fact that the Emission Factors are based on the Year of Manufacture and, in some cases, the horsepower rating of the engine, so I have to circle back around to the Engine table:  I don’t know how to connect the emission factor for, say, Arsenic to a specific engine, say a 154 hp Chevy made in 2014,  based on that engine’s year of manufacture and hp rating.

So my ERD keeps looking something like this:  Engines --->Emission Factors--->Engines   or   Engines ---<  Emissions >---Emission Factors--->Engines

My computer keeps puking ones and zeroes all over my keyboard when I try either setup in my Relationship Graph!

The only other way I can think of accomplishing these seemingly simple calculations is to write a boatload of scripts with variables, but I’m not even sure I could come up with all of the combinations/permutations involved.  And, unfortunately, I can’ even spell SQL, let alone know how to execute one of ‘em!

I have attached a (simplified) copy of the spreadsheets I currently use to better explain my problem.  I hope one of you will find this to be mind-numbingly simple and explain how it should be done.  Thanks very much for your time and consideration.

Sincerely,

Guy

Engine Emissions Calc Sheet MOD for FMF.xlsx

Share this post


Link to post
Share on other sites
comment    1,382
Posted (edited)
On 03/22/2017 at 1:13 AM, Guy_Smith said:

the Emission Factors are based on the Year of Manufacture and, in some cases, the horsepower rating of the engine

You have to start by splitting this into individual records in an EmissionFactors table that has fields for:

• Substance
• YearOfManufacture
• MinimumHorsePower
• EmissionFactor
• Units

Then for your report generate a record in a third Emissions table for each Engine/Substance combination, and let it calculate the total emission. So your structure would be:

Engines -< Emissions >- EmissionFactors

and the relationship between Emissions and EmissionFactors would be defined as:

Emissions::Substance = EmissionFactors::Substance
AND
Emissions::YearOfManufacture = EmissionFactors::YearOfManufacture
AND
Emissions::HorsePower ≤ EmissionFactors::MinimumHorsePower

with the records on the EmissionFactors side sorted by MinimumHorsePower, descending.

 

On 03/22/2017 at 1:13 AM, Guy_Smith said:

how long the engine ran during a given year

I don't see a given year in the example. If you have  year-by-year data, this may affect the structure.

 

 

 

 

 

Edited by comment
  • Like 1

Share this post


Link to post
Share on other sites
Guy_Smith    0

Comment:  Thank you so much for the help on this!  And, of course, you're a step ahead of me already - I am currently only collecting data for 2016, but I anticipate that it will turn into an annual event and I will need to modify my database to include multiple reporting years.  Any helpful hints on how to tweak the solution to handle that?

Thanks again,

Guy

 

Share this post


Link to post
Share on other sites
comment    1,382
1 hour ago, Guy_Smith said:

I will need to modify my database to include multiple reporting years.

Again, you will need a table where the yearly engine consumption data is stored using an individual record for each engine/year combination. I don't know how you're getting your data, but ideally you would have:

Engines -< EngineYearlyConsumptions -< Emissions >- EmissionFactors

Note that a record in Emissions table must lookup the YearOfManufacture and HorsePower values from its ancestor in Engines, in order to link to the correct record in EmissionFactors.

Share this post


Link to post
Share on other sites
Guy_Smith    0

That actually makes sense to me - who'd have thunk it?!

Thanks again, Comment - your willingness to help us neophytes is a true blessing!

Share this post


Link to post
Share on other sites
Guy_Smith    0

Rats! Phooey!  I'm still stuck - I can't get the emissions join table to populate.  I split the emission factor data into individual records, I've verified that I have the box checked to allow creation of records in the Emissions table and I've tried using a portal from the Engine table to display the emissions, but still nothing.  I maybe shouldn't be pulling the data for the Engine bhp and Tier from the Engine table or the CAS and Pollutant Names from the Emissions Factor table (for the Emissions table), but I don't know how else to do that (I also tried to do automatic lookups for those fields, but it didn't work, either). Any chance of looking at my file and seeing where I went wrong?

And, by the way, you will see the word "Tier" used in place of Year of Manufacture:  Tiers are basically groups of years used by the EPA. Using them in this instance saves me some time as I only have to enter emission factor records for one Tier instead of three or four records, one for each year in the Tier.

 

Thanks again for all of your assistance,

Guy

HARP DICE EmsInv Calc Sheet.fmp12

Share this post


Link to post
Share on other sites
comment    1,382
Posted (edited)

I am afraid I cannot look at your file. In case it wasn't clear, you need to run a script that will create a record in the Emissions table for each combination of  EngineYearlyConsumptions and Substance (i.e. every row in your report). There is no other way to populate the table (unless you want to do it manually, or you have an outside source you can import)).

 

Edited by comment
  • Like 1

Share this post


Link to post
Share on other sites
Guy_Smith    0

Yeah, I sorta missed the "generate a record" part of your first response :B

I got the script to work and my database is up and running thanks to your guidance - thanks so much and have a great afternoon!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Who Viewed the Topic

    1 member has viewed this topic:
    JerrySalem 
  • Similar Content

    • By 123
      Hey,
      I have the following problem. I'm working on a solution for the owner of several restaurants. Each restaurant has meetings once or twice a month. How would I structure the tables so that I could have a layout on which I select a restaurant and then inside a portal I get a list of all employees. On the portal for each row I want to have a button or checkbox to mark if an employee is present or not.
      I already have the following tables:
      Restaurants, Employees and Meetings
      So far I have a relationship between the Restaurants and Employees table which I use to assign employees to a restaurant, and a relationship between the Meeting and Restaurant table, which allows me to show all employees that work at a selected restaurant using a portal on the meetings layout. How do I proceed to solve my problem? Do I create another table MeetingAttendees or something similar that I use to keep track of people attending a meeting? How would I create a relationship then to allow me to mark certain employees as present and absent?
      Thanks in advance,
      Mike
    • By Martin Pineault
      Hi,
      I have a configuration table (named 'Usager actif') with 'Projet ID actif' and start/end date values (field name 'Bilan date début' and 'Bilan date fin') linked to a timesheet table (named 'FTemps lié Projet bilan') to show only the records that match, according to the following relation:

      This table is also linked to a step table (named 'Étapes lié FTemps lié Projet bilan') to show a list of steps used by the timesheet table (named 'FTemps lié Projet bilan'), base on the following relation:

      So the last table (named 'Étapes lié FTemps lié Projet bilan') is shown in a portal and it's working correctly (show only two steps used, for all the timesheet records). But I wanna show the total time used for each step (first step is 5 hours, and last one 95 hours). It's working correctly if the start/end date included all timesheet records. But if I shrink the ending date (removing 2 hours from the timesheet in step 2), the total time by step still show all time since the context does not follow from timesheet table to the step table in relation.
      I still see 5 hours for step 1 and 95 hours for step 2 (should be 93). The total time for all step is now 98 hours, since the ending date has been shrink (removing 2 hours on step 2).
      How can I do this (hope fully I'm clear enough)?
    • By NewBoard
      I am currently building a database to handle the inspection sheets used by our Quality lab. Here is the relationship layout:

      For each revision on the Revisions table, I have a differing number of Dimensions that need to be measured (some parts/revs will have 5 dimensions, while others will have 15). Each dimension has a specified type of instrument used in measurement, and a max/min value. The instrument used is a drop down menu that references a list of instruments/gages that we use.
      Here is how I have my inspection sheet currently laid out:

      The portal on the left points towards my dimensions table. It shows the data corresponded to the specified Part Number and Revision level. The data contained in this portal is uneditable from this form. The portal on the right points towards my RecordedDimensions table. It will store data that will correspond back to the Lot# associated with that inspection sheet.
      I also have a table that pairs all the different types of instruments we use, with their corresponding gage ID's:

      Now I would like my user to be able to click on the dropdown box under Instrument#, and that drop down box will only show the Gage ID's for the specified gage. So, if on the dimensions table, a dimension calls out for Digital Micrometers to be used, then when my user goes to select the instrument ID (which is saved in the RecordedDimensions table) it will would allow them to choose 151, 152, 153, 306, 324, 330, and 331.
      Please let me know if I have not provided enough information/pictures, I will gladly provide more.
      Thank you in advance for any assistance you can offer me!
    • By Auraboros
      Hi...I have created a database using a "modified" anchor buoy method and so far everything works great...but...I have a portal in a "join table" that works fine...however, my button that is supposed to open the record in the portal row does not function...it returns nothing. The records are indeed related (see RG) as the fields show up from the related table without relational conflicts, but this button that is supposed to open the "library" table with the related record pulled up simply does nothing. 
      I have attached the DB. Also a screen shot of the table where the portal is (it is actually a join table layout, this may be the problem). I don't understand how the button (with the little arrow) does not go to the table that the actual portal record, which IS related because I see it show up on the portal row...what is not right here?
      Thanks!
      Todd


      **MusicalAmericaPRESUB copy.fmp12
    • By Rikki
      Hi guys,
      Sorry I do not know the terminology of what I need.
      I am trying to setup a database to record some info on postcodes. What I need to do is, each supplier can have 4 types of lead (back data, profile, survey, 2nd use). I have two tables for suppliers and postcodes already. I also have 2 join tables for many to many relationships. I need each field in the postcodes database to be able to store a count for each supplier's lead type.
      Eg:
      Supplier    - Lead Type - Postcode - Count
      Supplier A - Back Data - AB            - 20
                                           - BB            - 155
                                           - CB            - 30
       
                           Profile     - AB             - 55
                                          - BB             - 27
      My problem is with the way I have set the relationships up currently. When I add a count for a postcode it adds that count for all suppliers. I need the postcode count to be specific to each supplier and lead type.
      Can anyone tell me what I need to do to get this working correctly? I have attached a screenshot of my relationships.
      Thanks

×

Important Information

By using this site, you agree to our Terms of Use.