Part B: Course Detail

Teaching Period: Term1 2025

Course Code: COSC7395C

Course Title: Apply structured query language in relational databases

Important Information:

Please note that this course may include compulsory in-person attendance requirements for some or all teaching activities.

It is recommended that you check your Canvas course shell closer to the course start date for details about any mandatory in-person attendance requirements.

Please be aware that the course delivery method may need to change quickly in response to evolving course requirements. Students must maintain regular communication with their teachers to stay informed about any updates.

School: 520T Future Technologies

Campus: City Campus

Program: C5402 - Diploma of Information Technology

Course Contact: Trevor Stone

Course Contact Phone: +61 3 9925 4612

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 clustered for delivery and assessment with the following unit: 

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 the candidate will demonstrate the ability to complete the tasks outlined in the elements, performance criteria and foundation skills of this unit.


Details of Learning Activities

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

 The proposed teaching schedule for ICTWEB451 is detailed below: Week Date TopicAssessment / Learning activities
Week 1 10-16 February

1. Determine database requirements

1.1 Conduct user-needs analysis with required personnel and determine database functionality requirements

1.2 Determine user-needs analysis technical requirements
Week 2 17-23 February 1. Determine database requirements

1.3 Develop conceptual model of database according to organisational requirements

1.4 Submit conceptual model to required personnel

1.5 Seek and respond to feedback and make required changes

Assessment 1 - Stage 1 released
Week 3 24 Feburary-2 March

2. Develop logical data model

2.1 Identify attributes and determine data types

2.2 Undertake normalisation of attributes
Week 4 3-9 March 2. Develop logical data model

2.3 Develop entity-relationship (ER) diagram and clarify cardinality of relationships

2.4 Document attributes, normalised data and ER diagram

2.5 Submit documentation to required personnel and seek and respond to feedback
Week 5 10-16 March

3. Design data structures

3.1 Confirm primary and foreign keys according to technical requirements

3.2 Identify referential integrity constraints and organisational business rules

Assessment 1 - Stage 1 due
Week 6 17-23 March

3. Design data structures

3.3 Establish database management system constraints and incorporate into database design

3.4 Design and develop data validation rules, indexes and data dictionary

3.5 Document database design according to organisational policies and procedures

Assessment 1 - Stage 2 released
Week 7 24-30 March

4. Design queries, screens and reports

4.1 Design database according to organisational requirements

4.2 Design required queries and output reports
Week 8 31 March - 6 April

4. Design queries, screens and reports

4.3 Compare physical design against conceptual model and user-needs analysis

4.4 Incorporate all required changes into database design

Assesment 1 - Stage 2 due
Week 9 7-13 April

5. Design access and security systems

5.1 Review existing business security plan and use as design base

5.2 Design database password and access system

Assesment 1 - Stage 3 released
Week 10 14-20 April

5. Design access and security systems

5.3 Identify multiple-user requirements

5.4 Develop required organisational access profiles

 
Mid-Semester Break 18-25 April

 

  
Week 11 28 April - 4 May

6. Confirm database design

6.1 Determine database backup and recovery requirements

6.2 Develop and document database backup and restore procedures
Week 12 5-11 May

6. Confirm database design

6.3 Submit database and documentation to required personnel

6.4 Seek and respond to feedback from required personnel

6.5 Obtain final task sign off from required personnel

Assessment 1 - Stage 3 due
Week 13 12-18 May

1. Identify database requirements

1.1 Determine information required from database

1.2 Identify tables holding this information

Assessment 2 released
Week 14 19-25 May

1. Identify database requirements

1.3 Identify primary keys in these tables

1.4 Identify relationships between these tables including foreign keys
Week 15 26 May - 1 June 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-8 June 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

Assessment 2 due
Week 17 9-15 June

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 16-22 June

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

 

*Please note that this timeline is subject to change based on semester requirements. We recommend checking your Canvas course shell regularly to stay updated with the latest schedule. 

Student directed hours involve completing activities such as reading online resources, assignments, individual student/teacher course-related consultation. Students are required to self-study the learning materials and complete the assigned out of class activities for the scheduled non-teaching hours. 


Learning Resources

Prescribed Texts


References


Other Resources

Software Requirements:

Microsoft Office (Word, Access)

Microsoft Visio (To Create ER Diagrams)

XAMPP (MySQL & Web Server) - Open Source Software

Virtual Box- Open Source Software


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 and various types of assessments.

Full assessment briefs will be provided and can be found on CANVAS.


Assessment Tasks

This unit has the following assessments:

  • Task 1 Case Study
  • Stage 1
  • Stage 2
  • Stage 3
  • Task 2 Practical Assessment

 

Feedback

Feedback will be provided throughout the semester in class and/or online discussions.  You are encouraged to ask and answer questions during class time and online sessions so that you can obtain feedback on your understanding of the concepts and issues being discussed. Finally, you can email or arrange an appointment with your teacher to gain more feedback on your progress.

You should take note of all feedback received and use this information to improve your learning outcomes and final performance in the course.  


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:

  • Identifying SQL requirements from job specifications
  • Testing and verifying SQL results

 

Task 1

Task 3

 

  1.  

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:

  • IN and BETWEEN conditional operators
  • Mathematical operators
  • Table joins (relationships)

 

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:

    • Internet access
    • SQL Server (installed on student laptops)
    • SQL Server (server) used to create SQL statements (AT2).
    • MS VISIO installed on (https://mydesktop.rmit.edu.au/)
    • Task specific documentation and requirements provided in the assessment description.
    • Assessors satisfy the requirements for assessors in applicable vocational education and training.

 

 

Other Information

Credit Transfer and/or Recognition of Prior Learning (RPL): 

 You may be eligible for credit towards courses in your program if you have already met the learning/competency outcomes through previous learning and/or industry experience. To be eligible for credit towards a course, you must demonstrate that you have already completed learning and/or gained industry experience, that is:   

• Relevant 
• Current 
• Satisfies the learning/competency outcomes of the course   

To find more information about credit transfer and RPL, please refer to the following link: https://www.rmit.edu.au/students/my-course/enrolment/apply-for-credit 

 Study and Learning Support:   

RMIT University Library provides free study support services and resources to help you build your academic skills. 
 
Study and Learning Centre (SLC) provides free learning and academic development advice to you. Services offered by SLC to support your numeracy and literacy skills are:   

• Assignment writing, thesis writing and study skills advice 
• Math and science developmental support and advice 
• English language development 

To find more information about Study and Learning Support, please refer to the following link: https://www.rmit.edu.au/students/support-services/study-support   

Equitable Learning Services (ELS):   

The Equitable Learning Services team (ELS team) supports and creates equal opportunities for students with a disability, long-term illness and/or mental health condition. We also support primary carers.    

ELS works in partnership with students to create an Equitable Learning Plan. Your plan is tailored to you, supports your needs and establishes how RMIT can provide ongoing assistance so you can access and participate in your studies. The ELS team can assist you to manage your Equitable Learning Plan.   

To find more information about services offered by Equitable Learning Services (ELS), please refer to the following link: https://www.rmit.edu.au/students/support-services/equitable-learning 

  

Extensions and Special Consideration    

Extensions:   

• Extensions are available for unforeseen circumstances of a short-term nature.   

• Applications must be submitted to the school at least one working day before the due date of the assessment.   

• Extensions can be approved for up to 7 days past the due date for an assessment. (Where students need an extension exceeding 7 days, they must instead apply for special consideration.)   

 

Special Consideration:   

• An application for special consideration is made in advance of an assessment wherever possible, but will normally be accepted within five working days after the assessment date. For more information, see the Special Consideration page of the RMIT website. 

Plagiarism: 

Plagiarism is a form of cheating and it is very serious academic offence that may lead to expulsion from the University. 

Please Refer: www.rmit.edu.au/academicintegrity to find more information about plagiarism. 

Other Information: 

All email communications will be sent to your RMIT email address and you must regularly check your RMIT emails.

Course Overview: Access Course Overview