COP4710 Database Systems (Spring 2024)

Building a Database-driven Web-based Information System

Topic

There is a semester-long course project of building a database-driven, Web-based application for a real-world domain of your interest. The project will be done in several stages: First, you will identify an application of your interest that requires relational database systems as backends. Then, you will design your underlying database and key functionalities you may provide with the database. You will create an actual database using a relational DB system, such as MySQL, PostgreSQL, sqlite3, and populate the database. After that, you will write SQL queries and modifications on the database to support your application logic. Finaly, you will build a Web-based interface to support readily interaction with your real-world application. You will demo your system at the end of the semester.

This programming project takes up 30 points (out of 100) of your total score. You have another option to implement a simplified project without Web interfaces, with 5 points deduction. Namely, your highest score will be 25 if you choose this simplified option without Web programming. The programming project is a multi-stage project and for each stage, you have delivables to submit. You cannot proceed to the next step until you get approval (with comments and suggestions) from the TA.

Stage 1: Group formation (0%)
Stage 2: A detailed description of your real-world application and the Entity-Relationship diagram (15%)
Stage 3: The relational design and development plan (15%)
Stage 4: Demo and final report (70%)

Stage 1: Group formation

You must form a group of at most three students for the project (Sorry, more than three is not allowed). Please provide student names, fsuIDs, and emails for each group member. If a group contains more than one student, you are tied up throughout the semester. If one happens to leave/drop from the class, the others should be on your own and shoulder the workload. So choose your groupmate carefully!

Note: If you have difficulties forming a team, here are the solutions: 1) go to class and talk to people in the class; 2) ask *TA* for help.

Stage 2: Application description and ER diagram

Your group identifies an application you would like to manage with relational databases. Please pick an application of your interest, as you'll be working with it for the entire semester. Your application should be substantial (at least three different entities, and least three different relationships), but not too complicated.

You need to submit a project summary including:

Description of the application domain of your choice. State as clearly as possible what you want to do, and pay attention to the following factors: (1) Usefulness. State as clearly as possible why your chosen application is useful. Make sure to answer the following questions: Are there any similar or equivalent web sites/applications out here? If so, what are they and how is yours different? (2) Realness. Describe what your data is and where you will get them.
Description of the functionality that you plan to offer. This is where you talk about how to meet the functionality requirements. There are two types of functions you need to offer: (1) Basic Functions: see Stage 4 for what basic functions you need to offer. (2) Advanced Functions: Remember to include at least one creative thing (advanced functions), i.e., something that don't exist in equivalent web sites/applications, or not supported sufficiently well. The advanced functions should go beyond the basic functions listed above; in addition, an advanced function should also be something that is technically challenging, meaning you would need to spend some significant time (at least a few days work) to implement it. Of course, such functions should be relevant and therefore useful for your application. Make clear what the features are, and explain why you think it is cool to have them.

You should submit the ER diagram of your application domain (the minimum number of entities is 3, so is the number of relationships), plus English descriptions on the assumptions you make.

Stage 3: Development Plan

Each group must submit a detailed and well thought-out development plan (one- or two-page long) including the following:

The relational schema of your database. Remember to include all keys and dependencies (e.g., functional dependencies) as appropriate.
The final choice of relational databases (MySQL/MariaDB, PostgreSQL, sqlite3, etc.) and software platforms/languages (XAMPP(PHP), Flask(Python), etc.) that you will be using.
Where and how you will get data for your application. Do you get it from the Web, some other applications, or do you make it up.
Describe the labor division among group members.
A project timeline with milestones.

Note: You also need to address the staff's concerns at Stage 2 in your Development Plan.

Stage 4: Demo and Final Report

Each group must demo its application to the TA. The demonstration takes 15-20 minutes, and will contain all of the following:

Basic Functions: (1) show how to insert records to the database; (2) show how to search the database and list or print returned results; (3) show a few different interesting queries over your database. One of the queries must involve join of multiple tables, and one must be an aggregate query; (4) show how to update records; (5) show how to delete records.
Advanced Functions: the advanced functions should go beyond the basic functions listed above; in addition, an advanced function should also be something that is technically challenging, meaning you would need to spend some significant time (at least a few days work) to implement it. Of course, such functions should be relevant and therefore useful for your application.

Clarifications on advanced functions An advanced function should be: (1) useful for your users; (2) technically more challenging than the basic functions; (3) creative or novel with respect to other similar applications in your domain. Examples: It's an advanced function because of novelty: as an example, there was a project that archived all the weather forecasts from several major weather sites. One of its advanced function predicted which weather site is the most accurate, given the current weather state. It's an advanced function because of implementation. As another example, there was a project that did recipe search. One of its advanced functions is to recommend recipes based on the user's dietary constraints. Users enter their constraints, such as calorie intake, ingredient preferences, etc, and the function returns a list of recipes that meet the constraints. The novelty comes from their search implementation. The search is an exhaustive search over 1000+ recipes, involving multiple table joins and pair comparisons, and they optimized this search with indexes and views. Essentially, advanced functions are opportunities for your group to be creative and fun. At demo time, your group needs to make a case why your functions are advanced.

Report requirement: Please hand in your final project report in the final week. As a complete project report, you need to include all the detailed contents of your project and write it as a complete story. The report is expected to be single-column, 4-8 pages in length (the font size is 11pt). You need to address the following key components in your report:

Introduction: What is your application? Why do you want to choose this application, instead of others, as the course project (motivation)? What are the key components in your project?
Database details: How do you design the database? What is your E-R model and relational model? What are the tables you include in the database? How do you design you database to accommodate functional dependencies? Are your tables in BCNF or 3NF? How about other constraints?
Functionality details: What are the basic functions and advanced funcitons in your database and how do you implement them?
Implementation details: What languages and platform you've chosen to do the implementation? How do you implement the front-end Web interface and the application logic? How does the front-end Web interface interact with the backend database? Students are encouraged to upload their code to some online repositories, such as Dropbox, Github, Bitbucket, and provide the link in the report.
Experiences: What have you learnt from this project? How have you solved hard problems in this project? How to extend your project to more advanced, mature systems in the future?
References: Any resource you have turned to for help during the implementation of your project.