@[toc](IntroDB Outline) ## Introduction to Relational Databases ## Introduces relational databases as a solution to issues with storing redundant data in a spreadsheet, providing one copy of the data in a collection of tables that are connected using keys to answer questions, called *queries*, over that related data.  ---------- ### Data: An overview of how data are used in our everyday lives.  ---------- ### Spreadsheet: An introduction to how data are stored using a spreadsheet.  > Viewers are invited to click on the various column headers to sort and review the data. ---------- ### Questions: Examples of asking questions over data stored in a spreadsheet.  > Answering a question using spreadsheets typically involves copying of the data, followed by filtering the rows and selecting the desired columns. ---------- ### Anomalies: An introduction to some problems that may occur when using a spreadsheet to store data.  > The redundant data appearing in this spreadsheet results in problems for certain update, delete, and insert operations. ---------- ### Database: What is a database?  ---------- ### Breakdown: Illustrates how data from a spreadsheet can be broken down into several parts to avoid anomalies.  > The spreadsheet stores three different concepts, resulting in redundant data. Each concept has a button, which visualizes the relevant data for that concept in green with indications of redundant data to be deleted in red with a strikethrough. ---------- ### Relations: What is a relation?  > The three concepts stored in the spreadsheet are represented as three tables in a database. The anomalous operations on the spreadsheet do not have any issues when the data is stored as a collection of tables. ---------- ### Keys: An introduction to primary and foreign keys, and how a database uses them to identify and relate information.  > Primary keys, indicated in gold, represent the attributes that uniquely identify a row in the table. Foreign keys, indicated in orange, represent a primary key of a different table. These primary and foreign keys link the tables together to answer queries over the data. ---------- ### Queries: A brief introduction to asking questions over a database.  > Visualizes the same query asked in the Questions topic using the primary and foreign key relationships over the database tables. ---------- ### Checkpoint: Quiz yourself  > 20 multiple choice and True-False questions provide a formative self-assessment component for the learner. ---------- ## Pedagogy ## Besides assigning the formative self-assessment with a performance goal for participation credit, the use of a cooperative learning exercise in class is highly recommended. A [sample cooperative exercise][1] presents a spreadsheet of employees in departments and asks students to perform the following tasks within their group: - identify any redundancy in the spreadsheet - identify any anomalies (update, insert, delete) that may occur due to the redundancy - propose a breakdown into multiple spreadsheets/tables to remove the redundancy, drawing the tables with the associated data - for each table in the breakdown, identify its primary key - for each table in the breakdown, identify any foreign keys - for each table in the breakdown, create a question/query that can be answered using only that table - for each primary‐foreign key relationship, create a question/query that can be answered using the relationship between the tables. Sample databases for the Students Taking Courses enterprise in the visualization: - [Access][2] - [MySQL Scripts][3] ---------- ## Source ## The OSF FLEX Source Code folder contains the exported Flash Builder project for IntroDB: [IntroDBMain.fxp][4]. [1]: https://osf.io/4vtcn/ "IntroDB Cooperative Exercise" [2]: https://osf.io/ur7gs/ "Access Database" [3]: https://osf.io/8vbne/ "MySQL Scripts" [4]: https://osf.io/9bq5h/ "FLEX Source for IntroDB"