Welcome To Florida Sign
CSC 3340: Databases
(Fall 2024)

Syllabus

LMS

Teachers


Assignments


Other Pages

Project 4:
Please Join Us at the Table


Assigned: Wed Oct 02 2024
Due: 11:59:00 PM on Fri Nov 08 2024
Team Size: 2
Language: Python 3 and SQLite
Out of: 100 points


In this project, you'll add some tables and practice joined searches on them. As always, you can test your code with this tester script, though it does not contain all the tests that I will use.

Part 0, 10 points: Add a new table, factoids, which will store interesting facts about the lakes of lakeland. It must have these fields:

  • factoid_id: a unique integer for each factoid. Hint: if you make this the Primary Key, it will automatically assign values if you leave the field NULL when inserting the record.
  • lake: the name of the lake the factoid is about.
  • factoid: the actual factoid itself.
Add appropriate constraints to the table, which I will certainly be testing.

Part 1, 10 points: Create another table, factoid_votes, for feedback on the factoids. We'll pretend that some users have been up- or downvoting them, and each record will be one of those votes. It needs these fields:

  • factoid_id: an integer as in factoids
  • value: an integer that is either 1 (upvote) or -1 (downvote)
  • username: the name of the user that placed this vote.
Make all appropriate constraints as you can think of.

Part 2, 20 points: Write a Python function, cast_vote, that modifies the factoid_votes table. cast_vote(db_connection, factoid_id, value, username) should add/change the vote for that user on the specified factoid.

Part 3, 10 points: Now let's write some search queries that use the JOIN operator. First write a function, get_factoids_and_votes_query() that returns a record for each vote of each factoid, listing both the factoid and the vote and the user who voted for it (as well as any other fields you'd like to include).

Part 4, 10 points: Write another join query, get_gator_sightings_and_lake_area_query(), which returns a record of each recorded gator sighting as well as the surface area of the lake. Each record in the result should look like: (lake name, number of gators sighted, date of sighting, lake area)

Part 5, 20 points: Write another join query, get_worst_gators_query(), which returns a record for each lake and the highest number of gators spotted in that lake. (If there haven't been any sightings there, the highest number of gators should be 0.)

Part 6, 10 points: Write another join query, get_worst_gators_per_acre_query(), which returns a record for each lake and the highest number of gators spotted per acre. (If there haven't been any sightings there, the highest number of gators should still be 0.)

Part 7, 10 points: Write another join query, get_total_factoid_score_query(), which returns a record for each factoid (the text) and the total score from votes.

Submitting your Project:

Choose a team name that no other team will choose. Then make sure all your code is in a file labelled with your team name, followed by _project4.py all in snake_case. (For example, my file name would be: kburke_project4.py.) It's very important to name your file correctly in order for me to grade it. Make sure your code runs, then upload it to the project on Canvas. If there is already a file up on Canvas, I recommend deleting that before uploading the new version. If you submit well before the deadline, send me a message on Slack. If there's adequate time, I'll check it and post feedback on Canvas. If you submit after the deadline, please message me so I know I have something late to grade.