Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CS 6400

Summer 2023

Alternakraft

Project Overview

The purpose of this project is to analyze, specify, design, implement, document, and demonstrate an app your team   develops.  The project follows the three phases outlined in the Database Application Development Methodology: Analysis & Specification, Design, and Implementation.  Details regarding acceptable project toolsets and stacks will be communicated in the assignment directions for each project phase.  The due dates and deliverables that must be submitted for each phase will be provided via Canvas.

When reading through this project description, please make the following assumptions: unless otherwise specified as optional, all attributes are required; unless otherwise specified, if given a list of potential values, choices should be limited to that list; If a set of values is listed with "and/or”, combinations of those values are possible, while “or” indicates only a single value is possible; that you should create normalized schemas, and minimize the use of NULL attributes whenever and wherever possible; ensure that you store non-numeric data that appear as numbers (such as street numbers, phone numbers, postal codes, etc.) as strings and not numeric data types; and avoid “catch-all” forms with unnecessary inputs that the user would leave empty or NULL. You also do not need to be concerned about handling concurrent operations that could conflict and introduce inconsistencies in your database.

This spec contains a functional description along with some screen mockups. The user interfaces depicted in this project description are examples to guide your thinking and are not intended to capture all functionality as described. You may implement the project UI as a traditional standalone application (e.g., Java GUIs or Python’s TkInter) or as a web application (e.g., web scripting languages like PHP or JSP). Remember that your project will not be graded on its aesthetic appeal, but on its functionality.

Do not create any additional functionality that is not mentioned in this specification (such as email notifications, etc.) or attempt to enhance your final product beyond what the specification requires. Adding unwanted functionality can and will impact your grade!

Overview

Alternakraft is a nonprofit organization that wishes to accumulate data regarding households in the United States, specifically around alternative power sources and other household properties.  The data collected will be “open” in the sense that anyone will be able to submit their data. Conversely, anyone can browse the selected set of reports available on the Alternakraft website.

Definitions

This section provides some basic details about the things and their properties that the system tracks.  Note that some operational attributes may not be mentioned as they might not be an essential property of something.  Pay close attention to the functionality section as it may imply operational attributes that may need to be included in your design.

At the core of Alternakraft are households.  The following details will be collected for a household :

•    Email

o The email address provided will be used to identify the household.

•   The square footage of the household, as a whole number

•   The household type: House, apartment, townhome, condominium, modular home, or tiny house.

•    The public utilities that are used by the household: electric, gas, steam, and/or liquid fuel.

o A household that does not use any utilities is considered “off-the-grid” .

•    The regular thermostat setting, in whole degrees Fahrenheit

o For heating (required if the household has heating)

o For cooling (required if the household has cooling)

•    Postal code where the home is located (with matching city/state and geolocation)

o A listing of postal codes with city, state, and their central latitude & longitude will be provided and should be used to validate user input. Entering an invalid postal code should be rejected.

o This listing of postal codes will not change, and you do not need to worry about updating it.

o  A short sample listing of postal codes is below.

For each household, information regarding appliances – air handlers and water heaters – must also be collected.  Each appliance will be identified by the household with which it is associated, along with the sequential order number it was   entered into the system for that household (the first would be identified as “1”, the second as “2”, etc. with numbering   restarting at “1” for the next household).  A household may have one or more air handlers, each supporting any one, any pair, or all three heating/cooling methods.  Each appliance will be associated with a particular manufacturer, which will   be chosen from a populated, updatable list retrieved from the database. The submitter may also optionally provide the appliance’s model name.

Additional properties for appliances include:

•    BTU rating, whole number

•    Appliance type

o Air handler

§ Fan rotations per minute (RPM), whole number

§ Heating/cooling method

•    Air conditioner

o Energy efficiency ratio (EER), decimal number (to the tenth decimal point)

•    Heater

o  Energy source: Electric, gas, or thermosolar

•    Heat pump

o Seasonal energy efficiency rating (SEER), decimal number (to the tenth decimal point)

o Heating seasonal performance factor (HSPF), decimal number (to the tenth decimal point)

o Water heater

§ Tank size in gallons, decimal value (to the tenth decimal point)

§ Current temperature setting, optional, whole number

§ Energy source: Electric, gas, fuel oil, or heat pump

Finally, household power generation information may be collected, which is optional unless a household is “off-the-grid” . A household may have none, one or more than one power generator.  Many power generators can also have battery storage, whose capacity must also be recorded.  Each power generator will be identified by the household with which it  is associated, along with the sequential order number it was entered into the system for that household (the first would  be identified as “1”, the second as “2”, etc. with numbering restarting at “1” for the next household).  For each generator, the following information must be collected:

•    Generation type: solar or wind-turbine

•    Average monthly kilowatt hours generated, whole number

•    Battery storage capacity in kilowatt hours, optional, whole number

Functionality

The user interface for the application should be consistent, with all inputs appropriately labeled, and with appropriate input controls used (such as a textbox for text, drop-downs for lists, spinner for whole numbers, etc.).  Any instances   where a NULL value might be returned should be replaced with an empty string.

Main menu

When initially accessing the application, a menu with two choices should be displayed: either enter household information, or view reports.

Household Information

When the “Enter my household info” option is chosen, the user will be provided with the following interface to enter their household information into the system.  Note that the user will not have an option to go back and/or be able to change data they have previously entered.

Appliance listing

After adding an appliance, the appliance listing is shown, which will list each appliance’s number, type, manufacturer, and model name. Three options should be given on this screen: either to add another appliance (returning the user to the add appliance form), to delete an appliance (if details were entered incorrectly), or to finish adding appliances and move on to the next screen.

Add power generation

Like appliances, a newly added household will not have any power generation information, so the user should be shown the “Add power generation” form.  However, if a household is NOT “off-the-grid”, the user will have an option to skip this screen and finish submitting their data. If a house is “off-the-grid”, no option for skipping should be presented. If the user enters data on this form, hitting “next” will save the entered data and take them to the power generation listing.

Power generation listing

This form will be displayed after adding a power generation method.  For each method that has been entered, the generator type, monthly kilowatt hours, and battery storage capacity (if applicable) are displayed. Three options should be given on this screen: to add another power generation method (returning the user to the add power generation form), to delete a power generation method (if details were entered incorrectly), or to finish and move on to the next screen. (If all generators are deleted and the household is “off-the-grid”, the user cannot leave this screen until at least one generator has been added.)

Wrapping up

After the user has finished adding or has skipped power generation information, a thank you message should be displayed to them, with a link to the main menu provided.

Reports

When the user chooses the “View reports” menu option, a list with links to generate each report listed in this section    will be displayed.  If a report does not require any parameters, it should be displayed immediately. Otherwise, appropriate inputs for the parameters should be displayed along with a “submit” button to execute the report with the parameters.  Missing or incorrect parameters should prevent a report from executing, with an appropriate error message shown.  Tables, when displayed, must have an appropriate header row.

Some report pages, as defined, can provide information utilizing a single database query and are expected to be written with a single query.  In some relatively rare cases, a report page will require consolidating non-tabular information from multiple queries, with each query providing results as different columns and data types, in which case the use of multiple queries is acceptable.  Whether a report page requires a single or multiple queries will not be indicated here     and must be inferred from the description provided.

In the event a report does not return any results, an appropriate message should be displayed instead of a blank page or an empty table.  Any instances where a NULL value might be returned should be replaced with an empty string, unless    otherwise specified in the report definition. If a sort order is not specified as ascending or descending, then ascending     order is implied.  If a number is rounded, unless otherwise specified, it should follow the “half rounds up” method that   most DBMSes implement.  If a report definition asks to limit the number of rows returned from a larger set of sorted       results, allow the DBMS to arbitrarily choose that subset, with no more than the specified number of rows returned -  “tie-breaking” to determine which rows are shown is not required.

No UI mockups are provided for this section.

Top 25 popular manufacturers

This report will list the top twenty-five manufacturers with the most appliances in the database.  A column for each manufacturer will be displayed, and a column for the raw count of appliances for that manufacturer (as an integer), ordered by count descending.

In addition, an option to view a drilldown report for a particular manufacturer must be provided and should be selected  by the user with an appropriate mechanism, such as a button or link from the parent report’s row for that manufacturer. The drilldown report for the manufacturer will list the manufacturer name at the top, with a table listing the count of appliances belonging to each appliance type produced by that manufacturer (as an integer). All possible appliance types should be present in the table even if the manufacturer does not have any appliances belonging to one or more appliance types. The orientation of the table can either row-wise or columnar for each appliance type.

Manufacturer/model search

This report will allow the user to enter any string and return a list of distinct results where the entered string matches  (with case insensitivity) any part of a manufacturer name or model name.  For example, a search for “air” would match on the manufacturer names “Airsense”, “Springaire”, “Max Aire” and all models from those manufacturers, or the model name “Fairstone” and its manufacturer. Columns for the manufacturer name and model name must be displayed, ordered by manufacturer name ascending and model name ascending. In addition, the cell with the manufacturer name, model name (or both) that matched the search string must be highlighted with a light green background to indicate to the user which field matched their search term.

4.    A table listing, for all off-the-grid households, the percentages (as decimal numbers, rounded to tenths) for each household type.  All household types must be displayed even if that household type has no off-the-grid households

5.    A table that displays the average water heater tank size for all water heaters (as a decimal number, rounded to tenths) in all off-the-grid households, alongside the average water heater tank size for all water heaters (as a decimal number, rounded to tenths) in all “on-the-grid” households.

6.   A table that lists the minimum, average and maximum (as whole numbers, rounded) BTU values for all off-the- grid households’ appliances will be displayed, grouped by appliance type. All appliance types should be displayed; if there is no data for a specific appliance type then all values for that appliance type should be displayed as zeroes.

Household averages by radius

It is believed that users will be interested to know the household statistics within a certain distance of a postal code. This report will require two user inputs: the postal code to center the search on, and the search radius (a whole number, with the following choices available: 0, 5, 10, 25, 50, 100, and 250). The postal code input should be validated, and if invalid, an appropriate error message displayed.  The search radius would include any postal codes whose distance is less than or equal to the distance input.  For example, if the user chooses 5, then anything within <=5.0 miles would be returned. A search radius of zero (0) is acceptable to allow the user to search within only a single postal code. The search result should include the postal code, the search radius, and, for the households within that search radius, the count of households, the count of households for each household type (displaying 0 if there are none for that type), the average square footage (as a whole number, rounded), the average heating temperature (as a decimal number rounded to tenths), the average cooling temperature (as a decimal number rounded to tenths), which public utilities are used (displayed in a single cell, separated by commas), the count of “off-the-grid” homes, the count of homes with power generation, the most common generation method for all households with power generation, the average monthly power generation per household (as a whole number, rounded) and the count of households with battery storage. All averages should be calculated using only households which have corresponding attributes. If an attribute is not present for all of the households within the search radius, no value should be displayed for that attribute.

To determine the distances between postal codes, the haversine formula should be used to calculate the straight-line  distance between two points.  The formula is provided below (additional details can be found at http://www.movable- type.co.uk/scripts/latlong.html, along with examples for utilizing it in database queries).  Note that latitude and longitude are expressed in degrees, which must be converted to radians for these calculations, the Earth’s radius (approx. 3958.75 mi) should be used for R.

To better understand using complex functions such as this one within database queries, you should perform this calculation individually every time it is needed instead of using a built-in or custom function. The calculation should not be performed outside of the database.