The final project will be to implement a database---starting with a description of the miniworld---and test it with queries, reports, and forms. 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.
The rules on academic integrity apply to this final project.
Submitting the project. The project has several deliverables explained in detail below with the deadlines when they are due.
Late submission of any part will not be accepted.
Decide on a topic for the miniworld for which you want to build a database. Be creative. Here are some possibilities:
Your classical CD collection (CDs, musical pieces, composers, conductors, soloists, orchestras, ...)
A restaurant database for a newspaper webpage (restaurants, neighborhoods, reviews, reviewers, user ratings, ...)
A database to drive the webpage of your art gallery (paintings, artists, customers, salesperson, schedules, ...)
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 Pine Valley Furniture 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 (for the company database, or the Pine Valley Furniture database [see book, page 123/125]. Be specific, to the point, include business rules. The more precise your description, the easier it will be to do your ER model.
First Deliverable, due October 30th
[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 6th
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 13th
Implement your relational schema as a database in Microsoft Access. This includes:
setting up the tables with all fields, and good choices for data types
creating the primary key and foreign key constraints
populating the tables with enough data to make your SQL queries meaningful (see requirements on queries below)
[Queries, Forms, and Reports]
Also, design five different SQL queries for your database, illustrating all of the below:
joining of at least three tables
nontrivial nested queries
use of function (with or without aggregated information)
a complex query, combining several of the features above
Describe what your queries do (in English), and include both the queries and their output when run on your database.
Write one form for your database (you can use the form wizard). Include a short description of what your form does.
Create one report for your database (you can use the report wizard). There should be at least one calculated field. Include a short description of what your report does.
Final Deliverable, due November 20th
At this point, submit the following (resubmit, even if you submitted the same earlier):
Electronic copy of
You can drop off hardcopies and disks on the fourth floor (even after hours).
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%.
[3%] submission of 1st deliverable
[3%] submission of 2nd deliverable
[3%] submission of 3rd deliverable
[5%] Miniworld Description
[20%] ER Diagram (appropriateness for miniworld description, correctness, completeness, minimum complexity)
[20%] ER Mapping (correct mapping based on ER Diagram]
[10%] Database implementation with data
[20%] SQL Queries (minimum complexity, correctness, documentation)
[10%] Form and Report.
[6%] Overall quality of project.