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 ex- actly 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

See the back pages of this handout for some info on how to get started with these in a few common languages.
You’ll have a chance to askquestions about it in class on Wednesday to clear up any last-minute issues, but you really don’t want to wait until then to start it. When you’re readyto hand it in, use the handinscript as described at the end of this document.

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

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.

void query1(sqlite3 * db) {
    // Prepare SQL statement ob ject representing SELECT query
    sqlite3_stmt *stmt;
    int code;
    sqlite3_prepare_v2(db, "select * from student;", -1, &stmt, NULL);
    cout << "print all records\n";
    // Execute the query statement
    // While there are rows left in the result,
    // access and print the columns in the current row
    while ((code = sqlite3_step(stmt)) == SQLITE_ROW) { /* 2 */
       printf("%5d\t%s\n", sqlite3_column_int(stmt, 0),
                sqlite3_column_text(stmt, 1)); /* 3 */
    // Clean up the statement object

By purchasing this solution you'll be able to access the following files:
Solution.cpp and textdb.

for this solution

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

Find A Tutor

View available C-Family Programming 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