Normalization

My excel file consists of following content:

OwnerID,OwnerLastName,OwnerFastName,OwnerAddress,,OwnerCity,OwnerPhone,Posession,WellLocationLong,WellLocationLat,WellNo,WellDrillDate,WellDepth$($m$),$TestId,DateCollected,DateTested,Calcium$($ppm$),$ Magnesium$($ppm$),$Nitrate$($ppm$),$Sulfate$($ppm$),$Iron$($ppm$),$Sodium$($ppm$),$pH$,$LabTech,EmailAddress

This is the excel sheet contents with $0$normal formats. normalize this data into $1$st$,2$nd and $3$rd normal forms. You will take your $3$rd NF and put it in Access and design some queries, reports and forms to facilitate use of the data.

Imagine: The provincial water quality board has been recording water quality data in an excel spreadsheet.. The board explains the following rules:

$$ Every well is provided with a unique Well number $($WellNo$).$

$$ Owners can own multiple wells.

$$ A well will only have $1$ current owner.

$$ A well can have multiple tests against them.

$$ Multiple tests can be collected and completed in a single day.

$$ We currently have no way to uniquely identify owners.

Given these rules and the provided data, normalize the data into $1$st$,2$nd$,$ and $3$rd normal forms in excel.

Access

Tables

Using your $3$rd normal form from the previous section create an access database and add your $3$NF tables into the database. Be sure to:

$$ Assign appropriate primary keys $($including any required composite keys$).$

$$ Assign appropriate datatypes, Null values can be blank.

$$ Use input masks on any phone numbers.

$$ Use Short Date input mask on any dates.

$$ Assign appropriate relationships at the database level $($and enforce referential integrity$)$

$$

Forms

Create the following forms, all forms should be $$end user ready$$ meaning you should have correct spacing, tab order, be presentable and logical$.$ All labels for a control $($text box$)$ should be bold. Failure to do so will result in lost marks.

Tech Form

Create a form for Tech management it should:

$$ Display the full tech name.

$$ Display the full tech email addresses.

$$ Use this form to add yourself as a Tech.

Owner Management

Create a form for owner management. It should:

$$ contain all the owner table fields

$$ have a title called: Owner Management, and a second label with your name

$$ Form labels should be bold.

$$ The current date should appear in the top right corner of the form $($this is programmatic$)$

$$ Buttons for record navigation $($Back$,$ Add New, Next$)$

$$ A button to exit the form.

$01$ Owner Management Form

Test Form

Create a form for existing test management it should:

$$ List all the fields for editing in a logical manner

$$ Have an image of a chemical beaker $($this can be found online$)$ in the forms header

$$ In the header have $[$Your Name$]$s Test Entry form next to the beaker image.

$$ For the tech field, display the tech$$s in a combo box by their name, this should use the tech table as a source.

$$ Have todays date $($programmatically$)$ in the footer of the from.

$$ Use this form to make yourself the tech for test number $4.$

Queries

All queries that use prompts should have an appropriate prompt that lets the user know exactly what is being searched for. All Queries should only return the columns asked for. All Queries should be named based off the header in this document. Any custom Columns should have a meaningful name $($not ExprX$)$ Failure to do any of these things will result in lost marks.

Old Wells

Write a query that returns all well data for wells that were drilled before $1999.$

Wells Owned

Write a query that Prompts for a Well Owner$$s last name and returns:

$$ OwnerFName,OwnerLname,WellNo,WellLocationLong,WellLocationLat,WellDrillDate

Test Results

Write a query that returns:

$$ WellNo,WellDepth,DateCollected,DateTested,All chemistry fields,LabTechName,LabTechEmail

Sort this query by DateCollected Descending

Well Health

Write a query that groups by WellNo and returns the average of all chemical fields.

Reports

All Reports should have an appropriate title.All data should be shown in full $($not cut off$/$truncated$).$ Failure to do this will result in reduced marks on the report.

Test Results

Use your test results query to create a report that:

$$ Lists all the columns from the Test Results query.

$$ Chemical column labels should be vertical.

$$ Page number and today$$s date should be in the page footer.

Average Well Health

Use your Well Health Query to create a report that will highlight the average well health.

$$ All Chemical column headers should be vertical.

$$ Remove avgOf from the column headers and capture it across the top of the page header.

$$ Use Conditional Formatting to apply Bold red font to fields that break the following water quality standards:

Chemical Operator Value

pH $>8\mathrm{.}5$

pH $<6\mathrm{.}5$

Calcium $=>80$

Magnesium $=>30$

Nitrates $=>10$

Sulfates $=>250$

Iron $=>\mathrm{.}3$

Sodium $=>250$

make access database and excel spreadsheet