Final Project (11/1, 11/8, 11/15, 11/22)

The final project will be to implement a database---starting with a description of the miniworld---populate it with data and test it with queries. This process will include several steps: ER-modeling, ER-mapping,  implementation, and testing. There will be several deliverable along the way. Details are supplied below.

Please read carefully and completely, and let me know early if you have any questions.


  1. Your final project is an individual project, that is, you need to perform all the work on your final project yourself.
  2. The rules on academic integrity apply to this final project. All the work done for this project (both preparation and implementation) must be done by yourself alone.

  3. Submitting the project. The project has several deliverables explained in detail below with the deadlines when they are due.

  4. Late submission of any part will not be accepted.

[Miniworld, Description]

Decide on a topic for the miniworld for which you want to build a database. Be creative. Here are some possibilities:

This should also give you an idea of what level of complexity to aim for in your miniworld (somewhere between the company database and the Henry Book database). In terms of the ER model you will build later, you should expect to have at least 6 different entities which are related to each other in interesting ways (you should have both many-one and many-to-many relationships). The description should be in the style of the descriptions we saw in class (e.g. the company database example we did in class). Be specific, to the point, and include business rules. The more precise your description, the easier it will be to do your ER model.

First Deliverable, due November 1st

  • The (typed) description of your miniworld (as described above). This deliverable should be about half a page to a page.

[Miniworld, ER model]

Build an ER-model for the miniworld you described in your first deliverable. If you find out at this point that your description was incomplete, modify or extend it. First identify entities, relationships and their attributes. Decide which entities should be strong/weak, and distinguish single-valued from multi-valued, stored from derived, and simple from composite attributes. Select key attributes. Add relationships (select names well, to make the model readable). For each relationship identify attributes (if any), and determine cardinality constraints (minimum/maximum).

If the diagram gets too large with all attributes, you may choose to list only the main attributes (including the primary key) in the ER diagram and submit a list of all entities and all of their attributes on a separate page.

Second Deliverable, due November 8th

  • Resubmit your miniworld description (first deliverable) with any updates you made.

  • ER model of your miniworld, with all entities, relationships, and attributes. You can use VISIO (available in the labs and through the Microsoft MSDNAA license) if you want, but you can also draw it by hand (as long as it is neat and readable). I will not accept scribbled diagrams.


Map your ER-model to a relational database (a set of relational schemas). Include all primary keys and foreign keys. If you make changes at this point, be sure to adjust your ER model.

Third Deliverable, due November 15th

  • Resubmit your ER model with any updates you made

  • Submit your relational schema (with underlined pks, and fks pointint at their corresponding pks.

[Relational Database]

Implement your relational schema as a database in Microsoft Access. This includes:

[Testing and Queries]

Also, design five different SQL queries for your database, illustrating all of the below:

  1. joining of at least three tables

  2. nontrivial nested queries

  3. aggregating information

  4. use of function (with or without aggregated information)

  5. a complex query, combining several of the features above

For each of your five queries:

Final Deliverable, due November 22nd

At this point, submit the following (resubmit, even if you submitted the same earlier):

Hardcopies or electronic copies of:

  • your miniworld description
  • your ER-model,
  • your relational schema after the mapping,
  • descriptions of your queries, SQL and the output of running your queries on your database.

Electronic copy of

  • your database file, which includes all queries. If submitted by email: zip up the file (otherwise I can't open it), or submit it on a disk.

You can drop off hardcopies and disks on the fourth floor (even after hours). Electronic copies can be emailed to me.



The project will be graded out of 100%. Note that by this grading scheme not handing in the first three deliverables results in a reduction of about 10%.

Marcus Schaefer
Last updated: October 20th, 2010.