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

Due Tuesday November 17 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 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
  • 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. Of course partial Social Insurance numbers cannot be entered. (0.1 GPA)
  • LastName: a field of alphabetic text
  • FirstName: a field of alphabetic text
  • 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.2 GPA)

  • BaseSalary: a numeric value that indicates the base dollar compensation earned by the employee. (A formula - shown later - will be used to calculate an employee's actual salary). 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 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 'attachment' type. (0.1 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> (0.3 GPA)

 

Fields of the Locations table
  • 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 is what uniquely identifies one location from another.

  • City: a field of alphabetic text

  • Address: a text field that can not only contain alphanumeric information but potentially other characters.
  • Postal code: consists of six characters in the following format: <char><digit><char>-<digit><char><digit> (0.2 GPA)

 

Fields of the Titles table

  • 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 is what uniquely identifies one title from another.

  • Title: a field of alphabetic text

Note: the above properties of the attributes are something that you need to enforce in the "DataSheet" view of MS-Access and it's not required when the queries are entered nor do you have to explicitly enforce these properties 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 (because in real life you will only be given the formatting requirements because your client won't be able to tell you if you should, say, use an input mask or validation rule).

Style requirements:

 But 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:

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:

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).

 

Creating a graphical interface for entering new data:

Since the Employees table will be updated frequently you are to add a graphical form (to make it more convenient to view and change the data). 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 and upload it to D2L along with your database. (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)

 

  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. Your SQL can be the 'simplified' version taught in lecture (e.g., 'JOIN' without specifying "INNER JOIN"). (0.2 GPA)

 

 

For the remaining queries you need to use the following formula: Actual Salary = Base salary * (1 + (Years of service/100)). This value MUST be a calculated value that is generated as part of the query in order to get credit for the query.

 

  1. What employees have an actual salary $100,000 or greater. Query results should show: the full name and the actual salary.  (0.1 GPA for query conditions awarded only if the calculated value correctly specified, 0.1 GPA for specifying the calculated value = 0.2 GPA)

 

  1. What employees have "extreme pay" an actual salary that's either less than $20,000 or greater than $120,000 (exclusive of both end points). Query results should show: the full name and the actual salary. (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 (#8 employees with extreme pay), generate a 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 assignment documents (Access database, Word document and ERD all zipped together) must be electronically submitted according to the assignment submission requirements using D2L.
  2. 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].