Web and Mobile Development - Databases

Activity Goals

The goals of this activity are:
  1. To explain the role of relational databases in web service architectures
  2. To normalize a database to minimize data duplication
  3. To join databases by a primary and foreign key
  4. To use the SQL language to insert, query, and update database tables

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 take notes for the group, and appoint another member to discuss your findings with the class. After class, think about the questions in the reflective prompt and respond to those individually. 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: Relational Databases

People
PersonID Name Age
1 Bill 38
2 Alex 25
3 Lee 19

Questions

  1. Draw a flow chart of the function calls in the program below.

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: The SQL Join

People
PersonID Name Age
1 Bill 38
2 Alex 25
3 Lee 19

Courses
CourseID Name CourseNumber
1 Introduction to Computer Science CS173
2 Object Oriented Programming CS174
3 Web and Mobile Development CS471

Enrollments
EnrollmentID CourseID PersonID
1 1 1
2 2 1
3 2 2
4 2 3


SELECT * FROM Enrollments 
  INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID
  INNER JOIN People ON Enrollments.PersonID = People.PersonID;

Questions

  1. What are the primary keys and foreign keys in the tables above?
  2. What do you think JOIN means?
  3. What is the resulting table generated by the query above?
  4. Why not just have a single table with the course name, person name in it to begin with, like the one you obtained through the JOIN statement?

Model 3: 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? The solution is provided automatically by a node.js library, which you should use when concatenating user input to executable code.

Model 4: node.js Promises, Revisited

Questions

  1. In the first example, why is the code hard to follow?
  2. If you put a process.exit()statement at the bottom of the first example, what do you think would happen?
  3. In the second example, why is there a return statement in the middle of execution? This statement does not return from the function: what do you think it does?
  4. What are the advantages and disadvantages of the await statements in the third example?

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: RESTful Services with a Database Backend

See the Example Below

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 Collaborative Spaces section of OneNote. You can add a page with your name and your group members' names, and today's date, as the title. Under the appropriate section (i.e., "Class Notes", "Collaborative Spaces", "Reflective Prompts") that you can select on the left side of the screen, you can click "Add Page" on the right side. You can answer any reflective prompt questions in the Reflective Journal section of your OneNote Classroom personal section.