At a glance
1 day
NZD $990 + GST
including in-house delivery.
Advanced SQL
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.
This course covers:
- 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.
Intended For
Anyone who needs to design, develop and maintain SQL databases.
Prerequisites
Participants are expected to have an understanding of the SQL SELECT, INSERT, UPDATE and DELETE statements.
A recommended lead-in course is Software Education's Introduction to SQL course.
Learning Outcomes
By the end of this course, participants will:
- 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
Database Design (give me what I need, not what I want)
Normalisation is a process that simplifies the content of databases. It results in a more natural group of the information into entity types, making the data more flexible and usable. A greater understanding of the data as a whole is also achieved as the information is grouped in a more objective fashion.
Views (give me what I want, not what I need)
The information in a database is not always organised in an easy-to-use structure, especially when the database is highly normalised. Views allow us to define a logical perspective on the data without changing the underlying structure and organisation of the database. These views can provide the information formatted according to our needs and so simplify any queries against the data.
Stored Procedures (who needs a programmer to mess up)
A stored procedure is a code function that lives and runs within the database. Some of the benefits are: better performance than writing code outside of the database; code re-use, centralising common processes in the database; ensuring data integrity when updating data; and protecting against SQL injection attacks.
Triggers (the ghosts in the machine)
Triggers are essentially stored procedures. However, rather than being invoked by an external application, they are invoked by the database itself in response to events such as data being inserted, updated or deleted.
Performance (making a silk purse out of a sow's ear)
The flexible nature of SQL and relational databases means some data access can be slow. Indices and partitioning help improve performance, although they do have drawbacks which need to be considered.
OLAP (aka "what the...")
Transactional systems (OLTP) tend to use a highly normalised database structure, allowing very efficient updates but complicating the reporting. Furthermore, there are typically many disparate systems, each with their own database. A data warehouse draws data from all the different sources into a single repository for reporting. Data marts within the warehouse provide the data formatted for efficient reporting.
Security (never smile at a crocodile)
Data entropy, where the integrity of the information degrades over time, and SQL injection attacks are very real possibilities. Controlling access to the tables and using stored procedures help manage these risks.
Method Used
This is a hands-on course, with participants applying what they learn to a SQL Server database. Every participant will have dedicated use of a workstation for the database exercises.
