Assignment III - LIS 558

Because all of the other assignments in the course are done independently, this project provides the opportunity to work with another student in the class for all parts of this assignment. Feel free to discuss your database with others but please limit your collaboration to verbal communication only with anyone other than your partner. Keep backup copies of your working files at all stages (even after submission) in the event something happens to your originals.

The objective of the course project, Assignment III, is to provide you with an opportunity to demonstrate your theoretical understanding of relational database design and the practical skills you have learned during this course. Your task is to design conceptually and then implement a relational database application of your choice, subject to a number of design constraints as specified below. Your database application may be developed based on some real need or you may elect to construct an application whose purpose is wholly contrived. The purpose of this proposal is twofold: 1) it will give you a chance to formulate a preliminary description and conceptual design of the database application to be constructed in Assignment 3d and; and 2) it will provide me with an opportunity to preview your design model and determine its feasibility.

In your proposal, which should be approximately two pages in length (single-spaced; one-inch margins; section headings), describe your database application. Describe what data are contained in it and for whom it will be constructed. Describe the basic transactions your database will be capable of handling (e.g., how data entry is achieved, what reports are generated, what queries are performed, what information is tracked, etc.), as well as any assumptions you make that may impact on the design. Include an entity-relationship model of your design complete with a tentative set of transformed skeleton tables showing primary keys and the most important fields.

In essence, your textual description, E-R design model, and the set of transformed tables is a working plan by which you will realize the construction of your project later in the term. I expect you to give considerable thought to the purpose (real or otherwise) of the database, to the design of the model to achieve that purpose, and to the transactions your database will support. I do not expect your final projects to be exactly the same as the proposals you submit but there should be reasonable and substantial correspondence; there will be a small component in the final project that evaluates the extent to which you followed your plan as given in your proposal.

• Include no less than four and no more than seven entities with corresponding binary relationships (may include IS-A relationships and/or recursive relationships)
• No more than one ternary relationship (better yet none -- keep it simple)
• More than three transformed tables (not including validation lookup tables)
• Integrity constraints included in all tables
• At least five representative sample records in each table
• At least three forms that provide various views of underlying tables
• At least five queries, of which at least two queries will include aggregate functions
• At least three derived (calculated) fields
• At least three reports, some of which are based on queries and some on tables
• Command buttons on forms to facilitate interaction amongst various database components
• At least five supported transactions (database objectives)
• At least three event-driven macros
• Include a startup (splash) screen

Part IIIb - Implementation of Project Proposal LIS 558 - Due April 13, 2000

Implement your database using Microsoft Access '97 according to your revised project proposal (which is a submitted, assessed, and returned version of Assignment IIIa. Note that the degree to which you design your project according to your proposal will account for 10% of the project evaluation). Further develop your database application according to the design specifications and include some user interface features, such as a splash screen, toolbars, forms, queries, reports, and command buttons that can perform the transactions for which your database is designed to accomplish. Your project will be evaluated based on the effectiveness of the data model, properly normalized relational model, appropriate use of indexes, well-constructed views, forms and reports, completeness, overall quality, and degree of difficulty.

Set up appropriate lookups and referential integrity. Use appropriate data elements and field properties (formats, input masks, default values, etc.). Test each method to make sure it works (i.e., include test data that ensures requirements for each transaction are correct). Each database table should be populated with enough records as to prove thorough testing and demonstration of all applications.

Part IIIc - Project Report - LIS 558 - Due April 13, 2000

Your report should present an entity-relationship model of your design complete with a set of transformed tables (normalized to 3NF) showing the most important fields.

Describe your database application. Explain what data is contained in it and for whom it was constructed. Discuss the basic transactions your database is capable of handling (e.g., how data entry is achieved, what reports are generated, what queries are performed, what information is tracked, etc., what views of data are required by users) as well as any assumptions you made that impacted on the design. Explain how each function to support your database transactions is implemented.

Indicate and describe the integrity constraint mechanisms you used in your database (e.g., table(s) lookups, referential integrity, validity checks, etc.). Describe the user interface features as implemented in your database. Justify your design decisions.

Assess the efficiency of your implementation and critically evaluate your project. Determine its strengths and limitations. Assess the adequacy of the database for addressing the original goals. Does it meet the objectives and requirements effectively? Explain what you would do differently if you had to do the project again from the beginning. Was the design effective? What changes were necessary with respect to the original data model? What further improvements could be made? Suggest features you would add or remove to enhance this database application if you had to work on it for the next month on a full-time basis.

Your report should be between four and six typed single-spaced pages in length, excluding figures (E-R model) and appendices (table of data types and domains and the original proposal). Present the content of your report in a logical sequence and include section headings and sub-headings to maximize readability. The grade will reflect style, spelling, grammar, report organization and presentation.

Part IIId - Project Presentation - LIS 558 - Due April 6/13, 2000

Each demonstration will consist of a 12-minute presentation of the database designed in Assignment 3a and implemented in Assignment 3b. The demonstration will be followed by a 3-minute question and answer period in which all students are expected to participate.

Presentations should be organized as follows:

Introduce the topic:

• background information necessary for members of the group to understand the enterprise for which the database has been created
• how the database will assist the organization perform its functions

Describe the database:

• the major entities
• the relationships between the entities

Demonstrate the application:

• show the forms used for data entry
• show the reports and describe underlying queries
• point out any special or unique features you have incorporated

Close the demonstration:

• assess the adequacy of the methods used to meeting the original stated objective
• suggestions for enhancing the current database or application if more time was available

Each member in the audience will mark the presentations using the following marking scheme and submit them to me at the end of each class. The final presentation mark for the student will be the average of the assigned marks after the single highest and lowest mark are omitted.