|
Ensuring data integrity |
|
| |
Feature |
Max marks |
Student mark |
| |
EMPLOYEES TABLE: Salary |
3 |
|
| |
EMPLOYEES TABLE: Years of service |
3 |
|
| |
LOCATIONS TABLE: Location ID (half for 'L', half for the digits) |
6 |
|
| |
LOCATIONS TABLE: Postal code (1 mark for each character) |
7 |
|
| |
LOCATIONS TABLE: Province (2 marks for only letters, 2 for ensuring
upper) |
4 |
|
| |
LOCATIONS TABLE: Telephone (1 mark for each character: dash, bracket or
'digit') |
13 |
|
| |
TITLES TABLE: Title ID (Half for 'T', Half for digits) |
6 |
|
| |
TOTAL INTEGRITY |
42 |
|
|
|
|
|
|
|
Other changes to tables |
|
|
| |
Providing useful error messages |
4 |
|
| |
Documenting the 'description' fields |
10 |
|
| |
Add images to the 'picture' attribute |
2 |
|
| |
Setting the primary key for each table |
6 |
|
| |
Establish foreign-primary key relations between tables |
8 |
|
| |
TOTAL FOR OTHER TABLE CHANGES |
30 |
|
| |
|
|
|
|
Queries |
|
|
| |
First query (showing correct fields: 2 marks, query: 2 marks) |
4 |
|
| |
Second query (showing correct fields: 3 marks, query: 4 marks) |
7 |
|
| |
Third query (showing correct fields: 3 marks, query: 4 marks) |
7 |
|
| |
Fourth query (showing correct fields: 2 marks, query: 4 marks, sorting:
2 marks) |
8 |
|
| |
Fifth query (showing correct fields: 4 marks, query: 3 marks) |
7 |
|
| |
Sixth query (showing correct fields: 4 marks, query: 7 marks) |
11 |
|
| |
Seventh query (showing correct fields: 4 marks, query: 7 marks) |
11 |
|
| |
TOTAL FOR THE QUERIES |
55 |
|
| |
|
|
|
|
ERD |
|
|
| |
Employees table (1 mark for primary key, remainder for the rest of the
table) |
3 |
|
| |
Locations table (1 mark for primary key, remainder for the rest of the
table) |
3 |
|
| |
Titles table (1 mark for primary key, remainder for the rest of the
table) |
3 |
|
| |
Relationships |
2 |
|
| |
Multiplicity/cardinality |
2 |
|
| |
TOTAL FOR THE ERD |
13 |
|
| |
|
|
|
| |
Contact information excluded (max 14 marks lost) |
|
|
|
Total raw score |
140 |
|
| |
|
|
|
|
Grade point |
|
|