Lecture notes for the Introduction to problem solving with applications by James Tam Return to the course web page

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.

 

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 area code is bracketed and 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 a ‘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 include 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 124 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., should a date be stored as text, a number or in some other form). Finally a picture is needed for each employee (you choose the image) and a 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.

 

 

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 for both), Query results should show: the full name, employee number and the actual salary.

 

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

 

Be sure that your assignment fulfills the requirements of the [general submission requirements].

[Marking of this assignment]