Registration season is upon us, and it serves as the inspiration for the fourth project in this course: maintaining a searchable and updatable database of course offerings and registrations. The universe here is somewhat simpli- fied, but broadly similar to what the registrar at Longwood (and any other college) needs to maintain.


In the course of this project, the successful student will:

• use a published API to access a library,
• design a relational data schema with multiple tables, and
• write complex queries in a standard database query language (SQL).

The data

The central data for the system will be the courses themselves. As at Long- wood, the courses will have titles as well as course numbers and sections—so, CMSC 201-1 “Computer organization” would be a perfectly valid course. As a bit of a simplification, we’ll say that all courses are either Monday- Wednesday-Friday or Tuesday-Thursday, and each course meets during exactly one numbered period (1–8 on MWF and 1–5 on TT). Courses have a maximum number of enrolled students (e.g. 20 or 30), and when that many students are in the course, the course is full.
Each course is taught by only one professor. You can assume that a professor’s full name is unique; but each professor has a “display name” as well, which is often their last name (if that is unique in the system, e.g. “Blaheta”) but may have a disambiguating initial or something (e.g. “W. Smith”). The display name will be what you want to show in most listings.
Students will, in general, sign up for more than one course. All their courses will meet at distinct times.

The requests

Students need to be able to register for classes. In addition, they need to be able to get useful listings of courses, as well as (after registration) a useful list of the courses they actually signed up for.
Professors need to be able to get a listing of courses they’re teaching, and for individual courses, they need to be able to get a listing of all the students in the course.
The registrar herself need to enter and modify data, and create summary reports such as student transcripts.
See the “rubric” section at the end of this handout to get more specific details on some of these requests.

Prep work

Your initial work on this project involves getting your programming language of choice to successfully talk to a database file through the SQLite API. This is in some ways more complicated than previous prep work (although more amenable to following tutorials, and see the back pages of this handout for additional help), but it’s absolutely prerequisite to getting anything else in the project done, so I’ve front-loaded it.
For the prep work, you should have:

• A .db file in SQLite format that contains sample data: at least one table, with at least two columns and at least three rows,
• A program that, when compiled and run with the .db file as its command-line argument, accesses that file using the SQLite API for that language, runs “SELECT * FROM” the table in the file, and prints the results; and then reads a single word from standard input and runs a “SELECT * FROM ... WHERE ...” query based on that word, and prints the results.
• A readme file that says how to compile and run that program, even if this is just repeating back instructions I’ve given you at some point.

The database file can be built by hand through running the sqlite3 inter- active command-line program. The program will have the following broad structure:

Import/include necessary libraries or headers Open connection to given database file
Prepare SQL statement object representing SELECT query
Bind parameter in the prepared statement to the value read from input Execute the query statement
While there are rows left in the result,
access and print the columns in the current row Clean up the statement object
Close the connection

Design work

Once you’ve got the shell of the program running (or perhaps while you are working on that, but the design work depends on course content we won’t cover until next week), you can start thinking about the query and schema design.

1. Devise a short list of courses and other data that can serve to fuel test cases. Each type of data should in general have just two or three instances, to facilitate easy typing and editing and quick queries.
2. Reflect on your data and write out your database schema: for each table, be sure to name each column and give it an SQL-appropriate data type. As you edit your schema, you may feel the need to update your examples from the previous part, and vice versa—this is fine! But when you’re done, the schema should be consistent with the sample data.
3. Draw an entity-relationship diagram that corresponds to your data and schema. Again, as you work on this you may want to revise the other parts; just make sure they are all consistent with each other when you’re done.
4. Reread the section “The requests” from the beginning of the handout and look through the rubric at the end, and write out an interaction scenario between a student and your program—from the time they sit down, what does the program ask them? What do they ask the program? How should the program respond to their request? What changes occur as a result? Use your data example as a starting point and be as specific as possible.

Final version

A full-credit final version will be a complete, non-buggy, working implementation of the database system described here, TOGETHER WITH convincing proof that it is correct. When run, it should be provided with one command line argument—the name of the database file containing the registrar data.
Your front end, written in the high-level language of your choice (e.g. C++, Java, etc), will use a standard database API to interact with a database in SQLite format, and print out results. Some statements will be commands that cause an update to the system; others will be SELECT queries, whose output may be in table form but should still reflect the user’s understanding of the data.
That means:

• DO use database queries to do things like provide the user with a valid list of options to select from;
• DO NOT just print out a bunch of tables and assume the user can “just figure it out”;
• DO NOT just make a bunch of SELECT * queries and read them into maps and vectors and combine and aggregate information in the front end; and
• ESPECIALLY DO NOT overly subject your users to weird artifacts of how things are stored internally, such as cryptic ID numbers or the fact that some data is spread over multiple tables.

It’s ok if the output isn’t especially formatted, but the response to a query should be a single table with all the requested info and not a lot of cruft. You should in general be displaying the whole result of the query to the user—if there is too much cruft, trim it out by refining the SQL query itself.
To make this project doable for students who haven’t had experience with writing GUIs, we’ll do the interaction via a text-based menu, giving it a bit of a 1980s feel. In a “real” version of this, there would be a web or GUI front end that would handle the user interaction and display results—but that’s outside the scope of this course.
Your system should be able to handle all the requests indicated in the open- ing section of the handout, as well as requests to do basic maintenance of the system (e.g. adding and removing courses and sections from the system). It doesn’t need to worry about authentication or security, or preventing one student from seeing another’s listings, or anything like that.

Implementation notes

Spend time early in the project thinking about what data you’ll need to represent, even for the parts that you don’t plan to implement until later. It’s not impossible to change your data schemas later, but it’s easiest if you get the structure of the data (mostly) right first.
Work on the queries and updates one at a time; and when you’re working on one, start by constructing the SQL by hand to make sure you can find an SQL statement that works, before trying to make the front end program build and execute it.
Though SQL is generally case-insensitive, use the all-caps forms of at least the keywords when you’re generating the SQL statements. This sometimes makes it easier to tell the difference between the part of your code that is in the general-purpose language and the part that’s in SQL.

Getting started with SQLite libraries

In C++:

The sqlite3 library and its header sqlite3.h is already installed on torvalds and the lab machines. For instance, to first open the database, you might write

int result; sqlite3* db;
result = sqlite3_open_v2(argv[1], &db, SQLITE_OPEN_READWRITE, nullptr);

The actual return value of each function is a result code; in this case if it is not SQLITE OK it means there was an error opening the file, and you should exit the program gracefully. You should always assign the result of the function call to a variable, and you should always check the value of that variable to know if the command succeeded.
Use the v2 versions of the SQLite functions where they are available.
Look over the documentation and compare to the pseudocode on p. 3 of this handout; try to see which functions correspond to which lines of pseudocode (for instance, the line above corresponds to “open connection to given database file”). Write code to call them, getting each to compile be- fore tackling the next one.
The sample code that you’ll find online that uses sqlite3 exec is really really not what you want to be looking at—among other things, it doesn’t let you prepare statements and bind values into the parameters.
Note that the type of several parameters (notably in sqlite3 bind text) is not a true C++ string, but a const char *, so if you’re using strings you’ll need to use .c str() to get them into the right type for use in binding.
Note further that the return type of sqlite3 column text is const unsigned char *, so you really can’t assign it to a string: first, it could return nullptr (if the value in that column of the current row is NULL), and converting that value to a string throws a runtime exception. Second, the builtin string type is built to contain char, not unsigned char. The good news is, printf and cout are both happy to work with C-strings of type const unsigned char *. So, since you mostly shouldn’t be doing a lot of processing of these result strings anyway, you can mostly just use variables of the type const unsigned char * to hold them until you print them out. (If you feel you need to go back and forth between string and this other type, see me and I can help you make that work.)
When you compile your .cpp, you will need to explicitly link it against the sqlite3 library:

compile myfile.cpp -lsqlite3

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

#include <cstdlib>
#include <sqlite3.h> // Import include necessary libraries or headers
#include <iostream>
#include <vector>
#include <string>
#include <string.h>

using namespace std;

// Basic queries:

void getAllTerms(sqlite3 * db, vector<string> &terms);

void getTermAndYear(sqlite3 * db, string & term, int & year);

void printCourses(sqlite3_stmt *stmt);
* Executable program can list all the courses on offer (this term)
* @param db
void showCourseOffer(sqlite3 * db, string term, int year);

string getDepartment(sqlite3 * db);

* Executable program can list all the courses from a particular
* department (this term)
* @param db
* @param term
* @param year
void showCourseFromDepartment(sqlite3 * db, string term, int year);

int getProfessor(sqlite3 * db);

* Executable program can list all the c ourse s taught by a particular professor (this term)
* @param db
* @param term
* @param year
void showCourseOfProfessor(sqlite3 * db, string term, int year);

// Basic up dates:

void addStudent(sqlite3 * db);

void addProfessor(sqlite3 * db);

void addCourse(sqlite3 * db);

* Executable can interact with the user to add items (professors,
* students, courses) to the database
void addItem(sqlite3 * db);

unsigned int getModifiedCourse(sqlite3 * db,
       vector<string> & couser_num, vector<string> & dept);
void modifyCourse(sqlite3 * db);

int getStudentID(sqlite3 * db);
void modifyStudent(sqlite3 * db);

void modifyProfessor(sqlite3 * db);

int getOfferingCRN(sqlite3 * db);
void modifyOffering(sqlite3 * db);

unsigned int getModifiedTaking(sqlite3 * db,
       vector<int> & student_id, vector<int> & crns);
void modifyTaking(sqlite3 * db);

* Executable can interact with the user to mo dify items that
* are in the database
* @param db
void modifyItem(sqlite3 * db);

// Complex queries and up dates:

*Implemented at least one query involving joining data from
two tables, without duplicate columns or spurious data
* @param db
void joinTables(sqlite3 * db);

void printCoursesInRange(sqlite3_stmt *stmt, string day,
       int low_period, int hight_period);
* List all the courses (this term) that meet during a range of
* times (e.g. MWF during 1st through 4th p erio d)
* @param db
void listCoursesInRange(sqlite3 * db, string term, int year);

* Register a student for class(es) (for this term)
* @param db
void registerAClass(sqlite3 * db, string term, int year);

* Show a student the course schedule that they signed up for (this term)
* @param db
void showStudentSchedule(sqlite3 * db, string term, int year);

* Give a professor course lists showing all the students enrolled
* in all their courses (for this term)
* @param db
* @param term
* @param year
void showStudentTakingALLProfessorCourse(sqlite3 * db, string term, int year);

* List all the courses (this term) that are not yet full
* @param db
* @param term
* @param year
void listNotYetFullCourse(sqlite3 * db, string term, int year);

* Give a student a transcript that shows all the courses they’ve
taken at this scho ol, over all terms
* @param db
void showAllCourseOfStudent(sqlite3 * db);

* Give a stude nt a list of all the courses (this term) that don’t
* conflict with the courses they are already signed up for

struct Course {
    string title;
    int secnum;
    string day;
    int period;
    int course_num;
    string dept;

By purchasing this solution you'll be able to access the following files:

50% discount

$282.00 $141.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Data Structures and Algorithms Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Upload a file
Continue without uploading

We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats