Final CSCI 284 Projects

There will be several "milestones" (checkpoints) as indicated below.

0. proposal - already assigned
1. ENGLISH - design & queries
2. MYSQL - design & queries
3. PREMIM - scripts and stylesheet
4. DEMOS IN FINAL EXAM TIME SLOT

As we've seen, developing a good database design and queries are reinforcing tasks. The queries you choose will help improve your database design and vice-versa.

To have a data plan simply means that you know what your database tables are going to look like, and can convince me you will have the data loaded in time to get this assignment done. Part of approval for your data plan is to identify specifically where your data will come from. Don't put this off!

Database Design

A normalized database is required. It should have no fewer than five inter-related tables and may have more. The database should be normalized through Second Normal Form.

Use PowerPoint or some sort of office suite you're familiar with to diagram each table, showing the data each will hold and how they use foreign keys to look up information from another table. Do not scribble this out free-hand. If you do draw yourself use a ruler and write very very clearly. Label all primary and foreign keys and all other table "parts". This design SHOULD NOT BE IN THE FORM OF MYSQL, but rather an annotated and well thought out consideration of what kinds of data you will need and how they are related. DO NOT INCLUDE MINUTIAE such as size of fields, etc. unless doing so adds to the understanding of your design. Good field names are a must and should be fully spelled out and described; later in the actual SQL you may choose shorter names if you like. For example if a field will hold someone's date of birth, say that and not an abbreviation like DOB.

If you have a "Who Owns What" table do not refer to it as whoOwnsWhat or (shudder) www.

Clarity here is key and this milestone should be optimized for HUMAN UNDERSTANDING of your readers, which is me as well as you (and anyone working with you).

Submit your design diagram as a file with filename dbDesign (and appropriate file extension) posted to BrightSpace.

Queries

Here "Query" means any interaction you envision between the user, the app, and the database. For this milestone, queries should be composed as English sentences. Provide at least five non-trivial queries. They should clearly pertain to the information which your database provides and maintains. The tables in the database design and queries should "match up" as much as possible. That is, it should be clear that the provided design diagram contains the information and associated keys to provide the answers to your queries.

Be sure your English queries do not look like attempts to make an SQL query look like an English version. A bad (super simplistic) example for the Sewanee Dog Park Project might be "List all dogs." There is no nuance or interest in the question. Interest is added by filtering the data in some way such as: list the name and contact info for all owners of female dogs between the ages of 3 and 5 years.

Be bold. Don't write safe queries that you are sure you can answer. Write the interesting query. You'll figure out the SQL that satisfies it in the next milestone.

Your submission should be a text or PDF file submitted to BrightSpace.

Submit two separate text files to BrightSpace. Both should be PLAIN TEXT.

Both text files should have your name(s) and information as MySQL style comments at the top (follow this convention for ALL future submissions).

Both files should follow reasonable coding standards as established in the course notes.

db-build.sql

1. SQL for table creation
2. SQL for (preliminary or test) table data insertions

Note: the insertions are for testing / demonstration purposes only and can be fake data. You'll surely write a Python program to populate the final database.

queries.sql

1. Include English queries from your previous submission (Milestone 1A) AS COMMENTS appropriate for MySQL.

   Below each comment put the SQL statements that implements the query. You may use more than one SQL statement to solve the described English statements. However, it should be very clear how they are related and work together to accomplish the goal.

   Set off your query so it's as readable as possible. For example, you might use dashed lines above and below your query and maybe a line or two of white space as well.
   If it is easily readable, I'll be happy.

   Here's an example...

   /*-----------------------------------------------------------------------------------
     1) What are names & owners of all dogs registered for Sewanee Dog Park Membership?
   ------------------------------------------------------------------------------------*/
   select dogname as 'Dog', fname as 'Person'
                  from dogs, owners, whoBelongsToWho
                  where    dogs.pid = whoBelongsWithWho.pid
                           and
                           owners.pid = whoBelongsWithWho.pid
                  ;
   
   
   /*----------------------------------------------------------------------------------
     2) How many dogs have EVER been registered to use the park?
   ----------------------------------------------------------------------------------*/
   select count(*) as 'Number Registrations' from dogs;
   

Python Scripts

You will be asked to demonstrate that your web app allows the user to interact with your database as described in your proposal. Depending on your individual proposal these interactions might include some of the following:

1. register / login (optional - where appropriate)
   • Have a Terms Of Service Agreement to do so!
   • Use pattern matching (such as regular expressions) on passwords and login names
2. make selections
3. edit information
4. delete records
5. combine information in interesting ways in the web application
6. do some information analysis in your database

   This might include answers to questions like...

   • What are the most 3 popular colors of dog coats?
   • What is the average age of all dogs in the pets_and_owners database?
   • Which dogs are alive and well, whose owners live on University Ave?
   • What range (min,max) of interest rates were offered to first time homebuyers in various zipcodes in and around Nashville between the years of 2000-2021?
   • and so on....

Post to BrightSpace as a zipfile called scripts.zip

Choose a good name for your website!

Your web app will have a coherent style and depend on a limited number of stylesheets of your own creation.

Do not download whole styles. You can borrow ideas, but Make It Your Own.

Use colors that most would find somewhat pleasing -- at least not jarring. This "style" should follow guidelines of accessibility as much as possible.

These choices should not be "your opinion" of what works. The web is full of accessibility guidelines and suggestions. Look some up and include them as references. You should provide up to three references which are distinct and provide useful information that the others do not. Note exactly what useful information each reference provides.

Examples of "accessibility" are:

• Your website should have a number of appropriate images. All images will have descriptive alternative text (in case some image doesn't load).
•  Font colors & sizes should be readable & distinguishable from background colors and/or images.
• No dark-on-dark or light-on-light schemes. Aim for maximum legibility while still hanging onto your aesthetic.
• Use at least some color; all black and white won't cut it.

Test by using the stylesheet in existing web pages and make sure it gives the look you want. Make sure at least one of your pages includes an HTML <form> tag with various inputs that you actually intend to use. This is just for the "look and feel" of the pages -- colors, fonts, layouts etc.

Note
Include styling for small form factors such as smartphones! See mobile devices.

Submission is simply to drop a link to at least one web page that uses your stylesheet on BrightSpace.

Prepare a 10-12 minute presentation of your work to be presented in class to your esteemed peers and professor.

Your complete and total attention to all presentations is expected and required.

10-12 minutes is not much time -- especially if we allow time for Q&A and setup on projecting machine.

Here are a few qualities of both good and bad presentations...

A GOOD presentation...

• has been practiced beforehand. You should know what you intend to say and how long it takes to say it.
• falls within 2 minutes plus or minus of the alloted time. (You know this because you've PRACTICED IT)
• gives a compelling description of project as if you're telling us about it for the first time. If you're not pleased with your project and eager to tell us about it, chances are we won't be very pleased either and that will be reflected somewhere in your grade.
• walks us through a bug free demo in a logical order and shows us interesting things a user can do on the site and the information they can access while helping us understand why that is important thing. DO NOT assume the audience is familiar with your application area especially if it is highly technical or aimed at a specific audience (say, biologists or gamers). You can introduce the topic area if there's a chance not everyone in the room is familiar, but BE BRIEF.
• has a presenter who makes eye contact and speaks confidently.
• convinces us the project is perfect and makes us want to login and explore it for ourselves.
• makes the time fly.

  In a bad presentation...

• the speaker describes or shows us source code (mysql,python,html,css) without anyone asking to see it; Of course, doing so is fine if some of your audience is eager to see it... ("Hey, how'd you do that?!?")
• the speaker utters some variation of the following deadly phrases:
  • Ours does pretty much what you'd expect.
  • Ours is kinda like group X's (insert name of previous speakers or group) does.
  • You've probably seen me/us working on this already ...
  • We/I had problems with... (insert technical issue here)
  • This is still pretty buggy but...
  • Never could figure out why it does this...
• the website doesn't do anything interesting and so speaker (however enthusiastic) does not have enough material to fill the allotted time.

Final Website & Source

Post a link to your operational website AND a zip file containing all files necessary to recreate it.

Further submission instructions: yet another checklist for submissions