CPSC 203: Assignment Two (Marking List)

Part I: Tables and relationships (52 marks)

SIN:

• Set as the primary key: 1 mark
• Field size changed to nine: 1 mark
• Input mask created: 4 marks
• Field is required: 1 mark

LastName:

• Field is required: 1 mark

FirstName:

• Field is required: 1 mark

Address:

• Field is required: 1 mark

City:

• Field is required: 1 mark

Province:

• Field is required: 1 mark

Postal code:

• Field size changed to seven: 1 mark
• Input mask created: 4 marks
• Field is required: 1 mark

HomePhone:

• Field size changed to ten: 1 mark
• Phone number input mask has been properly employed: 2 marks

BirthDate:

• Data type changed to Date/Time (short format): 1 mark
• Validation rule properly implemented: 3 marks

PayRate

• Validation rule properly implemented: 3 marks
• Field is required: 1 mark

TimeBilledID:

• Set as the primary key: 1 mark
• Field is required: 1 mark

EmployeeID:

• Field is changed to a foreign key: 3 marks
• Field is required: 1 mark

DepartmentID:

• Field is changed to a foreign key: 3 marks
• Field is required: 1 mark

StartPayPeriod:

• Data type changed to Date/Time (short format): 1 mark
• Field is required: 1 mark

HoursWorked:

• Validation rule properly implemented: 3 marks
• Field is required: 1 mark

DepartmentID:

• Set as the primary key: 1 mark
• Field is required: 1 mark

DepartmentName:

• Field is required: 1 mark

Relationships between the tables:

• TimeBilled and Departments table, referential integrity ensured in the relationship: 2 marks
• TimeBilled and Employees table, referential integrity ensured in the relationship: 2 marks

Part II: Queries (29 marks)

First query: Employee pay rates

• The four fields of the Employees table included in the query: 4 marks

Second query: Gross pay

• The four fields of the Employees table included in the query: 4 marks
• The two fields of the TimeBilled table included in the query: 2 marks
• The one field of the Departments table included in the query: 1 mark
• Gross pay properly implemented as a calculated field: 4 marks
• Order of the fields is incorrect: -2 marks

Third query: Employees with unusual pay

• The four fields of the Employees table included in the query: 4 marks
• The two fields of the TimeBilled table included in the query: 2 marks
• Gross pay properly implemented as a calculated field: 4 marks
• Query criteria properly implemented: 4 marks
• Order of the fields is incorrect: -2 marks

Section III: Reports (9 marks)

Showing the queries:

• First query, Employee pay rates: 2 marks
• Second query, Gross pay: 2 marks
• Third query, Employees with unusual pay: 2 marks

Presentation and layout of the reports:

• Evaluating the three reports according to the principles of Contrast, Repetition, Alignment and Proximity: 3 marks

Information shown in the report:

• Report headings are incomplete/cutoff: -1 marks
• Information in the fields are incorrect (order or otherwise) or missing: -2 marks

Other marking criteria:

• Contact information not included (this assumes that the marker can actually determine who submitted the assignment, if this is not the case then no marks will be given): -4 marks
• Database is not named correctly (E-Solutions prefixed by the tutorial number): -4 marks

TOTAL MARKS (out of 90):

LETTER: