COST: Contact us
DURATION: 1 day


Designing an SQL database is a specialised skill that needs a deep understanding of the use, application and storage of information in computer systems. 

Database designers must balance various conflicting requirements, such as providing the performance required for an on-line transaction processing (OLTP) system while simultaneously ensuring quick and easy reporting.  The need for on-line analytical processing (OLAP) drives the design and development of a data warehouse to centralise the information for reporting. Well-designed data marts can improve the reporting performance by a factor of up to 1,000.

Databases designed for OLTP systems are typically normalised for performance but then require various de-normalised views, as well as functionality incorporated into the database via stored procedures and triggers.  In particular, stored procedures help to maintain the integrity of the data and protect against security threats such as SQL injection attacks.

Intended for:

Anyone who needs to design, develop and maintain SQL databases and their structure. This course is designed to be delivered in-house for groups of individuals from a single organisation.

Prerequisites:

You are expected to have a good understanding of and experience with the SQL SELECT, INSERT, UPDATE and DELETE statements.

A recommended lead-in course is our Intermediate SQL course.
 

Learning outcomes:

By the end of this course you will be able to:

  • Understand the differences between designing transaction databases (OLTP) and reporting databases (OLAP)
  • Be able to create views, stored procedures, triggers and indices
  • Know how to use stored procedures to protect the data integrity and prevent SQL injection attacks

Content:

  • The design of databases, including normalisation
  • Improving performance with indices and partitions
  • Presenting different perspectives on the data with views
  • Programming databases with stored procedures and triggers
  • Data warehouses and data marts
  • Protecting against data degradation and SQL injection
     

Method used:

This is a hands-on course. You will have dedicated use of a workstation for the database exercises and apply what you learn to a SQL server database.  

Related courses:

Course outline:
Download PDF