The purpose of this assignment is to bring together the SQL and DB material that you have been exposed to in this course. To do this, you will be asked to translate the ER Modeling, normalization, SQL, and other related concepts into a "real world'' project and report. This project can be completed by dyads who completed the earlier assignments or by two such dyads combined into a team of up to four members. The project should be completed for Majestic Airwaves, Inc. It is important that you turn in an evaluation of your team members' participation on this project.
You will be asked to generate a project report and working system in MS Access. This will include the following components.
1. A statement of the problem (15%). This should include…
a. Background information that defines the context for the system.
b. A statement of the reason(s) that the new information system is needed.
c. A statement of the scope of the problem (e.g., What will the system do and not do? Where will the data that the system will use come from?).
d. A description of the requirements for the system.
2. Documentation of the system components (25%). This should include…
a. An E-R diagram reflecting the conceptual model for your system
i. This should be a revised and corrected version of the model you submitted for the earlier assignment.
ii. Include in this model cardinality and participation constraints.
iii. All M:N and other problematic relationships should be removed.
b. Documentation about the entity types. Include a table containing definitions of what the entities mean and related information about them.
c. Document the attributes. Include a table showing the attributes, their definition, and characteristics of each attribute (i.e., foreign keys, primary key, data type and length, primary and alternate keys, default values, allowable nulls, and derived status).
d. Document the relational schema for the system.
i. Include a table listing the entity/table name, the attributes, the primary key(s), alternate key(s), and foreign key(s)
ii. This should be a normalized relational schema. All relations should be normalized to at least 3NF. If any relations are not in 3NF, a reasonable reason for this should be stated somewhere in the documentation (be as specific as possible!). You do not need to show the normalization process, but you should speak to the issue of normalization.
e. A Table of Contents must be provided that lists in one place in your document all of the table names, all of the script names (or descriptions), the fields that contain indexes, and the names for all views
3. A working implementation of the system plus related documentation (60%). The working components should include…
a. Populated data tables.
i. All tables represented in the relational schema should be created and documented in Access.
ii. All tables should incorporate any of the domain constraints specified in the documentation (see the documents referenced above).
iii. All tables should contain a representative sample of data that can be used to utilize the system.
b. Working Forms and Queries.
i. Include Forms for every table that enable interactive updates of existing records and interactive additions of new data.
ii. Include queries that enable users to interact with the database in complex ways. These queries should…
1. Address the problems or requirements that were highlighted in the problem statement.
2. Include both single and multi-table queries.
3. Include both static and interactive queries that allow users to search the tables.
4. A good rule of thumb for the number of queries is 3-4 per table, but your grade will depend not so much on quantity but on the quality and creativity represented in the queries.
c. Include indexes for those fields that your queries will order during the query process.
What to Turn in: You are required to turn in printed documentation of all of the components specified above. Where appropriate, include printouts of any forms or screens that you consider to be relevant. The printouts should be collated and logically organized (the use of appendices, embedded tables and figures, descriptive headings, and similar documentation devices is encouraged). Make sure that you include a cover sheet that includes your group member's names, the date, and the assignment identifier.
Up to 10% Extra Credit: In addition these basic MS Access components, create a working prototype that includes supporting scripts, macros, and web support. You will not need to turn in printouts of every component of the Access system, merely turn in a disk or CD containing the MDB file and an executive summary outlining what the MS Access DB contains. To get maximum credit for this component you will need to build a system that contains not only the forms and tables, but also working navigation components and other interactive features. The objective is to create something that will be useful for the target organization.
The final evaluation criteria and point distribution for these elements are also available