Database Application Assignment

See also: PowerPoint Presentation

The purpose of this assignment is to expose the student to the practice of application design (also called "system design") by working through the central steps of the process on a simple problem. This is an exercise in the first steps of designing, presenting and prototyping a simple database technology application. The objective is to familiarize the student, at a relatively high level, with the concepts and methodology employed in the development of such applications.   It is not intended to provide a serious foundation in systems design or implementation, but rather to promote a general understanding of the process through 'hands on' experience. It is based on 13 separate scenarios, each presenting requirements that can be addressed through a particular, multi-table database design. Each scenario also lists three searches or report types that need to be expressed in the form of an SQL query. In some cases, these can be provided as MSAccess database reports or HTML documents.

This assignment involves some group work (in groups of three), but the bulk of your grade will be assigned to work that can be completed on an individual basis. This assignment involves the creation of a document describing your design activities and solutions (a design document) and corresponding MS Access database design, and an XML-formatted record. The assignment is divided into five parts:

  1. Use Cases: Create a use case diagram individually, with statements of pre-conditions, flows and post-conditions. Collaboratively combine the use case diagrams and corresponding statements into a single use-case document, with a common list of actors. Ensure that the use cases in your document are consistent with one another; the use case document should be incorporated into the larger design document that is to be the final product of this assignment.
  2. Entity Relationship diagram: Develop an Entity Relationship diagram together (as a group), and incorporate it into the design document.
  3. MSAccess Database Structure & Contents: Develop a corresponding database structure together (consisting of tables, relations, and a small quantity --15-25 records-- of sample data). The database structure should be recorded as a screen capture image that is inserted into your design document. The database contents (sample records) should be submitted as a database (.mdb) file.
  4. Query: Create an SQL representation of a database query. You can first design the query using in MSAccess if you like, but your SQL expression must reflect the simplified syntax outlined in the course materials and readings.
  5. Query Output: Using either HTML or MSAccess' Reports feature, create a document that formats the output of your your query:
    1. HTML: Paste the formatted Web page in the contents of your design document, and provide the source code in an appendix.
    2. MSAccess Report: Merge the contents of your report into your design document.
  6. XML record: Include an XML record that could be used to transfer the query results or table contents from one implementation or application to another.

In response to their problem statement each team will produce a design document, comprising the five parts listed above.

Submitting the assignment:

  1. This assignment is to be submitted as a single MS Word file and a single MSAccess file, named nn.doc and nn.mdb, where nn is the number of the scenario assigned to the team.  
  2. The author of each individualized component of the assignment (Query, Query output and XML record) should be indicated in the title of that diagram or query.
  3. The files are to be emailed, by midnight December 5th, to the following email account: colin.furness@utoronto.ca

Evaluation:

The grading is structured so that both individual effort and the overall coherence of a team's project contribute to each individual's grade. The overall coherence is determined by the logical consistency of the solution submitted; that is, on the extent to which the parts form a coherent whole such that the use cases, the ERD and the database design and contents represent a plausible and potentially useful application.

Component

Value

Group/Individual Evaluation

Use Case

5

Group

Entity Relationship Diagram, Database structure, and overall coherence

5

Group

Query

5

Individual

Report

5

Individual

Sample XML Record

5

Individual

At first reading of this assignment it might seem that you need to produce a lot of paper as a response.   This is not the case; your design should not run to more than about 1/2 a dozen pages, in addition to the cover page.

The key to success in the diagramming and description portion of this assignment is the appropriate application of a few simple concepts to generate a clear and concise description and instantiation of the application at a fairly high level of abstraction.   It is not a scholarly exercise, so there is no need to consult and cite references.   This is to be your own creation.

Each team will create an MS Access Database that realizes the design represented by their ERD and the requirements indicated by their use-case scenarios.

The purpose of this assignment is to expose the student to the practice of application implementation by working through the central task of creating a set of use cases, a prototype database from a design represented by the ERD, and by generating and formatting some of the required outputs from that database.

The data to populate the database need not be extensive; I expect that you will need only a few rows in each table, generally between 15 and 25 rows.   All that is needed is enough to produce two or three rows of query output, or three complete entries in each of the corresponding reports.

For the use cases, the ERDs and the SQL queries, please use the terms, notation and syntax provided in the course materials, and not those provided in MSAccess or in other online sources.

Note that it is possible that when you come to implement your database you will discover a need to make changes, such as adding attributes or reorganizing your entities and relationships.   Be prepared to engage in an iterative design process!

The Scenarios:

01. Equipment Reservation: Imagine a small company that loans out equipment for different sports. It wishes to devise a way of renting out its equipment to avoid double-bookings. Your task is to create a database system that is able to reserve different pieces of equipment for different customers, and that allows company staff to enter information and determine the availability of the equipment.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

02. Video Rental Store: Imagine that you are designing a basic system that will help a video store keep track of its rentals. It is especially important to track the video titles that are out on loan, who has them, and to be able to contact the right customers when when these rentals are late.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

03. Banking: Besides a great deal of other information, a bank employees need to keep a record of customer information, the types of bank account(s) each customer has (e.g. savings, chequing, term deposit), and the account holders (name, etc), accounts. account types: (for the purpose of this assignment, the bank does not have joint accounts; only one customer can be associated with an account; but it is common for one customer to have multiple accounts.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

  1. most popular account type,
  2. accounts by customer,
  3. customers by account types

04. Restaurant: Imagine you are designing a system for small restaurant. The restaurant has had difficulty ensuring that it has enough of the right ingredients for the most popular menu items. Your task is to create a database system that tracks the menu items ordered (including the dates and times of the orders), the price of the menu items, and a primary ingredient required for each of those items, as well as its cost. (Note: For the purposes of this assignment, each dish is associated with one primary ingredient, and two or more dishes can be associated with the same ingredient.)

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

05. Publisher's royalty payments: Imagine you are developing a system to assist a publisher with royalty payments. This system has information on authors, the books they have written (and the number of copies sold), and the royalties paid, including the date and amount per sale paid. For the purposes of this assignment, one author can have multiple books, but none of the books are co-authored.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

06. Hotel Reservations: Imagine that you are designing a system to deal with hotel reservations. The system records and manages information on guests, reservations, and rooms. Reservations include dates and expected arrival times; rooms have different amenities. One customer can reserve more than one room.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

07. Catalogue: Imagine you are creating a catalogue for a prestigious publishing company. This catalogue provides information for each title, and provides listings of the titles by subject category (e.g. fiction, sociology, political science) and by author. It also provides information about the authors. Some authors have multiple books that are to be listed in this catalogue.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

08. Human Resources Department: Imagine you are developing a database system for a HR department in a large company. This system should be capable of recording basic information for each employee, including their social security number, date of birth, date hired, etc. This system also records the department in which each person is currently employed, and maintains a listing of the insurance plans (e.g. health, life, auto) held by each employee. It is used maintained by HR staff, and used by managers to monitor employment.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

09. Sales: Imagine you are creating a system for tracking sales of products (say, automobiles) that are sold to particular customers by particular sales people. One sales person will sell a number of different types of products to a range of customers, with one customer generally buying one product at a time. Sales people will enter customer information, recording (among other things) whether the customer is male or female); product information is provided by the manufacturer.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

10. Bibliographic database with user tracking: Imagine that you are creating an online bibliographic database that classifies books and articles by subject, requires users to log in, and that also records the searches carried out by users. Standard bibliographic data is recorded, along with user's names and the date they created their database account. For each search, the time and date is recorded, as well as the search parameter or term(s) submitted. Each work in the database one subject classification. The database is maintained by two professional indexers.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

11. Radio Station CD Collection: Imagine that you are creating a system for tracking CD's and their use for airplay at a small radio station. The CD's at the station are labeled and filed according to genre: classical, rock, country, etc. There is also a log kept by disc jockeys of when a particular CD was played. An intern working at the station maintains/updates the database.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

12. Specialist Dictionary: Imagine you are compiling a dictionary for a relatively new subject area (like a word-list for blogs, a new popular music genre, etc.). To ensure that the database contains as little redundancy as possible, note that multiple terms can share one definition (provide a few examples of these). Terms can be classified as verbs, nouns, and adjectives. The database will have a number of authors and users.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

13. Students and Courses: Imagine you are creating a database for the efficient recording, searching and display of information for a number of courses in a degree program. This database contains information about students (e.g. Name, student number, age), and information about assignments (name of assignment, description of assignment); finally, there is a table of courses (course numbers, titles, descriptions, etc.). Many students are in one course (to keep it simple, no student is enrolled in more than one course at a time). Many assignments are also associated with one course. The database is to be used by teachers, assistants and students.

After creating a sample database, create SQL and corresponding outputs in response to these questions and statements:

Developed 2005 by Dr. Norm Friesen