Database Systems

Introduction to Database Design and SQL Querying (Course Code: 024)

Course Schedule:

Location	Days	Course dates	Duration	Cost (ex GST)	Availability	Enrol Here
City	Mon - Wed	28 - 30 Jul 2008	3 days	1550.00	Open	Corporate Group / Individual
City	Mon - Wed	01 - 03 Sep 2008	3 days	1550.00	Open	Corporate Group / Individual
City	Mon - Wed	01 - 03 Oct 2008	3 days	1550.00	Open	Corporate Group / Individual
City	Mon - Wed	03 - 05 Nov 2008	3 days	1550.00	Open	Corporate Group / Individual
City	Wed - Fri	03 - 05 Dec 2008	3 days	1550.00	Open	Corporate Group / Individual

Overview

Well designed database solutions are critical to every business. A core requirement in any IT professional’s kit bag is good database design techniques, an understanding of the Relational Data Model and of database querying language – Structured Query Language (SQL).

This course aims to provide an overview of database technology, based on relational database concepts, and of the database design process. Testing strategies will be outlined and the theory presented will be supported by a case study project.

Learning Method

Instructor Led
The course will consist of four days class based instruction, approximately 7 hours per day. You will have your own PC to complete the lab sessions.

Who will benefit from this course?

Beginners interested in learning the principles of electronic data storage and retrieval. Professionals interested in expanding their existing Access database skills to a Server environment.

Pre-requisites

You should be familiar with basic computing functions provided in Windows. No prior knowledge of databases is required.

What can you expect to gain from this course?

This course aims to provide an overview of database technology and terminology. Upon completion you will understand the Relational Data Model and be able to use the SQL commands for database definition and data retrieval. The use of SQL in a commercial Relational Database Management System (RDBMS) environment will also be covered.

Each of the major phases of database design will be introduced from conceptual design through to logical design, physical design and implementation.

Course content

Module 1:Introduction: Databases and the web

Overview
The file system
Databases and the ACID test

Module 2:Overview of relational databases and SQL

What is a relational database?
Tables, columns, rows
Talking to the database using SQL
SQL history and major DBMS vendors
Queries and Recordsets

Module 3:Anatomy of a simple query

The basic query
The WHERE clause
Additional rules for SQL statements
Selecting all columns
Select distinct
ORDER BY

Module 4:Enhancing queries

Extended WHERE conditions
Calculated columns
Aliases…or name that column

Module 5:Functions

Text functions
Case-insensitive searches
Text manipulation functions
Date functions
Date formats

Module 6:Numeric functions

TRUNCATE, ROUND (number, decimal)
Aggregate functions
Fine-tuning aggregate functions
GROUP BY, the HAVING clause
Querying data across multiple tables

Module 7:Query within a query - subqueries

Data Manipulation: INSERT, UPDATE, DELETE statements

Module 8:Database definition

SQL CREATE TABLE

Module 9:Data integrity

Controlling what goes into a database

Module 10:Establishing a primary key

Auto-numbered columns
Creating foreign keys
NOT NULL
DEFAULT values
UNIQUE
INDEX

Module 11:Renaming tables or columns

Adding or deleting columns
DROP TABLE

Module 12:Re-using queries as views

Database design

Module 13:Data modelling

Handling more than one phone number
Relationships between tables
Normalization
Many-to-many relationships

Module 14:Tying it all together

Creating the movies database
Case study project: Design a database
A possible solution to case study