© Web Design and Database Application (Assessment 2) v2.1 Page 1 of 17 
 
School of Tech 
NZ Diploma in Informatton Technology Technical Support (Level 5) 
 
 Course Code & Title: IT504 Web Design and Database Applicatton 
Assignment Title Assessment 2 Assessment Type 
Database Applicatton 
Level 5 Credits 15 
Term & Cohort: 
Due date: 
Overall Weighttng: 50% 
Total marks available 100 
Tutor: 
 
Course aim 
This course provides students with the skills and knowledge to design and create robust websites 
implementtng HCI principles and usability tests to meet user requirements. Students will also learn 
the knowledge of database fundamentals and implement a database applicatton to solve business 
problems. 
 
Purpose 
The purpose of this assessment is to design a database solutton by implementtng data modelling 
principles and developing a database applicatton by implementtng the concepts of relattonal database. 
 
Learning Outcomes (LOs) 
This assessment is mapped to the following learning outcomes for this course: 
LO 4 Design a database solutton by applying data modelling principles. 
LO 5 Develop a database applicatton by implementtng the concepts of relattonal database. 
 
Graduate Proffle Outcomes (GPOs) 
3 
Apply knowledge of database administratton and query languages to meet organisattonal 
data storage and retrieval requirements, including database management system (DBMS) 
opttmisatton, cleansing, security, and backups. 
5 
Apply the fundamentals of informatton systems concepts and practtce, including business 
concepts, development life cycles, data modelling and administratton, to support and 
enhance organisattonal processes and systems. © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 2 of 17 
 
Assessment Information 
• This is an individual open-book assessment. The assessment is worth 50% of your total 
assessment weighting of the entire course. 
• To achieve a pass in this assessment all tasks must be attempted. A minimum pass mark of 
50% for the assessment and meeting all the requirements for LO4 and LO5. 
• You must complete the Student Declaration and attestation on Canvas LMS. 
• You must submit your completed assessment to Canvas. 
• You must ensure you are familiar with NZSE’s academic policies regarding assessments, 
and the relevant resubmission regulations that apply to this course. These policies and 
regulations can be found on your Course Outline on Canvas LMS. 
• Your assessment will be marked on Canvas, and your assessment grade will be provided 
on Canvas too. 
• A maximum of 15% of the content may be quoted or paraphrased from other sources 
provided you acknowledge and cite the original source of material you use. 
• All cases of plagiarism and/or cheating will be investigated and dealt with according to A08: 
Misconduct in Assessment Policy. 
 
Submission Instructions 
You are required to ensure you have carried out the following before submitting your assessment: 
• Assessments submitted online should use the following naming convention: 
o Course Code_ Assessment Number_ Assessment Name_ Student 
Number_Document number o For example, 
IT504_A2_DatabaseApplication_1800XXXX_1 
• All answers must be written in your own words. 
• Proofread and spell check all written assessment work carefully. 
• DO NOT email your document to your tutor, it must be uploaded to the NZSE LMS 
• Check that all evidence required has been uploaded to the link provided on the NZSE LMS 
(Canvas). 
Plagiarism 
• All cases of plagiarism and/or cheating will be investigated and dealt with according to A08: 
Misconduct in Assessment Policy. 
 © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 3 of 17 
Assessment Scenario 
You are working for a company called Maximum Velocity Cars as a Junior IT technician. Maximum Velocity 
Cars is a car rental company whose Headquarters is based on Queen Street, Auckland, New Zealand. The 
company opens from 6 am until 10 pm every day and has twenty-eight employees working on a shift basis 
to cater for customers during opening hours. 
The owner of the company, Mr Archer has asked you to create a Database to help his company keep track of 
all the cars that are available to rent or are rented. He would also like the destination to be included so pick 
up of cars can be arranged at noncompany drop-off points. 
Your task is to create a database file by using the following information: 
• CarFleet Table: 
o CarID (AutoNumber) 
o CarManufacturer 
o CarModel 
o CarBodyStle 
o CarFuelType 
o CarTransmission 
o CarReleaseYear 
o CarNumberofSeats 
o CarColour 
o CarLicensePlate 
o CarRentalPrice 
 
• Company Table: 
o CompanyID 
o CompanyBranchName 
o CompanyLocation 
o CompanyPhone 
o CompanyEmail 
 
• Rental Table: 
o RentalID 
o RentalCustomerID 
o RentalCarID 
o RentalPickupID 
o RentalDays 
o RentalTotalCost 
 
• Customer Table: 
o CustomerID 
o CustomerFirstName 
o CustomerLastName 
o CustomerPhone 
o CustomerEmail 
o CustomerDrivingLicense 
o CustomerPassport (If international) 
o CustomerNextofKin 
o CustomerNextofKinContactNumber © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 4 of 17 
 
 
Note: Take screenshots to show the various steps taken to produce the final database application and 
screenshots of the SQL query statements used with query output. 
 
Assessment Tasks: Database application creation (100 Marks) 
LO 4: Design a database solution by applying data modelling principles. 
LO 5: Develop a database application by implementing the concepts of relational database. 
 
1. Based on the scenario, design an Entity Relationship Diagram (ERD) by following the steps below: 
a. use Crow’s foot or Chen notation.; (LO4) (5 marks) 
b. contains (LO4) (15 marks) 
• all the entities, (max 4 marks) 
• it’s attributes (max 6 marks) 
• and their relationships; (max 5 marks) 
c. Implement (LO4) (10 marks) 
• primary keys, (max 4 marks) 
• foreign keys, (max 3 marks) 
• cardinality constraints(max 3 marks) 
2. Perform a normalisation in continuation with the ER diagram. (LO4) (16 marks) 
a. Apply 1NF and translate: (6 marks) 
• Convert all entities into tables (max 2 marks) 
• Transform all attributes into columns (max 2 marks) 
• Apply correct data types (max 2 marks). 
b. Apply 2NF upon 1NF and eliminate partial dependencies. This must include: (10 marks) 
• Converting all entities into tables (max 2 marks) 
• Transforming all attributes into columns (max 2 marks) 
• Applying correct data types (max 2 marks). 
• Providing valid relationships with primary and foreign keys. (max 4 marks) 
 
3. Build the database based on the normalisation. This must include: (LO5) (10 Marks) 
a) Query to create database. (max 2 marks) 
b) All tables based on 2NF (max 4 marks) 
c) Correct fields in each table (max 2 marks) 
d) Correct datatypes for each field (max 2 marks) 
4. Populate (minimum 10 rows need to be populated for each table). (LO5) (8 Marks) 
5. Create a query, in design view, which will extract the Customer name and contact details (phone and 
email), make and model of the car, and the number of days they have rented the car for. (LO5) 
 (5 Marks) © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 5 of 17 
6. Design and create three (3) forms, showing the following tables, Customers, Car Fleet and Rental 
tables. (LO5) (3 Marks) 
7. Generate a report, showing the output from the query created in task 5, include the company name 
in the heading, and the date report was generated. (LO5) (5 Marks) 
8. Show two ways to enhance and optimise the database. (LO5) (4 Marks) 
9. Create a backup of the database. Please provide evidence, such as screenshots, and also include the 
backup file (LO5) (2 Marks) 
10. Set up a strong password for the database with a minimum of 16 characters, including numbers, 
alphabets, and special characters. Additionally, suggest another method of securing the 
database.(LO5) (5 Marks) 
11. Create queries: 
a. Use the SELECT SQL statement to query data from different COLUMNS (RentalCarID, 
RentalCustomerID and RentalDays) but from the same TABLE (Rental Table) (LO5) (2 marks) 
b. Use the WHERE SQL clause to filter records using a condition (CarFuelType is petrol). (LO5) 
 (2 marks) 
c. Use the NOT SQL operator to create a query to filter the date (CarReleaseYear) based on a 
certain condition (the condition is up to the student). (LO5) (2 marks) 
d. Use the AND SQL operator to create a query to filter the date (CarReleaseYear) based on a 
certain condition (the condition is up to the student). (LO5) (2 marks) 
e. Use the ORDER BY SQL keyword to sort the result-set for CustomerLastName in ascending 
order. (LO5) (2 marks) 
f. Use the MIN() SQL function to return the smallest value from the RentalTotalCost column. 
(LO5) (2 marks) 
 
 
 
Note: Check the marking rubric on pages 8-14 for the mark allocation and guidelines to ensure you gain 
optimum marks. 
 
 
 © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 6 of 17 
 
 
 School of Tech 
NZ Diploma in Information Technology Technical Support (Level 5) 
Cover Sheet and Student Declaration 
This sheet must be signed by the student and attached to the submitted assessment. 
Course Title: 
Web Design and Database 
Application 
Course code: IT504 
Student Name: Student ID: 
Assessment No 
& Type: 
Assessment 2 
Database Application 
Cohort: 
Due Date: 
Date 
Submitted: 
 
Tutor’s Name: 
 
Assessment 
Weighting 
50% 
 
Total Marks 100 
 
 
Student Declaration: 
 
I declare that: 
• I have read the New Zealand School of Education Ltd policies and regulations on assessments and 
understand what plagiarism is. 
• I am aware of the penalties for cheating and plagiarism as laid down by the New Zealand School 
of Education Ltd. 
• This is an original assessment and is entirely my own work. 
• Where I have quoted or made use of the ideas of other writers, I have acknowledged the source. 
• This assessment has been prepared exclusively for this course and has not been or will not be 
submitted as assessed work in any other course. 
• It has been explained to me that this assessment may be used by NZSE Ltd, for internal and/or 
external moderation. 
 
Student signature: 
Date: 
 © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 7 of 17 
 
Tutor only to complete 
 
Assessment result: Mark /100 Grade 
LO4 Requirements 
Achieved ☐ 
Not Achieved ☐ 
Tutor Signature 
LO5 Requirements 
Achieved ☐ 
Not Achieved ☐ 
 © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 8 of 17 
 
 
IT504 - Web Design and Database Application 
Assessment 2 - Assessment Marking Rubrics 
LO 4: Design a database solution by applying data modelling principles. 
LO 5: Develop a database application by implementing the concepts of relational database. 
Criteria 
 LO 4: Design a database solution by applying data modelling principles. 
1. ER 
Diagram 
 
(LO4) 
1a. 
(5 marks) 
Student created ERD using a 
professional tool (crow’s foot 
/Chen’s notation) 
 
 
 (5 marks) 
Student created ERD using a 
professional tool with minor 
mistakes (crow’s foot /Chen’s 
notation) or vice-versa. 
 
(3-4 marks) 
Student created ERD using a 
professional tool with major 
mistakes (crow’s foot /Chen’s 
notation) or vice-versa. 
 
(1 -2 marks) 
Student did not create ERD using any 
professional tool. 
 
 
 
(0 marks) 
1b 
 
(15 marks) 
Student has correctly created the ER Diagram based on the given scenario that included the requirements of 
the ERD: 
1. Four (4) entities (max 4 marks, 1 mark per entity) 
2. Thirty (30) attributes (max 5 marks, 1 mark per 6 attributes) 
3. Three (3) relationships (max 6 marks, 2 marks per relationship) 
 
 
 (1 - 15 marks) 
Student did not include any of following in 
ERD correctly. 
1. entities 
2. attributes 
3. relationship 
 
 (0 marks) 
1c 
(10 marks) 
Student has correctly implemented the requirements of the ERD specified below: 
1. Four (4) primary Keys (max 4 marks, 1 mark per primary key) 
2. Three (3) foreign keys (max 3 marks, 1 mark per foreign key) 
3. Three (3) cardinality constraints (max 3 marks, 1 mark per cardinality constraints) 
 
 
 (1 - 10 marks) 
Student did not include any of following in 
ERD correctly. 
1. Primary keys 
2. foreign keys 
3. cardinality constraints 
 
(0 marks) 
2. Normalisation 
(16 marks) 
(LO4) 
Student has correctly applied the 1NF and translated the following: 
 
1. All entities into tables (max 2 marks, 1 mark per 2 tables) 
2. All attributes into columns (max 2 marks, 1 mark per 15 columns) 
3. Applied correct data types (max 2 marks, 1 mark for correct datatype on 15 columns ). 
 
(1 – 6 marks) 
Student did not apply 1NF. 
 
 
 
 
(0 marks) © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 9 of 17 
 
 
Student has correctly applied the 2NF upon 1NF and eliminated partial dependencies as well as included the 
following: 
 
1. Converted all entities into tables (max 2 marks, 1 mark per 2 tables) 
2. Transformed all attributes into columns (max 2 marks, 1 mark per 15 columns) 
3. Applied correct data types (max 2 marks, 1 mark for correct datatype on 15 columns). 
4. Provided valid relationships with primary and foreign keys. (max 4 marks, 1 mark for correct relationship) 
 
(1 – 10 marks) 
 
Student did not apply 2NF. 
 
 
 
 
 
(0 marks) 
 
 
LO 5: Develop a database application by implementing the concepts of relational database. 
3. Database 
creation 
(10 Marks) 
(LO5) 
Student has correctly built the database and included the following: 
 
1. Query to create database. (max 2 marks) 
2. All tables based on 2NF (max 4 marks, 1 mark per table) 
3. Correct fields in each table (max 2 marks, 1 mark per 2 tables correct fields) 
4. Correct datatypes for each field (max 2 marks, 1 mark per 2 tables datatypes) 
 
 (1 - 10 marks) 
Student has not provided evidence of creating and populating any of the 
tables in the database as per a given scenario. 
 
 
 
 
(0 marks) 
4. Populate 
tables 
(8 Marks) 
(LO5) 
Student has populated ALL tables and included: 
1. 10 or more rows in a table (max 2 marks) 
2. 6 to 9 rows in a table (max 1 mark) 
3. 5 or fewer rows in a table (0 marks) 
 
(1 - 8 marks) 
Student has populated all tables but has fewer than 5 rows in each 
table. 
 
 
 (0 marks) 
5. Create a 
query in 
design 
view 
(5 Marks) 
(LO5) 
Student has correctly 
created a query that can 
extract ALL of the 
following required fields 
from multiple tables. 
1. Phone Number (max 
1 mark) 
2. Email (max 1 mark) 
3. Car Make 
(manufacturer) (max 
1 mark) 
Student has correctly 
created a query but can 
extract only four (4) 
required fields mentioned 
from the below required 
fields from multiple 
tables. 
1. Phone Number (max 
1 mark) 
2. Email (max 1 mark) 
Student has correctly 
created a query but can 
extract only three (3) 
required fields mentioned 
from the below required 
fields from multiple 
tables. 
1. Phone Number (max 1 
mark) 
2. Email (max 1 mark) 
Student has correctly 
created a query but can 
extract only two (2) 
required fields mentioned 
from the below required 
fields from multiple 
tables. 
1. Phone Number (max 1 
mark) 
2. Email (max 1 mark) 
Student has correctly 
created a query but can 
extract only one (1) 
required fields mentioned 
from the below required 
fields from multiple 
tables. 
1. Phone Number (max 
1 mark) 
2. Email (max 1 mark) 
 Student has not created 
the query required. 
 
 
 
 
 
 
 © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 10 of 17 
 
 
4. Car Model (max 1 
mark) 
5. Number of days 
(Rental days) (max 1 
mark) 
 
 
 
 
(5 marks) 
3. Car Make 
(manufacturer) (max 
1 mark) 
4. Car Model (max 1 
mark) 
5. Number of days 
(Rental days) (max 1 
mark) 
 
(4 marks) 
3. Car Make 
(manufacturer) (max 1 
mark) 
4. Car Model (max 1 
mark) 
5. Number of days 
(Rental days) (max 1 
mark) 
 
(3 marks) 
3. Car Make 
(manufacturer) (max 1 
mark) 
4. Car Model (max 1 
mark) 
5. Number of days 
(Rental days) (max 1 
mark) 
 
(2 marks) 
3. Car Make 
(manufacturer) (max 
1 mark) 
4. Car Model (max 1 
mark) 
5. Number of days 
(Rental days) (max 1 
mark) 
 
(1 marks) 
 
 
 
 
 
 
(0 marks) 
6. Create 3 
Forms 
(3 Marks) 
(LO5) 
Student has created all three (3) Forms. 
 
 
(3 marks) 
Student has created two (2) Forms. 
 
 
(2 marks) 
Student has created one (1) Form. 
 
 
(1 mark) 
Student HAS NOT created 
any forms. 
 
(0 marks) 
7. Create report 
(5 Marks) 
(LO5) 
Student has generated a 
report with correct format 
that: 
• Shows the output 
from query created 
in task 5, (max 3 
marks) 
• Includes the 
company name in the 
header, (max 1 
marks) 
• Includes the date the 
report was generated 
in a visible location. 
(max 1 marks). 
 
 
 
 
 
 
(5 marks) 
Student has generated a 
report with correct format 
that: 
 
Shows the output from 
query created in task 5, 
(max 3 marks) 
 
BUT 
 
Does not include the 
company name in the 
header (max 1 marks). 
 
OR 
 
Does not include the date, 
the report was generated 
in a visible location. (max 
1 marks). 
 
(4 marks) 
 Student has generated a 
report with correct format 
that: 
 
Shows the output from 
query created in task 5, 
(max 3 marks) 
 
BUT 
 
Does not include the 
company name in the 
header (max 1 marks). 
 
AND 
 
Does not include the date, 
the report was generated 
in a visible location. (max 
1 marks). 
 
 
(3 marks) 
Student has generated a 
report with correct format 
that: 
 
Does not show the output 
from query created in task 
5, (max 3 marks) 
 
BUT 
 
Does include the company 
name in the header (max 
1 marks). 
 
AND 
 
Does include the date, the 
report was generated in a 
visible location. (max 1 
marks). 
 
 
(2 marks) 
Student has generated a 
report with correct format 
that: 
 
Does not show the output 
from query created in task 
5, (max 3 marks) 
 
BUT 
 
Does include the company 
name in the header (max 
1 marks). 
 
OR 
 
Does include the date, the 
report was generated in a 
visible location. (max 1 
marks). 
 
 
(1 mark) 
Student has not 
attempted to create the 
report. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
(0 marks) © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 11 of 17 
 
 
8. Optimise 
Database 
(4 Marks) 
(LO5) 
Student has correctly shown two ways to enhance and 
optimise the database. 
 
 
(3 – 4 marks) 
Student has correctly shown only ONE way to enhance and optimise the 
database. 
 
 
(1-2 marks) 
Student has not 
attempted the task. 
 
 
 (0 marks) 
9. Backup 
Database 
(2 Marks) 
(LO5) 
Student has created a backup of the database and provided ALL the 
following: 
1. Backup file (max 1 mark) 
2. Screenshot of the database backup process(max 1 mark) 
 
 
(2 marks) 
Student has created a backup of the database but provided either. 
1. Backup file (max 1 mark) 
OR 
 2. Screenshot of the database backup process(max 1 mark) 
 
 
(1 Mark) 
 Student has not 
created a backup of the 
database. 
 
 
(0 marks) 
10. Apply security 
to database 
(5 Marks) 
(LO5) 
Student has set up a strong password with: 
 
1. A minimum of 16 characters (max 1 mark) 
2. Password includes numbers, alphabets, and special characters 
(max 2 marks) 
3. AND provided another way of securing the database (max 2 
marks) 
 
(4 – 5 marks) 
Student has set up a strong password with: 
1. A minimum of 16 characters (max 1 mark) 
2. Password includes numbers, alphabets, and special 
characters (max 2 marks) 
 
BUT NOT provided another way of securing the database (max 2 
marks) 
 
(1 – 3 marks) 
Student has NOT set 
up a strong password 
AND NOT provide at 
least 1 other way of 
securing the database. 
 
(0 marks) 
11. Create Queries 
(12 Marks) 
 
(LO5) 
using Select 
SQL 
(2 Marks) 
 Student has provided evidence of query using the correct SELECT 
SQL command to show the required output. 
 
(2 marks) 
Student has provided evidence of query using SELECT SQL 
command, but the output is incorrect. 
 
(1 mark) 
Student has not 
attempted the task. 
 
(0 marks) 
using WHERE 
SQL 
(2 Marks) 
 Student has provided evidence of query using the correct WHERE 
SQL command to show the required output. 
 
(2 marks) 
Student has provided evidence of query using WHERE SQL 
command, but the output is incorrect. 
 
(1 mark) 
Student has not 
attempted the task. 
 
(0 marks) 
using NOT 
SQL 
(2 Marks) 
 Student has provided evidence of query using the correct NOT SQL 
command to show the required output. 
 
(2 marks) 
Student has provided evidence of query using NOT SQL 
command, but the output is incorrect. 
 
 (1 mark) 
Student has not 
attempted the task. 
 
 (0 marks) © NZSE – (NZDITTS5) IT504 Web Design and Database Application (Assessment 2) v2.1 Page 12 of 12 
 
 
using AND 
SQL 
 (2 marks) 
 Student has provided evidence of query using the correct AND SQL 
command to show the required output. 
 
(2 marks) 
Student has provided evidence of query using AND SQL 
command, but the output is incorrect. 
请加QQ：99515681  邮箱：99515681@qq.com   WX：codinghelp