Analysis and Design Report
Design Databases
Created by
UPTOWN IT
For
<
YOUR NAME:
YOUR STUDENT NUMBER:
PROJECT REFERENCE:
DATE:
Contents
Task 1 – Meeting with client 2
Task 2 – Technical requirements 2
Task 3 – Conceptual model 2
Task 4 – Logical data model 4
Task 5 – Business rules and constraints 5
Task 6 – User interface 7
Task 7a – Physical design 8
Task 7b and 8 – Access and security 9
Task 9 – Client receipt 10
Remove all blue text once your report is completed. The blue text is intended at giving you some pointers for the report content.
Please ensure that you re-submit your ICTICT523 AT2 document with this assignment.
Task 1 – Meeting with client
NOTE: Already completed in ICTICT523 – Gather data to identify business requirements
a) This part consists of group work. Group size will depend on class numbers. Around four (4) people per group is an appropriate number. As a group, review the given scenario and collaboratively create a list of around 60 questions. Questions should be categorised as follow:
Hardware
Software
Data requirements
People – roles, access, etc.
Processes/business activities
Business rules
Security
b) Provide evidence of communication between the student group/developer and the client/teacher. Send an email to the client/teacher requesting a mutually suitable time for a meeting. In a second email, once the meeting time has been agreed to, send the meeting agenda prior to the meeting.
c) Each group will have a meeting with the teacher/facilitator who will be playing the role of the client – a non-technical business executive. The meeting will either be recorded or documented as an observation checklist (by the teacher) as evidence to demonstrate that you have:
met with the client to conduct the user-needs analysis
negotiated and understood the client’s requirements for the functionality of the website
used active listening, communicating in plain English, and summarising the client’s key points to understand their requirements.
A meeting is a good way of identifying user needs. List and explain other four (4) methods that can be used to identify user needs. Outline the advantages and disadvantages of each method.
Task 2 – Technical requirements
NOTE: Already completed in ICTICT523 – Gather data to identify business requirements
Based on the answers received as a result of the client meeting, recommend specific technical requirements for the database/website project.
You will also be required to research the technical requirements, and include references to where you sourced your information from.
Document the technical requirements (500-800 words). The document must include:
• technical requirements negotiated with the client/teacher during the meeting
• referenced sources of technical information you have obtained in preparing the technical requirements.
Task 3 – Conceptual model
NOTE: Already completed in ICTICT523 – Gather data to identify business requirements
Design a conceptual model using Visual Paradigm (or similar). The conceptual model will take the form of an entity relationship diagram (ERD).
The conceptual model submitted with your evidence must include:
• the completed conceptual model for the database – first draft ERD
• feedback from the client, incorporated as amendments in the model – provide evidence of communication between you and the client/teacher (exchange of e-mails)
• content written in plain English language which a non-technical client would understand.
Task 4 – Logical data model
a) Perform normalisation process to at least third normal form (3NF) showing the results for each step. (Hint: Normalisation to be completed on each of the forms provided for the scenario. Reference: 03a_SomeBackgroundOnNormalisation.doc.)
b) Update the ERD after normalisation. Ensure that the cardinality ratio is displayed. (Hint: Based on your initial version prepared in ICTICT523 – updated as required.)
c) Develop a data dictionary that clearly indicates the data types and precision parameters for each field (size, range and nullability), primary and foreign keys for tables, data validation and business constraints (checks or triggers). Template provided. (Hint: Export a data dictionary from Visual Paradigm (or equivalent))
NOTE: The logical data model submitted with your evidence must include:
• normalisation documentation (step-by-step)
• final ERD
• data dictionary
• approval of the logical data model to the client (exchange of e-mails).
To: Client
From: Me…
CC:
BCC:
Date: 19/07/2023
Subject: Logical database model
————————————————————-
Hi
…
Regards,
———————————————-
SystemsAnalyst@UptownIT.com.au
———————————————–
Task 5 – Business rules and constraints
a) Create a database – Create a database in-line with your ERD and normalisation within Microsoft Access, LibreOffice Base, Microsoft SQL Server or MySQL. Add the required tables and relationships within this database.
b) Add Sample Data – Add at least 2 records of sample data to each table of your database.
c) Database Data Dictionary – Generate a data dictionary (or equivalent) from your database. (Note: this is a second data dictionary, in addition to the one you have generated from your ERD.)
d) Business rules – Identify a minimum of five (5) business rules that apply to the database/website project. (Hint: These would be based on requirements noted by your client in your initial project requirements meeting for ICTICT523.)
e) Integrity constraints – Primary and foreign keys have been identified. Also identified in the data dictionary. (Hint: As per your updated ERD and Data Dictionary in Task4, and noted in the table below.)
f) Referential integrity constraints – Identify and set all referential integrity constraints. Use the table below to document the referential integrity constraints by indicating the rules and actions that apply to each foreign key. (Hint: As per your updated ERD and Data Dictionary in Task4, and noted in the table below.)
TABLE and FOREIGN KEY (FK) TABLE & PRIMARY KEY it REFEENCES (PK) REFERENTIAL INTEGRITY CONSTRAINTS REFERENTIAL ACTIONS
RULE 1
FK has the same declaration (data type and domain) that the PK it references RULE 2
FK value is equal to value in PK or NULL
Table_name
(FK)_Col_name Referenced_tbl_name
(PK)Col_name Yes Yes ON DELETE CASCADE,
ON UPDATE CASCADE
Add lines as required
g) Semantic and other constraints – These can take the form of validation rules, check constraints and triggers. These need to have been included in the data dictionary. Review and update this section the data dictionary until you are sure that all semantic constraints have been identified. (Hint: These would be based on requests from the client from your initial project requirements meeting for ICTICT523. Examples of these might include: phone numbers or email addresses having a correct format, a product code having the correct number of numbers and characters, a customer name having being entered, a date being in an appropriate date range, a quantity ordered having been entered, and being in an appropriate value range, etc.)
h) Indexes – Identify which table column will benefit from indexing and create the indexes. You can use the table below to record the indexing. (Hint: While many RDBMS add their own indices, additional indices might be added to improve search efficiency. For instance, indices could be added to customer names, or product codes and/or product names, etc, where searching would be more based on words rather than just IDs.)
INDEX NAME COLUMN NAME TABLE INDEX TYPE
(e.g. HASH or B-Tree) SEARCH BENEFITS
Add rows as required
i) Provide an estimation of the approximate size of the completed database. Explain how you have reached the estimate. (Hint: See sample spreadsheet provided)
Task 6 – User interface
Using inVision , MockFlow or similar program (Prototyper, Microsoft Visual Studio, etc) design a user interface for the required database/website project including menus, input forms, and reports. The number of screens and reports depends on the case study or scenario used for the project.
The queries necessary to display the required reports will be completed in Task 7.
Task 7a – Physical design
Create a database prototype following the blueprint or structure outlined in the data dictionary created in Task 4. Export or dump the database structure as an SQL file and/or data dictionary file. Submit the database or SQL file, and your exported data dictionary as evidence of database implementation. Apply the constraints identified in Task 5 to the database and provide evidence of the implementation of these.
a) Compare conceptual model and technical requirement with the actual database created and list and explain the differences. Make sure to make changes to the database, as necessary, while performing this review.
b) Populate the database with appropriate testing data – sufficient to run the queries.
c) Create and run the necessary queries to display the reports outlined in the case study or scenario project requirements. Provide screenshots of your queries and results. Remember that these queries are run on the database prototype and their purpose is to ascertain that the database structure is sufficient to produce the desired results.
Task 7b and 8 – Access and security
Outline a plan to backup and restore the database remembering that at this stage it is just a prototype.
Review the business security requirements for the case study/scenario. The points below may help you clarify these requirements.
• How the users will gain access.
• How the password will be stored securely.
• Who will be authorised to access the database.
• What permissions will be given
• The different permission levels.
At this stage, you are ready to document the access and security requirements (500-800 words). The document must ensure that:
• Security design meets the requirements of the business security requirements.
• The password access system for the database including encryption/hashing details.
• The different user groups and their access requirements and privileges. (Role-Based Access Control, RBAC)
• User access profiles.
Task 9 – Client receipt
a) Create a checklist to seek feedback from the client/teacher, change according to given feedback.
b) Write an email to the client/teacher to obtain sign off via communicating your intention to submit the completed database design documentation. Include this email with your submission with sign off sheet.
c) Upload your completed database portfolio (zipped folder) to Connect which include:
• Assessment document (this word file)
• Database and any associated SQL files of your database
• UI Design documents and Report samples– if separate from this report
• Excel file with data dictionary
• Any other additional files prepared for this assessment that have not been included in this dot-point list.
Example Sign-off:
DATABASE DESIGN REPORT SIGNOFF
Signing off on this document signifies that the Analysis Report complies with the Client Business requirements.
Project Manager
Signature:
Date:
Systems Analyst
Signature:
Date:
————————
__________________________________________________
Student Name Student Number
Unit Code/s & Name/s ICTDBS506 – Design databases
Cluster Name
If applicable N/A
Assessment Name AT2 – Database/website design project portfolio Assessment Task No. 2 of 2
Assessment Due Date Week 11 Date submitted / /
Assessor Name
Student Declaration: I declare that this assessment is my own work. Any ideas and comments made by other people have been acknowledged as references. I understand that if this statement is found to be false, it will be regarded as misconduct and will be subject to disciplinary action as outlined in the TAFE Queensland Student Rules. I understand that by emailing or submitting this assessment electronically, I agree to this Declaration in lieu of a written signature.
Student Signature Date / /
Instructions to Student Using the scenario provided, you are required to complete nine (9) tasks. Task 1 involves working in a group but the other eight (8) tasks you will be working alone or as directed by your teacher. These tasks are:
1. Meeting with a client
2. Technical requirements
3. Conceptual model
4. Logical data model
5. Business rules and constraints
6. User interface
7. Physical design
8. Access and security
9. Client receipt, feedback checklist, sign off sheet
The project involves the creation of a front-end website supported by a back-end database to implement the solution required. The website must be responsive to different screen sizes. Initial details of the client’s background, data requirements are supplied below.
Materials to be supplied:
• Case Scenario
• Excel file for data dictionary
• Access to a computer with:
o Internet access
o Web browser
o Microsoft office
o Database provider(MySQL/PhpMyAdmin/Microsoft SQL Server)
• Suggested applications are:
o inVision
o MockFlow or similar
Work, Health and Safety:
The environment should be assessed for safety prior to class. Special considering should be taken regarding potential ICT related hazards such as tripping hazards, electromagnetic radiation, ergonomics and posture.
TAFE Queensland health and safety policies and procedures should be followed at all times.
Details of location –
TAFE will provide simulated work environment in the classroom all practical activities should be completed in the classroom with teacher/tutor Helpance; however, it is possible to complete these tasks on a home computer with internet access, web browser and office suits or similar.
Time restrictions – this is a portfolio assessment designed to take place over 8 weeks. The student is expected to attend in class for 7 hours per week (this includes theory sessions) and should be able to commit up to 3 hours per week in their own time.
Level of Helpance permitted:
Teachers and tutors should be available in class, and accessible by email for students working from home. Staff cannot directly show students answers but guide them to where to go to complete tasks individually. Students with disability will receiver reasonable adjustments.
If you are unable to attend assessment you must notify your teacher before the assessment and supply a doctor’s certificate and approval from the team manager for extension. Reasonable adjustments will be made for students as and when appropriate, after consultation with the Disability and Counselling team. Extra time may be given for Language literacy and numeracy (LLN) or extenuating circumstances – you must see your teacher prior to assessment regarding this.
RPL (Recognition of Prior Learning) is available for this unit. Speak to your teacher/assessor to check if you qualify for RPL.
Assessment Criteria:
To achieve a satisfactory result, your assessor will be looking for your ability to demonstrate the following key skills/tasks/knowledge to an acceptable industry standard:
1. Meeting with a client
2. Technical requirements
3. Conceptual model
4. Logical data model
5. Business rules and constraints
6. User interface
7. Physical design
8. Access and security
9. Client receipt
Submission details
(if relevant) Insert your details on page 1 and sign the Student Declaration. Include this form with your submission.
Due Date:
Week 8
You need to submit three files in a zipped folder:
• Assessment document (this word file)
• SQL file of your database
• Excel file with data dictionary
Your assignment must be saved with your
surname_student number_unit/cluster_AssessmentNumber.zip.
For example:
surname_123456789_ICTDBS506_2.zip
For re-submissions, an “R” must be added to the file name. For example:
surname_123456789_ICTDBS506_2_R.zip
Submit your assessment to the allocated dropbox in Connect or to the allocated network folder. Your teacher will provide all the details for the submission system or network.
Connect details:
• TAFE Queensland Learning Management System: Connect url: https://connect.tafeqld.edu.au/d2l/login
• Username; 9 digit student number
• For Password: Reset password go to https://passwordreset.tafeqld.edu.au/default.aspx>
Instructions to Assessor Specifications of assessment – To be judged competent in this assessment item the student is required to demonstrate competence in all indicators shown in the marking guide.
Depending on the delivery mode and/or timetable constraints, the Study Guide and the Study Schedule must be customised to suit the mode.
Select project scenario or case study from the resources folder.
Information / Materials provided:
• Case Scenario
• Excel file for data dictionary
• Access to a computer with:
o Internet access
o Web browser
o Microsoft office
o Database provider(MySQL/PhpMyAdmin/Microsoft SQL Server)
• Specialisation specific project requirements
• Suggested applications are inVision , MockFlow or similar
Assessment location:
Computer lab that simulates an ICT workplace environment.
Time restrictions – this is a portfolio assessment designed to take place over 8 weeks. The student is expected to attend in class for 3 hours per week (this includes theory sessions), and also should be able to commit up to 3 hours per week in their own time.
Level of Helpance permitted – teachers and tutors should be available in class, and accessible by email for students working from home. Staff cannot directly show students answers but guide them to where to go to complete tasks individually. Students with disability will receive reasonable adjustments.
Work Health and Safety:
The environment should be assessed for safety prior to class. Special considering should be taken regarding potential ICT related hazards such as tripping hazards, electromagnetic radiation, ergonomics and posture.
TAFE Queensland health and safety policies and procedures should be followed at all times.
Interactions – teamwork skills are essential in the IT industry therefore you should work in teams to consult and collaborate on the practical activities. Students need to perform Task 1 in group, and other tasks individually and will be required to show their work (unless indicated).
Contingencies – reasonable adjustments can be made for students who require variations to assessment conditions.
If Microsoft Word is not available; other word processing software may be used, given that the items produced can still be exported to a format accepted by Microsoft Word.
Students can use MySQL, PhpMyAdmin or similar Database provider.
inVision , MockFlow or similar program may be used, given that it supports and same features and functionality as the suggested programs.
Assessment Conditions:
Skills in this unit must be demonstrated in a workplace or simulated environment where the conditions are typical of those in a working environment in this industry.
This includes access to:
• industry standard equipment and materials
• industry standard database software
• network and other systems required for remote or multi-user access
• organisational requirements and deliverables
• computer-aided software engineering (CASE) or diagramming software.
Note to Student An overview of all Assessment Tasks relevant to this unit is located in the Unit Study Guide.
Project Scenario
You are employed by Uptown IT’s Software Solutions Department as a Systems Analyst. You have been assigned to a new project and your task is to initiate contact with the client, attend a client meeting, gather project requirements, and document the findings by producing an Analysis Report. The Client for this project is Caroline’s Classroom Robots.
Your teacher/assessor will take on the role of the Project Manager assigned to this project by Uptown IT.
DETAILS:
Caroline is looking to get your support in programming one or more robots to Help her with classroom administration and supporting student activity.
Specifically, Caroline would like the robot(s) to Help her with:
• Welcoming students to the class and tracking their attendance.
• Interacting with students in the delivery of a range of tutorial exercises.
• Tracking the activities and results of students throughout each lesson.
• Store a record of these various details for review, reporting and statistical reporting purposes; and for planning future class exercises, activities and preparation.
Caroline would like screens and reports accessible via her computer tablet to review these various details on a day to day basis, and for reviewing periodic reports.
Caroline is seeking a meeting with your team so that she might provide more specific details.
Some Suggested Reports:
Some of the anticipated reports for this project include:
• Attendance of students in class on a given day
• Attendance, grouped by students between two dates
• Exercise results for a given student by Exercise Type (eg: Reading, Writing, and Mathematics), and by Exercise Topic within that (eg: Arithmetic Level 1, Arithmetic Level 2), between two dates
• Average of results grouped by Exercise Type and within that by Topic. Eg:
Type Topic Average (/10)
Reading Topic R1 8.2
Topic R2 7.6
Writing Topic W1 6.8
Topic W2 9.0
SECURITY REQUIREMENTS
The following security requirements must be met:
• Authentication
• Password encryption/hashing
• Multi-user access control
• Backup and restore strategy.
Assessment task
Task 1 – Meeting with client
a) This part consists of group work. Group size will depend on class numbers. Around four (4) people per group is an appropriate number. As a group, review the given scenario and collaboratively create a list of around 60 questions. Questions should be categorised as follow:
Hardware
Software
Data requirements
People – roles, access, etc.
Processes/business activities
Business rules
Security
b) Provide evidence of communication between the student group/developer and the client/teacher. Send an email to the client/teacher requesting a mutually suitable time for a meeting. In a second email, once the meeting time has been agreed to, send the meeting agenda prior to the meeting.
c) Each group will have a meeting with the teacher/facilitator who will be playing the role of the client – a non-technical business executive. The meeting will either be recorded or documented as an observation checklist (by the teacher) as evidence to demonstrate that you have:
met with the client to conduct the user-needs analysis
negotiated and understood the client’s requirements for the functionality of the website
used active listening, communicating in plain English, and summarising the client’s key points to understand their requirements.
d) A meeting is a good way of identifying user needs. List and explain other four (4) methods that can be used to identify user needs. Outline the advantages and disadvantages of each method.
Task 2 – Technical requirements
Based on the answers received as a result of the client meeting, recommend specific technical requirements for the database/website project.
You will also be required to research the technical requirements, and include references to where you sourced your information from.
Document the technical requirements (500-800 words). The document must include:
• technical requirements negotiated with the client/teacher during the meeting
• referenced sources of technical information you have obtained in preparing the technical requirements.
Task 3 – Conceptual model
Design a conceptual model using Visual Paradigm (or similar). The conceptual model will take the form of an entity relationship diagram (ERD).
The conceptual model submitted with your evidence must include:
• the completed conceptual model for the database – first draft ERD
• feedback from the client, incorporated as amendments in the model – provide evidence of communication between you and the client/teacher (exchange of e-mails)
• content written in plain English language which a non-technical client would understand.
Task 4 – Logical data model
a) Perform normalisation process to at least third normal form (3NF) showing the results for each step.
b) Update the ERD after normalisation. Ensure that the cardinality ratio is displayed.
c) Develop a data dictionary that clearly indicates the data types and precision parameters for each field (size, range and nullability), primary and foreign keys for tables, data validation and business constraints (checks or triggers). Template provided.
The logical data model submitted with your evidence must include:
• normalisation documentation (step-by-step)
• final ERD
• data dictionary
• approval of the logical data model to the client (exchange of e-mails).
Task 5 – Business rules and constraints
a) Business rules – Identify a minimum of five (5) business rules that apply to the database/website project.
b) Integrity constraints – Primary and foreign keys have been identified. Also identified in the data dictionary.
c) Referential integrity constraints – Identify and set all referential integrity constraints. Use the table below to document the referential integrity constraints by indicating the rules and actions that apply to each foreign key.
TABLE and FOREIGN KEY (FK) TABLE & PRIMARY KEY it REFEENCES (PK) REFERENTIAL INTEGRITY COSTRAINTS REFERENTIAL ACTIONS
RULE 1
FK has the same declaration (data type and domain) that the PK it references RULE 2
FK value is equal to value in PK or NULL
Table_name
(FK)_Col_name Referenced_tbl_name
(PK)Col_name Yes Yes ON DELETE CASCADE,
ON UPDATE CASCADE
Add lines as required
d) Semantic and other constraints – These can take the form of validation rules, check constraints and triggers. Keep in mind that MySQL does not support check constraints and triggers must be created to implement some checks. These have been included in the data dictionary. Review and update this section the data dictionary until you are sure that all semantic constraints have been identified.
e) Indexes – Identify which table column will benefit from indexing and create the indexes. You can use the table below to record the indexing.
INDEX NAME COLUMN NAME TABLE INDEX TYPE
(e.g. HASH or B-Tree) SEARCH BENEFITS
Add rows as required
f) Provide an estimation of the approximate size of the completed database. Explain how you have reached the estimate.
Task 6 – User interface
Using inVision , MockFlow or similar program, design a user interface for the required database/website project including menus, input forms, and reports. The number of screens and reports depends on the case study or scenario used for the project.
The queries necessary to display the required reports will be completed in Task 7.
Task 7 – Physical design
a) Create a database prototype following the blueprint or structure outlined in the data dictionary created in Task 4. Export or dump the database structure. Submit the SQL file as evidence of database implementation. Apply the constraints identified in Task 5 to the database and document which constraints were applied.
b) Compare conceptual model and technical requirement with the actual database created and list and explain the differences. Make sure to make changes to the database, as necessary, while performing this review.
c) Populate the database with appropriate testing data – sufficient to run the queries.
d) Create and run the necessary queries to display the reports outlined in the case study or scenario project requirements. Provide screenshots of your queries and results. Remember that these queries are run on the database prototype and their purpose is to ascertain that the database structure is sufficient to produce the desired results.
e) Outline a plan to backup and restore the database remembering that at this stage it is just a prototype.
Task 8 – Access and security
Review the business security requirements for the case study/scenario. The points below may help you clarify these requirements.
• How the users will gain access.
• How the password will be stored securely.
• Who will be authorised to access the database.
• What permissions will be given
• The different permission levels.
At this stage, you are ready to document the access and security requirements (500-800 words). The document must ensure that:
• Security design meets the requirements of the business security requirements.
• The password access system for the database including encryption/hashing details.
• The different user groups and their access requirements and privileges.
• User access profiles.
Task 9 – Client receipt
Create a checklist to seek feedback from the client/teacher, change according to given feedback.
Write and email to the client/teacher to obtain sign off via communicating your intention to submit the completed database design documentation. Include this email with your submission with sign off sheet.
Upload your completed database portfolio (zipped folder) to Connect which include:
• Assessment document (this word file)
• SQL file of your database
• Excel file with data dictionary
.
End of assessment
________________________________________________
DATABASE DESIGN REPORT NOT APPROVED
Please provide feedback on the changes needed.
______________________________________________________________________-
Assignment Scenario: Caroline’s Classroom Robots
You are employed by Uptown IT’s Software Solutions Department as a Systems Analyst. You have been assigned to a new project and your task is to initiate contact with the client, attend a client meeting, gather project requirements, and document the findings by producing an Analysis Report. The Client for this project is Caroline’s Classroom Robots.
Your teacher/assessor will take on the role of the Project Manager assigned to this project by Uptown IT.
DETAILS:
Caroline is looking to get your support in programming one or more robots to Help her with classroom administration and supporting student activity.
Specifically, Caroline would like the robot(s) to Help her with:
• Welcoming students to the class and tracking their attendance.
• Interacting with students in the delivery of a range of tutorial exercises.
• Tracking the activities and results of students throughout each lesson.
• Store a record of these various details for review, reporting and statistical reporting purposes; and for planning future class exercises, activities and preparation.
Caroline would like screens and reports accessible via her computer tablet to review these various details on a day to day basis, and for reviewing periodic reports.
Caroline is seeking a meeting with your team so that she might provide more specific details.
Some Suggested Reports:
Some of the anticipated reports for this project include:
• Attendance of students in class on a given day
• Attendance, grouped by students between two dates
• Exercise results for a given student by Exercise Type (eg: Reading, Writing, and Mathematics), and by Exercise Topic within that (eg: Arithmetic Level 1, Arithmetic Level 2), between two dates
• Average of results grouped by Exercise Type and within that by Topic. Eg:
Type Topic Average (/10)
Reading Topic R1 8.2
Topic R2 7.6
Writing Topic W1 6.8
Topic W2 9.0
_____________
Student Name Student Number
Unit Code/s & Name/s ICTDBS506 – Design databases
Cluster Name
If applicable N/A
Assessment Type ☐ Assignment ☐ Project ☐ Case Study ☒ Portfolio
☐ Third Party Report (Workplace) ☐ Third Party Report (Peer) ☐ Other
Assessment Name AT2 – Database/website design project portfolio Assessment Task No. 2 of 2
Assessment Due Date Week 11 Date Submitted / /
Assessor Feedback:
Attempt 1 Satisfactory ☐
Unsatisfactory ☐
Date / /
Assessor Name Assessor Signature
☐ Student provided with feedback and reassessment arrangements (check box when completed) Date scheduled for reassessment / /
Attempt 2 Satisfactory ☐
Unsatisfactory ☐
Date / /
Assessor Name Assessor Signature
Note to Assessor: Please record below any reasonable adjustment that has occurred during this assessment e.g. written assessment given orally.
Assessment Criteria / Benchmarks
The evidence submitted demonstrates that the student has satisfactorily:
Attempt 1 Attempt 2
Date
__/__/__ Date
__/__/__
Y N Y N
Task 1: meeting with client
1. Prepared an email to client to organise a meeting to conduct user needs analysis. ☐
☐
☐
☐
2. Supplied second email to client/teacher with meeting agenda ☐
☐
☐
☐
3. Prepared a list of around 60 questions for the meeting. ☐
☐
☐
☐
4. Attended a meeting with client/teacher and asked appropriate questions to clarify client requirements. This was shown by:
Video/audio recording of meeting submitted
OR
Observation checklist: ☐
☐
☐
☐
Systems Analyst /Student appropriately dressed/prepared ☐
☐
☐
☐
Agenda prepared and presented ☐
☐
☐
☐
Appropriate introduction ☐
☐
☐
☐
Purpose and length of meeting clarified ☐
☐
☐
☐
Each section of meeting appropriately signposted ☐
☐
☐
☐
Client responses/feedback noted as appropriate ☐
☐
☐
☐
Clarification sought where there are uncertainties ☐
☐
☐
☐
Reflective listening used ☐
☐
☐
☐
Actions against agenda items identified/noted. ☐
☐
☐
☐
Meeting closed appropriately with next meeting / relevant actions summarised. ☐
☐
☐
☐
5. Listed and explained four (4) methods for identifying user needs ☐
☐
☐
☐
6. Outlined advantages and disadvantage of each method. ☐
☐
☐
☐
Task 2: Technical requirements
7. Negotiated technical requirements with the client/teacher during the meeting ☐
☐
☐
☐
Referenced sources of technical information obtained in preparing the technical requirements. ☐
☐
☐
☐
Complied with specified length of document (500-800 words) ☐
☐
☐
☐
Task 3: Conceptual model
8. Completed the first draft ERD of the conceptual model for the database. ☐
☐
☐
☐
Incorporated feedback from the client as amendments in the model and included evidence of communication between them and the client/teacher (exchange of e-mails). ☐
☐
☐
☐
Wrote content written in plain English language which a non-technical client would understand. ☐
☐
☐
☐
Task 4: Logical data model
9. Presented the normalisation documentation to at least the third normal form (3NF) showing step-by-step process. ☐
☐
☐
☐
10. Entity relationship Diagram (ERD):
Presented the updated E R diagram (after normalisation). ☐
☐
☐
☐
Identified the cardinality ratio between entities in the ERD. ☐
☐
☐
☐
11. Submitted a data dictionary which includes:
tables ☐
☐
☐
☐
attributes ☐
☐
☐
☐
primary keys ☐
☐
☐
☐
foreign keys ☐
☐
☐
☐
data types ☐
☐
☐
☐
precision parameters for each field (size, range and nullability), ☐
☐
☐
☐
data validation (checks or triggers) ☐
☐
☐
☐
business constraints (checks or triggers) ☐
☐
☐
☐
12. Provided proof of approval of the logical data model to the client (exchange of emails) ☐
☐
☐
☐
Task 5: Business rules and constraints
13. Identified a minimum of five (5) business rules. ☐
☐
☐
☐
14. Identified integrity constraints and included them in data dictionary. ☐
☐
☐
☐
15. Completed the referential integrity constraints table. ☐
☐
☐
☐
16. Identified semantic and other constraints in the data dictionary. ☐
☐
☐
☐
17. Completed Indexes table for all tables. ☐
☐
☐
☐
18. Provided an estimation of approximate database size. ☐
☐
☐
☐
Task 6 – User interface
19. Completed design layouts of User interface Screens and Reports – as directed by your teacher. ☐
☐
☐
☐
Task 7: Physical design
20. Created database prototype from data dictionary information. ☐
☐
☐
☐
21. Submitted SQL export or dump ☐
☐
☐
☐
22. Completed comparison of the conceptual model and the technical requirements ☐
☐
☐
☐
23. Updated, if necessary, database prototype as per comparison results. ☐
☐
☐
☐
24. Populated the database prototype with sufficient testing data. ☐
☐
☐
☐
25. Created and ran all queries necessary to run the reports required in the selected case study or scenario. ☐
☐
☐
☐
26. Provided screenshots of queries and results – as directed by your teacher. ☐
☐
☐
☐
27. Provided backup and restore plan for database prototype. ☐
☐
☐
☐
Task 8: Access and security
28. Provided a review and explanation of security requirements. ☐
☐
☐
☐
29. Documented security requirements and addressed the following:
Security design meets the client requirements ☐
☐
☐
☐
Password encryption/hashing ☐
☐
☐
☐
Multi-user access control ☐
☐
☐
☐
User access profiles ☐
☐
☐
☐
Task 9: Client receipt
30. Checklist created to seek feedback from the client/teacher and responded to the feedback. ☐
☐
☐
☐
31. Included the email to the client/teacher communicating their intention to submit the completed database/website design project with sign off sheet ☐
☐
☐
☐
32. Submitted the final documentation for the project. ☐
☐
☐
☐
____________________________________________________________________
