Details of Assessment
Term and Year Time allowed
Assessment No 1 of 2 Assessment Weighting 50%
Assessment Type Written, Practical
Due Date Week 5 Room
Details of Subject
Qualification ICT40118 - Certificate IV in Information Technology
Subject Name Database Development
Details of Unit(s) of competency
Unit Code (s) and Names ICTDBS403 Create basic databases
ICTDBS413 Determine database requirements

Details of Student
Student Name
College Student ID
Student Declaration: I declare that the work submitted is my own and has not been copied or plagiarised from any person or source. I acknowledge that I understand the requirements to complete the assessment tasks. I am also aware of my right to appeal. The feedback session schedule and reassessment procedure were explained to me. Student’s
Signature: ____________________
Date: _____/_____/_________
Details of Assessor
Assessor’s Name
Assessment Outcome
Assessment Result 0 Competent 0 Not Yet Competent
Marks /50
Feedback to Student
Progressive feedback to students, identifying gaps in competency and comments on positive improvements:
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Assessor Declaration: I declare that I have conducted a fair, valid, reliable and flexible assessment with this student.
0 Student attended the feedback session.
0 Student did not attend the feedback session.
Assessor’s
Signature: ___________________
Date: _____/_____/________
Purpose of the assessment
The purpose of this assessment is to assess the student in the following outcomes: Competent
(C) Not yet Competent
(NYC)
Performance Criteria: ICTDBS403 Create basic databases
1. Analyse the requirements for the database
1.1 Determine the information that the database is required to hold
1.2 Develop a written requirement report for the functionality of the database
1.3 Complete the documentation, and submit it to the appropriate person for approval
2. Use data modelling to design the database to suit requirements
2.1 Design an entity-relationship (ER) diagram to model the relationships between the entities and the attributes that the database will hold
2.2 Develop primary and foreign keys to link the entities
2.3 Develop a data dictionary
2.4 Complete the documentation, and submit it to the appropriate person for approval
3. Create a database on a web or database server
3.1 Use the appropriate language on a web or database server to create one or more databases
3.2 Use the appropriate language on a web or database server to create tables
3.3 Populate the database fields
4. Test the database and debug
4.1 Test the database on the web or database server
4.2 Ensure that the information represented matches the requirements
Assessment/evidence gathering conditions
Each assessment component is recorded as either Competent (C) or Not Yet Competent (NYC). A student can only achieve competence when all assessment components listed under “Purpose of the assessment” section are recorded as competent. Your trainer will give you feedback after the completion of each assessment. A student who is assessed as NYC (Not Yet Competent) is eligible for re-assessment.
Resources required for this assessment
• Computer with relevant software applications and access to internet
• Weekly eLearning notes relevant to the tasks/questions
Instructions for Students
Please read the following instructions carefully
• This assessment has to be completed 1 In class 0 At home
• The assessment is to be completed according to the instructions given by your assessor.
• Feedback on each task will be provided to enable you to determine how your work could be improved. You will be provided with feedback on your work within two weeks of the assessment due date. All other feedback will be provided by the end of the term.
• Should you not answer the questions correctly, you will be given feedback on the results and your gaps in knowledge. You will be given another opportunity to demonstrate your knowledge and skills to be deemed competent for this unit of competency.
• If you are not sure about any aspect of this assessment, please ask for clarification from your assessor.
• Please refer to the College re-assessment for more information (Student handbook).
Assessment marking and observation sheet for trainers
Part A: Written/Short Question Answer
Part B: Written/Case Study to Design Worksheet and Practical/Book Database Codes
Marking List Marks
PART A – Short Question Answer – 10 Marks
Question 1 /2
Question 2 /2
Question 3 /2
Question 4 /2
Question 5 /2
SUB TOTAL /10
PART B –Practical Exercise Documentation – 20 Marks
Task 1
Question A /4
Question B /1
Question C /1
Task 2
Question A /1
Question B /1
Question C /1
Question D /2
Question E /2
Question F /1
Question G /1
Question H /1
Question I /1
BONUS MARK (for completing Task 1 and Task 2 /4
SUB TOTAL /20
PART B – Practical Exercise (Observation of Practical) – 20 Marks
SUB TOTAL /20
TOTAL /50
Assessment - Short Question Answer and Practical Project
Part A: Short Question Answer (10 Marks)
How and what you must submit:
1. All answers for your questions should include the answers in short form.
2. Submit a Digital copy of this assessment as per guidelines in e-Learning.
3. Hard copy of all pages including assessment coversheet pages submitted to your assessor.
Question 1 (2 Marks):
Name the different data models that are available for database systems.
WRITE YOUR ANSWER HERE (state with any important notes as required)

Question 2 (2Marks):
What are the primary key and foreign key? How do they link to the entities? Explain the data dictionary.
WRITE YOUR ANSWER HERE (state with any important notes as required)

Question 3 (2 Marks):
What is data modelling and why use it to design a database?
WRITE YOUR ANSWER HERE (state with any important notes as required)

Question 4 (2 Marks):
Write the SQL statement to CREATE table on database server. Write SQL statement to INSERT data into table.
WRITE YOUR ANSWER HERE (state with any important notes as required)

Question 5 (2 Marks):
What is a naming convention and why is it useful? Give two examples of naming conventions appropriate to database design.
WRITE YOUR ANSWER HERE (state with any important notes as required)
Part B: Practical Exercise / Evidence (20%)
Based on the class exercises and tutorials do the following practical. Download the Assessment II resource material zip file from eLearning.
You must submit:
? Digital copy of all pages
? Screenshot proof of the code (query part), PHP page published result. Tables and column structure in the database interface.
Task 1: (Total 6 Marks)
A. CREATE TABLES: students, subjects (presume each subject can be studied by many students). Ensure use of primary and foreign key that are underlined below.
Table: students (stuid, stuname, gender, age, email, mobile, sbjid, enroltimestamp)
Table: subjects (sbjid, sbjname, sbjcode, sbjdescription) (4 marks).
B. INSERT: You will need 15 students in the table, 3 subjects in the subject table.
(1 Mark).
C. Use your SQL Server to capture data dictionary of above tables. (1 Mark).
Task 2: (Total 10 Marks)
A. ER diagram of these two tables. (1 Mark)
B. Make a PHP page with query to list all students in the students table with all columns. (1 Mark)
C. Make a PHP page with query to list all students with “stuname, gender, age, sbjcode, sbjname”, the result is sorted with age. (1 Mark)
D. Make a PHP page with query to list all students who is older than 20. (1 Mark)
E. Make a PHP page with query to list all students who is older than 20 and also male in gender. (2 Mark)
F. Make a PHP student list page, with each student row made into a link. Once the link is clicked, it opens a PHP student details page. It must have all students’ detail as well as the subject he/she studies. (1 Mark)
G. Make a PHP student list page, at the end showing the average, youngest, and eldest age of the students by query. (1 Mark)
H. Make a PHP student list page of the students whose name is less than 10 characters in length. Make a PHP student list page of the students whose name’s first 10 characters include ‘u’. (1 Mark)
I. Make a PHP page showing students enrolled in this year using extract year function of timestamp. (Yes, that might be all the students, but that’s OK as only as the query is correct.). (1 Mark)
BONUS MARKS (Total 4 Marks) are given for the ability to complete all TASK 1 and 2, including where queried information matches the records.