CS 3200 – Database Design – Fall 2022

Important Documents

Lecture Notes

Homework Assignments

  • HW 00
  • HW 01 – Due Sept 20 @ 11:59pm. 
  • HW 02 – Due Oct 4 @ 11:59pm to GradeScope. 
  • HW 03 – Due Oct 18th @ 11:59 to GradeScope Canvas.  You’ll submit a single URL to the slide deck.  PLEASE read the instructions on Canvas about how to properly submit
  • HW 04 – Due Nov 1 @ 11:59 in PDF to GradeScope.  Please remember to properly link your solutions to the question in GradeScope.
    • With respect to Question 1, assume that in a proper ER Diagram, all unique attributes should be underlined.
  • HW 05 – Due Dec 7 @ 11:59 PM.  You’ll submit by sharing a tutorial AppSmith app with Dr. Fontenot. 

Semester Project

Extra Material

This material was moved to the Class Project Page at the bottom.  

Flask GET /form Route

@app.route("/form")
def get_form():
    return """
    <h2>HTML Forms</h2>

    <form action="/form" method="POST">
    <label for="first">First name:</label><br>
    <input type="text" id="first" name="first" value="John"><br>
    <label for="last">Last name:</label><br>
    <input type="text" id="last" name="last" value="Doe"><br><br>
    <input type="submit" value="Submit">
    </form> 
    """

Challenge problem from Northwind:  Provide a list of all customers whose average order total is below the average for all companies in their same region.  

 

SELECT customerAvg.customerid,
       customerAvg.Region as CustomerRegion,
       customerAvg.custAvg,
       regionAvg.avgOrderTotal
FROM (SELECT Region, AVG(orderTotal) AS avgOrderTotal
      FROM (SELECT o.orderid, o.customerid, c.Region, sum(od.unitPrice * od.quantity) AS orderTotal
            FROM Customers c
                     JOIN Orders o ON c.CustomerID = o.CustomerID
                     JOIN OrderDetails od ON o.OrderID = od.OrderID
            WHERE region IS NOT NULL
            GROUP BY o.orderid, o.shipRegion) AS orderTotals
      GROUP BY Region) AS regionAvg
         JOIN
     (SELECT customerid, region, avg(orderTotal) as custAvg
      FROM (SELECT o.orderid, o.customerid, c.Region, sum(od.unitPrice * od.quantity) AS orderTotal
            FROM Customers c
                     JOIN Orders o ON c.CustomerID = o.CustomerID
                     JOIN OrderDetails od on o.OrderID = od.OrderID
            WHERE region IS NOT NULL
            GROUP BY o.orderid, o.shipRegion) AS CustomerTotals
      GROUP BY customerid, region) AS customerAvg ON regionAvg.Region = customerAvg.Region
WHERE custAvg < avgOrderTotal;

SQLite is a small-footprint embedded database system.  Below are some resources to help you understand it better. 

  • The Textbooks (and a ton of other useful material) can be accessed through the University Library Website.
    1. Go to https://library.northeastern.edu
    2. Click on Databases and Journals (A-Z).
    3. On the left side or under A-Z Databases, click ‘O’ for O’Reilly.
    4. The first link under O is “O’Reilly for Higher Education”.  Click it.  You may need to make an account on O’Reilly using your NEU email. 
    5. Once you’re logged in, you can search for the “CS 3200 – Fall 2022” Playlist or follow > this < link. 

Important: Only have MySQL Running when you’re actively using it.  Don’t leave it running in the background as this could pose a security risk.  To stop the server, choose the MySQL line in the list and click stop

XAMPP on Mac

Note: You can remove the previous version of XAMPP if you’ve already installed it. 

  1. Go to > this < link and download the file named xampp-osx-8.1.6.0-installer.dmg (NOT the one that ends in -vm.)
  2. Install the application. 
  3. After installation is complete, you should have a XAMPP folder in your Applications folder. In that folder, double click manager-osx.app. You may need to authenticate for it to fully start.
  4. In the Window that pops up, choose Manage Servers tab at the top. 
  5. Click the MySQL Database entry then click Start.  It will take a bit for it to start.  The red dot will first transition to yellow.  When it is completely started, the dot will become green.  
    • If the dot goes from red to yellow to red, send me a DM on slack.