CS 3200 Fall 2022 Project

Please note that this is a living document and will be updated regularly over the next several weeks as the project evolves. Check back frequently.

The 50,000 ft. Overview

Your mission in this project is to design implement a database-driven software product MVP.

In teams of up to 4 students, you will

  • identify the general idea of the product,
  • outline major features of three or more archetype users of the product and describe data view for each of them,
  • develop a conceptual model that incorporates/integrates the archetypical users’ views of the data,
  • iterate on the conceptual model and develop a logical model for a relational database,
  • implement that model in MySQL,
  • design and implement a REST API to provide access to the data
  • implement a collection of user interface pages that cohesively represent an MVP for your data product.


Your team can consist of up to 4 members from either of Dr. Fontenot’s CS 3200 sections in fall 2022. You could also choose to do the project individually, in a team of 2 or a team of three. However, no teams larger than 4 will be allowed.

General Architecture Overview

The figure below gives you a high-level overview of the project’s architecture.

On your local machine/laptop, you’ll run two Docker containers: MySQL Server and the Flask API service.

  • The AWS Docker documentation describes it as follows: “Docker is a software platform that allows you to build, test, and deploy applications quickly. Docker packages software into standardized units called containers that have everything the software needs to run including libraries, system tools, code, and runtime.” We won’t be deploying on AWS, but this description was on-point in my opinion. Once you have a system set up to run in Dockerized containers on your development platform, they can be deployed to production fairly easily to a system like AWS, Azure, etc.
  • MySQL is a popular relational database management system. You may have also heard of MariaDB.
  • Flask is a light-weight framework written in Python in which you can build web apps, REST APIs, etc. In is very lean by design, but extensions provide tons of additional functionality.
  • Typically, services like MySQL, containers, etc running on your laptop cannot be accessed from the public internet very easily. Think firewalls, no domain name, and other useful impediments to the security of your system from being breached. However, sometimes you want to specifically allow certain access from the public internet. ngrok allows you to run a small service on your laptop (not in a container by the way) that create a private tunnel to the ngrok system. This will allow you to link up the AppSmith server running in the cloud to your REST API running on your laptop.
  • AppSmith (also check out this YT Video) is marketed as a low-code, open source platform for building and maintaining internal software tools such as dash boards, admin panels, and simple CRUD applications. While fairly straight forward and low-code, it will provide sufficient functionality to build a UI for this project. You can create access to data sources (such as your REST API), create queries, and use a drag-and-drop designer to build out the UI linking various components to the queries you create. You’ll develop your UI on an AppSmith server I am hosting in the cloud which will then connect to your REST API via ngrok running on your local machine.

Project Phases

The following deliverables will be submitted via a forthcoming Google Form:

  1. Name for your team
  2. Names and contact info for team members
  3. Product name
  4. Brief synopsis of your product and what problem or problems you hope it solves. This should be no more than 1 professionally-written paragraph.
  5. Minimum of 3 User-Personas/Archetypes. For each persona, develop a:
    1. Name / category of user
    2. Brief description of the user’s role in using the system.
    3. 2 – 3 features or tasks that this user should be able to perform in the system.

While you will submit the above via Google Form, if you were to write it in a Word document, I wouldn’t expect it to exceed a page. However, favor completeness over brevity.


  • Google Form can be found > here <.
  • Due: Tuesday Nov 8 @ 11:59 pm.

In Phase 2, you will develop a localized ER Diagram for each persona and (at least) two wire-frame models of screens each persona would need. Then you will merge those into one overall ER diagram. That will then be converted into a relational model that can be implemented in MySQL. This will include creating a MySQL script file incorporating the DDL plus sample data creation.

Deliverables for Phase 2

  1. For each persona/archetype for your application, develop an ER diagram representing the data needs for that particular persona. Be as detailed and complete as you can in these diagrams.
    • Do not include any entities or attributes in the localized models that aren’t needed by that persona. For example, if you’re developing an application to manage a toy manufacturing company, the design engineering that dream up, prototype, and test new toys probably don’t need to know anything about customers or sales people. Similarly, customers of that company very likely don’t need to have a view into the quality assurance and testing done by the design engineers.
    • When two personas’ ER diagrams refer to the same entity, use the same name in both diagrams. For example, if two of the localized ER diagrams for the toy company mentioned above refer to customers of the company, label both entities with the same name, customers for example.
  2. From the 3 localized ER diagrams, develop 1 ER diagram that incorporates the data needs of all three personas. Be intentional about not duplicating data. Include as much detail as you can (cardinalities, participations, etc…)
  3. Translate the ER diagram from Step 2 into a relational model using the process covered in class.
  4. From the relational model generated in Step 3, create a 1st draft database bootstrap SQL file. This file should include:
    1. the creation of the database
    2. the creation of a webapp user that is granted appropriate privileges to the database,
    3. the creation of all tables including appropriate integrity constraints
    4. the creation of a small amount of sample data for each table (you’ll add more later). Check out Mockaroo or similar tools.
  5. Generate 2 wireframe screens for each of your personas. Since practically every project would include the need for UI functionality for creating users, resetting passwords, etc., please do NOT include these as wireframes for any of your users. Stick with wireframes that are central to that particular persona. You can use Lucid Chart, Miro, Figma, or any other wire-framing tool (even Powerpoint or Google Slides).

Model Complexity

You may be wondering how complex your model needs to be.  I don’t want to give hard and fast minimums and maximums as that may stifle creativity.  I think you can probably guess that a database with 1 table and 5 attributes won’t cut it.  But you probably also realize that 100 tables and 4000 attributes might be over-kill for a semester project. 

For a project of this scope and scale, I think it would be reasonable for you to have between 12 and 18 tables with a combined 50 to 80 attributes. If you want to have more, great – go for it.  If you want to have less just ping me about it.  I can either give you the thumbs up or give you some ideas about how to expand your product.  


  • DUE: Tuesday Nov 22, 2022 @ 11:59 PM EST
  • You will submit 2 files to Canvas:
    • a professionally formatted PDF containing ER Diagrams, the Relational Diagram, and Wireframes
    • .sql file containing all the DDL code for your database include basic sample data. 

For Phase 3, Do the following:

  1. Fork/clone the Flask MySQL repo. Only one fork per team is needed. See Phase 3 – Setting Up for more information.
  2. Add/modify the boilerplate code to:
    • add your Database bootstrap file.
    • add routes needed for the wireframes you submitted in Phase 2.
    • remove all sample routes that you are not using in your project.
    • You should have a minimum of 8 routes, at least 2 of which should support POST requests.
  3. Create an AppSmith Application consisting of 4 or more pages (screens) that make use of connecting to your REST api.
    • At least one of the pages must insert or update data in the database.
    • The remainder of the pages can be simple data views using GET requests.
    • More info on how to set up AppSmith will be released today.
  • a 3-5 minute video pitch of your product that includes demo of the AppSmith app.
    • Brief intro of team
    • “Elevator Pitch” for your application
    • Quick review of the routes you’ve created
    • Demo of the Appsmith Pages you’ve created
      • Show that for any POST routes, the data is ending up in the DB. 
    • You can host the video anywhere you’d like.  Please put a link to the video in the GitHub repo README.md. 
  • a GitHub repository with all necessary code and infrastructure needed to run the back-end containers. The repo should be professionally organized including a top-level README.md file.
  • a UI implemented in AppSmith on Fontenot’s server.

Extra Resources

  • General Python
    • I recommend installing Anaconda’s Python distro.
    • You should be able to access conda, the command line tool for Anaconda, via your system’s terminal or command prompt.
      • On Windows, go to the start menu and search for Anaconda Prompt.
      • On Mac, if installed correctly, it should already be set up and ready to go.
      • Run conda --version in terminal or command prompt to see if things are set up correctly. That command should return something like conda 22.9.0, however, the number might be slightly different.
  • Flask Stuff
  • Docker Stuff
  • General Command Line Stuff
    • On a Mac? Then check out > this < video from TopTechSkills or > this < video from Academind.
    • On Windows? Then check out > this < video from Jobskillshare Community. You can probably watch this on 1.5x speed.

Here is a playlist of YouTube videos I’m working on for Flask.