Database Programming
Web Database Project

You are required to create a project and present this project to the class at the end of the semester.  You will work on teams (size to be determined in class).

The project may be any type of web database project, or some type of project requiring complex querying.  Possible project topics are an ecommerce system, a contact list application, a help-desk type of system, an email notification system, or practically any kind of system that would require some kind of database CRUD on a website.  There are many sample projects, some in the materials that we have used in class, and many available elsewhere.  You are not required to build the project from scratch.  You may use one of the projects that were demonstrated in class, or build upon examples that were used in labs.  However, you must modify the code to your specifications.  I will be in regular contact with teams, so please get my approval if you would like to use modules of code that you find on the web or other sources.  It is primarily the database elements (the ERD and the queries) that must be original.

The application requirements (with some presentation guidelines) of the project:

  1. Assume that this is a database for an organization/business.  What is the title of this application?
  2. What is the organization/business for whom this application is being developed?
  3. What is the mission of this organization? 
  4. What is the purpose of this application?
  5. Why will this application support the mission of the organization?
  6. Provide an ER diagram of the database, both before and after any structural changes.  See point #3 under Technical Requirements.
  7. Walk through how this application is used, both as a user and as an adminstrator.
  8. During your demonstration, be sure to point out the technical specifications below. But make sure that the technical features make sense in the context of your application.  For example, if you are demonstrating an aggregate function, you might say, "This is a query that tells us how many orders each customer has placed.  This is useful if we are trying to analyze repeat business."
  9. You will probably find it easier to take screen shots of your data entry, validation and queries, and paste them into PPT slides.  In this way, your presentation will be smoother, you won't rely upon scrolling through results or navigating through your application during your presentation.  In addition, you won't have any unpleasant surprises if your server goes down or any other unanticipated issues arise.

Some of the technical specifications/components of the project:
  1. The project should be written using Python and MySQL, and should use Flask with Flask-MySQLAlchemy.
  2. You may add css and/or javascript, but the emphasis in this course is on database programming.  You may certainly incorporate bootstrap, as shown in class.
  3. Include at least one structural change to the database using DDL.  (Create, Alter, Drop). Clearly, your application will be written on one version of the design.  So you may satisfy this requirement by discussing what changes in the business requirements might precipitate a change in the design, and how would you implement that?  An example would be:  the design is based on the reality that every employee is assigned to exactly one department.  But the company has changed its management structure to a more project-based structure, and now, employees can be assigned to multiple departments.  This necessitates a change in the cardinaly of department:employee from 1:n to m:n.  This now requires a new relationship entity.  Other examples might include changing the format of a zip code from XXXXX to XXXXX-xxxx.  
  4. Provide the DDL as well as the INSERT SQL for creating the tables and initially populating the database.  You may do this in any text editor and then paste into the SQL window of PHMyAdmin, or you may use the DDL from the terminal or from within a Python program, as demonstrated in class.
  5. Include at least one insertion of a new record that will occur during the execution of the application.  This will most likely be as the result of a transaction or some component that should be added to the database.  For instance, in our labs, we added a department, and we also added an employee-project assignment.  Both of these required the insertion of a new record in the database.  In regular SQL, this would use the INSERT statement.  However, you should use Flask-SQLAlchemy for this purpose.
  6. Include at least one update of a record--changing an existing record, not adding a new one.  Use SQLAlchemy.
  7. Include at least one delete of a record.  Use SQLAlchemy.
  8. Include at least one simple SELECT SQL statement.  Use regular SQL for this.  This will require a database connection using one of packages that were showed in Lab-4-connect.
  9. Include one query using Flask-SQLAlchemy filter or filter_by.
  10. Inlcude at least one SELECT using an aggregate function.  Use regualr SQL for this.
  11. Include at least one SELECT using a compound condition using regular SQL, and also the equivalent of a compound condition select using Flask-SQLAlchemy.
  12. Include at least one JOIN query using SQL, and also one using Flask-SQLAlchemy.
  13. Include at least one subquery.  Regular SQL.  Excellence points if you also use Flask-SQLAlchemy.
  14. Use a form to collect user data, as shown in our CRUD labs.
  15. Populate a field on a form or table from the database.  This would most likely be for your update, and you can model this directly off of our examples in class.
  16. Check for empty data fields. You can use the built-in validations for this.
  17. Implement referential intergrity.  Demonstrate what happens when it is violated. Or, if you constructed your program so that it can't be violated, demonstrate how it references a primary key and prevents a vioaltion of referential intergrity.  For example, if employee has a foreign key deptNo that references the primary key deptNo in the relation Department.  If you populate a drop-down box with existing department numbers, this will prevent the user from entering an invalid department number, thereby enforcing referential integrity.  We did something similar in Lab-4-c.  If you just have a text box, the user can enter a department number that doesn't exist, and you can demonstrate that this will cause a referential integrity error.  Either method is fine, but be clear on what you are trying to achieve and demonstrate.
  18. Use an appropriate structure for your project package.  Any of the structures that we used in class is fine.  I would recommend using the structure that we used for Lab-4-c, as that is a good starting point for the project.
Excellence points can be earned for:
  1. Using additional flask or flask-sqlalchemy features that we did not cover in class.
  2. Using additonal WTForms components that we did not cover in class. (Examples:  importing other html form components that we did not demonstrate in class; check boxes, radio buttons, etc. multiple drop-down boxes)
  3. A correlated subquery.
  4. A Flask-SQLAlchemy subquery.
  5. An especially complex query.
  6. An especially complex ERD.
  7. Additions to your html that add to the functionality, navigability or appeal of your website.  This includes bootstrap that we didn't use in class.
  8. Javascript, JQuery, other client-side programming
  9. Negotiate with me--anything that adds a challenge or a difficulty level to your application will be considered for excellence points!!
The more complicated your queries, the more points you will recieve.
Emphasis will be on understanding the business value of the system and knowing how to apply the appropriate technologies to the business needs as well as on demonstrating proficiency in the ability to model a database and query its contents.

What to submit:  (See Presentation Guidelines for more detail)
  1. Your PPT presentation.
  2. A zip file with your entire application. 
  3. A short report, listing the points listed above, with a short explanation of how and where you have included each component.