CIS 430,1Final Project Fall, 2003
50 points Due:Friday, Dec. 12, 2003
You may turn this in at the Final Exam, if you like. If you turn it in after the Final Exam, slide it under my office door (BU 323). The latest you may submit it is Dec. 12, 2003 by 5:00 p.m..
For this assignment, you are to model a portion of the "real world" in an
ER diagram, and then implement the model using Access. The particular
portion of the "real world" that you model may correspond to an actual
portion of the "real world", or may correspond to a hypothetical, but
realistic, portion of the "real world". Include "rules" of this miniworld,
similar in format to the rules given for the Company database on pages 51
and 52 of your textbook. On the ER diagram, give (min, max) values to
indicate the participation constraints on an entity type in a relationship
type. Then map the ER diagram into a relational database schema (a set of relation schemas). Use Access to create the tables corresponding
to the relation schemas. Load the database with actual data. Then create
at least 12 SQL queries, ranging in complexity from simple to sophisticated.
In addition to those stated above, this assignment has the following requirements.
1. The ER diagram should have at least 6 non-weak entity types and at least 1 weak entity type. It should include at least 1 one-to-one relationship type, 4 one-to-many relationship types, and at least 2 many-to-many relationship types. It should include at least one multivalued attribute.
2. Among your SQL queries (at least 12 total SQL queries), you should have:
-at least 5 nested queries
-at least 4 different aggregate functions with the GROUP BY feature
-at least 4 queries involving the existence of certain values
-at least 2 queries involving a date attribute (other than in the SELECT clause)
-at least 2 queries involving the HAVING clause
-at least 1 query involving the UNION operation
NOTE: Using pen, next to each SQL query write which category(ies) it satisfies. For e.g., you may write “Nested” and “Aggregate” next to one query. Also, for each query highlight in yellow that part of the query that qualifies it as satisfying a category requirement. For e.g., you may highlight in yellow the entire inner query, showing you have created a nested query, or you may highlight in yellow an aggregate function. FAILURE TO DO THIS WILL RESULT IN SIGNIFICANT LOSS OF POINTS!
3. At least 2 forms should be created for the purpose of inputting data into two of the tables.
4. At least 2 reports should be created. The reports should contain aggregate and summary information about your database.
5. At least 2 macros should be created for whatever useful purpose you deem appropriate.
Turn in the following.
1. A disk containing your database application. Have a label on the disk, with your name, “Final Project”, course number, semester AND the name of your Access database file.
2. A typed description of the "rules" of the miniworld you are modeling.
3. Your ER diagram, either neatly hand-drawn, or drawn using software.
4. Your relation schemas, underlining primary keys. Under each schema, list each foreign key and the relation that the foreign key references.
5. The structure of all Access tables (include table names, attribute names and attribute types).
6. The data that was loaded into the Access tables.
7. For each query, turn in:
a)A typed statement of the query being solved.
b)The SQL statements used to answer the query.
c)The results of each query.
Be sure to have all information a), b) and c) all together for each query.
8. A copy of each of your Access Reports.