Web and Mobile Development - Databases
Activity Goals
The goals of this activity are:
- To explain the role of relational databases in web service architectures
- To normalize a database to minimize data duplication
- To join databases by a primary and foreign key
- 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
- 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
- What are the primary keys and foreign keys in the tables above?
- What do you think
JOIN means?
- What is the resulting table generated by the query above?
- 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
Questions
- What is the problem being demonstrated here? How might you write a program that is vulnerable to this type of attack?
- 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
- In the first example, why is the code hard to follow?
- If you put a
process.exit()statement at the bottom of the first example, what do you think would happen?
- 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?
- 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.
