Course Title: Monitor and administer a database

Part B: Course Detail

Teaching Period: Term2 2012

Course Code: COSC6080C

Course Title: Monitor and administer a database

School: 650T TAFE Business

Campus: City Campus

Program: C5194 - Diploma of Information Technology (General)

Course Contact : Paul Lewis

Course Contact Phone: +61 3 9925 1615

Course Contact

Name and Contact Details of All Other Relevant Staff

Course Contact:  Channdra Nannagari:

Course Contact Phone:  +61 3 9925 1615

Course Contact Email:

Nominal Hours: 30

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


Course Description

In this unit you will develop the basic knowledge and skills required to implement a range of database monitoring and administration tasks on an SQL server.

Major tasks include implementing security and back-ups in accordance with organisational requirements.

This unit is delivered using classroom-based and activities. Self-directed work can be carried out by installing a virtual machine on your home computer. Please ask for assistance if required.

If you are undertaking this course in Melbourne from semester 2, 2012 onward your class will be held in a specialist computing lab. Students are not required to have their own mobile computing device during class time. It is however recommended that you have access to a mobile computing device to allow greater flexibility in terms of where you can work on campus outside class times.

National Codes, Titles, Elements and Performance Criteria

National Element Code & Title:

ICAS4125B Monitor and administer a database


1.  Start up a database

Performance Criteria:

1.1. Configure system for database start-up
1.2. Monitor database start-up and operation for irregularities


2.  Manage database

Performance Criteria:

2.1. Take action to ensure that a data dictionary has been compiled and that data structures are in place
2.2. Maintain data integrity constraints according to business requirements
2.3. Create and design indexes and multiple-field keys according to business requirements
2.4. Monitor the locking options chosen for the database
2.5. Confirm that recent back-ups of the database have been stored and that back-ups can be retrieved as a full working copy
2.6. Monitor the data storage space for ongoing viability and resize as needed
2.7. Update data according to organisational guidelines


3.  Manage database access

Performance Criteria:

3.1. Allocate or remove access privileges according to user status
3.2. Monitor network server log-in log file for illegal log-in attempts or for security breaches
3.3. Manage system resources in the context of database administration

Learning Outcomes

Details of Learning Activities

Learning activities will include laboratory-based sessions on a variety of topics as shown in the Teaching Schedule below. Learning activities will generally consist of practical computer exercises based on teacher handouts.

Students should attend all sessions. Students will also be expected to undertake work on a small Web project and other activities for homework.

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.
WeekWeek CommencingTopicsAssessment
025th Jun 2012Pre-training review:
• LLN as required

Familiarisation with:
• Program
• Services/facilities
• Other students
• Teacher(s)
• Where to get support
• Student responsibilities
• Course requirements
• Course support documents
• Pre-reading
• Blackboard

Overview of assessment requirements:

• Grading
• Plagiarism
• Appeals
• Extensions
• Feedback
• Privacy
• Submission requirements
• Resubmission policy

Installation of Operating System
• Installation of Windows server 2008
• Optimisation of Windows settings
• Installation of required software
12nd Jul 2012Configuring Operating Systems
• Installation of drivers
• Testing connectivity
• Setting up Active Directory
• Setting Group Policies
• Creating domain accounts
Installation of SQL Server 2008
29th Jul 2012Simple DBMS Exercises
• Creating a 2-table database & view
• Attaching databases
• Creating Database Diagrams
• Importing Access files
TASK 1 - Practical Tasks reviewed
316th Jul 2012 Creating Database Objects
• Data types & constraints
• Creating tables, views & stored procedures
423rd Jul 2012Creating Database Objects (CTD)
• Creating queries & triggers
• Creating functions & indexes
• Creating SQL Server reports
TASK 1 - Practical Tasks reviewed
530th Jul 2012STUDENT STUDY WEEKNo classes / SAB open
66th Aug 2012Database Sizing Issues
SQL Server Security
• Creating domain users & groups
TASK 1 - Practical Tasks reviewed
713th Aug 2012SQL Server Security (CTD)
• Creating logins, roles & mappings
• Assigning permissions
820th Aug 2012Backups & Restores
• Creating backups sets & different types of backups
• Performing restores
MID-SEMESTER BREAK (27th Aug - 31st Aug 2012)
93rd Sep 2012Major Revision Exercises
• Revision exercise on security
• Revision exercise on indexes
1010th Sep 2012Revision Exercise & Research
• Revision exercise on backups
• Theory on DBMS concepts
1117th Sep 2012Database Monitoring
Creating operators, jobs & alerts
TASK 1 - Practical Tasks reviewed
1224th Sep 2012Theory Test conducted
More on Database Monitoring

• Using Windows’ system monitor
• Creating alerts & traces
TASK 2 - Theory Test conducted
131st Oct 2012Practical Networking Exercise
• Installation of Windows 8
• Setting folder permissions
• Joining a domain
• Testing a network drive
TASK 2 - Theory Test feedback given
148th Oct 2012Revision for Practical TestTASK 1 - Practical Tasks reviewed
1515th Oct 2012Practical Test conductedTASK 3 - Practical Test conducted
1622nd Oct 2012Course & assessment feedbackTASK 3 - Practical Test feedback given
1729th Oct 2012Re-submission of work if required  

Learning Resources

Prescribed Texts

Jorden, Joseph L. (2007), SQL Server 2005 DBA STREET SMARTS, Wiley Publishing ISBN 0-470-08349-2

This book is pitched at an ideal level for beginning students who wish to move towards certification-level. It is also very moderately priced.

It is expected that all students will purchase a copy of this text as soon as possible. Students will be working directly on practical tasks from this book in most classes.



Other Resources

Other materials will include worksheets distributed by the teacher in class and various Websites which will be indicated.

In the first part of the course books on SQL Server 2005 or 2008 Express may also be useful, particularly the sections related to monitoring and administration.

Overview of Assessment

Students must demonstrate an understanding of all elements of competency to be deemed competent.

A range of assessment methods are used to assess practical skills and knowledge, for example:
• Direct questioning combined with review of portfolios of evidence and third party workplace reports of on-the-job performance by the candidate
• Review of authenticated documents from the workplace or training environment
• Demonstration of techniques

Assessment Tasks

You are required to complete 3 tasks. You must successfully complete all 3 tasks to be deemed competent in this unit.

TASK 1 – Practical Tasks (Periodic Reviews as per Teaching Schedule)

For this assessment task you will be expected to complete wide range of practical tasks related to monitoring and administering a SQL Server database. These tasks will need to be performed in the classroom using the systems you installed in the initial weeks. These tasks will be assessed via regular practical demonstrations and a portfolio of documents to be presented as required.

Therefore in some cases you will need to demonstrate some aspect of the completed task to your teacher. In other cases you will need to capture screen shots and answer questions relating to your understanding of the related concepts. Your work must be saved in a folder and available for review as required. Files must of course be created individually and submitted to your teacher, as per the Teaching Schedule. In some cases printouts may be required.

To be deemed competent in this task you must demonstrate the following:
• Knowledge and understanding of important DBMS concepts
• The ability to start up SQL Server and create a simple database
• The ability to create a range of database objects such as tables & queries
• An understanding of database sizing issues and related practices
• The ability to manage SQL Server database security
• An understanding of database locks & indexes
• The ability to perform backups and restores
• The ability to work with backups and monitor database performance

TASK 2 - Theory Test (Conducted in Week 12)

This task will assess your knowledge and understanding of the key concepts associated with monitoring and administering SQL Server databases. Resources such as Websites, textbooks may be used.

You need to draw upon all the knowledge which you have what you have developed in this unit. To be deemed competent in this task you will need to demonstrate your knowledge in the following areas in particular:
• Basic Windows Server concepts
• DBMS architecture and products
• Database sizing issues in SQL Server
• Database files & backups in SQL Server
• Access security in SQL Server

TASK 3 - Practical Test (Conducted in Week 15)

This assessment task will consist of an in-class practical test in which you will be expected to complete series of practical tasks related to monitoring and administering a SQL Server database. Again these tasks will need to be performed in the classroom using the systems you installed in the initial weeks. You will also need to capture screen shots and answer questions relating to your understanding of these tasks.

You need to draw upon the knowledge and skills which you have developed in this unit. To be deemed competent in this task you will need to demonstrate knowledge and skills in the following areas in particular:
• Starting services & authentication
• Creating & understanding indexes
• Creating tables, views & queries
• Running stored procedures
• Database roles & permissions
• Creating backups & restores
• Performance monitoring & alerts

Assessment Matrix

Other Information

Submission Requirements:

Assessment tasks need to be submitted via Blackboard.

You should:
• Ensure that you submit assessments on or before the due date. If your performance in the assessment is affected by unexpected circumstances, you should consider applying for Special Consideration. Information on the process and application forms is available at;ID=ls0ydfokry9rz website.
• Always retain a copy of your assessment tasks. (hard copy and soft copy)
• When you submit work for assessment at RMIT University you need to use a cover sheet that includes a declaration and statement of authorship. You must complete, sign and submit a cover sheet with all work you submit for assessment, whether individual or group work. On the cover sheet you declare that the work you are presenting for assessment is your own work. An assignment cover sheet for submission of work for assessment is available on blackboard.
• Each page of your assessment should include footer with your name, student number, the title of the assessment, unit code and title and page numbers. For example, Julie Macpherson, 324567, Task 2, OHS2345C Ensure safe workplace, Page 1 of 10.

Marking Guide (Competency):

Vocational Education and Training (VET) is based on current industry needs and the focus on preparing you for the workplace. Because VET courses are informed by practical application of knowledge and skills, they are based on a system known as ‘competency based training’ (CBT).

So when you are assessed in VET it is about whether you are competent to do the job, as well as having a firm grasp on the knowledge and skills required to do that job, as opposed to traditional curriculum based education settings that are often based on knowledge retention.

You need to demonstrate you are competent in each element of the unit of competency you are studying.

You will receive feedback on each assessment task that will inform you whether you are competent or not and how well you are performing. Once competent in all elements of the unit you receive a competency grading.

Please refer to the Final Grades table below.

Marking Guide (Grading):

After achieving competency we then grade your performance in the unit; this gives you the opportunity to have the level of your performance formally recognized against industry standards and employability skills.

The grading is according to the following criteria:


We are looking for a high level of ability to complete all tasks independently as per the specifications given to in class. You should endeavour to demonstrate a high level of initiative in your approach to administering your databases and monitoring performance issues. Problem-solving in particular will require considerable amount of resourcefulness.


We are looking for depth of understanding of the key concepts and knowledge required in monitoring and administering a SQL Server database. You will also need a good understanding of client-server database management systems in general. You should be prepared to research topics to improve your understanding.


We are looking for proficiency in the various skills required of a SQL Server database administrator and ability to achieve organisational goals. You should aim to consistently demonstrate your skills in each assessment task.

You also need to be able to overcome the various problems which that can arise developing and administering a SQL Server database. You will need to demonstrate a degree of perseverance, and be able research possible solutions via the Web.


You should aim to attend all class sessions and save your solutions to all exercises and assessment tasks you have completed, for example on your home drive or USB device. An organised folder structure is highly recommended.

It will be very advantageous to prepare for and review class materials using suggested resources, such as the recommended textbook or Websites indicated by your teacher.


We expect to see contributions to relevant discussions in class and with your partner. For a many of the practical tasks, such as the networking exercise, you will need to work effectively in pairs, and manage your work to ensure timely completion of these tasks as per requirements. You will also need to liaise with your team leader (in this case your teacher) in order to ensure that all tasks have been completed satisfactorily.

Final Grades Table:

CHD   Competent with High Distinction
CDI     Competent with Distinction
CC      Competent with Credit
CAG    Competency Achieved – Graded
NYC    Not Yet Competent
DNS    Did Not Submit for assessment

Further information regarding the application of the grading criteria will be provided by your teacher.

Course Overview: Access Course Overview