Semester projects

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

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

A good submission on this checkpoint allows you to bring greater detail to your project. As we've seen, developing a good database design and query list are interactive tasks. Your queries will help improve your design and vice-versa.

Database Design

Your project will require a normalized database. It should have no fewer than five inter-related tables and may have more. Your database should be normalized through Third Normal Form.

Use some sort of office suite (power point?) and draw up a good diagram for each of your tables, 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 for this design stage shoudld be fully spelled out and described; later you may choose more brief values but here if you are talking about the date of birth of someone or something, do not use 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 checkpoint should be optimized for HUMAN UNDERSTANDING of your reader, me.

Submit your design diagram as BOTH i) a clearly legible hardcopy AND ii) the same file attached to an email.

Queries

By "Query" mean any interaction you envision between the user, the app, and the database. For this milelstone, 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. Your database design (tables!) 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 contain 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. Future submission(s) will ask you to figure out the SQL that satisfies it.

Your submission should be a text or PDF format 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.

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 likely write a Python program to populate the final database.

queries.sql

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

   Below each comment put the SQL statements that implement 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.

   I WANT to the see the ASCII dashed lines above and below your query and you maybe a line or two of white space as well. Make it as readable as possible and 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 pet;
   

Scripts - Python

You will be asked to demonstrate that a visitor to your site can do some of the following or other actions which interact with your database as described in your proposal. With details 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 regular expression on passwords and possibly login names chosen.
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

   The 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 and whose owners live on University Ave?
   • Range(min,max) of interest rates offered to first time homebuyers in various zipcodes in and around Nashville between the years of 2000-2021 ?
   • and so on....

Submit to Brightspace as a zipfile called SCRIPTS.zip

Choose a good name for your website!

Your webpages 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 accessability 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 "accessibilty" are:

• Include some images on your website as appropriate. Images should all have descriptive alternative text (in case 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 aesthetics.
• There does have to be some color; black and white won't cut it.

Test by using the stylesheet in existing  webpages and make sure it gives the look you want. Make sure at least one of your pages includes an html <form> 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 format (read smartphone!) mobile devices.

Submission is simply to drop a link to these pages 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. It's easy to describe what I don't want to see but trickier to say what I do want. Here goes...

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 of your site can do and the information they can access while helping us understand why that is an important thing to do. (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). Include some comments about 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 project 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,javascript,jquery,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..
  • We/I never could figure out why this does ...
• the website doesn't do anything interesting and so speaker (however enthusiastic) does not have enough material to fill the alloted time.

Final Website & Source

Post a link to your operational website AND a zip file containing all your source files.

Further submission instructions: yet another checklist for submissions