Course Title: Design a database

Part B: Course Detail

Teaching Period: Term2 2011

Course Code: COSC5331C

Course Title: Design a database

School: 650T TAFE Business

Campus: City Campus

Program: C5194 - Diploma of Information Technology (General)

Course Contact : Chandra Nannegari

Course Contact Phone: +61 3 9925 1657

Course Contact Email:chandra.nannegari@rmit.edu.au


Name and Contact Details of All Other Relevant Staff

Nawal Shomali

Phone: +61 3 9925 5826 
Email: Nawal.Shomali@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

None

Course Description

This unit defines the competency required to establish client needs and technical requirements and to design a database that meets those requirements.


National Codes, Titles, Elements and Performance Criteria

National Element Code & Title:

ICAA5139A Design a database

Element:

1. Determine database requirements

Performance Criteria:

1.1 Meet with client and conduct a user needs analysis to determine database functionality
1.2 Analyse results of user needs analysis to identify technical requirements
1.3 Develop a conceptual model of the database
1.4 Submit conceptual model to client for review
1.5 Evaluate client feedback and make changes as required

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 diagram to clarify cardinality of relationships
2.4 Document attributes, normalised data and entity relationship diagram
2.5 Forward documentation to client for confirmation

Element:

3. Design data structures

Performance Criteria:

3.1 Confirm primary and foreign keys for tables
3.2 Review client business rules
3.3 Identify referential integrity constraints
3.4 Establish relevant database management system constraints and incorporate into database design
3.5 Develop validation rules for data
3.6 Design indexes and develop data dictionary
3.7 Document the database design

Element:

4. Design queries, screens and reports

Performance Criteria:

4.1 Design user interface for database, including menus, input screens and outputs
4.2 Design queries based on requirements
4.3 Design output reports based on requirements
4.4 Compare physical design with conceptual model/user needs analysis
4.5 Incorporate changes as required

Element:

5. Design access and security systems

Performance Criteria:

5.1 Review business security plan as basis for commencing access and security design
5.2 Design password and access system for database
5.3 Identify multiple user requirements
5.4 Develop client access profiles using client business model

Element:

6. Confirm database design

Performance Criteria:

6.1 Identify database back up and recovery requirements
6.2 Develop and document the database back up and recovery procedures
6.3 Submit database and documentation to client for final approval


Learning Outcomes



Details of Learning Activities

Learning activities will include:

  • theory presentations
  • practical lab classes
  • tutorial sessions
  • out-of-class research and homework


Teaching Schedule

 Week  Week commencing  Theory Session Topics  Practical Laboratory Session Topics
 1  04/07/2011  Course overview.
 Review of relational DB concepts.
 Determinant & Dependencies
 Teacher handouts in-class exercises.
 Submit Worksheet for Assessment – 1.
 2  11/07/2011  Data Redundancy & Anomalies  Teacher handouts in-class exercises.
 Submit Worksheet for Feedback
 3  18/07/2011  Steps in Normalisation.
 Relational Schema.
 Normal Forms -1NF & 2NF
 Teacher handouts in-class exercises
 Submit Worksheet for Feedback
 4  25/07/2011  Normal Forms - 3NF.
 Steps in Database Development
 Teacher handouts in-class exercises
 Submit Worksheet for Assessment – 1
 5  01/08/2011  ERD concepts.
 Binary & Ternary relationships
 Teacher handouts in-class exercises
 Submit Worksheet for Feedback
 6  08/08/2011  ERD’s continued.
 Recursive relationships. IS A relationship
 Teacher handouts in-class exercises.
 Submit Worksheet for Assessment – 1Project Posting
 7  15/08/2011  Developing a design.
 Index, Data Dictionary
 Teacher handouts in-class exercises
 Submit Worksheet for Assessment – 1
 8  22/08/2011  Business Rules, SQL – MySQL Queries  Teacher handouts in-class exercises
 Submit Worksheet for Assessment – 1
  Mid Semester Break (Monday 29 August - Friday 2 September)
 9  05/09/2011  SQL – Queries – Continued  Teacher handouts in-class exercises - SQL Continued.
 Data validation.
 10  12/09/2011  Identification of relevant software.
 DBMS constraints
 Assessment – 2: Stage 1: Project submission -(ERD, RS and BR)
 11  19/09/2011  Access and security  Project and Test Feedback.
 Submit DB for Assessment – 1
 12  26/09/2011  DB design documented and confirmed  Project Feedback.
 Teacher handouts in-class exercises. Submit Worksheet for Assessment – 1
 13  03/10/2011  Design Reports  Submit DB for Assessment – 1 . Project Feedback
 14  10/10/2011  Backup and recovery requirements  Backup and recovery.
 Submit DB for Assessment – 1
 15  17/10/2011  Assessment – 2: Project submission and Demonstrations.
 Feedback on Competency (CA / NYC).
 Revision
 16  24/10/2011  Assessment – 3: TEST

Please Note: The total number of scheduled hours of teaching, learning and assessment involved in this course will include all planned activities. This may incorporate face-to-face classes, lectures, workshops and seminars; wherever possible workplace visits, online learning and other forms of structured teaching and learning. The total scheduled hours includes the amount of effort required to undertake, evaluate and complete all assessment requirements, including observation of work performance, discussions with supervisors and others providing third party evidence and one on one and group assessment sessions with students.


Learning Resources

Prescribed Texts

Peter Rob , Carlos Coronel & Steven Morris, Database Systems: Design, Implementation & Management, 9th Edition, Course Technology

0-538-46968-4


References

David M. Kroenke, Database Processing: Fundamentals, Design & Implementation, 9th Edition. Prentice Hall

0-13-120971-X

Jeffrey A. Hoffer, Prescott & Topi, Modern Database Management 9/E , Prentice Hall

978-0-13-6003


Other Resources


Overview of Assessment

Assessment tasks will consist of a portfolio of in-class exercises, a project with associated documentation, and a combined theory and practical test.

You will be provided with feedback throughout the course to check your progress.

You are advised that you are likely to be asked to personally demonstrate your assessment work to your teacher to ensure that the relevant competency standards are being met.



Assessment Tasks

There will be 4 assessments throughout the semester. Assessments description and maximum % grade allocation as follows:

  • Assessment 1 (Worksheets): 30%
    The purpose of these worksheets (Teacher handouts in-class exercises) is to provide feedback to students from class exercises.
    Printouts submitted during class. In addition, Element 2, Element 3, Element 4, Element 5 and Element 6 of the competency will be assessed through the class exercise worksheets.
  • Assessment 2 (Project) 45%
    The purpose of this Project is to ensure that students are able to implement all the skills they have developed to date.
    Firstly, students will be required to model a database using client specifications. Each student is expected to develop an individual design using VISIO. Design faults will be eliminated via several prototyping stages, using client feedback.
    Sample data will then be loaded once the database design is implemented on a RDBMS platform, and queries will be created in SQL. Forms, reports and VBA modules will also be required. Basic security features and backup procedures will also be implemented.
  • Assessment 3 (Test) 25%
    A final Test will be offered to students who have achieved CA.


Assessment Matrix

ICAITAD139A
Design a Database
Assessment  1
Worksheets
(CA / NYC), 30%
Assessment 2
Project
(CA / NYC), 45%
Assessment 3
Test
25%
1. Determine database requirements V  
2. Develop logical data model V V V
3. Design data structures V V V
4. Design queries, screens and reports V V V
5. Design access and security systems V V  
6. Confirm database design V V V
 

Other Information

Competency Requirements

  • To be deemed competent students must demonstrate an understanding of all elements of this competency. Assessment methods have been designed to measure achievement of each competency in a flexible manner over multiple tasks.
  • Students are advised that they are likely to be asked to personally demonstrate their work to their teacher to ensure that the relevant competency standards are being met.
  • All competency-based assessments for this course must be completed in order to achieve a CA (Competency Achieved) award. Each assessment task will also carry a numerical weighting to be used for grading purposes. The grade applied will only be awarded if a CA has been achieved in all compulsory assessment tasks.
  • If a student has been assessed as NYC (Not Yet Competent) in any of the assessment tasks, the weighting will carry no value until the student has obtained a CA. If a CA is achieved on re-assessment, a maximum mark of 50% of the original weighting will be granted for that assessment.
  • Re-assessments will only be granted to students who were either unsuccessful in the first attempt, or have an approved application for special consideration.
  • A final examination worth 25% will be offered to students who have achieved CA.

Course Overview: Access Course Overview