Unit Code/s & Name/s: ICTDBS506 – Design databases
Cluster Name: N/A
Assessment Type: ☐ Case Study ☐ Assignment ☐ Project ☒ Other (specify)
Assessment Name: AT1 – Database research questions
Assessment Task No.: 1 of 2
Assessment Due Date: Week 8
Date Submitted: / /
Assessor Name: [Assessor Name]
Instructions to Student:
General Instructions:
You are required to answer 25 questions using your own research skills to provide the answers along with examples that illustrate your answers. You are also required to include a list of references at the end, which includes all the sources you accessed. Ensure you use an appropriate reference style. There are websites available that generate references for you, such as HarvardGenerator.
Information/Materials provided:
• A computer with internet access provided in the classroom.
Work, Health and Safety:
The environment should be assessed for safety prior to class. Special consideration 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 a 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 suites or similar.
Time restrictions:
Students can start completing this assessment from week 2; however, students have until week 7 to complete and submit the assessment.
Level of Helpance permitted:
Teachers and tutors should be available in class and accessible by email for students working from home. Staff cannot directly provide students answers but can guide them to where to go to complete tasks individually.
If you are unable to submit an assessment, you must notify your teacher before the assessment due date and supply a doctor’s certificate and an extension approval from the team manager.
Reasonable adjustments will be made for students as and when appropriate, after consultation with the Accessibility and Counselling team. 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:
Relational database
Database features
Data analysis process
Conceptual data modelling
Logical model and physical model
Entity relationship diagram (ERD)
Identifying relationship on an entity relationship diagram (ERD)
Normalisation, data redundancy and normal forms
DBMS, technical, and economic factors in choosing a DBMS.
Password encryption, authentication
Data structure, data type
Foreign key constraint, referential integrity constraint rules
Data definition language (DDL)
Case diagram
Check constraints.
The concepts of the object model design
Vertical scalability and horizontal scalability
Number of Attempts:
You will receive up to two (2) attempts at this assessment task. Should your 1st attempt be unsatisfactory (U), your teacher will provide feedback and discuss the relevant sections/questions with you and will arrange a due date for the submission of your 2nd attempt. If your 2nd submission is unsatisfactory (U) or you fail to submit a 2nd attempt, you will receive an overall unsatisfactory result for this assessment task. Only one re-assessment attempt may be granted for each assessment task.
For more information, refer to the Student Rules.
Submission details:
Insert your details on page 1 and sign the Student Declaration. Include this template with your submission. Your teacher will provide all the details for the submission system or network.
Due Date: Week 7
You are required to answer the questions in this document in the space provided under each question and save the document as [surname_student number_unit/cluster_AssessmentNumber.zip]. For example: [surname_123456789_BSBCRT512_1.zip]
For re-submissions, an “R” must be added to the file name. For example: [surname_123456789_BSBCRT512_1_R.zip]
Assessment to be submitted to the allocated dropbox in Connect or to the allocated network folder.
• 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 for the Assessor:
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 customized to suit the mode.
Specifications of assessment:
There are 25 main questions that must all be answered correctly to achieve a satisfactory result for this assessment. Students will be able to resubmit their work a second time if they are not successful on the first attempt.
Equipment or material requirements:
A computer with internet access.
Details of location:
TAFE will provide a simulated work environment in the classroom; however, it is possible to complete these tasks on a home virtual network using a computer with internet access.
Level of Helpance permitted:
Teachers and tutors should be available in class and accessible by email for students working from home. Staff cannot directly provide students answers but can guide them to where to go to complete tasks individually.
Interactions:
Teamwork skills are essential in the IT industry; therefore, students should work in teams to consult and collaborate on the practical activities. However, each student is required to complete the tasks individually.
Contingencies:
Reasonable adjustments will be made for students as and when appropriate, after consultation with the Accessibility and Counselling team. You must see your teacher prior to assessment regarding this.
Work Health and Safety:
A work health and safety check of the assessment environment is to be conducted prior to the assessment, and any hazards addressed appropriately.
Assessment Condition:
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
• organizational 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.
Complete the following research questions. Remember that you cannot copy and paste from the Internet or any other source. Plagiarism will not be tolerated.
In answering the questions, ensure that you:
• Provide a list of references at the end of the document, including all sources accessed using an appropriate referencing style such as HarvardGenerator.
• Provide examples to illustrate your answers. You can use your Database/Website project for this unit as a source for your examples.
Assessment questions:
What is a relational database? (80 words) (Note: you might copy in your ERD from ICTICT523 AT2 and use this to illustrate your answer.)
Answer:
Describe the following nine (9) database features: (Note: you might copy in your ERD from ICTICT523 AT2 and use this to illustrate your definitions.)
Feature:
Description:
Explain the data analysis process, covering the following in your answer: (Firstly consider the meeting you had with your client in ICTICT523. How would you know the data types for the various attributes in your draft ERD? EG: would a product code need to have the data type of: int, double, varchar, other? How would you determine this?)
a) Determining the required data types. (50 words)
Answer:
b) Determining the required data structures. (50 words)
Answer:
c) Designing queries and reports. (50 words)
Answer:
Describe conceptual data modeling. (50 words)
Answer:
With reference to the conceptual model, logical model, and physical model, what are the differences between all three? (80 words)
Answer:
What is an entity relationship diagram (ERD)? (50 words)
Answer:
How do we represent an identifying relationship on an entity relationship diagram (ERD)? (50 words)
Answer:
a) What is normalization? How does normalization identify data redundancy? (80 words)
Answer:
b) Explain in detail and with examples the first three (3) normal forms (1NF, 2NF, 3NF) (number of words as required)
Answer:
What is a DBMS? (80 words)
Answer:
Explain five (5) technical, and five economic factors in choosing a DBMS. (150 – 200 words)
Answer:
Technical:
1.
Economic:
1.
Explain five (5) reasons an organization will be required to upgrade an existing DBMS. (150 – 200 words)
Answer:
Reasons:
1.
What is password encryption? (50 words)
Answer:
What is authentication? (50 words)
Answer:
Why should encryption and authentication be incorporated into the database specifications? (50 words)
Answer:
Which form of encryption offers the best protection against brute force attacks? (50 words)
Answer:
What is a data structure? Provide examples of data structure in your answer. (80 words)
Answer:
As part of designing the physical data model, we select data types for each column. Explain which data type is best when dealing with currency in MySQL, and why? (100 words)
Answer:
Examine the table below. Is this database table compliant to third normal form (3NF)? Explain, in detail, why or why not. Use correct terminology when referring to database terms. (100 words)
Answer:
What is a foreign key constraint? In your answer, explain the referential integrity constraint rules and the three (3) possible actions. (80 words)
Answer:
The organization you work for, a shop selling motorbike spare parts, would like to ensure that every product name and product code stored in the database is never duplicated. Explain how you would achieve this. (50 words)
Answer:
Explain what the Data Definition Language (DDL) is. (50 words)
Answer:
Explain what an oval shape represents in a use case diagram. Illustrate your answer with an example. (Potential reference: Learner Guide, search for ‘Use Case’. Or on the Internet, search for ‘Use Case Diagram’. Don’t forget to add an example.)
Answer:
a) What is the purpose of check constraints? Provide examples to illustrate your answer.
Answer:
b) Support for check constraints was introduced in MySQL 8.0.16 – older versions did not support the check feature. What other technique can you use instead of check constraints?
Answer:
You have been tasked to design the data structures, queries, screens, and reports of a new database. Describe the concepts of the object model design and how you would use this in your database design. (80 words)
Answer:
Explain the terms “vertical scalability” and “horizontal scalability”. (50 words)
Answer:
References:
Insert references here using an appropriate referencing style such as HarvardGenerator.
Assessment questions
1. What is a relational database? (80 words) (Note: you might copy in your ERD from ICTICT523 AT2 and use this to illustrate your answer.)
2. Describe the following nine (9) database features: (Note: you might copy in your ERD from ICTICT523 AT2 and use this to illustrate your definitions.)
Feature Description
Field
Table
Primary key
Foreign key
Constraint
Superkey
Index
User interface (UI)
Report
3. Explain the data analysis process, covering the following in your answer: (Firstly consider the meeting you had with your client in ICTICT523. How would you know the data types for the various attributes in your draft ERD? EG: would a product code need to have the data type of: int, double, varchar, other? How would you determine this?)
a) Determining the required data types. (50 words)
_____________________________A relational database is a type of database that organizes data into tables consisting of rows and columns. It is based on the relational model, which establishes relationships between tables using primary keys and foreign keys. In a relational database, data is stored in a structured manner, allowing for efficient data manipulation, Question Assignmenting, and retrieval. For example, in an ERD (Entity-Relationship Diagram) from ICTICT523 AT2, you can visually represent the tables and their relationships, demonstrating how the data is organized and connected.
Database Features:
Field: A field represents a specific piece of data within a table. It is the smallest unit of data storage and holds a single value, such as a customer’s name or a product price.
Table: A table is a collection of related data organized in rows and columns. It represents a specific entity or concept, such as customers, products, or orders.
Primary key: A primary key is a unique identifier for each record in a table. It ensures that each record can be uniquely identified and serves as a reference point for establishing relationships with other tables.
Foreign key: A foreign key is a field in a table that refers to the primary key of another table. It establishes a relationship between two tables by linking records based on common values.
Constraint: A constraint is a rule or condition that limits the values allowed in a field or a combination of fields. It ensures data integrity and enforces data consistency.
Superkey: A superkey is a set of one or more attributes that uniquely identify a record within a table. It can be a combination of multiple fields that, when taken together, form a unique identifier.
Index: An index is a data structure that improves the speed of data retrieval operations on a database table. It allows for quick access to specific data based on indexed columns.
User interface (UI): The user interface refers to the visual or interactive components through which users interact with the database. It includes forms, menus, buttons, and other graphical elements that facilitate data input, manipulation, and retrieval.
Report: A report is a formatted presentation of data from the database. It provides a structured and organized view of specific information, often in a printed or electronic format.
Data analysis process:
a) Determining the required data types (50 words): In the data analysis process, determining the required data types involves understanding the nature of the data and selecting appropriate data types for each attribute. For example, a product code could be represented as a varchar or a string data type, as it typically consists of alphanumeric characters. The decision on data types can be influenced by factors such as data size, expected range of values, and specific data manipulation requirements. Consulting with the client and understanding their business needs can help in determining the appropriate data types for the attributes in the draft ERD.
