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: Internet access Web browser Microsoft office Database provider(MySQL/PhpMyAdmin/Microsoft SQL Server) Suggested applications are: inVision 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 assistance; 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 assistance 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: Meeting with a client Technical requirements Conceptual model Logical data model Business rules and constraints User interface Physical design Access and security 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: Internet access Web browser Microsoft office Database provider(MySQL/PhpMyAdmin/Microsoft SQL Server) Specialisation specific project requirements Suggested applications are inVision1, 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 assistance 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. inVision2, MockFlow3 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 assist her with classroom administration and supporting student activity.

Specifically, Caroline would like the robot(s) to assist 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

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

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.

 

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

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

Perform normalisation process to at least third normal form (3NF) showing the results for each step.

Update the ERD after normalisation. Ensure that the cardinality ratio is displayed.

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

Business rules – Identify a minimum of five (5) business rules that apply to the database/website project.

Integrity constraints – Primary and foreign keys have been identified. Also identified in the data dictionary.

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

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.

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

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

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.

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.

Populate the database with appropriate testing data – sufficient to run the queries.

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.

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

Tags: academicwriting, assignment, assignmentday, assignmentdue, assignmenthelp, assignmenthelper, assignmenthelpers, assignments, assignmentsdue, assignmentssuck, assignmentstress, assignmenttime, assignmentwriting, college, dissertation, essay, essayhelp, essaywriting, essaywritingservice, homework, homeworkhelp, researchpaper, student, studentlife, students, thesis, thesiswriting, university, universityassignment, writing