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).
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.
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.
|
-
What employees have a location ID of L01?
Query results should show: the full name and the location code. (0.1 GPA)
|
|
-
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.
|
|
-
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)
|
|
-
Which employees have from 10 to 20
years of service? (0.2 GPA) Query results should show: the full name and years of
service.
|
|
-
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)
|
|
-
(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)
|
|
-
(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:
- 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.
- 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:
- 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.
- 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).
- 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).
- 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.
- 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).