Part B: Course Detail
Teaching Period: Term2 2024
Course Code: COSC7395C
Course Title: Apply structured query language in relational databases
Important Information:
Please note that this course may have compulsory in-person attendance requirements for some teaching activities.
To participate in any RMIT course in-person activities or assessment, you will need to comply with RMIT vaccination requirements which are applicable during the duration of the course. This RMIT requirement includes being vaccinated against COVID-19 or holding a valid medical exemption.
Please read this RMIT Enrolment Procedure as it has important information regarding COVID vaccination and your study at RMIT: https://policies.rmit.edu.au/document/view.php?id=209.
Please read the Student website for additional requirements of in-person attendance: https://www.rmit.edu.au/covid/coming-to-campus
Please check your Canvas course shell closer to when the course starts to see if this course requires mandatory in-person attendance. The delivery method of the course might have to change quickly in response to changes in the local state/national directive regarding in-person course attendance.
School: 520T Future Technologies
Campus: City Campus
Program: C5402 - Diploma of Information Technology
Course Contact: Trevor Stone
Course Contact Phone: +61 3 9925 4448
Course Contact Email: trevor.stone@rmit.edu.au
Name and Contact Details of All Other Relevant Staff
Nominal Hours: 60
Regardless of the mode of delivery, represent a guide to the relative teaching time and student effort required to successfully achieve a particular competency/module. This may include not only scheduled classes or workplace visits but also the amount of effort required to undertake, evaluate and complete all assessment requirements, including any non-classroom activities.
Pre-requisites and Co-requisites
None
Course Description
In this course you will gain the skills and knowledge required to produce structured query language (SQL) statements to work with server-side scripts, enabling web developers to interact with web server databases.
This course is delivered and assessed with the following: ICTDBS506 Design databases
National Codes, Titles, Elements and Performance Criteria
National Element Code & Title: |
ICTWEB451 Apply structured query language in relational databases |
Element: |
1. Identify database requirements |
Performance Criteria: |
1.1 Determine information required from database 1.2 Identify tables holding this information 1.3 Identify primary keys in these tables 1.4 Identify relationships between these tables including foreign keys |
Element: |
2. Build and implement SQL in relational databases |
Performance Criteria: |
2.1 Identify and build SQL statements according to task requirements 2.2 Create tables in a database using SQL statements 2.3 Create primary and foreign keys required in database table 2.4 Manipulate data in a database using SQL statements 2.5 Query database using SQL statements 2.6 Retrieve information from database using written SQL statements |
Element: |
3. Test and verify SQL results |
Performance Criteria: |
3.1 Construct test data and test SQL statements according to organisational procedures 3.2 Determine expected results of SQL statements 3.3 Verify result of constructed SQL statements against expected results and document findings 3.4 Confirm task requirements are met and obtain sign-off from required personnel |
Learning Outcomes
On successful completion of this course you will have developed and applied the skills and knowledge required to demonstrate competency in the above elements
Details of Learning Activities
Note this course is clustered with ISYS7590C - Design Databases - see Part B info in separate area in CAPs
A range of learning activities are planned for this course including self-paced and collaborative classroom based activities.
The collaborative classroom based activities will include theory sessions, practical lab sessions, tutorial sessions, and out-of-class research and homework.
We expect you to participate and contribute in all scheduled learning activities
Teaching Schedule
See week 1-12 for first part of clustered course on Database Design
| Week 13 | 1. Identify database requirements |
1.1 Determine information required from database 1.2 Identify tables holding this information |
| Week 14 | 1. Identify database requirements |
1.3 Identify primary keys in these tables 1.4 Identify relationships between these tables including foreign keys |
| Week 15 | 2. Build and implement SQL in relational databases |
2.1 Identify and build SQL statements according to task requirements 2.2 Create tables in a database using SQL statements 2.3 Create primary and foreign keys required in database table |
| Week 16 | 2. Build and implement SQL in relational databases |
2.4 Manipulate data in a database using SQL statements 2.5 Query database using SQL statements 2.6 Retrieve information from database using written SQL statements |
| Week 17 | 3. Test and verify SQL results |
3.1 Construct test data and test SQL statements according to organisational procedures 3.2 Determine expected results of SQL statements |
| Week 18 | 3. Test and verify SQL results |
3.3 Verify result of constructed SQL statements against expected results and document findings 3.4 Confirm task requirements are met and obtain sign-off from required personnel |
Learning Resources
Prescribed Texts
References
Other Resources
You will need laptop with charger for each class
- Windows laptops we do recommend an i5 or better machine with a minimum 4 GB of RAM.
- Macs you will need 8 GB of RAM to run a virtual machine
You may also refer to various media, websites, textbooks and e-books as indicated in Canvas such as
- USB or external hard drive for backup
- Text editor such as Visual Studio Code
Overview of Assessment
Assessment for this course is ongoing throughout the semester. Your knowledge and understanding of course content is assessed through participation in class exercises, oral presentations and through the application of learned skills and insights to your written tasks. Full assessment briefs will be provided and can be found on CANVAS
Assessment Tasks
Task 1 and 2 Practical Assessment.
Assessment Matrix
Element |
Performance criteria |
|
|
|
|
Assessment 1: Product - Case Study (Database Design & Implementation) - Stage1-3 |
Assessment 2: Practical -SQL |
1. Identify database requirements |
1.1 Determine information required from database |
STAGE 1 (1) |
|
1.2 Identify tables holding this information |
STAGE 2(4) |
|
|
1.3 Identify primary keys in these tables |
STAGE 2 (4) |
|
|
1.4 Identify relationships between these tables including foreign keys |
STAGE 2 (4) |
|
|
2. Build and implement SQL in relational databases |
2.1 Identify and build SQL statements according to task requirements |
|
Task 1,Task 2, Task 3 |
2.2 Create tables in a database using SQL statements |
|
Task 1,Task 2,Task 3 |
|
2.3 Create primary and foreign keys required in database table |
|
Task 1,Task 2,Task 3 |
|
2.4 Manipulate data in a database using SQL statements |
|
Task 3 Q12-Q20 |
|
2.5 Query database using SQL statements |
|
Task 3 Q1- Q11 |
|
2.6 Retrieve information from database using written SQL statements |
|
Task 3 Q1- Q11 |
|
3. Test and verify SQL results |
3.1 Construct test data and test SQL statements according to organisational procedures |
|
Task 1,Task 2, Task 3 |
3.2 Determine expected results of SQL statements |
|
Task 3 (Q1-Q20) |
|
3.3 Verify result of constructed SQL statements against expected results and document findings |
|
Task 3 (Q1-Q20) |
|
3.4 Confirm task requirements are met and obtain sign-off from required personnel |
STAGE 3 (9) |
|
Foundation Skills – This section is only completed when foundation are explicitly stated in the unit of competency. In most Training Packages the foundation skills are integrated into the unit of competency and this is clearly stated. | |||
Foundation skill |
Description |
Describe how each foundation skill is demonstrated through the relevant assessment. |
|
Reading |
Analyses and consolidates information and data from a range of sources, against defined criteria and requirements
|
STAGE 1 (4c) Design and document client access profiles in accordance with client’s requirements.
|
Task 1 (1a) Using Scenario and ERD determine information required to build the database model. |
Writing |
Uses clear and specialised technical syntax when creating codes and interrogating databases
Prepares and produces, diagrammatic models and associated documents and conveys complex relationships between data
|
STAGE 1 (1) Students will write a description (1-2 paragraphs) of the type of business they will create a database for.
STAGE 1 (2) Students will write ten to fifteen open-ended questions to determine database functional, technical, and security requirements (access profiles, multiple users, passwords, etc.).
STAGE 2 (1) Develop a conceptual model: Use Visio (or another computer-aided software engineering (CASE) or diagramming software) to create an ERD (First Draft). Show how objects are related – clarify the cardinality of relationships. |
Tasks 1,4 Document findings. |
Self-management |
Takes responsibility for planning, sequencing and prioritising tasks and own workload
|
STAGE 1 (6), STAGE 2 (5), STAGE 3 (9) Clearly define team member responsibilities, communication, and participation. |
|
Problem solving |
Makes routine decisions and implements standard procedures for routine tasks, using formal decision-making processes for more complex and non-routine situations Addresses less predictable problems and initiates standard procedures in response to these problems, applying problem-solving processes in determining a solution |
STAGE 3 (7b) Assume that your database is damaged or has data problems. Restore your database.
|
|
Technology |
Uses familiar digital technologies and systems to access information, search and enter, data and code, present information and communicate with others, cognisant of data security and safety |
STAGE 2 (1a) Use Visio (or another computer-aided software engineering (CASE) or diagramming software) to create an ERD (First Draft). STAGE 3 (1), Create a blank database. |
Tasks 1-3 Build the database model using MS SQL Server. |
Performance Evidence | ||
The candidate must demonstrate the ability to complete the tasks outlined in the elements, performance criteria and foundation skills of this unit, including evidence of the ability to: |
Assessment Task 1: Product - Case Study (Database Design & Implementation) - Stage1-3 |
Assessment 2 Practical -SQL |
|
Apply structured query language (SQL) to a relational database and gather required information according to task requirements.
|
|
Task 3 (Q1-Q20) |
|
In the course of the above, the candidate must:
generate queries for one or more tables and provide required data add, modify, retrieve and delete records from database tables as required test and verify SQL statements. |
|
Tasks 1-3 Tasks 1-3 are designed to create tables and insert data (Tasks1-2), and add modify, retrieve, delete records test and verify SQL statements (Task 3).
|
Knowledge Evidence | |||||
|
The candidate must be able to demonstrate knowledge to complete the tasks outlined in the elements, performance criteria and foundation skills of this unit, including knowledge of:
|
Assessment 1: Product - Case Study (Database Design & Implementation) - Stage1-3 |
Assessment 2: Practical -SQL |
|||
|
Organisational policies and procedures relating to SQL application including:
|
|
|
|||
Features and application of primary and foreign keys in database tables, aggregate functions and clause functions required in SQL application |
|
Tasks 1, 2, Task 3 (14,16, 17)
|
|||
|
Principles of "combining and/or condition" in SQL statements and Boolean operators:
|
|
Task 3 (5,9,10)
Task 3 (4,7,18,19, 20)
Task 3 (5,6)
Task 3 (4,7,11,14) |
|||
Documentation techniques relevant to applying structured query language in relational databases |
|
Task 4 (1)
|
|||
SQL statement testing methodologies |
|
Task 4 (2)
|
Assessment conditions |
Describe how assessments meet the 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: Task specification documentation Structured query language (SQL) Industry standard software package An integrated development environment (IDE) A database server Required hardware, software, and its component.
Assessors of this unit must satisfy the requirements for assessors in applicable vocational education and training. |
All assessments can be undertaken on RMIT premises in conditions which accurately reflect the typical environment and conditions experienced in industry. Students have full access to all equipment and resources they need to complete the assessments including:
|
Other Information
GENERAL SUBMISSION INFORMATION
Ensure that you submit assessments on or before the due date.
Always retain a copy of your assessment tasks.
For submissions you have to read, understand and agree to the content and expectations of the Assessment declaration.
Late Submission Procedures
- You are required to submit assessment items and/or ensure performance-based assessment is completed by the due dates.
Submit your assessment by the due date as any further submission will close unless extension or special consideration is granted - Apply for an extension of ONE WEEK
- By emailing the filled document Application-for-extension-of-time-to-submit-work.pdf to your teacher at least one business day before the due date and wait for approval. e.g. by Friday if due date is Sunday.
- Submit this approved form along with your assessment.
- If you need an extension of time BEYOND the one week, apply online for special consideration using this link
- You will be given ONE more opportunity to RESUBMIT the ASSESSMENT TASK
- This will be open in week 16
- No further re-submission will be available
- A satisfactory result for all assessments will result in a CA - Competency Achieved for this course.
- An unsatisfactory result for any assessment will result in a NYC - Not Yet Competent for this course
Course Overview: Access Course Overview