Due Tuesday Mar 14 at 5 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.05 GPA for designating the appropriate attribute(s) as the primary key for each table x 3 tables = 0.15 GPA for the 3 keys).

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 consists only of SQL, Query #7 must also include an MS-Word document with the SQL specification of the query as well as the graphically formed query).

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

Reporting your results

For the last query (#8  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)

Points to keep in mind:

1. Due time: All assignments are due at 5 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. The latest versions of the files that 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 you 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 other's assignment solution.
5. Execution: documents and 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).

D2L configuration:

• Multiple submissions are possible for each assignment: You can and should submit many times before the due date. D2L will simply overwrite previous submissions with newer ones.
• Important!  Multiple files can be submitted for each assignment. I am allowing you to submit multiple files for each assignment so you don't have to worry about archiving/compressing multiple files using a utility such as zip. However, this means that TAs will only mark the latest versions of each file submitted via D2L. Even if the version of a document that you want marked has been uploaded into D2L if it isn't the latest version then you will only get marks for the latest version. (It's unfair to have the TAs check versions or to remark assignments because marking is enough work as-is).

Marking:

• Assignments will be marked by your tutorial instructor (the "Teaching Assistant" or "TA"). When you have questions about marking this is the first person that you should be directing your questions towards. If you still have question after you have talked to your TA, then you can talk to your course (lecture) instructor.