Course Title: Design databases

Part B: Course Detail

Teaching Period: Term1 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
1.2 Determine user-needs analysis technical 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

Element:

2. Develop logical data model

Performance Criteria:

2.1 Identify attributes and determine data types
2.2 Undertake normalisation of attributes
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

Element:

3. Design data structures

Performance Criteria:

3.1 Confirm primary and foreign keys according to technical requirements
3.2 Identify referential integrity constraints and organisational business rules
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

Element:

4. Design queries, screens and reports

Performance Criteria:

4.1 Design database according to organisational requirements  
4.2 Design required queries and output reports
4.3 Compare physical design against conceptual model and user-needs analysis
4.4 Incorporate all required changes into database design

Element:

5. Design access and security systems

Performance Criteria:

5.1 Review existing business security plan and use as design base
5.2 Design database password and access system
5.3 Identify multiple-user requirements
5.4 Develop required organisational access profiles 

Element:

6. Confirm database design

Performance Criteria:

6.1 Determine database backup and recovery requirements
6.2 Develop and document database backup and restore procedures 
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


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
•  Course introduction & overview. Relational database concepts.
•  Introduction to Access. Web forms & data entry. 2-table relationships  

SQL:

Simple SQL statements to retrieve and sort data

 
2 11-17 Jul

Design Databases:

Key sources of data. Business rules.
Basic ERDs. 1:M relationships & foreign keys

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.
More on ERDs: M:N relationships & bridging tables

SQL:

SQL statements to extract data from more than one table.

 
4 25-31 Jul

Design Databases:

Gathering data. Referential integrity.
Building up complex ERDs

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.
Redundancy, dependencies & normalisation

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.
Groupable data. Forms & reports

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 
Review of client requirements. Data dictionaries

SQL:

Stored procedure

Task 1 (Product) Stage 3 - Q7  (SQL Server) - Backup & Restore (Demonstration) - Individual
11 19-25 Sep

Design Databases:

Review of normalisation.
Class work on project & documentation

SQL:

Left Join, Right Join & UNION

 
12 26 Sep-2 Oct

Design Databases:

Format of security & backup plans.
Class work on project & documentation

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