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.
[Rules]
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.
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.
[Miniworld, Description]
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 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
|
[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
|
[ER-mapping]
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
|
[Relational Database]
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)
[Testing and Queries]
Also, design five different SQL queries for your database, illustrating all of the below:
joining of at least three tables
nontrivial nested queries
aggregating information
use of function (with or without aggregated information)
a complex query, combining several of the features above
For each of your five queries:
Describe what the query does (in English),
include both the query and its output when run on your database
Specify which of the 5 categories (a,b,c,d,e above) it illustrates.
|
Final Deliverable, due November 22nd At this point, submit the following (resubmit, even if you submitted the same earlier): Hardcopies or electronic copies of:
Electronic copy of
You can drop off hardcopies and disks on the fourth floor (even after hours). Electronic copies can be emailed to me. |
[Grading]
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
Final Deliverable
[5%] Miniworld Description
[20%] ER Diagram (appropriateness for miniworld description, correctness, completeness, minimum complexity)
[20%] ER Mapping (correct mapping based on ER Diagram]
[15%] Database implementation with data
[25%] SQL Queries (minimum complexity, correctness, documentation)
[6%] Overall quality of project.