Ass Ictdbs502 V3

ICTDBS502
DESIGN A DATABASE
ASSESSMENT
These material is developed by Enhance Your Future Pty Ltd for Australian Institute of
Science and Technology (AIST)
2
ICTDBS502 - Design a database Version 3
ASSESSMENT INFORMATION FOR
LEARNERS
Throughout your training we are committed to your learning by providing a training
and assessment framework that ensures the knowledge gained through training is
translated into practical on the job improvements.
You are going to be assessed for:

 Your skills and knowledge using written and observation activities that apply
to the workplace.
 Your ability to apply your learning.
 Your ability to recognise common principles and actively use these on the job.
All of your assessment and training is provided as a positive learning tool. Your assessor
will guide your learning and provide feedback on your responses to the assessment
materials until you have been deemed competent in this unit.
How you will be assessed

The process we follow is known as competency-based assessment. This means that
evidence of your current skills and knowledge will be measured against national
standards of best practice, not against the learning you have undertaken either recently
or in the past. Some of the assessment will be concerned with how you apply your skills
and knowledge in the workplace, and some in the training room as required by each
unit.
The assessment tasks have been designed to enable you to demonstrate the
requirements of the performance criteria and knowledge in this unit to successfully
demonstrate competency at the required standard.
Your assessor will ensure that you are ready for assessment and will explain the
assessment process. Your assessment tasks will outline the evidence to be collected and
3
how it will be collected, for example; a written activity, case study, or demonstration and
observation.
The assessor will also have determined if you have any special needs to be considered
during assessment. Changes can be made to the way assessment is undertaken to
account for special needs and this is called making Reasonable Adjustment.
What happens if your result is ‘Not Yet Competent’ for one or more
assessment tasks?
Our assessment process is designed to answer the question “has the desired learning
outcome been achieved yet?” If the answer is “Not yet”, then we work with you to see
how we can get there.
In the case that one or more of your assessments has been marked ‘NYC’, your trainer
will provide you with the necessary feedback and guidance, in order for you to resubmit
your responses.
What if you disagree on the assessment outcome?

You can appeal against a decision made in regards to your assessment. An appeal should only
be made if you have been assessed as ‘Not Yet Competent’ against a specific unit and you feel
you have sufficient grounds to believe that you are entitled to be assessed as competent. You
must be able to adequately demonstrate that you have the skills and experience to be able to
meet the requirements of units you are appealing the assessment of.
Your trainer will outline the appeals process, which is available to the learner. You can request a
form to make an appeal and submit it to your trainer, the course coordinator, or the
administration officer. The RTO will examine the appeal and you will be advised of the outcome
within 14 days. Any additional information you wish to provide may be attached to the appeal
form.
What if I believe I am already competent before training?

If you believe you already have the knowledge and skills to be able to demonstrate
competence in this unit, speak with your trainer, as you may be able to apply for
Recognition of Prior Learning (RPL).
Assessor Responsibilities
Assessors need to be aware of their responsibilities and carry them out appropriately.
To do this they need to:
4
 Ensure that participants are assessed fairly based on the outcome of the
language, literacy and numeracy review completed at enrolment.
 Ensure that all documentation is signed by the learner, trainer, workplace

supervisor and assessor when units and certificates are complete, to ensure
that there is no follow-up required from an administration perspective.
 Ensure that their own qualifications are current.
 When required, request the manager or supervisor to determine that the

learner is ‘satisfactorily’ demonstrating the requirements for each unit.
‘Satisfactorily’ means consistently meeting the standard expected from an
experienced operator.
 When required, ensure supervisors and learners sign off on third party
assessment forms or third party report.
 Follow the recommendations from moderation and validation meetings.
How should I format my assessments?

Your assessments should be typed in an 11 or 12 size font for ease of reading. You must
include a footer on each page with the learner name, unit code and date. Your
assessment needs to be submitted as a hardcopy or electronic copy as requested by
your trainer.
How long should my answers be?

The length of your answers will be guided by the description in each assessment, for
example:
Type of Answer Answer Guidelines
Short Answer 4 typed lines = 50 words, or
5 lines of handwritten text
Long Answer 8 typed lines = 100 words, or

1
10 lines of handwritten text = of a foolscap page
3
Brief Report 500 words = 1 page typed report, or
5
1
50 lines of handwritten text = 1 foolscap handwritten pages
2
Mid Report 1,000 words = 2 page typed report
Long Report 2,000 words = 4 page typed report
How should I reference the sources of information I use in my

assessments?
Include a reference list at the end of your work on a separate page. You should reference
the sources you have used in your assessments in the Harvard Style. For example:
 Website Name – Page or Document Name, Retrieved insert the date. Webpage
link.
 For a book: Author surname, author initial Year of publication, Title of book,
Publisher, City, State.
6
ASSESSMENT GUIDE
The following table shows you how to achieve a satisfactory result against the criteria
for each type of assessment task. The following is a list of general assessment methods
that can be used in assessing a unit of competency. Check your assessment tasks to
identify the ones used in this unit of competency.
Assessment Method Satisfactory Result Non-Satisfactory Result
You will receive an overall result of Competent or Not Yet Competent for this unit. The
assessment process is made up of a number of assessment methods. You are required to
achieve a satisfactory result in each of these to be deemed competent overall. Your
assessment may include the following assessment types.
Questions All questions answered Incorrect answers for one

correctly or more questions
Answers address the Answers do not address

question in full; referring the question in full. Does
to appropriate sources not refer to appropriate or
from your workbook correct sources.
and/or workplace
Third Party Report Supervisor or manager Could not demonstrate

observes work consistency. Could not
performance and confirms demonstrate the ability to
that you consistently meet achieve the required
the standards expected standard
from an experienced
operator
Written Activity The assessor will mark the Does not follow
activity against the guidelines/instructions
detailed
guidelines/instructions
Attachments if requested Requested supplementary

are attached items are not attached
All requirements of the Response does not

written activity are address the requirements
addressed/covered. in full; is missing a
response for one or more
areas.
7
Responses must refer to One or more of the

appropriate sources from requirements are
your workbook and/or answered incorrectly.
workplace Does not refer to or utilise
appropriate or correct
sources of information
Observation/Demonstration All elements, criteria, Could not demonstrate

knowledge and elements, criteria,
performance evidence are knowledge and
demonstrated at the performance evidence at
appropriate AQF level the appropriate AQF level
Case Study All comprehension Lack of demonstrated

questions answered comprehension of the
correctly; demonstrating knowledge required to
an application of complete the case study
knowledge of the topic questions correctly. One
case study. or more questions are
answered incorrectly.
Answers address the Answers do not address

question in full; referring the question in full; do not
to appropriate sources refer to appropriate
from your workbook sources.
and/or workplace
Practical Activity All tasks in the practical Tasks have not been
activity must be completed completed effectively and
and evidence of evidence of completion
completion must be has not been provided.
provided to your
trainer/assessor.
All tasks have been
completed accurately and
evidence provided for
each stated task.
Attachments if requested Requested supplementary
8
are attached items are not attached
9
ASSESSMENT COVER SHEET
Assessment Cover Sheet
Learner’s name:
Assessors Date
Name: Submitted:
The following questions are to be completed by the assessor:
Is the Learner ready for assessment? Yes No
Has the assessment process been explained? Yes No
Does the Learner understand which evidence is to be

Yes No
collected and how?
Have the Learner’s rights and the appeal system been fully
Yes No
explained?
Have you discussed any special needs to be considered

Yes No
during assessment?
The following documents must be completed and attached:
 Written Activity and Checklist
The learner will complete the written activity provided to S NYS

them by the assessor. The Written Activity Checklist will be
completed by the assessor.
 Practical Activity Checklist
The learner will demonstrate a range of skills and the

assessor will observe where appropriate to the unit. The S NYS
Practical Activity Checklist will be completed by the
assessor.
 Questions and Questions Checklist
The learner will answer a range of questions either S NYS

verbally or written. The Questions Checklist will be
completed by the assessor.
Learner Declaration
I agree to undertake assessment in the knowledge that information gathered will only
be used for professional development purposes and can only be accessed by the RTO
10
I declare that:
 The material I have submitted is my own work
 I have kept a copy of all relevant notes and reference material that I used in the
production of my work
 I have given references for all sources of information that are not my own,
including the words, ideas and images of others
Learner
Date:
Signature:
Result and Feedback
Feedback to Learner:
Overall Outcome Competent Not yet Competent
Assessor
Date:
Signature:
11
WRITTEN ACTIVITY
Your task is to write the answers to each of the following questions. Answers should
exceed 50 words but no more than 100 words for each questions:
1. Reflect on the process of data analysis, including its role in determining data
types and data structures, in query and in report design.
ANSWER:
Data analysing process involves investigating and organising data ensuring data
is able to be used for creating logical and operational database.
Structuring database represents way used by organisation. Data analysing

identifies how and what data would be used in database. The appropriate and
logical data types and data structures required to be able to address the
organisation’s requirement, are assisted to be determined. However, query
design and reports are based on data type and data structure design, but in
certain cases, these decisions are recommended to be reversed so that required
queries and reports ensure to be produced.
2. Describe data modelling as it relates to developing a conceptual data model.
ANSWER:
Data modelling is data model creating process for the data to be stored in a
database. This data model is conceptual representation of data objects,
associations among various data objects and rules. Modelling and conceptual
design practices must be applied to the analysed data so that it would be
determined that which type of data structures and types would likely to be best
supporting the queries and reports which are required by organisation. Data
model emphasize on what data is requires and how it should be organised.
Conceptual models are used to response toward questions which go into the
database.
12
3. Explain what data redundancy is, and how it might be identified.
ANSWER:
A condition which is to be created in database is likely called data redundancy in

which same piece of data is kept in two separate places. Redundant data is
associated as the data which is contained in more than one place in a database;
therefore, the database id larger than it should be. Data redundancy would likely
to be identified through normalising data, creating indexes, searching and data
modelling processes.
4. Describe the fundamentals of DBMS, particularly as these are applicable during

the design phase.
ANSWER:
The fundamentals of DBMS are;
 The physical databases require to be designed in alignment with the

specific DBMS to ensure data is able to be read and understood by DBMS.
 The database engine would be considered in the design phase of the

database to ensure data is able to be accessed correctly to utilize queries
and modified as per the user requirement.
 The database scheme is a structure of database and data included in

structure required and these are essential to be completed as per the need
of DBMS used to ensure two items are compatible to each other.
5. What is encryption and authentication, and how do these apply to database

security features?
ANSWER:
Database encryption is to involve the process of applying algorithms to the data

sent and received from database to make it able to be transformed into cypher
and encoded text in order to prevent it to be understood from hackers if it is
13
intercepted. The different encryption types are to be varied on the basis of type
of stored data and the security plan needs which apply to the organisation.
Authentication is the process of ensuring that database is accessed by only

authorised parties who are authorised to perform particular approved action in
database.
6. Explain the functions and the features of data types and data structures.
ANSWER:
Understanding the functions and features of data types and data structure helps
to be able to design a functioning database. Data structure allows data to be
structured as per the type of database in tables and would have definable
attributes and identifiers to organise data in database in order to enable queries
and searches to be conducted.
The Data types are identified to ensure designing database as per the data types’
requirements. The function of various data types is to ensure data is able to be
sequenced and structured to allows information to be received and used as
needed.
7. Describe the features and the functions of databases.
ANSWER:
Databases not only allow complex data to be stored in searchable, organised and
useable manner, also allow organisation’s data needs to be managed to make
them structured and able to be reported and queried.
Database enables data to be presented to ensure responding the queries and

displaying the information to user by user-interface to report information in
various formats. Database allows data to be held at secure place as per the
organisation’s security policy along with letting user to add, delete and update
data. Database manages the information as per business needs alongside having
different searching and indexing functions.
14
8. Describe the concepts of logical design, particularly as they relate to designing
data structures, queries screens and reports.
ANSWER:
The logical design of database is defined as involving the process of generating

multiple relationships among different data structures which would likely to be
based on the manner in which they are logically related to each other through
business process or requirements. Logical design concepts include organising
data into entities, relationships, and attributes. Logical design is to be essential to
data structures’ development, queries screens and reports and can be used to set
data out and make decisions towards data would be logically used.
9. Explain the concepts of object model design, particularly as they relate to the
design of data structures, queries, screens and reports.
ANSWER:
Object-oriented programming is a concept based on objects’management which

contain data a form of fields known as attributes and code. Thereafter, objects
could be programmed with variables to define how the objects pertain to and
converse with each other. Class-based object-oriented programming languages
require to be managed to use class variables. During using these concepts to
design data structures, queries, screens, and reports, the data is to be categorised
into the object group and behaviours. The data is structured as per the rules
which allow the queries, screens and reports to be written in object-oriented
programming language.
10. In relation to databases, explain the term ‘scalability’.
ANSWER:
Scalability is associated as the ability of something to alter the size in response to

requirement. Scalability is of major importance to the management of databases,
and there are many ways by which it is managed. The scalability of databases is
to be managed and controlled by set size databases, automatically scalable
databases, partitions, vertical or horizontal scaling and the use of distributed
15
computers or servers.
16
WRITTEN ACTIVITY CHECKLIST
Written Activity Checklist
For this assessment, the learner must complete the written activity.
The written activity is a stand-alone activity that will allow the learner to display the
requirements of the performance criteria and knowledge evidence in this unit that are
essential when deciding overall competency.
Learner Name:
Assessor Name:
Has the learner

satisfactorily completed
the written activity?
Yes No
Did the Learner provide evidence of their ability to:
Reflect on the process of data analysis, including its role

in determining data types and data structures, in query  
and in report design.
Describe data modelling as it relates to developing a

 
conceptual data model.
Explain what data redundancy is, and how it might be

 
identified.
Describe the fundamentals of DBMS, particularly as

 
these are applicable during the design phase.
What is encryption and authentication, and how do

 
these apply to database security features?
Explain the functions and the features of data types and

 
data structures.
Describe the features and the functions of databases.  
Describe the concepts of logical design, particularly as

they relate to designing data structures, queries screens  
and reports.
17
Explain the concepts of object model design,
particularly as they relate to the design of data  
structures, queries, screens and reports.
In relation to databases, explain the term ‘scalability’.  
Result  Satisfactory  Not Yet Satisfactory
Assessor’s Signature: Date:
18
PRACTICAL ACTIVITY
The following task is to be demonstrated and assessed in conditions that are safe and
replicate the workplace. Noise levels, production flow, interruptions and time variances
must be typical of those experienced in the database field of work, and include access to:
 Equipment and materials
 Current industry accepted database software
 Network or other system for remote or multi-user access
 Business requirements
 Computer-aided software engineering (CASE) or diagramming software
For this task you are to complete the following activity to demonstrate your ability to
design a database.
Prepare for the task by completing the following actions to determine the database
requirements:
1. Meet with the client, and conduct a user-needs analysis to determine database
functionality. Provide the documented user-needs analysis.
2. Analyse the results of the user-needs analysis to identify the technical
requirements. Record this analysis, including the technical requirements.
3. Develop a conceptual model of the database, and submit this to the client for
review.
4. Evaluate the client feedback on the conceptual model, making changes as
required. Document this evaluation, and record any changes made.
Develop a logical data model through completion of the following steps:
5. Identify the attributes and determine the data types.

6. Undertake the normalisation of attributes.
7. In order to clarify the cardinality of relationships, develop an entity-relationship
(ER) diagram.
19
8. Accurately document the attributes, normalised data, and the ER diagram, and
forward this documentation to the client for confirmation. Provide evidence of
the client confirmation.
Complete the following actions to design the data structures:
9. Confirm, and record, the primary and foreign keys for the tables.
10. Review and record the client business rules.
11. Identify, and document, the referential integrity constraints.
12. Establish, and document, the database management system constraints, and
incorporate these into the database design.
13. Develop, and document, the validation rules for data.
14. Design the indexes and develop the data dictionary.
15. Accurately document the database design.
Design the queries, screens and reports through completion of the following steps:
16. Design and document the user interface for the database, including the menus,
input screens and outputs.
17. Based on the requirements, design, and document, the queries and the output
reports.
18. Compare the physical design with the conceptual model, or with the user-needs
analysis, and incorporate changes, as required. Document this comparison,
noting any changes incorporated.
Perform the following actions to design the access and security systems:
19. Review the business security plan as a basis for commencing the access and
security design. Document this review.
20. Design and document the password and access system for the database.
21. Identify, and record, multiple-user requirements.
22. Using the client business model, develop the client access profiles.
20
Confirm the database design with the client through the following actions:
23. Identify the requirements for database backup and recovery. Record these
requirements.
24. Develop, and document, the procedures for database backup and restoration.
25. Submit the database, and documentation, to the client for their final approval.
Provide the final approval obtained.
21
Design a database
 Determination of the database requirements
 The client is met, and user-needs analysis is to be conducted in

order to determine database functionality:
A collection of data arranged in structured system is called database which allow

piece of data to be found out, selected and analysed as per prefixed aspects of
each piece of data. In order to find out what functionality would be needed for
database, the personnel and the administrators are required to meet the clients
and conduct user-needs analysis which would the best way to get information
from clients. The popular databases are commercial off-the-shelf COTS database
packages, object-relational databases, proprietary databases and relational
databases.
User-needs analysis would help to find out what elements and functionality are
required to be present in design of database as per the needs of client. Once the
needs of client have been identified, information can be analysed by interpreting
each need as a function or design aspect of the proposed database.
 The results of the user-needs analysis are to be analysed to identify

the technical:
The result or information collected from user-needs analysis would be used for
spotting out the technical requirements which are defined as the elements
essential to be present in sequence for the database to function according to the
client requirements. A user-needs analysis is also expected to disclose what
technical requirements exists based on the knowledge of designer toward
database design and construction together with information collected during
user-needs analysis. Afterward, correlations among technical aspects of database
design and construction and requirements are made.
User-needs analysis was to be done in which found that client disclosed that it
requires a database allowing details related mail orders to an online retail store
22
to be held in along with including all relevant information about the customers,
the product that they have ordered and shipping information. Database is to
accessed by internal staff and external vendors. In this case the subsequent
technical requirements are to be determined by designer;
 Required Storage space amount,
 What type of security is to be included for external parties and
organisational departments,
 How small data is to be broken down,
 Multiple languages are needed or not in the design of database,
 What relationship would be in data groups to their users and uses.
 A conceptual model of the database is to be developed and

submitted to the client for review:
Conceptual model of database:

The conceptual model would be created with stakeholders by means of user-
needs analysis. It is to be the first model in whole process of data modelling and
used to develop physical data model and answer the questions which are to be
concerned what would go into the database and information would be used to
determine how the information would be implemented into a full DBMS.
Submitting the conceptual database to client for review and review the
model:
23
The conceptual database model is to be submitted to the client for the review in
order to ensure the data and relative manner in which the data is arranged is
congruent with the requests and needs of the client. In review, conceptual model
is being presented to the client, client requests changes and further clarification
is to be made. After making alterations, updated model again is submitted for
further review. This process would be repeated until client will accept the
conceptual model.
 The client feedback on the conceptual model is to be evaluated for

making changes as required and this evaluation is to be
documented and changes are to be recorded:
Evaluating feedback:
A model is to be submitted to a client. Client gives feedback. Then appropriate
measures are taken to address the feedback. The process of evaluating feedback
consider to evaluate how the feedback pertain to conceptual model which would
allow to represent what data would be included in database; however, not fully
demonstrate how data would be handled. Effective communication would be
required to avoid errors that negatively effect the outcomes of the database as
well as damage to the relationship among client and designer.
Making the changes in conceptual model:

Alteration would require to be made in conceptual model. As per the review
results, adjustment and changes are made in definition of entities, relationships
among entities, metadata and data being removed or added to the model and
finally submitted to client for review.
 Development of a logical data model
 The attributes are to be identified along with determining the data

types.
24
Identifying the data type:
Before identifying attributes, the function and feature of different data type and
data structure which is a manner in which data is designed would require to be
understood. Afterwards, attributes which are component in the database like
table, a column or row in a table and a field would be identified in order to use
this information to assist design the data structures of the database.
Determining the data type:
A range of data type which are required to be included in design of database

would be determined in order to design database according to needs. The type of
data type can be numeric, date and time, character and string, Unicode character
string, binary data, and miscellaneous data type. Function of datatype is to
ensure data to be sequenced and structured in a manner which allows
information to be retrieved and used if required.
 The normalisation of attributes is to be undertaken:

25
Undertaking attributes normalisation:
.Before undertaking normalisation of attributes, the meaning of attribute

normalisation would be understood. Attribute normalisation is the process of
organising the attributes to ensure consistency of entity type and relationship
based on these entity type. Normalisation of attributes are to be undertaken to
enable the functionality and integrity of database design. Attribute normalisation
would allow data redundancy to be minimised. A range of rules would consider
to be applied in the process of attribute normalisation to ensure consistency and
to have integrity.
Recording data normalisation rules:

Data normalisation rules would be recorded along with ensuring all the
attributes are effectively normalised for a compliant and error-free database
structure in order to make it able to be queried effectively.
 An entity-relationship (ER) diagram is to be developed in order to

clarify the cardinality of relationships:
Entity relationship model:
An entity-relationship model exist in database is a diagram and designed to form

relationships. ER diagram would explain the subsequent items about data:
26
The ER diagram would look like as below:
Relationships’ cardinality:
Cardinality of relationship is to be rated and explained through a number of

entities which would be included in the data and high cardinality would contain
huge amount of unique entities which would be identified by considering the
subsequent relation type like Many-to-many, Many-to-one, One-to-many and
27
One-to-one relation type which would be as below.
 The attributes, normalised data, and the ER diagram are to be

accurately documented and this documentation is to be forwarded
to the client for confirmation along with providing evidence of the
client confirmation:
Documenting the data:
All the database attributes, normalised data and the ER diagram would be
documented as per the organisational standards. Attributes are to be
documented to ensure they are able to be used in decision making. Normalised
data would be documented by using unified business modelling language to
communicate normalised data. ER diagram would be documented by applying
the agreed ER rules and format along with defining the cardinality of
information.
Forwarding documentation to client for confirmation:

Once documentation is complete, documentation would be checked and assessed
to ensure it is correct and required information is effectively contained in
documentation according to documentation standards prior to submit it for
confirmation. Finally, after ensuring all documentation is according to standards,
the documentation would be submitted to the client for confirmation by using
agreed method in order to ensure documentation is able to be reviewed. In this
process, client receive documentation and confirm the database design and
approve the next development stage.
 Designing of data structures

28
 The primary and foreign keys for the tables are to be Confirmed
and recorded:
Before confirming the keys, primary key and foreign key would be understood as
it is a table attribute which is used to uniquely identify the records. Once
understand the meaning of both keys, they need to be confirmed for the tables.
Various ways used for structuring data would be considered along with those
methods which are potential to be used so that primary and foreign keys can be
confirmed which are required to be used. then primary key and foreign keys are
to be recorded in DBMS.
Foreign key in database management system;
 The client business rules are to be reviewed and recorded:

Client business rules which are to apply to the database design would be
reviewed. A client business rule is a statement that would apply a constraint to a
specifics aspect of the database which is to be designed. This client business rule
would be applied to elements allowable in a specific field, field specification
requirements and relationship characteristics. All business rules provide by
client would be reviewed before recording them into database in order to ensure
29
appropriate database design decision would be made. And finally, the client
business rules would be recorded in DBMS.
The following statement is an example of a typical business rule:

A SHIP DATE cannot be prior to an ORDER DATE for any given order.
This particular business rule imposes a constraint on the Range of Values element of
the field specifications for a SHIP DATE field. It will help ensure that the value of SHIP
DATE is meaningful within the context of a sales order. Without this constraint, you
could enter any date into the field (including one prior to the ORDER DATE), making
the SHIP DATE field's value absolutely meaningless. The business rule is what makes
the SHIP DATE field's value contextually meaningful.
 The referential integrity constraints are to be identified and

documented:
Before identifying the referential integrity constraints, referential integrity

would be understood and then referential integrity constraints would be
necessary to be grasped. Referential integrity is a requirement which ensure
relationships are consistent. Referential integrity constraints are limitations
applied to the manner in which entity are defined. All of the relationships which
are required along with interactions would be assessed to ensure referential
integrity constraints are to be identified. After identifying the constraints, all the
referential integrity constraints would be documented in system.
 The database management system constraints are to be established

and documented, and incorporated these into the database design:
For establishing the database management system constraints and incorporating

them into database design, database management system fundamentals would
be described during the design phase. DBMS is made of physical database, the
database engine, and the database scheme.
 The physical database would be designed in alignment with the
specific DBMS to ensure data is able to be read and understood by
DBMS.
30
 The database would be considered in the design phase of the
database to ensure that data is ready to be accessed correctly by
using queries and modified as per requirement of the client.
 The database scheme which is a structure of database and data
contained in the structures would be important to ensure these are
completed according to the needs of DBMS so that two items can be
ensured to be compatible to each other.
DBMS would perform the subsequent action like creating database data, reading
database data, updating or deleting database data, managing data, viewing data.
DBMS constraints are any limitations that would be applied to database
management due to DBMS which would include features, functions, applications
and allowable actions.
These DBMS constraints would be identified in order to manage them during
design phase; however, some critical situation would force us to select new
DBMS.
 The validation rules for data are to be developed, and documented:
After the establishing DBMS, validation rules would be developed and

documented. The validation rules would be object, fields and case milestones
which would be used to verify and confirm data meets essential standards.
Validation would be created and applied for all instanced where forms are used
to enter data into the database to ensure information is saved to the database by
users would meet the business rules and would be able to be stored and used in
the database in an appropriate manner. Finally, validation rules would be
documented.
 The indexes are to be designed and the data dictionary is to be

developed:
Designing database indexes:

To improve the speed of the data retrieval processes, database index would be
used; therefore, indexes would be designed in order to enable search functions to
be applied to the index tables. Indexes would create parameters to be applied to
31
searches. Unique identifiers as keys would be used by indexes to apply categories
to the data. Indexes would be in logical manner would meet the usage needs of
the client.
Developing the data dictionary:

Data dictionary would be developed which is a document and would be used to
define the elements of the data which would allow data which is stored in the
database able to be identified and categorised. Data dictionary would contain
metadata, table, description, name, relationships.
 The database design is to be documented:
Different types of information would be considered to be documented along with

documenting the appropriate methods used to achieve this. Documentation
would include design parameters documentation, relationship and data
modelling, data structure diagrams, definitions and rules, reports on
functionality, specifications sheets, and user documentation. Documentation
would meet organisational standards including; format, layout, level of detail,
version control, style guides, and design standards.
 Designing of the queries, screens and reports
 The user interface for the database, including the menus, input
screens and outputs are to be designed and documented:
User-interface would be designed by considering the subsequent interface

elements including input controls, navigational components, informational
components and containers. While designing the user interface, menus would be
designed in logical manner to make it easy to navigate to different area of
functionality and control. Moreover, input screens would be configured to ensure
user is able to input queries, requests and commands to achieve desired output.
Furthermore, to meet the specific user needs of the organisation output for user
interface would be considered including type of output, format and visual display
32
requirements. All the best practice elements would be considered as a part of
interface design.
 The queries and the output reports are to be designed and

documented based on the requirements:
Designing Queries based on requirements:
Queries would be designed to collect information from the database on the basis
of requirement of client. Queries would be written according to the programming
language and the type of database. They would be based on data structure, type
and other factors applied during design and building phase. Queries would be
made able to meet the requirement of functionality based on requirement. The
SQL SELECT would be used to retrieve data from the database where statement
would be selected in SQL to allow the queries needed to collect the data rom
database in order to show it on user interface.
SELECT Column Example

The following SQL statement selects the "CustomerName" and "City" columns
from the "Customers" table:
Example
SELECT CustomerName, City FROM Customers;
The selection of query would be tailored by using parameters and would be

structured as below:
33
Designing output report based on requirement:
Output reports would be designed based on the requirements that apply to the
particular database and context of its application in the organisation. output
reports would be designed in order to ensure user can request reports which
would show the information required to complete the necessary work functions.
Designing reports would lead us to plan what reports types would be needed,
how they would be structured and requested and report output format and
location and ensure reports would be based on logical design.
 The physical design is to be compared with the conceptual model,

or with the user-needs analysis, and changes are to be incorporated
as required. This comparison is to be documented along with
noting any changes incorporated:
Comparing the physical design with the conceptual model and user-need
analysis:
Physical design including tangible component of database like data type, data
structures, user interface, queries, reports, functions would be compared with
conceptual model to ensure it is compliant with client needs. Physical design
would be compared with user-need analysis in order to ensure it is compliant
with the identified user needs. Physical design elements would be assessed
which have been developed in response to the development of conceptual model
or the user needs analysis so that similarities or differences with the
requirements that apply can be identified.
Incorporating change as required:

34
Required changes would be incorporated after comparison in case there would
be some inconsistencies among conceptual model or user needs analysis
information by identifying them and planning changes and undertaking as result.
While identifying the changes, changes would be identified from the comparison
which was done of physical database design to conceptual design and user-needs
analysis. Afterward, planning would be done to make changes in physical model
in order to make it compliant with client needs. It would be decided how and
what would be changes?
Completing changes in a controlled manner:

Changes would be made to the physical database in a controlled manner that
would apply appropriate version control and changes management techniques to
ensure work is able to be rolled back if necessary. All the changes would be
tracked and recorded and documented along with the physical model with
incorporated changes.
 Designing the access and security system
 The business security plan is to be reviewed as a basis for

commencing the access and security design. This review is to be
documented:
Business security plan would be reviewed as a foundation for the commencing of

the access and security design. The security plan which applies to the security of
ICT assets held in the organisation would be accessed and reviewed to ensure
that all its needs are able to be identified and incorporated into planning process.
These security plan would be authentication, authorisation and integrity, privacy,
and security objectives of the organisation. The access design would meet the
parameters toward who can access data types and perform different actions in
the database. It would ensure how different security and ICT technologies and
practices are ensured.
35
Security design would be documented along with the review it would ensure that
review is stored in database, when it is accessed, and when it is transported and
output.
 The password and access system are to be designed & documented

for the database:
Passwords and access system which are a type of access and authorisation
system would be used to ensure only authorised and authenticated users can be
able to access the system. For designing password and access system, user
profiles and permission and authentication procedures and requirements would
be identified passwords would be developed to meet the security plan
requirements.
These passwords and access system would be documented along with the
process which is to be used to create password and access system.
 Multiple-user requirements are to be identified and recorded:
Multiple-user requirements would be identified in order to use it to plan the

access and password system design require to protect database from
unauthorised access and actions. Therefore; multiple-user requirements would
be defined in order to make user profiles able to be developed and implemented
with the required permission. The multiple requirements would be type of data
they can see, queries they can make, reports they can access, adding information
to the database, removing information from the database, and editing
information held in the database.
The multiple user requirements would be identified and recorded to ensure

information is used to develop client access profiles as required.
 The client access profiles are to be developed by using the client

business model:
Client access profiles would be developed by using client business model for each
of the different types of personnel in the organisation. These accounts would own
various permissions and authorities applied to them to ensure security of data to
be maintained as per the needs of the organisational security plans. User account
36
would be grouped on the basis of access requirements, permissions and
authorities.
 Confirmation of database design with the client
 The requirements are to be identified and recorded for database

backup and recovery:
Backup and recovery requirements will be identified by the access and

assessment of a range of information, including security plan, disaster recovery
plans, organisational policies and procedures and compliance plans. Database
backup and recovery requirements would be recorded and these documents
would be reviewed in order to ensure backup and recovery requirements are
able to be identified.
 The procedures for database backup and restoration are to be

developed and documented:
Database backup and restoration procedures would be developed which would

be compliant with the requirements applied to the organisation by taking
subsequent factors into consideration including; type of backup, frequency of
backup, who will complete the backup?, how will the backups be completed?,
where will backups be located, recovery points that will be created and recovery
data storage locations.
Then requirements procedures for backups and recovery would be documented
which would include clear and specific, detailed and comprehensive and
appropriately sequenced.
 The database, and documentation are to be submitted, to the client for their
final approval. The final approval obtained is to be provided:
The database and documentation would be submitted for approval as an

application and fully functioning and installed item and all necessary access
37
details would be ensured to be provided which are needed to access and approve
the database. These documents would be design documents, user instructions,
help manuals, specifications sheets, process maps.
Once database and database documentation is to be provided to the client, time

would be allotted to review the database and documentation and then final sign
would be obtained on the completed project as per the organisational
procedures.
38
39
40
PRACTICAL ACTIVITY CHECKLIST
*Multiple copies of the checklist will need to be printed if the activity is to be performed
more than once.
Practical Activity Checklist
For this assessment, the learner must complete the practical activity.
The practical activity is a stand-alone activity that will allow the learner to display the
knowledge and performance evidence that is essential when deciding overall
competency.
Learner Name:
Assessor Name:
Has the learner

satisfactorily completed
the practical activity?
Yes No
Did the Learner provide evidence of their ability to:
Meet with the client, and conduct a user-needs analysis

to determine database functionality. Provide the  
documented user-needs analysis.
Analyse the results of the user-needs analysis to

identify the technical requirements. Record this  
analysis, including the technical requirements.
Develop a conceptual model of the database, and  

submit this to the client for review.
Evaluate the client feedback on the conceptual model,

making changes as required. Document this evaluation,  
and record any changes made.
Identify the attributes and determine the data types.  
Undertake the normalisation of attributes.  
In order to clarify the cardinality of relationships,  

develop an entity-relationship (ER) diagram.
41
Accurately document the attributes, normalised data,
and the ER diagram, and forward this documentation to  
the client for confirmation. Provide evidence of the
client confirmation.
Confirm, and record, the primary and foreign keys for  

the tables.
Review and record the client business rules.  
Identify, and document, the referential integrity  

constraints.
Establish, and document, the database management

system constraints, and incorporate these into the  
database design.
Develop, and document, the validation rules for data.  
Design the indexes and develop the data dictionary.  
Accurately document the database design.  
Design and document the user interface for the

database, including the menus, input screens and  
outputs.
Based on the requirements, design, and document, the  

queries and the output reports.
Compare the physical design with the conceptual

model, or with the user-needs analysis, and incorporate  
changes, as required. Document this comparison, noting
any changes incorporated.
Review the business security plan as a basis for

commencing the access and security design. Document  
this review.
Design and document the password and access system  

for the database.
Identify, and record, multiple-user requirements.  
Using the client business model, develop the client  

access profiles.
42
Identify the requirements for database backup and  
recovery. Record these requirements.
Develop, and document, the procedures for database  

backup and restoration.
Submit the database, and documentation, to the client

for their final approval. Provide the final approval  
obtained.
43
QUESTIONS
The following questions may be answered verbally with your assessor or you may write
down your answers. Please discuss this with your assessor before you commence. Short
Answers are required which is approximately 4 typed lines = 50 words, or 5 lines of
handwritten text.
Your assessor will take down dot points as a minimum if you choose to answer them
verbally.
Answer the following questions either verbally with your assessor or in writing.
1. Reflect on conducting a user-needs analysis with a client to determine

database functionality.
ANSWER:
User-needs analysis is to be the process of identifying what design elements

and functionality require to be available, from a design perspective, based on
the expressed client’s requirements. Different tools and methodologies like
interviews or client documentation to find out client’ requirements are to be
applied.
2. List five (5) examples of technical requirements you might identify by

analysing the results of the user-needs analysis.
ANSWER:
 Amount of storage space required for database
 Type of security to be included for different parties
 How small to break the piece of information.
 Whether or not multiple languages are to be needed in design of database
 What relationships exist among data groups in relation to their user and uses.
3. What is a conceptual model of a database?
ANSWER:
44
Developing a conceptual model of a database is a process of forming a visual
model of databases. Mainly, the conceptual models demonstrate what data
objects prove to be included in a database, how the objects pertain to each other
and what rules run the data objects.
4. Discuss the basic components of reviewing a conceptual model with a client.
ANSWER:
These components are;
 Reviews would be conducted in face-to-face format or remotely.
 What is expected to be involved in the review should be established prior

to commencement of review process.
 Who are likely to be included in the review process will depend on size,
scope and stage of review process.
5. What types of changes might be made in response to evaluated client

feedback?
ANSWER:
In response to evaluated client feedback, the subsequent changes are to be made

like definition of entities, relationships among entities, metadata and data to be
removed and added to the model.
6. What is the normalisation of attributes?
ANSWER:
Attributes normalisation is to be the process od organising the attributes to

ensure that entity types are consistent along with ensuring all relationships
based on these are to be consistent with the purpose of preventing data
redundancy.
45
7. What are the relationship types you might clarify through developing an ER
diagram?
ANSWER:
The relation types which would be clarified through developing an ER diagram

include;
 The first relation type would be Many-to-many.
 The second relation type is to be Many-to-one.
 Third and fourth relation types are to be One-to-many and One-to-one

respectively.
8. What should you consider and apply when documenting the attributes,
normalised data, and the ER diagram?
ANSWER:
All attributes should be documented to make them able to be used to make

necessary decisions. Normalised data would be documented in unified business
modelling language.
During documenting ER diagram, redundant entities should be eliminated

alongside ensuring entities to be labelled and colour to be used to highlight the
important portions.
9. Why is it important to forward the documentation to the client for their

confirmation?
ANSWER:
Just after completing documentation design, it would be essential to forward the

documentation to the client for confirmation that design is as desired and
expected. It also be required to confirm that database design is approved for the
next stage of development.
10. Explain primary and foreign keys.

46
ANSWER:
The first key is primary key which is used to identify data in database and must
be one of attributes of record which would be used as suitable unique key for
each record. And foreign key connects the primary key from another entity to an
attribute in current entity.
11. When taking about databases, what are client business rules?
ANSWER:
The client business rules are the statements that apply a constraint to specific
aspects of the database that is to be designed. They can be developed for any
aspect of database and applied to elements allowable in a specific field, field
specification requirements and relationship characteristics.
12. Explain what referential integrity constraints are.
ANSWER:
Referential integrity constraints are likely to be the limitations which are applied
to manner in which entity are defined to ensure they are always in agreement
and consistent. All of the relationship requirements and interactions are required
to be assessed to ensure necessary referential constraints are able to be
identified.
13. What are database management system constraints?
ANSWER:
Database management system constraints are any limitations that would be

applied to database management due to DBMS’ features, functions, applications
and allowable actions. Identifying constraints that apply are ensured to make
them able to be managed during design phase; however, in some cases, a new
database management system is applied.
47
14. Discuss developing validation rules for the data.
ANSWER:
To prevent unusable poorly formatted data from being entered into database,
validation rules are made which are defined as the rules to be used to verify and
confirm that data meets the essential standards. Validation rules be generated
and applied where forms are used to enter data into database.
15. What are indexes?
ANSWER:
Database indexes, a kind of data structure which are able to be used to improve
the pace of data retrieval processes required as a part of database use, enable
search function to be applied to index table and will use unique identifiers like
keys to apply categories to the data.
16. What information would you include in the data dictionary developed?
ANSWER:
The data dictionary is to be designed as a document used to define the elements

of data in order to ensure data is able to be identified and categorised and likely
to contain the information like; metadata, table, description, name and relation of
each data component.
17. What information should be included when documenting the database

design? List at least five (5) examples
ANSWER:
During documenting the database design, the following information like; design
parameter documentation, relationship and fata modelling, data structure
diagrams, definitions and rules, reports on functionality, specifications sheets
and user documentation are to be included. Different type of information and
appropriate methods are considered to be used to achieve this.
48
18. What will need to be included when designing the user interface for
database?
ANSWER:
During designing user interface for database, the practice elements are
considered like; keeping the interface simple, creating consistency and using
common UI elements, being purposeful in page layout, using colour and texture
strategically, applying typography to form hierarchy and clarity, ensuring system
converses what’s happening and thinking about the defaults.
19. List five (5) examples of the areas of requirements you might need to
consider when designing queries.
ANAWER:
During the time queries are to be being designed, the subsequent requirements
are considered to be met for functionality which would be based on the identified
requirements and these requirements would be; application, business, database,
network, people in the organisation, platform, system, programming language
and type of database.
20. What are the typical requirements you will base and design your output
reports on?
ANSWER:
Typical requirements which would likely to be included in the designing output

reports are to involve; what report types will be needed, how the report would
be structured as well as requested, and report output format and location. Report
should be based on logical design.
21. Why is it necessary to compare the physical design with the conceptual
49
model, or with the user-needs analysis?
ANSWER:
Necessary comparisons are required to be considered in order to assess the

physical design elements which have been developed in response to the
conceptual model’s development or user needs analysis’ development so that
similarities or differences with the requirements that apply could be identified.
22. Briefly reflect on the process for incorporating changes into the database.
ANSWER:
After identifying required changes through comparing physical database design

with conceptual design and user needs analysis, changes to the physical model
are planned to make them compliant with client needs by deciding how and what
would be changed. Finally, changes are made in controlled manner and by
applying appropriate version control and change management techniques,
changes are tracked and recorded.
23. What types of information will you identify when reviewing the business
security plan as a basis for commencing the access and security design?
ANSWER:
A security plan is a part of every organisation that outlines the standards and
methods which are to be applied to the security of the ICT assets held in a
organisation. The information like authentication, authorisation and integrity,
privacy and security objectives of the organisation etc. are to be included in
security plan.
24. What information would you need to identify in order to design a password
50
and access system for the database?
ANSWER:
To design a password and access system: an access and authorisation system

which ensures only authorised user can access the system, the subsequent
information is required to be identified like profile and permissions of user, and
authentication procedures and requirements; however, passwords which meet
the security plan requirements are recommended to be developed.
25. Outline three (3) examples of the types of multiple-user requirements you
might identify.
ANSWER:
The types of multiple-user requirements may include; the types of data they can
see, queries they can make, reports they can access, adding and removing
information from the database, editing information which is kept in the database.
Multiple-user requirements are defined to make user profile able to be developed
and implemented with needed permission.
26. What information is it essential to consider and use to develop client access
profiles?
ANSWER:
In order to create a client access profiles, it would likely to be essential to ensure

that all of the various multiple user requirements are able to be identified and
recorded hence, identified information could be utilized to develop client access
profiles as required. User accounts be grouped based on access requirements,
permissions and authorities.
51
27. What are four (4) of the types of information you would assess to identify the
database backup and recovery requirements?
ANSWER:
Database backup alongside recovery requirements would likely to be defined by

the access as well as assessment of various information and this information
would include the security plans, disaster recovery plans, organisational policies
and procedures and compliance plans. These documents are to be reviewed to
ensure backup and recovery requirements are able to be identified.
28. What do you need to ensure about the procedure documentation developed
for the database backup and restoration?
ANSWER:
After each and every requirement for the backup and recovery procedures have
been created, the procedures are essential to be documented and the procedure
of documentation would require to be clear and specific. They should be detailed
and comprehensive. They are recommended to be appropriately sequenced.
29. What evidence should you obtain for client final approval of the submitted
database and documentation?
ANSWER:
For client final approval of database documentation, it would be required to

obtain design documents and user instructions. Not only, help manuals but also
specifications sheets are to be obtained. Process maps are also received.
52
QUESTIONS CHECKLIST
Questioning Checklist
The assessor needs to indicate whether they have conducted the questioning as
written questions or as verbal questions by ticking the box below.
 Written Questioning  Verbal Questioning

The assessor must provide evidence with the assessment tool. For written questions,
the assessor must provide the learner’s original written responses. For verbal
questioning, the assessor must provide dot points as a minimum on the learner’s
responses.
For details on how to conduct and contextualise this form of assessment, please refer
to the questioning checklist explanation in the assessor guide.
Learner’s name:
Assessor’s name:
If questions are answered verbally, the assessor must write Satisfactory

detailed notes on the learners responses. If answers are recorded response
separate to this document, they must be attached to the
assessment documents. Yes No
Reflect on conducting a user-needs analysis with a client to

1 determine database functionality.  
List five (5) examples of technical requirements you might

2 identify by analysing the results of the user-needs analysis.  
What is a conceptual model of a database?

3  
Discuss the basic components of reviewing a conceptual

4 model with a client.  
What types of changes might be made in response to

5 evaluated client feedback?  
53
What is the normalisation of attributes?
6  
What are the relationship types you might clarify through

7 developing an ER diagram?  
What should you consider and apply when documenting the

8 attributes, normalised data, and the ER diagram?  
Why is it important to forward the documentation to the

9 client for their confirmation?  
Explain primary and foreign keys.

10  
When taking about databases, what are client business rules?

11  
Explain what referential integrity constraints are.

12  
What are database management system constraints?

13  
Discuss developing validation rules for the data.

14  
What are indexes?

15  
What information would you include in the data dictionary

16 developed?  
What information should be included when documenting the

17 database design? List at least five (5) examples  
54
What will need to be included when designing the user
18 interface for database?  
List five (5) examples of the areas of requirements you might

19 need to consider when designing queries.  
What are the typical requirements you will base and design
20 your output reports on?  
Why is it necessary to compare the physical design with the

21 conceptual model, or with the user-needs analysis?  
Briefly reflect on the process for incorporating changes into

22 the database.  
What types of information will you identify when reviewing

23 the business security plan as a basis for commencing the  
access and security design?
What information would you need to identify in order to

24 design a password and access system for the database?  
Outline three (3) examples of the types of multiple-user

25 requirements you might identify.  
What information is it essential to consider and use to

26 develop client access profiles?  
What are four (4) of the types of information you would

27 assess to identify the database backup and recovery  
requirements?
What do you need to ensure about the procedure

28 documentation developed for the database backup and  
restoration?
What evidence should you obtain for client final approval of

29 the submitted database and documentation?  
55
56

Ass Ictdbs502 V3

Uploaded by

Copyright:

Available Formats

Ass Ictdbs502 V3

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Ass Ictdbs502 V3

Uploaded by

Copyright:

Available Formats

ICTDBS502

You are going to be assessed for:

 Your ability to apply your learning.

How you will be assessed

What if you disagree on the assessment outcome?

What if I believe I am already competent before training?

 Ensure that all documentation is signed by the learner, trainer, workplace

 Ensure that their own qualifications are current.

 When required, request the manager or supervisor to determine that the

 Follow the recommendations from moderation and validation meetings.

How should I format my assessments?

How long should my answers be?

Type of Answer Answer Guidelines

Short Answer 4 typed lines = 50 words, or

5 lines of handwritten text

Long Answer 8 typed lines = 100 words, or

Brief Report 500 words = 1 page typed report, or

Mid Report 1,000 words = 2 page typed report

100 lines of handwritten text = 3 foolscap handwritten pages

Long Report 2,000 words = 4 page typed report

200 lines of handwritten text = 6 foolscap handwritten pages

How should I reference the sources of information I use in my

Assessment Method Satisfactory Result Non-Satisfactory Result

Questions All questions answered Incorrect answers for one

Answers address the Answers do not address

Third Party Report Supervisor or manager Could not demonstrate

Attachments if requested Requested supplementary

All requirements of the Response does not

Responses must refer to One or more of the

Observation/Demonstration All elements, criteria, Could not demonstrate

Case Study All comprehension Lack of demonstrated

Answers address the Answers do not address

Attachments if requested Requested supplementary

are attached items are not attached

The following questions are to be completed by the assessor:

Is the Learner ready for assessment? Yes No

Has the assessment process been explained? Yes No

Does the Learner understand which evidence is to be

Have you discussed any special needs to be considered

The following documents must be completed and attached:

 Written Activity and Checklist

The learner will complete the written activity provided to S NYS

 Practical Activity Checklist

The learner will demonstrate a range of skills and the

 Questions and Questions Checklist

The learner will answer a range of questions either S NYS

 The material I have submitted is my own work

Result and Feedback

Overall Outcome Competent Not yet Competent

Structuring database represents way used by organisation. Data analysing

2. Describe data modelling as it relates to developing a conceptual data model.

A condition which is to be created in database is likely called data redundancy in

4. Describe the fundamentals of DBMS, particularly as these are applicable during

The fundamentals of DBMS are;

 The physical databases require to be designed in alignment with the

 The database engine would be considered in the design phase of the

 The database scheme is a structure of database and data included in

5. What is encryption and authentication, and how do these apply to database

Database encryption is to involve the process of applying algorithms to the data