联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp2

您当前位置:首页 >> Database作业Database作业

日期:2020-09-24 10:45

Due Oct 2nd, 8AM

This is an individual assignment with collaboration!  

Objective

To allow you to review and brush up on concepts from your C# and database classes, you have due a program that maintains and accesses a Microsoft SQL Server database using SQL.

Note:  This is an individual assignment with team collaboration.  You are welcome and encouraged to work with others, but the final product must be of your own design demonstrating your ability to design and implement a simple Windows Form application with connection to an MS SQL database.


Details of the assignment


In this assignment, you will use health data and Google search engine results to provide a general description of a geographic area (DMA), and then find a similar area.  Consider this a way to eventually determine if Covid spreading the same way in similar areas (for a future assignment).


Data Description

The data you will need for this assignment can be found in the spreadsheet titled HW1.xls.  It contains several tables.  


The first is “DATAELEMENTDESCRIPTION”, which contains a textual description for several of the tables.


The second table, “Regional_Intel”, contains information from Google Search results on certain marketing areas.  For example, this part of Arkansas is 670 (see row 136).  Think of these table numbers as being a form of search intensity.  Column C for example, has an average of about 44.  Our corresponding entry in row 136 is 48, so we have a slightly higher search for the term “cancer” in the year 2004.


The third table “DMA-zip” gives us a way to use the DMA CODE to get to the FIPS number.  In this table, FIPS is combined state and county.  For example, the Mobile-Pensacola DMA CODE is 698.  It corresponds to the FIPS of 1003.  Often we see FIPS written as 01 for the state, and 003 as the county within the state.  Here they are combined.  Further, 698 includes Montgomery, AL with FIPS 1013.  Yet further, it includes 1039 and 1041.  I’m betting you will find some Florida FIPS associated with this DMA too.


The fourth table “DEMOGRAPHICS” contains population measures.  It has the FIPS broken into two columns.  We would be interested in the population size, Poverty, and perhaps density among others.


The fifth table “SUMMARYMEASURESOFHEALTH” shows FIPS in two columns with data including Average Life Expectancy & Deaths.


The sixth table “RISKFACTORSANDACCESSTOCARE” has the FIPS also in two columns of strings.  It has a lot of interesting columns in it.


You will need to put these tables into a database, though you may want to clean up your potential primary keys using Excel before you import them into your database.


Assignment

The purpose of this project is to create a database that contains tables 2-6 and lets a user choose a DMA from table 2 and the resulting query will return some information from each of the tables in order to address the following.


If I were to choose the DMA (for example, our area #670), it should return

a.A summary description of the area including information from each table.  

b.A comparison on how the chosen DMA compares to national averages, highlighting where the difference is more dramatic.

c.Information related to the search trends for each of the nine search categories (cancer, diabetes, depression, …)

d.It should find what you consider to be the most comparable region from the other DMAs in the Intel table.  Why is this chosen DMA1 similar to DMA2?  



When a user logs into the system, it should log the access in a log table of your design.  This should include minimally the username and time stamp.


They should then be able to select one of the DMAs logically.  You should not create a dropdown control with >200 items!  Design your user interface in a way that would make it easy to select a DMA.

Application Design

Your design will include (minimally) the five database tables which were described previously and available in the spreadsheet, as well as a log file.  Designing and implementing the appropriate primary and foreign key constraints/relationships are integral and required.


You may get the data into the database however you can, including wring code or paste-and-cut.  Some of the tables are large so you will want to do this efficiently.  Ask friends…


This should be an interactive program that uses .NET to create a GUI user interface. You may design the multiple screens as you see fit. The program should allow the following actions:

?Provide an executable (.exe) file so a user can easily begin the program without using Visual Studio.Net

?Display a log-on screen to connect the database.  Create access for three potential users.

?Your password should not be embedded into your code, you will need to provide

?Handle both proper and failed logon attempts

?Display

?There must be the ability to select a given DMA minimally, though the user should not be knowledgeable or even know the numeric DMA is being used.  They should be able to select through a better way (perhaps state/region, or even a map).

?You will have to decide what information is relevant but consider this a tool that will eventually help us predict Covid.  Include any data that you think might eventually help predict a region’s susceptibility to Covid.

?As the system suggests a similar region, make sure it displays the fields that led to this calculation of similarity.

?User Experience

?The user interface and screen design are important and meaningful.  And, all processing should either prevent the possibility of making an error or display a message box which indicates an error has been made and allow the user to correct the error or continue processing without interruption

?Must be able to exit the program gracefully

?Choose your own adventure.

?Applications which show additional features or exceptional fit and finish may be eligible for bonus points.  Some form of analytics is highly encouraged.

The items detailed above and in the rubric below are minimum requirements.  You should take the time to think through how best to meet these requirements.  For instance, will you present all the data on one single page, or maybe you will use multiple tabs or multiple dialog boxes?

Feel free to refer to the .NET handout in Blackboard under course documents for implementation details regarding .NET & SQL.  


Grading Rubric

15 All the following are present

?Your application (as an .exe)

?Screenshot of database tables with relationships

?Screenshot of each table (5) and log and a few rows of data for each

10 Login screen

?Working properly (including successful and failed login attempts)

?Credentials are not stored in the application

30Allow the user to select a DMA region of interest and display some demographic information as well as some trends from search engine analytics

30 Show the nearest neighbor of the user’s DMA selection and the reasons why your system concluded they were similar

15 Prevent errors or handle them gracefully

Documentation in program (and otherwise)

User interface design

100 Total

(up to 10 pts) BONUS:  

?Use a map to allow the user to select a region.  (you do have some zip code data…)

?Allow the user to filter the similarity search.  For example, you may want to only look at regions physically close to the chosen region.

?Choose your own adventure – surprise me!

A few more hints from past semesters

?Don’t tackle the whole problem at once, solve in pieces.  For instance,  

?Clean the data so that the primary key of FIPS is in just one column for each table and has the same format

?Start by writing, testing, and perfecting the SQL queries you need in SQL Explorer.  (Better yet, make these queries into stored procedures!)

?Then build your form layouts, how will the screens work together.  Setup all the forms you need with standard buttons like Ok/Cancel and make sure you can navigate them

?Determine how to execute a query that returns data and puts it onto your form

?Repeat for the other two items

?What other steps can you take to break down the problem?

?User interface design matters.  


?Keep your graphics functional and clean.  

?

?


版权所有:编程辅导网 2018 All Rights Reserved 联系方式:QQ:99515681 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。