Lab: Database Programming with SQLite

In this lab, you will write a command-line program that queries and updates a SQLite database.

Instructions

You are welcome to work on this assignment either individually or with a partner.

Task 1: SQLite Python Tutorial

Skim through this SQLite Python tutorial. You may find it helpful to refer to the tutorial as you write your programs.

Task 2: Download Starter Files

Download:

The university.sqlite SQLite database
The university.py starter code

Task 3: Sketch the Database Schema

It will be easier to write your program if you understand the database schema. Draw a relational schema for the database, depicting primary keys and foreign keys. There’s no need to include sample data.

Hint: You may find it helpful to view the database schema using a tool like DB Browser for SQLite.

Task 4: Complete university.py

university.py is a simple command-line program that interacts with the SQLite database. The starter file implements argument parsing and connects to the database. You must implement the remaining functionality.

Use --help to see the program’s command-line interface:

> python3 university.py --help
usage: university.py [-h] [--add-student ID NAME]
                     [--enroll-student STUDENT COURSE]
                     [--declare-major STUDENT DEPARTMENT] [--show-students]
                     [--show-enrollments]
                     database

Interact with the university database

positional arguments:
  database              SQLite database file

options:
  -h, --help            show this help message and exit
  --add-student ID NAME
                        Add a student
  --enroll-student STUDENT COURSE
                        Enroll a student in a course
  --declare-major STUDENT DEPARTMENT
                        Declare a student's major
  --show-students       List all students IDs, names, and their majors
  --show-enrollments    List all courses and the number of students enrolled

View student and enrollment information:

> python3 university.py university.sqlite --show-students
ID|Name|Major
1|Adrian Smith|CSCI
2|Albert Holmes|Undeclared
3|Allison Jenkins|MATH
4|Julie Huffman|CSCI
5|Joyce Anderson|Undeclared
6|Joshua Wright|Undeclared

> python3 university.py university.sqlite --show-enrollments
Number|Enrollment
CSCI 120|2
CSCI 127|0
CSCI 220|0
MATH 120|0
MATH 121|1

Add records:

> python3 university.py university.sqlite --add-student 7 "Mary Jackson"

> python3 university.py university.sqlite --enroll-student 7 "MATH 121"

> python3 university.py university.sqlite --declare-major 7 MATH

Confirm student and enrollment information was updated:

> python3 university.py university.sqlite --show-students
...
7|Mary Jackson|MATH

> python3 university.py university.sqlite --show-enrollments
...
MATH 121|2

Invalid updates should be rejected with a readable error message:

> python3 university.py university.sqlite --add-student 1 "A Smith"
UNIQUE constraint failed: student.id

Hint: Start by implementing --show-students and --show-enrollments. Use outer joins to show students who haven’t declared a major and courses that don’t have students enrolled.

Note: Argument parsing is implemented with Python’s argparse module.

Submit

Upload your completed university.py to Gradescope.

Points will be deducted if your code is vulnerable to SQL injection. This lab will be graded as part of your assignments grade.