NZDITTS5讲解 、辅导 SQL设计编程
© NZSE – (NZDITTS5) IT504 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.
(1 mark)
Student has not
attempted the task.
(0 marks)
using ORDER
BY SQL
(2 marks)
Student has provided evidence of query using the correct ORDER
BY SQL command to show the required output.
(2 marks)
Student has provided evidence of query using ORDER BY SQL
command, but the output is incorrect.
(1 mark)
Student has not
attempted the task.
(0 marks)
using MIN()
SQL
(2 Marks)
Student has provided evidence of query using the correct MIN()
SQL command to show the required output.
(2 marks)
Student has provided evidence of query using MIN() SQL
command, but the output is incorrect.
(1 mark)
Student has not
attempted the task.
(0 marks)