Part B: Course Detail
Teaching Period: Term2 2022
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: Luke Eberbach
Course Contact Phone: +61 3 9925 4381
Course Contact Email: Luke.Eberbach@rmit.edu.au
Name and Contact Details of All Other Relevant Staff
Chandra Nannegari
Email: chandra.nannegari@rmit.edu.au
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 |
Element: |
2. Build and implement SQL in relational databases |
Performance Criteria: |
2.1 Identify and build SQL statements according to task requirements |
Element: |
3. Test and verify SQL results |
Performance Criteria: |
3.1 Construct test data and test SQL statements according to organisational procedures |
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
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 nominal hours associated with this are a guide only and represent the total teaching time and student effort required to successfully complete the course. This may include not only scheduled classes but also the amount of effort required to undertake, evaluate and complete all assessment requirements, including any non-classroom activities.
| Semester 2, 2022 | |||
| Week | Date | Topic/Description | Assessment |
| 1 | 4-10 Jul |
Design Databases: Introduction to DB design SQL: Simple SQL statements to retrieve and sort data |
|
| 2 | 11-17 Jul |
Design Databases: Key sources of data. Business rules. SQL: SQL statements that selectively retrieve data. |
TASK 1 (Product) Stage 1 - Project Specifications issued. Project Teams selected |
| 3 | 18-24 Jul |
Design Databases: Business documents & business rules. SQL: SQL statements to extract data from more than one table. |
|
| 4 | 25-31 Jul |
Design Databases: Gathering data. Referential integrity. SQL: SQL in multi-table table queries continued |
|
| 5 | 1-7 Aug |
Design Databases: SQL Server installation & tables. Intro to security. DB requirements & design improvements SQL: SQL aggregation and filtering of data. |
TASK 1 (Product) Stage 1 - DATABASE PROPOSAL (upload to canvas) due |
| 6 | 8-14 Aug |
Design Databases: Developing questions. Conceptual, logical & physical models. Forms & sub-forms SQL: SQL sub-queries |
|
| 7 | 15-21 Aug |
Design Databases: Analysing data. Suitable data types. SQL: SQL functions |
TASK 1 (Product) Stage 2 - Design (submit printed copy in class to teacher) Draft due. Individual Work.
TASK 2 (Practical) Stage 1 - SQL Queries - In Class Practical Observation. Individual Work. |
| 8 | 22-28 Aug |
Design Databases: Data integrity & data validation. SQL: SQL action Queries |
|
| Mid-Semester break (29 Aug to 4th Sep) | |||
| 9 | 5-11 Sep |
Design Databases: SQL Server security: logins, database roles & permissions. Class work on project SQL: Views |
(SQL Server) - Security (Demonstration)
TASK 1 (Product) Stage 2 - Design (submit to canvas) - Team submission |
| 10 | 12-18 Sep |
Design Databases: Backup & restore procedures in SQL Server SQL: Stored procedure |
Task 1 (Product) Stage 3 - Q7 (SQL Server) - Backup & Restore (Demonstration) - Individual |
| 11 | 19-25 Sep |
Design Databases: Review of normalisation. SQL: Left Join, Right Join & UNION |
|
| 12 | 26 Sep-2 Oct |
Design Databases: Format of security & backup plans. SQL: Revision |
|
| 13 | 3-9 Oct |
Design Databases: Reports with grouped data. SQL: Pending exercises |
TASK 1 (Product) Stage 3 - TEAM DATABASE DESIGN & IMPLEMENTATION (Draft) due |
| 14 | 10-16 Oct |
Design Databases: Project submission & demonstrations SQL: Assessment work |
TASK 1 (Product) Stage 3 - TEAM DATABASE & IMPLEMENTATION due (upload to canvas) |
| 15 | 17-23 Oct |
Design Databases: Final class work on project & documentation SQL: Assessment work |
TASK 2( Practical) Stage 2 - TEAM - SQL - Submit to canvas. |
| 16 | 24-30 Oct | Re-submission of work if required. Course & assessment feedback |
|
| 17 | 31 Oct - 6 Nov | Re-submission of work if required. Course & assessment feedback |
Learning Resources
Prescribed Texts
The language of SQL, Larry Rockoff |
978-1-4354-5751-5 |
References
Other Resources
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
Summary and Purpose of Assessment
This course is a cluster of TWO competencies:
- (ISYS7590C) ICTDBS506 Design databases
- (COSC7395C) ICTWEB451 Apply structured query language in relational databases
You are required to complete TWO assessments – Task 1 (Product) and Task 2 (Practical) You must successfully complete both the assessments to be deemed competent in this course.
- Task 1 (Product) is delivered in three stages (Stage 1, Stage 2, Stage 3). All of them are team submissions.
- Task 2 (Practical) is delivered in two stages. Stage 1 is an individual submission and Stage 2 is a team submission.
Task 1 (Product) Stage 1: Project Plan (Due: Week 05)
- Write a description (1-2 paragraphs) of the type of business you will create a database for.
- Write ten to fifteen open-ended questions to determine database functionality requirements.
- Meet with clients (teacher/assessor and other students) to determine functional, technical, and security requirements.
- Analyse client’s (teacher and other students) answers to determine database functionality and security requirements.
- Evaluate client’s (teacher’s) feedback and make changes (if required). Resubmit your work after making required changes.
- Clearly define team member responsibilities, communication, and participation.
Task 1 (Product) Stage 2: Database Design (Due: Week 09)
- Develop a conceptual (entity relationship) model.
- Explain how developed conceptual model meets client’s requirements.
- Ask for client’s feedback.
- Respond to feedback.
- Will make required changes.
- Define team member responsibilities, communication, and participation.
Task 1 (Product) Stage 3: Database implementation (Due: Week 14)
- Create a blank, desktop database with your student IDs (studentID1_studentID2) in the filename.
- Add the required number of tables and join tables to hold client’s data.
- Manage relationships between tables (connect tables).
- Create queries and sub-queries to search for, sort and retrieve data.
- Build data entry and display forms.
- Build reports to display and print formatted data.
- Will back up your database.
- Encrypt your database with a password to protect access to your database (studentID1_studentID2_backup).
- Define team member responsibilities, communication, and participation.
- Submit the complete database (MS Assess file), and all required documents to the client (teacher/assessor) for the final approval.
Task 1 (Product) Stage 3 -Q7 (SQL Server) - Backup & Restore (Due: Week 10)
- (Demonstration) - Individual
Task 2 (Practical) Stage 1: SQL Queries (Due : Week 7)
- Build Database in MS Access and populate sample data
- Demonstration of SQL queries in MS Access
- And submission to canvas.
Task 2 (Practical) Stage 2: SQL Queries (Due : Week 15)
- Designing SQL Queries in MS SQL Server
- Submission to Canvas
Assessment Matrix
| Element | Performance Criteria | ||
|
Assessment Task 1: Product - Case Study (Database Design & Implementation) - Stage1-3 |
Assessment Task 2: Practical -SQL – Stage1-2 |
||
| 1. Identify database requirements | 1.1 Determine information required from database | STAGE 1 (1) | STAGE 1 -Task 1a |
| 1.2 Identify tables holding this information | STAGE 1 - Task 1b | ||
| 1.3 Identify primary keys in these tables | STAGE 2 (4) | STAGE 1 - Task 1d, STAGE 2 - Task 1c | |
| 1.4 Identify relationships between these tables including foreign keys | STAGE 2 (4) | STAGE 1 - Task 1e, f | |
| 2. Build and implement SQL in relational databases | 2.1 Identify and build SQL statements according to task requirements | STAGE1 - Task 3, STAGE 2 - Task 3 |
|
| 2.2 Create tables in a database using SQL statements | STAGE 2 - Task 1b | ||
| 2.3 Create primary and foreign keys required in database table | STAGE 2 - Task 1c | ||
| 2.4 Manipulate data in a database using SQL statements | STAGE 1 - Task 3 (4), STAGE 2 - Task 3 (3,4,8,9,12,16,18,19,20) |
||
| 2.5 Query database using SQL statements | STAGE 1 - Task 3 (1, 2, 3, 4, 5, 6) | ||
| 2.6 Retrieve information from database using written SQL statements | STAGE 2 - Task 3 (1,2,5,6,7,10,11,13,14, 15,17) | ||
| 3. Test and verify SQL results | 3.1 Construct test data and test SQL statements according to organisational procedures | STAGE 2 - Task 3 | |
| 3.2 Determine expected results of SQL statements | STAGE 2 - Task 3 | ||
| 3.3 Verify result of constructed SQL statements against expected results and document findings | STAGE 2 - Task 3 | ||
| 3.4 Confirm task requirements are met and obtain sign-off from required personnel | STAGE 3 (9) | STAGE 2 (Task 5) |
Course Overview: Access Course Overview