Part B: Course Detail
Teaching Period: Term2 2022
Course Code: ISYS7590C
Course Title: Design 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: 50
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
Mone
Course Description
In this course you will gain skills and knowledge required to establish client needs and technical requirements and to design a database that meets identified requirements.
Is delivered and assessed with the following course ICTWEB451 Apply structured query language in relational databases
National Codes, Titles, Elements and Performance Criteria
National Element Code & Title: |
ICTDBS506 Design databases |
Element: |
1. Determine database requirements |
Performance Criteria: |
1.1 Conduct user-needs analysis with required personnel and determine database functionality requirements |
Element: |
2. Develop logical data model |
Performance Criteria: |
2.1 Identify attributes and determine data types |
Element: |
3. Design data structures |
Performance Criteria: |
3.1 Confirm primary and foreign keys according to technical requirements |
Element: |
4. Design queries, screens and reports |
Performance Criteria: |
4.1 Design database according to organisational requirements |
Element: |
5. Design access and security systems |
Performance Criteria: |
5.1 Review existing business security plan and use as design base |
Element: |
6. Confirm database design |
Performance Criteria: |
6.1 Determine database backup and recovery requirements |
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
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)
- 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. Determine database requirements | 1.1 Conduct user-needs analysis with required personnel and determine database functionality requirements | STAGE 1 (2) | |
| 1.2 Determine user-needs analysis technical requirements | STAGE 1 (3,4) | ||
| 1.3 Develop conceptual model of database according to organisational requirements | STAGE 2 (1) | ||
| 1.4 Submit conceptual model to required personnel | STAGE 2 (2) | ||
| 1.5 Seek and respond to feedback and make required changes | STAGE 1 (4,5) | ||
| 2. Develop logical data model | 2.1 Identify attributes and determine data types | STAGE 2 (4c) | |
| 2.2 Undertake normalisation of attributes | STAGE 2 (4b) | ||
| 2.3 Develop entity-relationship (ER) diagram and clarify cardinality of relationships | STAGE 2 (1) | ||
| 2.4 Document attributes, normalised data and ER diagram | STAGE 2 (4) | ||
| 2.5 Submit documentation to required personnel and seek and respond to feedback | STAGE 2 (3,4) | ||
| 3. Design data structures | 3.1 Confirm primary and foreign keys according to technical requirements | STAGE 3 (2c) | |
| 3.2 Identify referential integrity constraints and organisational business rules | STAGE 2 (4d) | ||
| 3.3 Establish database management system constraints and incorporate into database design | STAGE 2 (4a) | ||
| 3.4 Design and develop data validation rules, indexes and data dictionary | STAGE 2 (4c) | ||
| 3.5 Document database design according to organisational policies and procedures | STAGE 2 (4g) | ||
| 4. Design queries, screens and reports | 4.1 Design database according to organisational requirements | STAGE 3 (1,2,3) |
STAGE 1 (Task 1) STAGE 2 (Task 1) |
| 4.2 Design required queries and output reports | STAGE 3 (5,6) |
STAGE 1 (Task 3) STAGE 2 (Task 3) |
|
| 4.3 Compare physical design against conceptual model and user-needs analysis | STAGE 2 (4e) | ||
| 4.4 Incorporate all required changes into database design | STAGE 2 (4g) | ||
| 5. Design access and security systems | 5.1 Review existing business security plan and use as design base | STAGE 1 (2,3) | |
| 5.2 Design database password and access system | STAGE 3 (8) | ||
| 5.3 Identify multiple-user requirements | STAGE 1 (2,3) | ||
| 5.4 Develop required organisational access profiles | STAGE 1 (4c) | ||
| 6. Confirm database design | 6.1 Determine database backup and recovery requirements | STAGE 3 (7a,7b) | |
| 6.2 Develop and document database backup and restore procedures | STAGE 3 (7c) | ||
| 6.3 Submit database and documentation to required personnel | STAGE 3 (8) | ||
| 6.4 Seek and respond to feedback from required personnel |
STAGE 2 (2) STAGE 2 (3) |
||
| 6.5 Obtain final task sign off from required personnel | STAGE 3 (9) |
Course Overview: Access Course Overview