Reference no: EM132923604 , Length: word count:1500

ALY6030 Data Warehousing and SQL

Assignment: Dimension Table and Fact Table Techniques

Story:

Alcohol Use and Alcohol Use Disorders Among Adolescents and College Age Populations: Researchers estimate that each year:
• 1,825 college students between the ages of 18 and 24 die from alcohol-related unintentional injuries, including motor-vehicle crashes.
• 696,000 students between the ages of 18 and 24 are assaulted by another student who has been drinking.
• 97,000 students between the ages of 18 and 24 report experiencing alcohol-related sexual assault or date rape.
• Roughly 20 percent of college students meet the criteria for an alcohol use disorder (AUD).
• About 1 in 4 college students report academic consequences from drinking, including missing class, falling behind in class, doing poorly on exams or papers, and receiving lower grades overall.
Data:
• You will use the data from BRFSS files provided to propose methods to identify expected concentrations of heavy drinking among adolescents.
• The BRFSS is the nation's premier system of health-related telephone (land line and cellular) surveys.
• It collect state data about U.S. residents regarding their health-related risk behaviors, chronic health conditions, and use of preventive services.
• Established in 1984 with 15 states, BRFSS now collects data in all 50 states as well as the District of Columbia and three U.S. territories.
• BRFSS researchers complete more than 400,000 adult interviews each year, making it the largest continuously conducted health survey system
having more than two drinks per day and adult women having more than one drink per day)".
• The column marked Data_Value gives the number of people who responded "Yes" to the survey question.
• The column marked Sample_Size gives the total number of people who were asked the question.
• The groups are defined in terms of zip code.
• The time period will be 2009 and the questions pertain to the state of OK.

Task:
• Explore and gain an understanding of the data available from Behavioral Risk Factor Surveillance System (BRFSS) data warehouse.
• Explore survey responses ("Y") that are relevant to adolescent alcohol consumption.
• The time period will be 2009 for the state of Oklahoma (OK).
• Propose method(s) to identify (anonymous) groups of adolescents who may be at highest risk for alcohol abuse.
• Perform an initial design of a SQL database by constructing an ERD including table relationships.
• Find the areas of OK have highest and lowest number of respondents for adolescent alcohol abuse.
• Write a SQL query that would answer this question (you do not have to execute it).
• By city.
• By county.

Attachment:- Data Warehousing and SQL.rar