OSU CS-340 Databases Overview

Part Overview of SQL and part web-dev II

Oregon State University CS-340 is a class that starts off as an overview of SQL and database design and then by the halfway point the primary focus is on a group project which effectively turns the class into a sequel to CS-290 Web-dev. The class uses MySql and then gives each team a choice of either JavaScript using Node.js or Python using Flask for the back-end of their project.

During the first week everyone will be told to choose a stranger in the class to partner up with for a project that will last for the entire term. It is very important to choose a partner to pair with within the first week of the course. I imagined that the best time to reach out would not be on the first day, but I also did not want to wait too long as then the partner might be very likely to not be interested or dedicated. I reached out to someone on the course message board on day five, and that was just right. It is important to choose a partner that is open to that stack that you want to work with. We choose to use Node.js with Handlebars as the server-side templating engine because we had both worked with it before and we each hoped to get more practice and a deeper understanding. My partner didn’t want to have any pair programming sessions or video calls, but in the end we did do some simultaneous bug fixes and pushes to master at the very end of the term. We managed all of our communication through Slack chat with some of the conversations be synchronous and others just being a status report that the team member would read when they were next able to.

The group project is a large portion of your grade in the course and it will take the majority of your time in the course. The requirements for the group project were the same for all teams. The first step is brainstorm a subject for the application that would allow enough entities to be a good match for the required complexity, without it being so complex that the scope would need to creep far beyond that requirements to complete the final application. I suggested the project idea that we decided to build which was a software bug tracking application. This left us with numerous possible entities while still allowing us to expand or contract the set if we decided to.

The early steps of the project were to choose five entities with at least two of them having a many to many relationship with another entity. We then needed to take these entities and create an ERD (entity relationship diagram). After each step of the project a draft version of the submission would be posted to a class-wide message board where everyone in the class would be required to make comments on at least two submissions that had fewer than four comments already. This lead to many constructive comments for each submission. After the ERD we needed to create a schema and define all of data types and properties that each table would need to hold.

Throughout the course we had weekly readings that we would be quizzed on and which would prepare us for the database related portions of the project. The quizzes were on a third-party site called Gradescope where we would view the questions and enter our SQL queries to be tested against a grading script. I found that I preferred to use MySql workbench on my local machine to create and test the queries and then paste them into Gradescope. A few of the queries needed small adjustments for cases that were successful on my local machine but failed the grading script.

The book for the course is Relational Database Design and Implementation by Jan L. Harrington. I found the book was pleasant to read and that the examples and warnings that the writer provided were very helpful and that I would be well served to read the book over again as a review.

The next step in the project after the schema was to create all of the data definition and data manipulation statements. The manipulation statements would have temporary variables in them because they would end up referencing context object properties in Node.js, but they had to be properly formatted SQL statements. After a group review each team was turned loose to begin the project. This left just over a month to get the application working. My team member and I used Git and a remote repository on GitHub to collaborate and until the final week we did a pretty good job of making commits to new branches and then merging the new branch into master after the other team member had tested the new code. By the final week we were left with bug fixes where it just made to most sense to just commit to master. Each classmate was given a database instance and every computer science student has access to the school’s server to deploy and application. I decided to dedicate my database instance to the project because I was using MySql workbench on my local machine for the course’s quizzes and my teammate was unsure of how to create a local database, so he wanted to use his instance for the quizzes. I did all of the deployments to my school server instance and it went very smoothly. The school uses Centos 7 for its Linux distribution and it was very nice to work with and there were only a couple of Node module version problems to figure out.

My teammate’s name was Herman Cai and I’d like to thank him for being a pleasure to work with. I’m working on extending the application by adding log in, access control for administrative features, and a scheme to allow the data tables to be responsive on vertical mobile phone layouts. The currently deployed version of the project is hosted at this url within my projects on this site. https://adamjwright.com/bug_tracker

Leave a Reply