The Proposal and the Final Assignment

Below are the two assignments which will take us to the end of the semester.

I'll note that there are a couple of things in here (specifically, triggers and procedures) that we haven't learned yet, but we'll cover them well in advance of the due dates. We'll have covered everything necessary to complete these assignments next week, but I thought you'd like to have these assignments available earlier rather than later.

Cheatsheet

  • Proposal
    • Due March 29th
    • Written, not coded
    • A design for a database, including database tools
  • Presentation
    • Given April 18th/19th
    • Make your database
    • Tell us about making it (in five minutes)

Proposal Assignment

Your assignment is to propose a database architecture, (which you will later build, and present to the class).

Deliverable

The deliverable for the Proposal Assignment is not the finished database, but the plan for the database you will build for your Final Assignment.

In other words, you do not have to write a single line of code for your Proposal Assignment, just lay out your plan.

Your plan should include the following:

  • Normalized table architecture, including:
    • Columns, including:
      • Names
      • Data types
      • Keys
      • Constraints
      • Referential actions
  • At least one view
  • At least one table automatically logging data deleted from a significant table (or tables) in the database (via a trigger, which is a thing we'll learn about next week)
  • At least one function OR one procedure (which we'll learn about over the next two weeks)

Your plan should include the justification for all of the above features - in other words, you must state why these things are necessary.

Your database will be based on the following premise (unless you have a better idea that can satisfy all the requirements):

You are managing the data at an animal sanctuary. There are many possible database features that could assist in the sanctuary's operation. Here are some examples:

  • If an animal joins or leaves the sanctuary, how can you make it easy to update all necessary tables?
  • Assume that donors have the option to earmark their donations for certain types of animals. How can you see the total donated funds for each type of animal?
  • Sleep schedules of certain animals could determine when exhibits are open. How can we generate a schedule of when exhibits are open?
  • Dietary requirements of certain animals could determine a feeding schedule. How can we generate a feeding schedule?
  • The animal sanctuary is open to the public within certain hours, excluding certain holidays. How can we generate a calendar that shows when the sanctuary is open?
  • Employees must be scheduled for different shifts. How can we schedule different types of employees for different shifts, while making sure employees don't work more than 88 hours per two week period?

There are, naturally, other functions for this application that a database can help with - feel free to come up with your own!

Choose at least two features for your database (a feature being a solution to a real-world problem). Create your database tables and tools for these features. Design your solutions based on normalized data, and ease-of-use.

Deliver your proposal as a written document by end-of-day, March 29th. Include the following in your written document:

What to write about

  • What problems are you solving?
  • How will your features solve them?
  • What are your tables, how are they composed, and why is that justified by your solutions?
  • What are your database tools (procedures, functions, etc.), and why are they justified?

You're being marked on the following:

  • Description of the problems you're solving. 3 marks
  • Description of table architecture. 3 marks
  • Description of all database tools required for your solutions (procedures, views, referential actions, et al.). 2 marks
  • Justifications for the architecture and tools. 1 mark
  • Reasonably estimated timeline. 1 mark

Final Assignment

Your final assignment will be a five minute presentation to the class, with supporting materials.

Deliverables

1. Presentation

You will describe your animal sanctuary database, and, assuming you got it up-and-running, demonstrate your features. You can also present additional materials for context, such as a wireframe of a user-interface that would interact with the database. It equally valid to present on your failures as it is to present your successes. You will take questions from your classmates.

2. Supporting materials

Additionally, you will submit to me an sql document containing all queries and/or procedure calls required to use your features.

These presentations will be given to the class on April 18th/19th (depending on your section). You can also earn up to 2% bonus by asking good questions of your classmates.

What to talk about

  1. What you are trying to do?
  2. Why you are trying to do it?
  3. How you are trying to do it?
  4. Why did you not do it another way?
  5. What is working well?
  6. What is not going well?
  7. What would the next step be?
  8. What's left to do for this feature to be "done", and how are you going to get there?

You're being marked on the following:

  • How well do you explain the state and progress of your work (see above, 1-8)? 6 marks
  • How well did you plan and scope your feature? 3.5 marks
  • Was there anything innovative about your feature? 2 mark
  • How useful would your feature be to end-users and/or other developers? 1.5 mark
  • How well-formatted/easy-to-read is your code? 1 mark