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

Due Tuesday November 8 at 4 PM

A3: Database

For this assignment you must use the initial [Access database]. All students begin with a grade point of zero on this assignment. Implementing the features described below will earn you grade points equal to the value specified in the brackets e.g. properly setting the 3 primary keys of the three tables will earn you a 0.3 GPA for this assignment. As you implement more features your grade will increase. If you violate the assignment style requirements your grade can be decreased.

The data in the tables

The database consists of three tables: 'Employees', 'Locations' and 'Titles'. The Employees table is a data table while the Titles tables is a validation table. The Locations table provides the features of a lookup table but also contains data. An appropriate primary key must be selected for each table (0.1 GPA for each table x 3 tables = 0.3 GPA for the 3 keys).

Fields of the Employees table

  • (0.2 GPA) EmployeeNumber: consists of a 9 digit field which is the person's Social Insurance Number. Every 3 digits are separated by a space. (0.1 GPA) Each person employed in Canada must have a unique SIN. Partial Social Insurance numbers cannot be entered. (0.1 GPA)
  • LastName: no modifications needed
  • FirstName: no modifications needed
  •  (0.2 GPA) LocationID: a foreign key that refers to the LocationID field in the Locations table. Users should not be able to enter a LocationID in the Employees table that does not match a LocationID that hasn't already been entered into the Locations table.
  • (0.2 GPA) TitleID: a foreign key that refers to the TitleID field in the Titles table. Users should not be able to enter a TitleID in the Employees table that does not match a TitleID that hasn't already been entered into the Titles table.
  • (0.1 GPA) BaseSalary: a numeric value that indicates the base dollar compensation earned by the employee.  Salary must be a positive dollar value.

  • (0.1 GPA) YearsOfService: a numeric value that indicates how long the employee has been with the company. This field must be a non-negative number.
  • (0.1 GPA) Picture: due to the security requirements of the organization, each employee must have an up-to-date picture on file.  A picture is needed for each employee (you choose the image) and the image is already specified in the Access table as an  'attachment' type.
  • (0.3 GPA) A new field needs to be added to the original set of attributes 'Email': must be in the following format: <One alphabetic character> <Any number of any type of character> @ <One alphabetic character> <Any number of any type of character> <.com>

 

Fields of the Locations table

  • (0.2 GPA) LocationID: must be up to 3 characters in length. The first character will be an 'L' (0.1 GPA) to be followed by one or two digits. (0.1 GPA) The  user must enter a value for this attribute, it cannot be left blank because the location ID uniquely identifies one location from another.
  • City: no modifications needed
  • Address: no modifications needed

  • (0.2 GPA) Postal code: consists of six characters in the following format: <char><digit><char>-<digit><char><digit> (0.2 GPA)

 

Fields of the Titles table

  • (0.2 GPA) TitleID: must be 3 characters in length. The first character will be a 'T' (0.1 GPA) which is always followed by two digits. (0.1 GPA) The  user must enter a value for this attribute, it cannot be left blank because the title ID  uniquely identifies one title from another.
  • Title: no modifications needed
  • (0.2 GPA) Level: a numeric value from 1 - 5

Note: the above properties of the attributes are something that you need to enforce in the "DataSheet" view of MS-Access although they should also work in the graphical form (feature described below). Also the previous description indicates what the end result be like in Access without explicitly stating the feature of features of Access that you should employ. That means that before you start the assignment you should thoroughly study the materials from the database section and familiarize yourself with the concepts and terms that have been introduced because you will be required to recognize when a concept must be applied in order to implement a particular feature (in real life you will only be given the formatting requirements - your client won't be able to tell you if you should, say, use an input mask or validation rule).

Style 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 (whenever it's possible i.e. 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 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:

Both of these requirements are also important in the design of a real life database. In the case of the assignment these things were already done for you (e.g., base salary is already defined as a 'currency' type, all attributes have already been given names) so unless you decide to change the names or type of an attribute these requirements won't play a role in your grade for this assignment). The new email attribute of the Employees table should be text.

 

Creating a graphical interface for entering new data:

Since the Employees table will be updated frequently you are to add a graphical form (create->form). You don't need to do this for the other tables. (0.1 GPA)

Diagrammatic representation of your database

Using the notation that you learned in lecture draw an ERD (Entity-Relationship diagram) for the three 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 #6 must also include an MS-Word document with the SQL specification of the query).

Queries that refer to 'full name' include both the first and last name attributes of the Employees table.

 

  1. What employees have a location ID of L01? Query results should show: the full name and the location code. (0.1 GPA)

 

  1. Which employees have a location ID of L01 and a TitleID of T01? (0.2 GPA) Query results should show: the full name, LocationID and TitleID.

 

  1. Which employees have a 'six figure' ($100,000 and up) base salary. Query results should show: the full name and base salary. (0.1 GPA)

 

  1. Which employees have from 10 to 20 years of service? (0.2 GPA) Query results should show: the full name and years of service.

 
  1. Which employees have a last name that begins with the letter 'L'. Query results should show: the last name and then the first name sorted by last name and then by first name. (0.1 GPA)

 
  1. (Multi-table query: Employees, Locations) Show the full name and city of employees. (0.1 GPA) Type the SQL version of the query in Word and submit it along with your database. (0.1 GPA) Your SQL can be the slightly modified version taught in lecture (bracketing). (0.3 GPA)

 

  1. (Multi-table query: Employees, Titles) For the last query you need to use the formula: Virtual equity = Base Salary * Level. This value MUST be a calculated value that is generated as part of the query in order to get credit for the query. The virtual equity is a 'buy out' value that may be awarded to employees that are close to retirement when cutbacks are needed. Query results must show the full name, base salary, level with the virtual equity as a calculated value. (0.2 GPA for query conditions awarded only if the calculated value correctly specified, 0.1 GPA for specifying the calculated value = 0.3 GPA)

 

Reporting your results

For the last query (#7 virtual equity), generate a report (create->report) showing the query results. Below is a sample report. Results should be reasonably neat-looking and formatted. (0.1 GPA)

Submitting your work:

  1. The document must be electronically submitted according to the assignment submission requirements using D2L.

D2L configuration for this course

  • You can (and really should) submit work as many times as you wish before the due date
  • Only your latest submission (what you submitted previously will be overwritten by your latest submission)
  • You can only submit one file per assignment. To  submit multiple files (database and ERD)  then use 'zip' to contain all the documents in one document: [How to use zip in Windows 7]. Do not use other compression utilities otherwise your submission may not be marked.
  1. You are not allowed to work in groups for this class. Copying the work of another student will be regarded as academic misconduct (cheating).  For additional details about what is and is not okay for this class please refer to the [notes on misconduct for this course].

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. What you have submitted in D2L as of the due date is what will be marked.
  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 (TA's) will not be able to provide extension on their own and must receive 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. Method of submission: You are to submit your assignment using D2L [help link]. Make sure that you [check the contents of your submitted files] (e.g., is the file okay or was it corrupted, is it the correct version etc.). It's your responsibility to do this! (Make sure that your submit your assignment with enough time before it comes due for you to do a check).
  4. 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.
  5. Execution: programs must work on the machines in the 203 computer lab. It's up to you to test and check that this is the case. Non-functional submissions will receive only partial credit (if any at all).