Database-Design

Objective

To explore the associations of individual health status and transportation in the New York City.

• The relationship between patients’ hospital visiting frequency and his/her public transportation circumstances
• The relationship between patients’ disease and traffic condition
• the relationship between Hispanic male patients’ weights and his/her traffic condition
• Whether patients at different ages have a preference for taking public transportation
• The relationship between patients’drug visiting frequency and traffic safety condition.

Methods and Implementation

1. Data acquisition

• EHR data from OMOP
• NYC Open Data
• New York City Department of Transportation

2. Data preparation and transformation
3. Create an ER diagram to connect the patient and transportation tables
4. Create SQL queries to engineer features in ACCESS
5. Provide a sample linear regression that can be used to identify the relationship of health status and transportation given the data base.

Conclusion

For this group project, we created a database that linked traffic burden, traffic safety and transportation tools to the EHR data that contains information of patients’ health condition, drug service visiting and hospital visiting. By utilizing this database, we can do further data analysis to explore the relationship between NYC transportation and residents’ health status.