CS 348 - Homework 4

Festival of Science Database

In this assignment you are going to construct an Entity-Relationship database model for St.Lawrence University's Festival of Science. The requirements below were developed together in class on Tuesday October 14, 2008.

What to turn in by Thursday October 30.

  1. A neatly formatted and layed out ER Diagram. You can use Dia or another drawing tool you might have access to. Make sure you genertae a PDF file and turn in a file named fos_ER.pdf. Also turn in a printout.
  2. A document that describes each entity and relationship. Include descriptions of attributes, keys, participation constraints, mapping cardinalities, etc. Turn in a PDF file named fos_ER_doc.pdf.
  3. An SQL file of the create table commands used to construct the tables from your ER diagram. Include primary and foreign key constraints. This file should be commented and named fos_ER.sql. Turn in a printout as well.

Design Goals

Don't forget our two database design goals:

  1. Meet the functional requirements.
  2. Reduce redundancy.

Functional Requirements

  1. Projects have one or more mentors (usually faculty).
  2. A student project must have at least one mentor.
  3. A student can have more than one project.
  4. A project can have multiple students.
  5. A project can have multiple mentors.
  6. A student must be a current SLU student.
  7. A student initially submits a text abstract (less than 250 words)
  8. A student has a SLU ID, name, class year, SLU e-mail, major or program.
  9. A project is submitted by one or more student(s).
  10. We will genertate a web site from the database.
  11. A mentor has an e-mail, name, department or program.
  12. University relations needs student hometown newspaper.
  13. A project presentation is either a poster or an oral presentation.
  14. A presentation has a title.
  15. Special requirements for presentation
  16. An oral presentation gets assigned a time slot and a room number.
  17. A poster may have one or more associated pdf, ppt, or jpeg file(s). (We could require that all documents be either Ppdf files or powerpoint files.)
  18. A project is either presentated as an oral presentation or as a poster.
  19. A student can have both an oral and poster presentation of the same/diffent topics.
  20. A project could be honors, SYE, course related, fellowship.
  21. This database will be used for many years.
  22. We need general query capability. For example get all projects by mentor, get all projects by department or program, get all projects by student name, get all projects by year and much more.
  23. A project can be sponsored by multiple departments or programs.