CS 3200 – Database Design – Spring 2023

Important Info

• Syllabus
• Join my Slack Org: Click > here <.
• If you can’t see the course on Piazza, please DM Fontenot.
• Coming Soon:
  • TA Office Hours Info

Lecture Notes

• Introduction
• The Relational Model
• Relational Algebra (Solutions for Part 1)
  • 9:15 Section Relational Algebra Solutions from Jan 19
  • 10:30 Section Relational Algebra Solutions from Jan 19
  • YouTube Playlist with some Rel Alg Problem examples I made
• SQL DQL
  • Adding Northwind to DataGrip
  • Link to the a SQLite Chinook DB.
  • SQL Practice Problems for Chinook with Expected Output
  • Northwind Challenge Problem Solution
• SQL Grouping + Having And Chinook Problems.
  • Solutions to the Chinook in-class problems (note that the solutions might not exactly match those from class)
• SQL DDL (with a touch of DML)
• MySQL and Docker – SetUp
• MySQL DDL
• Advanced SQL
• Exam 1 Review (Fall 2022 Exam (aka Quiz) – Solutions (These are updated solutions; questions are in a different order, but same questions.)).
  • Info on Cheat Sheet Extra Credit
  • Info on what DB Schemas will be used on the exam
• Auto increment, Time Stamps, and Indexes
• Designing a Relational Database Part 1
• Designing a Relational Database Part 2
• Designing a Relational Database Part 3
• ER Model -> Relations – Solutions
• Exam 2 Review

Project Implementation Decks

• Background and Getting Started
• Flask 101
  • Flask-Examples GitHub Repo
• Designing a REST API
• Looking at the Project Boilerplate
• Adding Forms to the Mix
• UI with Data and GitHub for AppSmith
• Data Generation
  • Mockaroo.com
    • Create Project
    • Create Schemas for each table
    • Save DataSet in the Mockaroo Project (as CSV)
    • For foreign key references, use the “Dataset Column” option.
    • You can also download the CSV files that are generated for each schema
    • Check out > this < post for more info.
  • CVS to SQL Converter

Semester Project

• Project Handout
• Phase 1 due March 28 @ 11:59 pm.
  • Submissions will be done via Gradescope.
  • Template and submission directions can be found > here <.
  • Make sure you follow all the directions on page 1 of the template.
• Phase 2 due April 6 (Thursday) @ 11:59pm to GradeScope.
  • Phase 2 Handout
  • Phase 2 Template
• Project Boilerplate Github Repo
• Phase 3 Due April 19 @ 11:59pm to GradeScope and Google Forms (link forthcoming)
  • Phase 3 Handout
  • Phase 3 Submission Instructions

Homework Assignments

• HW 01 – Introduction and DataGrip Setup
  • EC Due Date: Jan 15 @ 11:59pm to GradeScope
  • Regular Due Date:  Jan 17 @ 11:59pm to GradeScope
• HW 02 – Relational Model and Relational Algebra
  • EC due Date: Jan 29 @ 11:59 to GradeScope
  • Regular Due Date: Jan 31 @ 11:59 to GradeScope
  • HW 02 Solutions
• HW 03 – SQL
  • EC Due Date: Feb 12 @ 11:59pm
  • Regular Due Date: Feb 14 @ 11:59pm
  • HW 03 Solutions
• HW 04 – SQL DDL
  • Extra Extra Credit Due Date: March 3 @ 5:00 pm
  • Regular Extra Credit Due Date: March 12 @ 11:59 pm
  • Regular Due Date: March 14 @ 11:59 pm
• HW 05 – ER Modeling & Relational Mapping
  • Extra Credit Due Date: March 26 @ 11:59
  • Regular Due Date: March 28 @ 11:59
• HW 06 – AppSmith Walkthrough
  • Extra Credit Due Date: April 17 @ 11:59pm
  • Regular Due Date: April 19 @ 11:59pm