Developing standardized data management (database) from spreadsheet

Course context: Developing and understanding the importance of data management is critical in STEM fields. Too often this process is treated as an afterthought. This project uses real, preliminary data collection for students to understand the issues that can arise with the lack of data standards/quality/management. This cALE was originally developed for a GIS Data Acquisition & Management course but is easily adaptable as GIS is not required for this assignment/project — the focus is on data management of real-world, collected datasets. Relevant Course Learning Objectives: Clearly document standard operating procedures in data acquisition and management processes Demonstrate development and use of structured naming conventions and file management systems Identify the fundamentals of database systems, design techniques, and their use in organizations Create and alter databases using standard relational database management systems (DBMS) and geodatabase structures Develop entity-relationship diagrams, relational schemas, and data dictionaries given a set of business rules Define, design, and implement attribute domains Write database queries using Structured Query Language (SQL) for a variety of data definition and data manipulation scenarios Define and identify differences between data and file types Describe what metadata is, why it’s important, and applicable standards for capturing and documenting metadata <!--%3Cmeta%20charset%3D%22UTF-8%22%20%2F%3E-->Background/Assignment: The Expanded Sustainable Interdisciplinary Research to Inspire Undergraduate Success (SIRIUS II) Project aims to provide interdisciplinary authentic learning experiences (ALEs) to STEM students across 67 courses at five institutions in the Sacramento Region, focusing on the human impacts of the American River Ecosystem. This project is based on the initial creation of a database management system for the SIRIUS-II project, specifically, for Water Quality research. You will base your initial design and implementation using data from research conducted at Putah Creek and along the American River. Because the SIRIUS-II project spans multiple campuses, departments, instructors, classes, and projects you need to implement data management strategies beyond simple spreadsheets in order to maintain data integrity. Your task is to take the spreadsheets we've been provided and come up with/document a robust database for use in tracking data collected. You need to ensure data entry integrity, a logical database schema and table layout, and consistent naming conventions. You will design, implement, and populate the database (this assignment). Please note: the data entry is not standardized — that’s your job. Standardize the data when entering into the database schema, flag any entries where you are unclear how to standardize/data is missing/etc. Document the development of the database, including data standards, data dictionary, entity-relationship diagram, and rationale for database design. Also include an SOP (standard operating procedure) for how to perform data entry across the database tables. There are a lot of strategies and visions you can employ. <!--%3Cmeta%20charset%3D%22UTF-8%22%20%2F%3E-->Your audience is faculty member(s) who will be using the database across institutions and departments. Student Materials: Email with the request email_SIRIUSII_ALE_data.pdf Spreadsheets provided: American River Data Fall 21.xlsx Putah Creek Data 9-24-21 I-80.xlsx Putah Creek Multi Semester with F21 Data.xlsx Additional Materials: Final Project - Putah Creek Database.pdf Final Project Presentation.pdf Software: MS Excel (or other spreadsheet program) MS Access (or other relational database Time given: 2 lab periods (one focused developing a sound database structure/entity-relationship diagram and second for implementation and documentation) OR used as a final project with students working in 2 small groups — one data focused and one role focused (as final project provided herein) Student Deliverables: Access database Documentation - Word document describing: (these can be subdivided logically into multiple files, indexed in a Table of Contents within one doc, or a combination thereof) Scope (of project and any alterations/enhancements you may have made. Basically, what are the asks and the expectations within), Data Dictionaries, Entity-Relationship diagram, SoP how to use the database, SoP of database development and rationale for choices made in the implementation. optional: oral presentation file ===== ADDITIONAL SYLLABUS CONTENT: An Authentic Learning Experience (ALE) Course! Welcome to an authentic experience course focused on providing you hands-on experience with real-world problems and datasets. They’re so real-world and experimental that very often there is no right, or one, answer! In fact, the answer may not even be possible with the information available. Over this course we’ll work on identifying those problems and situations, especially as they relate to data acquisition and management. Part of this experience is collaboration; therefore, you will be working on identifying problems/solutions with your fellow classmates. Developing strong collaboration and communication skills is incredibly important outside of Academia, even more so for GIS practitioners who are often working with other domain specialists in biology, ecology, archaeology, real estate, recreation, planning, public policy, demography, etc. ALE Learning Outcomes: Information Literacy: Distinguish between different types of information sources and develop skills for searching for geospatial data Problem Formulation: formulate criteria and constraints to guide brainstorming of possible solutions to initially defined problem(s) Study Design: Compare strengths and limitations of designs/solutions; identify methodological problems and success how to troubleshoot Communication: Use appropriate language and style to communicate geospatial STEM concepts to target audience(s); Use a variety of modes to communicate concepts (oral, written, visual) Identity: Develop confidence that you can understand and do geospatial STEM work; understand how geospatial STEM professionals work on real problems and that work is done in diverse teams; Develop tolerance for obstacles faces in the design/implementation process Metacognition: Know and use a variety of problem-solving strategies; demonstrate willingness to seek help from others; embrace a growth mindset with respect to intelligence and ability Community: Work with teammates to establish and update group plans and expectations (ex: goals, timelines, individual and collaborative tasks); elicit, listen to, and incorporate ideas from teammates with different perspective and backgrounds; critique others work/ideas constructively & respectfully