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

Due Friday February 27 at 4 PM

A2: Database

For this assignment you are to use the initial database: [Access 2007 database] - it won't work with Access 2003 because the latter program won't have the features that you need for the assignment.   If you're having trouble opening the 2007 database then you can at least get the information for the fields from the following set of spreadsheets [Excel 2003 version: <Employees table> <Locations table> <Titles table>]. In the latter case however you will need to add a new field in the 'Employees' table before you can add image data.

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 features of a lookup table but also contains data.

Fields of the Employees table
  • EmployeeNumber: consists of a 9 digit field which is the person's Social Insurance Number. Each person employed in Canada must have a unique Social Insurance Number.
  • LastName: a field of alphabetic text
  • FirstName: a field of alphabetic text

  • MiddleName: a field of alphabetic text
  • LocationID: a foreign key that refers to the LocationID field in the Location table.
  • TitleID: a foreign key that refers to the TitleID field in the Titles table.

  • 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 non-negative number.
  • YearsOfService: a numeric value that indicates how long the employee has been with the company. This field must also be a non-negative number.
  • Picture: due to security requirements of the organization each employee must have an up to date picture on file. The image can be an 'attachment' type.

 

Fields of the Locations table
  • LocationID: must be 3 characters in length. The first character will be an 'L' and the others must be digits. The value of this field is unique to each record. 

  • 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> <space>  <digit> <char> <digit>
  • Province: consists of exactly two upper case characters.
  • Office phone: consists of a 9 digit number in the format commonly accepted in North America where the 3 digit area code is bracketed and for the phone number the first three digits are separated from the last four digits by a dash.

 

Fields of the Titles table
  • TitleID: must be 3 characters in length. The first character will be an 'T' and the others must be digits. The value of this field is unique to each record. 

  • Title: a field of alphabetic text

Additional notes:

You should modify the database to account for the above requirements. If the user enters invalid data, useful error messages should be provided (whenever it's possible) that describes the format of the input e.g., "Age cannot be less than zero and greater than 123 years". Also you should provide addition information in the 'Description' attribute in the datasheet view for the person who maintains the database so that he or she is aware what information is contained within a field as well as formatting or range constraints. You should also make sure that the way in which information is stored in the database is appropriate to the data (e.g., something that will be used in calculations should be numeric). Finally a picture is needed for each employee (you choose the image) and an appropriate primary key must be set for each table.

 

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.

 

Graphically representing your database

Using the notation that you learned in lecture draw out 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 a drawing program to draw out your diagram, it needn't be done in a dedicated program. However your diagram must use the correct notation and it must be clear enough for your marker to understand. Save your ERD in one of the following formats: gif. jpg, png and upload it to D2L along with your database.

 

Queries:

 

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

 

  1. Which employees have a location ID of L01 and a TitleID of T01? Query results should show: the full name, locationID and TitleID.

 

  1. In which city does Nicolas Linnear live? Query results should show: the full name and the city.
 

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

 

 

For the remaining queries you need to use the following formula: Actual Salary = Base salary * (1 + (Years of service/100))

 

  1. What employees have an actual salary greater than $100,000 (inclusive). Query results should show: the full name, employee number and the actual salary.

 

  1. What employees have an actual salary between 41,000 and 101,000 (inclusive), Query results should show: the full name, employee number and the actual salary.

 

  1. What employees have an actually salary that's either less than 20,000 or greater than 120,000 (exclusive). Query results should show: the full name, employee number and the actual salary.

 

Reporting your results

For the last query (#7 employees with extreme pay), generate a report showing the query results. Below is a sample report. Results should be reasonably neat-looking and formatted.

Submitting your work

Upload your database and an image representing your ERD via [D2L]