Course web page: Introduction to problem solving with applications James Tam Return to the course web page

A2: Access Database

Proportion of term grade 10%, Due Tuesday November 14 at 4 PM

To help you learn how databases work you will build everything from scratch yourself by creating a "Blank desktop database" after you start Access.

Save your database as an Access database (an "accdb" file e.g. 'a2.accdb').

Assignment features

Similar to the last assignment you will start with a grade point of zero. As you implement the features listed below your grade for this assignment will increase by the amounts specified in the italicized text in the brackets. Note that your mark may be reduced by not following stylistic requirements which you were taught in class. However, the lowest grade point that you may be awarded for the assignment is 0.0 (negative grade points won't be awarded).

Creating the tables

Modifying the Employees table (0.05 GPA for just creating the field, additional marks are awarded by the error prevention mechanisms described below)

Modifying the Locations table (0.05 GPA for just creating the field, additional marks are awarded by the error prevention mechanisms described below)

Stylistic requirements:

It isn't sufficient for your database to "just work" (e.g., fulfill the above format and data validity requirements). It must also conform to good stylistic conventions.

Filling in the 'Description' field (max 0.3 GPA penalty):

The 'Description' attribute in the datasheet view should make other people aware what type of information should be contained within a field as well as formatting or range constraints. For this assignment every time that there are restrictions on the format of the data (e.g., postal code must be in a specific pattern of alphanumeric and a dash) or range of data (e.g., no negatives) the description field should document the requirement. If the description field is not filled in when it should be: -0.1 GPA if there was one missing description field, -0.2 GPA lost for the second occurrence, -0.3 GPA lost for more than two occurrences.

Clear and helpful error messages (max 0.3 GPA penalty):

If the user enters invalid data, useful error messages should be provided for the Validation Rules that helps the user keep from making the same error again e.g., "Age cannot be less than zero and greater than 123 years". If the error messages are not provided or inadequate then: -0.1 GPA will be deducted if there was one occurrence of a poor/missing error message, -0.2 GPA lost for the second occurrence, -0.3 GPA lost for more than two occurrences in the database.

Choosing a logical type (of information) for an attribute, choosing good table and attribute names (max 0.3 GPA penalty):

Both of these requirements are also important in the design of a real life database. There is a 0.1 GPA for the first occurrence of an inappropriate type of information used for an attribute (e.g. simple text used instead of date) or poor naming convention used, -0.2 GPA lost for the second occurrence, -0.3 GPA lost for more than two occurrences.

Creating a graphical interface for entering new data:

Since the Employees table will be updated frequently you are to add a graphical form (select the table and then select the following options in the ribbon create->form). You don't need to do this for the other table. To receive credit for this feature you must name the form 'EmployeesForm'. (0.05 GPA)


Diagrammatic representation of your database

Using the notation that you learned in lecture draw an ERD (Entity-Relationship diagram) for the database tables, their attributes and the relationships between the tables as well as the multiplicity. You can use any drawing program, it needn't be done with something specifically designed to create an ERD. Even hand-drawn diagrams may be acceptable (scan in the drawing). However your diagram must use the correct notation and it must be clear enough for your marker to understand. (You won't get marks if your marker cannot read your hand writing for instance). Save your ERD in one of the following formats (if it comes in another format it won't be marked): gif, jpg, pdf, png or as a PowerPoint slideshow. (0.2 GPA)

Queries: must be formed 'graphically' in Access using the "Query Design" feature (Query #4 & #5 must also include an MS-Word or PDF document containing the SQL specification of the query).

  1.  (0.15 GPA) Which employees have from 10 to 20 years of service? (0.1 GPA) Query results should show: the employee number and years of service. (0.05 GPA)

  2.  (0.2 GPA) Which locations have a city name that begins with 'C'. (0.15 GPA) Query results should show: the location ID and city sorted by city (ascending). (0.05 GPA)
  3.  (0.2 GPA) Which employees have an email that contains 'canada' somewhere in the user name but not in the domain information. The user name is in front of the '@' sign, the domain information follows the '@'. (0.15 GPA) Query results should show: the employee number and email sorted first by employee number and then by email (both in ascending order). (0.05 GPA)
  4.  (0.45 GPA) Define a calculated value for 'Virtual Equity': Virtual equity = Base Salary * YearsOfService. (0.1 GPA) To get marks for this feature it MUST be a calculated value derived as you form the query and NOT a calculated attribute of the 'Employees' table. Show only the virtual equity (along with Employee number, base salary and years of service) of employees who have been working 10 or more years. (0.15 GPA) Specify the SQL version of the query in Word (or as a PDF) and submit it along with your database. (0.2 GPA)
  5.  (0.3 GPA) (Multi-table query: Employees, Locations) Show the employee number and city of all employees. (0.1 GPA) Type the SQL version of the query in Word (or as a PDF) and submit it along with your database. (0.2 GPA) Your SQL can be the slightly modified version taught in lecture (modified bracketing).

Reporting your results

For Query #4 (Virtual Equity), generate a report (Select the query and then select the following options in the ribbon: create->report) showing the query results. Below is a sample report. Results should be reasonably neat-looking and formatted. (0.05 GPA)

Submitting your work:

D2L configuration for this course

  • You can (and really should) submit work as many times as you wish before the due date. Due dates are strict, only what is in D2L by the deadline is what will be marked. Other methods of verifying that your work was completed on time (e.g. checking timestamps, emailed files etc.) will NOT be accepted.  For some assignments multiple versions of files can be submitted. In that case only the latest version of a file will be marked.
  • For this  assignment you are allowed to submit multiple files (Access database and Word documents). Consequently all versions of your submissions will be retained. However only the latest two versions of your spreadsheets are the ones that will be marked, everything else will be ignored (because it is not fair to your marker to sort through multiple versions of your files).
  • Do not use compression utilities (such as zip) or archiving utilities (such as tar) otherwise your submission may not be marked

Important points to keep in mind:

  1. Due time: All assignments are due at 4 PM on the due dates listed on the course web page.  Late assignments or components of assignments will not be accepted for marking without approval for an extension beforehand.
  2. Extensions may be granted for reasonable cases by the course instructor with the receipt of the appropriate documentation (e.g., a doctor's note). Typical examples of reasonable cases for an extension include: illness or a death in the family. Cases where extensions will NOT be granted include situations that are typical of student life: having multiple due dates, work commitments etc. Tutorial instructors (TAs) will not be able to provide extension on their own and must receive written permission from the course instructor first. (Note: Forgetting to hand your assignment or a component of your assignment in does not constitute a sufficient reason for handing your assignment late).
  3. Collaboration: Assignments must reflect individual work, group work is not allowed in this class nor can you copy the work of others. To avoid problems students should not see each others assignment solution.
  4. Execution: programs must work on the machines in the 203 computer lab. It's up to you to test and check this is the case. Non-functional submissions will receive only partial credit (if any at all).

Marking:

Marking spreadsheet