CS377: Database Design - Database Tables (100 Points)

Assignment Goals

The goals of this assignment are:
  1. To design referential database tables, and to work with them in the SQL language using Sqlite

Background Reading and References

Please refer to the following readings and examples offering templates to help get you started:

The Assignment

In this lab, you will diagram a table structure using primary and foreign keys, and use Sqlite to construct, populate, and query that database.

Begin by sketching an ER diagram of your proposed database tables. You should have at least three tables, each with primary and foreign key dependencies. You should provide at least one aggregate query (i.e., an average), and mechanisms to insert, update, delete, and query data across multiple tables. When a deletion is made to a dependent table (which should occur at least once), an immediate deletion/update should be made on the parent table to ensure referential integrity.

The application domain is your choice, so feel free to be creative here!

Exporting your Project for Submission

When you’re done, write a README for your project, and save all your files, before exporting your project to ZIP. In your README, answer any bolded questions presented on this page.

Submission

In your submission, please include answers to any questions asked on the assignment page in your README file. If you wrote code as part of this assignment, please describe your design, approach, and implementation in your README file as well. Finally, include answers to the following questions:
  • If collaboration with a buddy was permitted, did you work with a buddy on this assignment? If so, who? If not, do you certify that this submission represents your own original work?
  • Please identify any and all portions of your submission that were not originally written by you (for example, code originally written by your buddy, or anything taken or adapted from a non-classroom resource). It is always OK to use your textbook and instructor notes; however, you are certifying that any portions not designated as coming from an outside person or source are your own original work.
  • Approximately how many hours it took you to finish this assignment (I will not judge you for this at all...I am simply using it to gauge if the assignments are too easy or hard)?
  • Your overall impression of the assignment. Did you love it, hate it, or were you neutral? One word answers are fine, but if you have any suggestions for the future let me know.
  • Any other concerns that you have. For instance, if you have a bug that you were unable to solve but you made progress, write that here. The more you articulate the problem the more partial credit you will receive (it is fine to leave this blank).

Assignment Rubric

Description Pre-Emerging (< 50%) Beginning (50%) Progressing (85%) Proficient (100%)
Algorithm Implementation (60%) The algorithm fails on the test inputs due to major issues, or the program fails to compile and/or run The algorithm fails on the test inputs due to one or more minor issues The algorithm is implemented to solve the problem correctly according to given test inputs, but would fail if executed in a general case due to a minor issue or omission in the algorithm design or implementation A reasonable algorithm is implemented to solve the problem which correctly solves the problem according to the given test inputs, and would be reasonably expected to solve the problem in the general case
Database Modeling and Design (30%) The database design is not conducive to implementing the system proposed or intended The database design is not appropriate or well justified to the application pursued, but a functional solution is provided The database design is slightly denormalized, but could be improved easily, or the document structure of a NoSQL approach is slightly disorganized in ways that can be easily improved. The database design is appropriately chosen and justified, with normalization and/or transactional models utilized to create a well-organized database system.
Writeup and Submission (10%) An incomplete submission is provided The program is submitted, but not according to the directions in one or more ways (for example, because it is lacking a readme writeup or missing answers to written questions) The program is submitted according to the directions with a minor omission or correction needed, including a readme writeup describing the solution and answering nearly all questions posed in the instructions The program is submitted according to the directions, including a readme writeup describing the solution and answering all questions posed in the instructions

Please refer to the Style Guide for code quality examples and guidelines.