CS377: Database Design - The Structured Query Language (SQL)

Activity Goals

The goals of this activity are:
  1. To use the SQL language to query and manipulate a database
  2. To integrate common programming constructs with SQL language
  3. To use a prepared statement to sanitize program inputs used in a SQL query

Supplemental Reading

Feel free to visit these resources for supplemental background reading material.

The Activity

Directions

Consider the activity models and answer the questions provided. First reflect on these questions on your own briefly, before discussing and comparing your thoughts with your group. Appoint one member of your group to discuss your findings with the class, and the rest of the group should help that member prepare their response. Answer each question individually from the activity on the Class Activity Questions discussion board. After class, think about the questions in the reflective prompt and respond to those individually in your notebook. Report out on areas of disagreement or items for which you and your group identified alternative approaches. Write down and report out questions you encountered along the way for group discussion.

Model 1: Python SQL Programming

Schema Definition Generated by eralchemy from the PythonSqlExample replit example

Questions

  1. Create a class to encapsulate the behavior of a SQL Query.
  2. Discuss with your group a potential data model and draw a schema for that model. As a class, create the database in SQL, and then implement it with your Python program.
  3. What are the primary keys, foreign keys, kernels, and dependencies in your data model? For each dependency, what kind of dependency is it?

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Model 2: Sanitizing Database Inputs

XKCD Exploits of a Mom

Questions

  1. What is the problem being demonstrated here? How might you write a program that is vulnerable to this type of attack?
  2. What can we do to prevent this?
  3. Look up a SQL Prepared Statement and write down how to execute one in your favorite programming language and library.

Model 3: Advanced SQL Queries

Schema Definition Generated by eralchemy from the Sql-Queries replit example

Questions

  1. Write a Python program to implement this database and queries.
  2. Add a statement to UPDATE a column in this database.
  3. Add a statement to DELETE a column in this database.
  4. Deleting records can be dangerous if your query has an error, and you may wish to preserve the record history and provenance, anyway. What is an alternative to deleting the record that would cause it to be hidden in your subsequent queries?

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Model 4: Group Clauses

Schema Definition Generated by eralchemy from the Sql-Groups replit example

Questions

  1. Write a query to count the number of dependents an employee has from the ER Model Activity

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Model 5: Subqueries

Schema Definition Generated by eralchemy from the Sql-Subqueries replit example

Questions

  1. How do subqueries relate to the SQL JOIN we've seen previously?

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Submission

Submit your answers to the questions using the Class Activity Questions discussion board. You may also respond to questions or comments made by others, or ask follow-up questions there. Answer any reflective prompt questions in the Reflective Journal section of your OneNote Classroom personal section.