ICTDBS502 Design a database
ICT50118 Diploma of Information Technology
Record of Assessment Outcome
Student name: Student ID:
Summary of evidence gathering techniques used for this assessment:
O Questioning O Project O Scenario
The evidence presented is:
O Valid O Sufficient O Authentic O Current
Unit result: Competent O Not Competent O
The student has been provided with feedback and informed of the assessment result and the reason for the decision.
Assessor name: Date assessed:
Assessor signature:
RTO contact info@scei.edu.au
Student declaration on assessment outcome
I have been provided with feedback on the evidence I have provided. I have been informed of the assessment result and the reason for the decision.
Student name: Date:
Student signature:
Reasonable Adjustment
Was reasonable adjustment applied to any of the assessment tasks? (please tick) Yes O No O
If yes, tick which assessment task(s) it was applied to.
O Questioning O Project O Scenario
Provide a description of the adjustment applied and why it was applied.
Name of assessor: Assessor signature:
Name of student: Student signature:
Student Declaration
Plagiarism constitutes extremely serious academic misconduct and severe penalties are associated with it. By signing below, you are declaring that the attached work is entirely your own (or where submitted to meet the requirements of an approved group assessment, is the work of the group). I certify that
? I have read and understood the Southern Cross Education Institute’s PP77 Assessment and submission policy and procedures.
? This assessment is all my own work, and no part of this assessment has been copied from another person.
? I have not allowed my work to be copied by another person.
? I have a copy of this work and will be able to reproduce within 24 hours if requested.
I give my consent for Southern Cross Education Institute to examine my work electronically by relevant plagiarism software programs.
Student signature: .......................................................... Date: ......../........../................
Assessment Outcome Summary and Feedback
Assessment Task 1 – Questioning
Submission No. Result Date
Assessed Assessor Name Assessor Signature
O First submission O S O NS
O Re-submission 1 O S O NS
O Re-submission 2 O S O NS
S = Satisfactory NS = Not Satisfactory
Feedback to the Student:
Assessment Task 2 – Project
Submission No. Result Date
Assessed Assessor Name Assessor Signature
O First submission O S O NS
O Re-submission 1 O S O NS
O Re-submission 2 O S O NS
S = Satisfactory NS = Not Satisfactory
Feedback to the Student:
Assessment Task 2 – Scenario
Submission No. Result Date
Assessed Assessor Name Assessor Signature
O First submission O S O NS
O Re-submission 1 O S O NS
O Re-submission 2 O S O NS
S = Satisfactory NS = Not Satisfactory
Feedback to the Student:
Student Instructions
This unit describes the performance outcomes, skills and knowledge required to build and implement a database using an established design.
To achieve competence the following assessment tasks must be successfully completed in the time allocated with the essential resources. Your trainer/assessor will give you the due date to submit the assessments and provide you with feedback after assessing your work.
Refer to the table below for the summary of Assessment Task for this unit:
Assessment Task Number Assessment Type Notes
1 Questioning To be completed in own time and submitted to the assessor by the due date
2 Project This assessment task will be completed in part in own time and under direct observation by the trainer/assessor in the classroom.
3 Scenario This assessment task will be completed in part in own time and under direct observation by the trainer/assessor in the classroom.
• Students may need to spend some hours outside the class hours without supervision to complete the assessments
• All assessment tasks must be satisfactory to achieve competency in the unit
• All the units of competency must be deemed competent to complete the qualification and obtain a certificate • The assessment requirement for this unit are presented clearly in the Unit of Competency located at: (http://training.gov.au/Training/Details/ICTDBS502) • In the Student assessment, you must be able to:
o Answer all questions o Complete all assessment tasks within the required timeframe o Complete all skills assessments tasks to a satisfactory standard
• The following resources are required for this assessment:
o Computer with internet access o Word processing and database software o CASE or diagramming software
Reasonable adjustment
For information on reasonable adjustment please refer to the student handbook located at:
http://scei.edu.au/?page_id=45
Record of Assessment Outcome
After all of the assessment evidence has been gathered from the assessment tasks for this unit/cluster of units of competency the Record of Assessment stating your result will be completed.
Information for the Student
If you do not understand any part of the unit or the assessments you are required to undertake, please talk with your trainer/assessor. It is important that you understand all of the aspects of the learning and assessment process that you will be undertaking. This will make it easier for you to learn and be successful in your studies.
Assessment Task No 1 Questioning
Instructions for
completion
• You are required to answer all questions in Assessment Task 1 – Questioning
• Responses to the questions may be typed or hand written
• If hand written, writing must be legible and in pen NOT pencil
• Use of correct grammar and spelling is required to demonstrate foundational
skills
• You must answer ALL questions in your own words or use appropriate referencing
• Use of APA referencing must be used where original sources other than your own have been used – to avoid plagiarism
• Write your name, student ID, the assessment task and the name of the unit of competency on each piece of paper you attach to this assessment document
• You answer all questions correctly to achieve a satisfactory result
• Submit to your trainer/assessor by the due date
DUE DATE The trainer/assessor will inform you of the due date
The due date for this task is _____________________________
1. What is a conceptual model and what are its features?
2. What does logical data model consist of? In your answer include the following: a. Identify the entities
b. Identify the attributes and data type for each entity
c. Identify the relationship between the entities
d. Normalise the attributes
3. Using diagrams and examples to support your answers, define the following: a. Primary key
b. Foreign key
c. Unique constraints
d. CHECK constraints
e. Referential integrity constraints
f. Database index
g. Data dictionary
4. What does a physical data model consist of? In your answer include the following
a. Assign the primary key and foreign keys to table
b. Refer to client’s business rules and identify the referential integrity constrains ( given an example) c. Add validation rules to database or tables
5. Explain data redundancy and provide two (2) examples of when used.
6. What is object model design concept? In your answer include the following: a. Data structures
b. Queries
c. Screens
d. Reports
7. What are the steps involved in designing the user interface? You answer should include the following: a. Type of interface
b. Fundamental parts of a user interface
c. User interface controls
8. Explain the following
a. What is SQL?
b. What can SQL do?
c. What is SQL statement?
d. How you retrieve data from the given table?
9. Explain the following terminology relating to database security
a. User authentication
b. Multiuser access
c. Encryption
d. Database profiles
10. What is the importance of the following as a database administrator? Answer should include the types of backups and recovery, as well as the devices that can be used.
a. Database backup and recovery requirements
b. Database backup and recovery procedures
c. Client feedback for a final approval
11. What is database scalability and how can this be achieved?
Assessment Task No. 2 Project
Instructions for completion • Read this project assessment task, complete all the requirements and submit to the trainer/assessor by the due date
• Where assessment tasks require the development of a document this must be typed (not hand written)
• Use of correct grammar and spelling is required to demonstrate foundational skills
• Use of APA referencing must be used where original sources other than your own have been used – to avoid plagiarism
• Write your name, student ID, the assessment task and the name of the unit of competency on each piece of paper you submit for assessment
Due Date • The trainer/assessor inform you of the due date
The due date for this task is _____________________________
This project will lead to Assessment 2 – Project in ICADBS412A Build a database.
Western Wear is a mail-order firm that offers an extensive selection of casual clothing for men and women. The company plans to launch a new website and the company wants to develop new set of product codes. Currently 650 different products exist, with possibility of adding more in the future. Many products come in various styles, sizes and colours. The project manager asked you to design a database that will provide individualised product codes to identify a specific item and its characteristics.
Part One – Determine database requirements
• Meet with the project manager of Western Wear (assessor) and conduct a user needs analysis to determine the database functionality
• Analyse the outcomes of the user needs analysis and identify the technical requirements e.g. platform, system, network
• Design a conceptual model of the database (version 1)and present it to the project manager (assessor) for review and feedback
• Analyse the feedback from the project manager and make modifications to the conceptual model as appropriate (version 2)
Part Two – Develop logical data model • Design a logical database model o Identify attributes and determine data types o Undertake normalisation of attributes o Develop entity relationship diagrams (ERP) o Document the attributes, normalised data and the ERP diagrams
• Forward the documentation to the project manager (assessor) for approval
Part Three – Design data structures
• Confirm primary and foreign keys for tables
• Identify the referential and database management system constraints and incorporate into database design
• Develop validation rules for data
• Design indexes and the data dictionary
• Document the database design
Part Four – Design queries, screens and reports
• Design the user interface (menus, input screens and outputs)
• Design queries to retrieve the data and output reports based on requirements
• Compare physical design with conceptual model or user needs analysis, identify discrepancies • Incorporate changes into design
Part Five – Design access and security systems
• Design the client authentication for database e.g. password and access codes
• Identify multiple user requirements
• Develop client access profiles using client business model
Part Six – Confirm database design
• Document database back-up and the recovery plan and procedures
• Submit database and documentation to the program manager (assessor) for feedback • Incorporate the feedback into the final database design
You are to submit the following on a CD/DVD to the trainer/assessor by the due date:
• User-needs analysis results and technical requirements (part one)
• Conceptual model of the database (all versions) (part one)
• Feedback from client on conceptual model of the database (part one)
• Logical data model and client approval (part two)
• Database structures including documentation of database design (part three)
• Design of user interface, queries, reports (part four)
• Outcome of comparison of design with conceptual model or user needs analysis (part four)
• Design of access and security systems (part five)
• Documentation of database back-up and recovery plan and procedures (part six)
• Client feedback on database design and final database design (part six)
Assessment Task No. 2 Scenario
Instructions for completion
•
• Read the scenario provided and complete all the requirements that follow Where assessment tasks require the development of a document this must be typed (not hand written)
• Use of correct grammar and spelling is required to demonstrate foundational skills
• Use of APA referencing must be used where original sources other than your own have been used – to avoid plagiarism
• Write your name, student ID, the assessment task and the name of the unit of competency on each piece of paper you submit for assessment
• Submit to the trainer/assessor by the due date
Due Date • The trainer/assessor inform you of the due date
The due date for this task is _____________________________
Scenario
Company Overview
The Good Drivers School of Motoring was established in Liverpool in 1990. Since then, the school has grown steadily and now has several offices in most of the main cities of Merseyside. The Director has provided the following brief description of how the Good Drivers School of Motoring operates.
Data Requirements
Each office has a manager, several instructors, and administrative staff. The manager is responsible for the dayto-day running of the office. Clients must first register at an office and this requires that they complete an application form, which records their personal details. Clients then book their lessons. A lesson is for one hour, which begins and ends at the office. A lesson is with a particular instructor in a particular car at a given time. Lessons can start as early as 8am and 5pm. After each lesson, the instructor records the progress made by the client and notes the mileage used during the lesson. Each office has a pool of cars, which are adapted for the purposes of teaching. The cars are regularly inspected by staff members in order to detect possible faults. Each car is allocated to a particular instructor. Once ready, a client applies to take a driving test at the local government testing centre. After the test is completed, the client returns to the school and the test results are recorded.
Tasks
1. Create a local conceptual data model for the one user view (the one modeling staff members, lessons, customers, driving tests, inspections, etc). State any assumptions necessary to support your design. Check that the local conceptual data model supports the required transactions using a pathway diagram.
2. Create and validate a local logical data model from the conceptual data model for the Good Drivers School of Motoring you created in task 1. Identify primary, alternate and foreign keys. Identify the referential integrity constraints, apply validation rules to the data and design data dictionary.
3. Create queries to retrieve the data from design database and design a user interface (input and out screens) for consumers to queries and data display.
4. Design the security system for the database based on the number of users including:
a. authentication
b. client access profiles
c. back- up and recovery procedures of the database design
5. Document the limitations of the database.
6. Submit tasks 1 to 5 to the trainer/assessor for feedback.
7. Incorporate the feedback into the final database design.
You are to submit the following on a CD/DVD to the trainer/assessor by the due date:
• Conceptual data model diagram (task 1)
• Logical data model diagram (task 2)
• Physical data model diagram (task 2)
• User interface diagram (task 3)
• Security system design and procedures (task 4)
• Report of database limitations (task 5)
• Documented feedback (task 6)
• Final database design (task 7)
Assessment Criteria
• identify and confirm client requirements for a database application • design database elements including:
- structure
- indexes
- queries
- screens
- reports
• design secure access to the database
• confirm final database design with the client.