代写HW 6编程、代做SQL/Python程序设计

日期：2025-03-13 08:18

HW 6 | Appointment Reservation System

Objectives: To gain experience with database application development, and learn how to use

SQL from within Python via sqlite3.

Note: Changes made after the spec release appear in blue.

Due dates:

● Setup: due March 6th, 2025 at 11:59pm

● Part 1: due March 10th, 2025 at 11:59pm

● Part 2: due March 17th, 2025 at 11:59pm

Contents:

Introduction

Setup (deliverables)

Homework Requirements

Part 1 (deliverables)

Part 2 (deliverables)

Grading

Optional: Extra Credit

Introduction

A common type of application that connects to a database is a reservation system, where users

schedule time slots for a 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 runs in the command-line terminal and connects to a local SQLite database that

you'll create.

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 implementation is broken up into two milestones, 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!*

2.1 Clone the starter code

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

https://github.com/avk5/vaccine-scheduler-python (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, unzip the ZIP file and retrieve the starter code.

2.2 Read through the starter code

We created the important folders and files you will be using to build your application:

● src.main.scheduler/

Scheduler.py:

■ This is the main entry point to the command-line interface application.

Once you compile and run Scheduler.py, you should be able to interact

with the application.

db/:

■ This is a folder holding all of the important components related to your

database.

■ ConnectionManager.py: This is a wrapper class for connecting to the

database. Read more in 2.3.4. You should run this document to connect

to the database so you can successfully interact with the application.

model/:

■ This is a folder holding all the class files for your data model.

■ You should implement all classes for your data model (e.g., patients,

caregivers) in this folder. We have created implementations for Caregiver

and Vaccines, and you need to complete the Patient class (which can

heavily borrow from Caregiver). Feel free to define more classes or

change our implementation if you want!

● src.main.resources/

○ aurora/:

■ Ignore this folder if you're using Python. It's for establishing an Aurora

connection, which is completely optional, and easier in Java.

○ sqlite/:

■ create.sql: SQL create statements for your SQLite tables, we have

included the create table code for our implementation. You should copy,

paste, and run this code (along with all other create table statements).

2.3 Configure your database connection

2.3.1 Installing Anaconda

We recommend using Anaconda for completing this assignment.

● Mac users, follow the instructions in the link to install Anaconda on macOS:

https://docs.anaconda.com/anaconda/install/mac-os/

● Windows users, follow the instructions in the link to install Anaconda on Windows:

https://docs.anaconda.com/anaconda/install/windows/. You can choose to install

Pycharm for Anaconda, but we recommend installing Anaconda without PyCharm as we

will be using the terminal.

Check that you have Anaconda installed by running conda -V. If it outputs a version, you are

good to go!

After installing Anaconda:

1. We first need to create a development environment in conda.

a. macOS users: launch terminal and navigate to the source directory.

b. Windows users: launch “Anaconda Prompt” and navigate to the source directory.

2. Follow the steps here to create an environment. Make sure you remember the name of

your environment.

a. Run: conda create -n [environment name] python=3.10

3. Activate your environment following the steps here.

a. Run: conda activate [environment name]

i. NOTE: You will know you activated the environment if you see the

(Homework_6) label instead of the (base) label

b. To deactivate, Run: conda deactivate

4. Run “conda env list” and ensure that the * is next to the environment you just created

2.3.2 Setting up credentials

The first step is to retrieve the information you need to connect to your SQLite database. You

should create a new SQLite database and note its path and file name.

2.3.3 Setting the environmental variables

Make sure to set this in the environment you created if you’re using virtual environments!

Remember, to go into the environment you created, you’ll need to activate it.

In your terminal or Anaconda Prompt, type the following (Note the change in variable name):

conda env config vars set DBPATH={}

Where “{}” is replaced by file and its path. Do not actually include the {}. For example, a

database path might look like: C:/Users/Me/hw6.db

You will need to deactivate and reactivate your environment after that with the commands

“conda deactivate” and then “conda activate [environment name]”. After running conda

activate, verify you set your credentials properly by running conda env config vars list. You

should see the DBPATH field.

2.3.4 Working with the connection manager

Note: There is nothing you need to do in this step.

In scheduler.db.ConnectionManager.py, we have defined a wrapper class to help you

instantiate the connection to your SQL Server database.

We recommend reading about sqlite3 Connection and Cursor objects for retrieving and

updating information in your database.

Here’s an example of using ConnectionManager.

# instantiating a connection manager class and cursor

cm = ConnectionManager()

conn = cm.create_connection()

cursor = conn.cursor()

# example 1: getting all names and available doses in the vaccine table

get_all_vaccines = "SELECT Name, Doses FROM vaccines"

try:

cursor.execute(get_all_vaccines)

for row in cursor:

print(name:" + str(row[‘Name’]) + ", available_doses: " + str(row[‘Doses’]))

except sqlite3.Error:

print(“Error occurred when getting details from Vaccines”)

# example 2: getting all records where the name matches “Pfizer”

get_pfizer = "SELECT * FROM vaccine WHERE name = ?"

try:

cursor.execute(get_pfizer, (‘fizer’,))

for row in cursor:

print(name:" + str(row[‘Name’]) + ", available_doses: " + str(row[‘Doses’]))

except pymssql.Error:

print(“Error occurred when getting pfizer from Vaccines”)

Helpful tutorial for using the sqlite3 library.

2.4 Verify your setup

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

output. You should be running the program in terminal (macOS) or Anaconda Prompt (Windows)

and in your conda environment.

Note: Command to run the program: “python Scheduler.py” or “python3 Scheduler.py”.

Welcome to the COVID-19 Vaccine Reservation Scheduling Application!

*** Please enter one of the following commands ***

> create_patient <username> <password>

> create_caregiver <username> <password>

> login_patient <username> <password>

> login_caregiver <username> <password>

> search_caregiver_schedule <date>

> reserve <date> <vaccine>

> upload_availability <date>

> cancel <appointment_id>

> add_doses <vaccine> <number>

> show_appointments

> logout

> quit

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 in your SQLite database before executing 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 SQLite (a simple SELECT showing that you

have created a caregiver would work), and upload to gradescope for 5 points.

Deliverables for Setup

Upload to Gradescope 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 in SQLite.

Requirements

Your assignment is to build a vaccine scheduling application 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.

● Reservations: these are appointments that patients will book.

In this assignment, you will need to:

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

(Part 1);

● Implement the missing functionality from the application (Part 1 & 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.

● We will be using an autograder to grade your implementation, so make sure that your

output matches the provided specifications exactly. In order to check this we will allow

for you to submit as many times as you would like before the due date without penalty.

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:

import hashlib

import os

# Generate a random cryptographic salt

salt = os.urandom(16)

# Generate the hash

hash = hashlib.pbkdf2_hmac(

'sha256',

password.encode('utf-8'),

salt,

100000,

dklen=16

)

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 and place it under src.main.resources (design.pdf).

● Write the create table statements for your design, create the tables in SQLite, and save

the code under src.main.resources.sqlite (create.sql).

You will also need to implement the corresponding Python classes of your design. We have

implemented Caregiver.py 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.py: data model for caregivers (implemented for you.)

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

● Patient.py: data model for patients.

○ You will implement this class, it can be mostly based on Caregiver.py

Implementation

Congratulations! You’re now ready to implement your design! For Part 1, 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.

You must print out your results exactly in the format we specify. The “<” and “>” symbols

are placeholders for your values and should not be included in the output.

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.

Deliverables for Part 1

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

● src.main.resources

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

● src.main.resources.sqlite

○ create.sql: the create statements for your SQLite tables.

● src.main.scheduler.model

○ Caregiver.py: the data model for your caregivers.

○ Patient.py: the data model for your users.

○ Vaccine.py: the data model for vaccines.

○ Any other data models you have created.

● src.main.scheduler

○ Scheduler.py: the main runner for your command-line interface.

Part 2

You will implement the rest of your application in Part 2. 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).

For Part 2, you will need to implement the following operations:

● 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

2023-12-03” should be:

Caregivers:

c1

c2

c3

Vaccines:

covid 2

flu 5

hpv 3

● Do not include any other formatting (punctuation, titles, etc). The

output should look exactly as above.

● If there are no available caregivers and/or no available vaccines (no

vaccines have ever been added) your output should be:

Caregivers:

No caregivers available

Vaccines:

No vaccines available

○ 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 2023-12-03 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 2023-12-03 p1

3 flu 2023-12-03 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 2023-12-03 c1

2 hpv 2023-12-03 c2

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

○ If no appointments exist for the user print “No appointments scheduled”

○ 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.

Grading

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

● Setup (5 points)

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

● Part 1 (45 points)

○ Design (15 points)

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

○ Implementation (30 points)

Your working implementation of the Part 1 functions:

create_patient, login_patient

● Part 2 (55 points)

○ Implementation (55 points)

The remainder of the functions for 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 up to 10 extra credit

points.

1. (2 Points) 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 “!”, “@”, “#”, “?”.

i. If the password entered is not strong, print

1. “Create patient failed, please use a strong password (8+ char, at

least one upper and one lower, at least one letter and one number,

and at least one special character, from “!”, “@”, “#”, “?”)”

2. “Create caregiver failed, please use a strong password (8+ char,

at least one upper and one lower, at least one letter and one

number, and at least one special character, from “!”, “@”, “#”, “?”)”

2. (8 Points) 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. Regardless of who canceled it, it should update the vaccine

table, remove it from the appointments, and add it back as an availability.

cancel <appointment_id>

a. Both patients and caregivers can perform this operation.

b. If the appointment id is valid print “Appointment ID <appointment_id> has been

successfully canceled”

c. If the appointment id is invalid print “Appointment ID <appointment_id> does not

exist”

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

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

Common Questions

Q: My IDE is not recognizing my imports, what should I do?

A: You will likely need to set up the source root for your IDE. Check the documentation for your

specific IDE on how to set up source roots.

Q: I’m getting an error: “AttributeError: 'NoneType' object has no attribute 'cursor'”.

A: This is indicating that there is something wrong with your database connection setup. Verify

that you have followed our instructions and read the error messages carefully.

Q: I’m setting a DB error when trying to create a caregiver for my setup.

A: Make sure you have created your tables in SQLite.

🎉🎉🎉 Congrats you made it to the end! 🎉🎉🎉