HW 6 | Appointment Reservation System

Due dates:

●    Part 1: due Friday, May 17th at 11:00pm (No Late Days!)

●    Part 2: due Friday, May 24th at 11:00pm

Contents:

Introduction

Setup

Homework Requirements

Part 1 (deliverables)

Part 2 (deliverables)

Grading

Introduction

A common type of application that connects to a database is a reservation system, where users schedule time slots for some centralized resource. In this assignment you will program part of an appointment scheduler for vaccinations, where the users are patients and caregivers keeping track of vaccine stock and appointments.

This application will run on the command line terminal, and connect to a database server you create with your Microsoft Azure account.

You will have two main tasks:

●   Complete the design of the database schema with an E/R diagram and create table statements

●    Implement the code that stores Patient information, and lets users interactively schedule their vaccine appointments. We have implemented the code that caregivers use to

manage the appointment slots and inventory, which will be a useful reference for you.

The project is broken up into two parts, Part 1 and Part 2, described below.

Be Careful: This homework requires writing a non-trivial amount of code; our solution is about 600 lines, including the starter code. It will take SIGNIFICANTLY more time than your previous assignments.

Setup

*Make sure to finish this part of the assignment and upload your setup verification of step 2.4 for the first 5 points of Part 1!*

We have a setup video for this part to help you with the steps! Here is the setup video for Java. (Log into Zoom to access.)

2.1 Clone the starter code

1.   Navigate to the Github repository hosting the starter code:

https://github.com/eden-ski/vaccine-scheduler-java (As you continue to work on the assignment, DO NOT manipulate the internal project structure of the assignment. This will save you some headache when it comes time to submit your code)

2.   Click on the green button “Code” and select “ Download ZIP” from the drop-down menu.

3.   Once your download completes, decompress the ZIP file and retrieve the starter code.

2.3.1 Installing dependencies

Our application relies on a few dependencies and external packages. You’ll need to install those dependencies to complete this assignment.

First, we need the JDBC drivers to allow our Java application to connect to an Azure database. The link can be found here. Download the zip for JDBC 9.4 drivers for SQL server.

The zip will contain various files as listed below. 

You should use the .jar that has the same JRE as your version of java. To find your java version, open a terminal in IntelliJ and type java --version . This should return what java version you have. After you determine what version of JDBC you’ll be using, copy the jar file for the version you will be using and place it into the root directory of your cloned directory.

Note: JDBC is only compatible with Java 8, 11, or 16, so you must be using one of those versions to proceed. If you need to download another Java version, follow this guide to change the Java version for your IntelliJ project.

2.3.2 Setting up IntelliJ

Note: We only officially support IntelliJ for this assignment. You may use another IDE, but please make sure it works before turning in your assignment!

1.   Now you will open your cloned directory using your IntelliJ so that your vaccine-scheduler-java is the root directory

2.  You will also need to mark vaccine-scheduler-java-main as the source root by right-clicking on the folder in IntelliJ and selecting “ Mark Directory As” -> “Sources Root” . 

3.   Now Add Configuration

a.    Select the Add Configuration option.

 

b.   Select “Add new Configuration” .

c.   Select “Application” in the list of options, and the following screen will appear.

Your options might look a little bit different based on your IntelliJ version, but the majority should be the same.

d.   Make sure you have a name for this configuration and the java is set to the correct version, and in the main class box write scheduler.Scheduler (this lets IntelliJ know where main is located).

 

If scheduler.Scheduler is highlighted red and can’t be located, 1) exit out of this screen and go to File -> Invalidate Caches / Restart and repeat the following steps, and 2) check that you have marked the source root mentioned in step 2.

e.  You can also include your environmental variables here for your server login in the format below with your corresponding Azure information (Refer to 2.3.3 for how to retrieve credentials, if you choose to include your environmental variables here you can skip 2.3.3a and/or 2.3.3b).

4.   Next, we need to ensure that our JDBC drivers are loaded into our environment. To do this, select File -> Project Structure to open this page and go to dependencies.

Select Modules under project settings, make sure your current configuration is selected, and then press the + and select Jars or Directories. This should prompt you to select your JDBC drivers, and once you select them they should be added to your environment. Then make sure to apply your changes.

2.3.3 Setting up credentials

The first step is to retrieve the information to connect to your Microsoft Azure Database.

●   The server name can be found in your Azure portal. Only use the part before “ .database.windows.net”

○    In the example below, it would be “data514server” .

○   YOU NEED TO CHANGE THIS ACCORDING TO YOUR DATABASE!

●   The database name can be found in your Azure portal

○    In the example below, it would be “data514db”

○   YOU NEED TO CHANGE THIS ACCORDING TO YOUR DATABASE!

●   The User ID would be of the format <user id>@<server name>

○    For example, it could be exampleUser@data514server where “exampleUser” is the login ID that you used to log in to the query editor on Azure and

“data514server” is the server name.

●    Password is what you use to log in to your query editor on the Azure portal.

 

Once you’ve retrieved the credentials needed, you can set up your environment variables.

Ignore the next sections for setting up environment variables if you already have done the setup in step 7 of 2.3.2 Setting up Intellij.

2.3.3a Setting up environment variables - MAC USERS ONLY

Open your terminal and type:

Where “{}” is replaced by the respective information you retrieved from step 2.3.3.

Do note that those environment variables are only added for the current session. If you’d like to add those environment variables permanently, copy the exports and paste them into “ .bash_profile” .

2.3.3b Setting up environment variables - WINDOWS USERS ONLY

Open a command prompt and type:

Where each “x” is replaced by the respective information you retrieved from step 2.3.3.

These environment variables will be saved permanently, and a “SUCCESS: Specified value was saved.” should be returned for each variable saved. To see these changes, close the command  prompt and open a new command prompt, and type:

This will return a list of your environment variables, and you should be able to see your new variables within this list.

2.3.4 Working with the connection manager

In scheduler.db.ConnectionManager.java, we have defined a wrapper class to help you instantiate the connection to your SQL Server database. We recommend reading about Java PreparedStatements for retrieving and updating information in your database. Here’s an example of using ConnectionManager.

Helpful resources on writing Java prepared statements:

https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html

2.4 Verify your setup

Once you’re done with everything, try to run the program and you should see the following output:

If you can see the list of options above, congratulations! You have verified your local setup.

Next, to verify that you have set up your database connection correctly, try to create a caregiver with the command “create_caregiver <username> <password>” . Make sure you have created    the tables on Azure before testing this command.

To verify you have done the setup, take a screenshot or phone picture of this screen on your

computer, along with your created caregiver on Azure (a simple SELECT showing that you have created a caregiver would work). You'll upload this screenshot to Gradescope in your Part 1 submission.

Requirements

Your assignment is to build a vaccine scheduling application (with a database hosted on

Microsoft Azure) that can be deployed by hospitals or clinics and supports interaction with users through the terminal/command-line interface. In the real world it is unlikely that users would be   using the command line terminal instead of a GUI, but all of the application logic would remain the same. For simplicity of programming, we use the command line terminal as our user interface for this assignment.

We need the following entity sets in our database schema design (hint: you should probably be defining your class files based on this!):

●    Patients: these are customers that want to receive the vaccine.

●   Caregivers: these are employees of the health organization administering the vaccines.

●   Vaccines: these are vaccine doses in the health organization’s inventory of medical supplies that are on hand and ready to be given to the patients.

In this assignment, you will need to:

●   Complete the design of the database schema, with an E/R diagram and create table statements (Part 1);

●    Implement the missing functionality from the application (Part 2)

A few things to note:

●   You should handle invalid inputs gracefully. For example, if the user types a command that doesn’t exist, it is bad to immediately terminate the program. A better design would be to give the user some feedback and allow them to re-type the command. Points will be taken off if the program terminates immediately after receiving invalid input. While you don’t have to consider all possible inputs, error handling for common errors (e.g., missing information, wrong spelling) should be considered.

●   After executing a command, you should re-route the program to display the list of commands again. For example:

○    If a patient ‘reserves’ their vaccine for a date, you should update your database to reflect this information and route the patient back to the menu again.

1.3 How to handle passwords

You should never directly store any password in the database. Instead, we'll be using a technique called salting and hashing. In cryptography, salting hashes refer to adding random data to the input of a hash function to guarantee a unique output. We will store the salted password hash and the salt itself to avoid storing passwords in plain text. Use the following code snippet as a template for computing the hash given a password string:

Part 1

Design

You will first need to work on the design of your database application. Before you begin,

please carefully read the assignment specification (including Part 2) and the starter code, and think about what tables would be required to support the required operations. Once you have an idea of how you want to design your database schema:

●    Draw the ER diagram of your design. Save the file as design.pdf in the src.main.resources folder.

●   Write the create table statements for your design, create the tables on Azure, and save   your create table statements in a file called create.sql in the src.main.resources folder.

Deliverables for Part 1

You are free to define any additional files, but Part 1 requires at least the following:

●   A screenshot or phone picture of the welcome prompt on your computer and the

successful “create_caregiver <username> <password>” command, along with your created caregiver on Azure.

●   src.main.resources

○   design.pdf: the design of your database schema.

○   create.sql: the create statement for your tables.

Part 2

Design

You will need to implement the corresponding Java classes of your design. We have implemented Caregiver.java for you, but feel free to change any of the details. You will need the following classes, and you may implement more data models if you feel the necessity:

●   Caregiver.java: data model for caregivers (implemented for you)

●   Vaccine.java: data model for vaccines (implemented for you)

●    Patient.java: data model for patients (you will implement this class, it can be mostly based on Caregiver.java)

Implementation

Congratulations! You’re now ready to implement your design! For Part 2, you will need to implement the following functionalities. We have implemented account creation for caregivers as an example for you, please read through our implementation before you begin.

Note: For most of the operations mentioned below, your program will need to do some checks to ensure that the appointment can be reserved (e.g., whether the vaccine still has available doses). Again, you do not have to cover all of the unexpected situations, but we do require you to have a reasonable amount of checks (especially the easy ones).

You will need to implement the following operations:

●   create_patient <username> <password>

○    Print "Created user <username>" if create was successful.

■    Example:  Created user p1

○    If the username is already taken, print “ Username taken, try again” .

○    For all other errors, print “Create patient failed” .

○   Your output must match exactly. Do not include the “< >” in your output.

●    login_patient <username> <password>

○     Print "Logged in as <username>" if login was successful.

■    Example:  Logged in as p1

○    If a user is already logged in in the current session, you need to log out first   before logging in again. In this case, print “ User already logged in, try again”

○    For all other errors, print "Login patient failed"

○   Your output must match exactly. Do not include the “< >” in your output.

●   search_caregiver_schedule <date>

○    Both patients and caregivers can perform this operation.

○   Output the username for the caregivers that are available for the date ordered alphabetically by the username of the caregiver.

○   Then, output the vaccine name and number of available doses for that vaccine separated by a space.

■    For example if we had 3 available caregivers (c1, c2, c3) and 3 available vaccines (covid, flu, hpv), the output of “search_caregiver_schedule

12-03-2023” should be:

c1

c2

c3

covid 2

flu 5

hpv 3

●    Do not include any other formatting (punctuation, titles, etc). The output should look exactly as above.

○    If no user is logged in, print “ Please login first”

○    For all other errors, print "Please try again"

●    reserve <date> <vaccine>

○    Patients perform this operation to reserve an appointment.

○   Caregivers can only see a maximum of one patient per day, meaning that if the reservation went through, the caregiver is no longer available for that date.

○    If there are available caregivers, choose the caregiver by alphabetical order and print “Appointment ID {appointment_id}, Caregiver username {username}” .

■    For example, the output of “reserve 12-03-2023 Covid” should be: Appointment ID 1, Caregiver username c1

○    If no caregiver is available, print “ No caregiver is available” and return.

○    If not enough vaccine doses are available, print "Not enough available doses" and return.

○    If no user is logged in, print “ Please login first” and return.

○    If the current user logged in is not a patient, print “ Please login as a patient” and return.

○    For all other errors, print "Please try again".

●   show_appointments

○   Output the scheduled appointments for the current user (both patients and caregivers).

○    For caregivers, you should print the appointment ID, vaccine name, date, and patient name. Order by the appointment ID. Separate each attribute with a space.

■    For example, if caregiver c1 is logged in, the output of “show_appointments” should look like:

1 covid 12-03-2023 p1

3 flu 12-05-2023 p3

○    For patients, you should print the appointment ID, vaccine name, date, and

caregiver name. Order by the appointment ID. Separate each attribute with a space.

■    For example, if patient p1 is logged in, the output of

“show_appointments” should look like:

1 covid 12-03-2023 c1

2 hpv 12-04-2023 c2

○    If no user is logged in, print “ Please login first”

○    For all other errors, print "Please try again"

●   Logout

○    If not logged in, you should print “ Please login first” . Otherwise, print “Successfully logged out” .

○    For all other errors, print "Please try again".

Deliverables for Part 2

When you’re finished, please turn in the entire repository by compressing the project folder into a zip file, then uploading it on Gradescope. If you turn in anything other than a zip file, you  risk getting a 0.

Grading

Your grade for this homework will be worth 100 points, divided as:

●    Part 1 (20 points)

○    Setup (5 points)

■    Finish setup through step 2.4 and upload your verification to Gradescope

○    Design (15 points)

■   Your database design including the files design.pdf and create.sql

●    Part 2 (80 points)

○    Implementation (80 points)

■   create_patient, login_patient, search_caregiver_schedule, reserve, show_appointments, logout

Additionally, you may receive up to 10 points of extra credit for implementing one of the options below.

Optional Extra credit

You can do either one of the following extra tasks by the final due date for 10 extra credit points.

1.  Add guidelines for strong passwords. In general, it is advisable that all passwords used to access any system should be strong. Add the following check to only allow strong

passwords:

a.  At least 8 characters.

b.  A mixture of both uppercase and lowercase letters.

c.   A mixture of letters and numbers.

d.   Inclusion of at least one special character, from “ !” , “@” , “#” , “?” .

2.   Both caregivers and patients should be able to cancel an existing appointment.

Implement the cancel operation for both caregivers and patients. Hint: both the patient’s schedule and the caregiver’s schedule should reflect the change when an appointment  is canceled.