In this assignment, you will design schemas for simple databases, each with 2-4 tables. Your design must enforce all the constraints in the problem statement by using properly designed primary keys and foreign key constraints.
For this assignment you cannot use SQL magic. You can use datajoint or pymysql.
import datajoint as dj
import pymysql
Problem 1: Tennis Club¶
Design¶
Design a database schema for a reserving courts at a tennis club.
The tennis club has members, who are identified by their cell-phone numbers
The tennis club has four courts: A, B, C, and D.
A member can reserve a court for a specific day for one of three slots: morning, afternoon, or evening. This means that no two members can reserve the same court for the same slot.
(extra credit) Add a constraint that no member can reserve more than one court for the same slot on the same day.
Populate¶
Populate each of the tables with several entries. You can use faker for this.
Queries¶
Write the following queries
Show all reservations made by a specific member (pick one) over the past month
Show the names of all members who have reserved Court A over the past week
Show the complete schedule for Court A for this week
Problem 2: Goal Tracking¶
Design¶
Design the database schema for a simple goal tracking app. This is a single user app, so there is not need to track users.
The user can create goal categories (e.g. “Fun”, “Work”, “Study”, “Reading”, “Exercise”)
The user can create a goal with a name, category, description, a start date, target completion date, and a challenge level 1-5.
The user can create goal achievement, one for each goal, with the achievement data, and the status “Complete”, “Partial”, or “Failed”.
Populate¶
Populate each of the tables with several entries.
Queries¶
Show all the achievements over the past month
Show all the goal categories along with the average rate of goal completion