Databases - Decision support systems

Objective
To provide the knowledge and skills required to identify the types of models, to understand the principal approaches, to construct schemas, and to evaluate the strengths, weaknesses and possible improvements of a data model.

Target audience
Analysts, designers and database administrators.

Prerequisites
Experience developing systems.

Topics covered

• Introduction to databases and the architecture
• Data models: conceptual, logical and physical
• Techniques, objectives, benefits and limitations of modeling
• The conceptual model: entities, properties, identifiers, relationships, cardinalities
• Normalization (1FN, 2FN, 3FN)
• Rules for constructing schemas
• Design procedures and strategies
• Tips and tricks: generalization, customization, grouping
• The logical model: tables, columns, join tables, primary keys, foreign keys, integrity constraints
• Rules for constructing a relational schema
• Transforming entities
• The physical model: internal organization of data and access methods
• The cardinality of relationships (1:1, 1:n, m:n), and attributes on associations
• Best practices

Objective
To provide the knowledge and skills required to formulate queries and to update a database using the SQL language.

Target audience
Analysts, designers, programmers and relational database administrators.

Prerequisites
Knowledge of conceptual data modeling (Course BD101).

Topics covered

• Introduction to SQL
• Data definition: CREATE TABLE, CREATE INDEX, CREATE VIEW, etc.
• Modifying data definitions: ALTER TABLE
• Querying data using the SELECT command
• Aggregates: COUNT, MIN, MAX and AVG
• Inner and outer joins
• Self-joins
• Nested queries
• Sorting results with ORDER BY
• Grouping with GROUP BY and HAVING
• Set-theoretic operators: union, intersection and difference
• Accessing data dictionaries: list of tables, table descriptions, index information, constraints and users
• Manipulation of data
• Updating data: INSERT, UPDATE and DELETE
• Transactions: COMMIT and ROLLBACK
• GRANT and REVOKE access privileges

Objective
To provide the knowledge and skills required to optimize SQL requests in order to significantly improve application performance.

Target audience
Analysts, designers, programmers and relational database administrators.

Prerequisites
Knowledge of conceptual data modeling (Course BD101) and SQL syntax (Course BD102).

Topics covered

• Review of relational database design and normalization
• Problems related to collation
• The importance of table and column sizing
• Operations on tables: DELETE, INSERT, UPDATE
• The SELECT instruction and joins
• Optimizing predicates: LIKE, BETWEEN, IN, NOT IN, EXISTS and INTERSECT
• Operations on groups (GROUP BY and correlated subqueries)
• Aggregation functions
• Auxiliary tables
• Handling views and temporary tables
• Optimizing subqueries and group operations
• The judicious use of indexes (for sorting and querying)
• Code optimization
• Cursors: when to use them and how to avoid them
• Triggers
• Presentation of some advanced techniques

Objective
To provide the knowledge and skills required to develop databases with Access.

Target audience
Programmers, analysts, designers and database administrators.

Prerequisites
Knowledge of conceptual data modeling (Course BD101) and of Windows.

Topics covered

• Strengths and weaknesses: comparison with SQL Server
• Tables: modeling, structure, creating tables and fields, primary keys, relations and data validation
• Working with data sheets: usage, formatting, sorting and filters
• Importing and linking to external data
• Designing and using queries: simple queries, multiple table queries, action queries, parameter queries, crosstab queries and calculated fields
• Forms: types of forms, using the assistants, modifying forms and adding controls
• Designing and printing reports
• Managing the database: analysis and documentation, compacting and multi-user environment
• A look at the other capabilities of Access: macros, security and replication

Note: Although the course is given using Access 2003, the concepts covered can be applied to Access 95, 97, 2000 and 2002. Access 2007 is covered separately in Course BD210.

Objective
To provide the knowledge and skills required to create databases using Access.

Target audience
Database programmers, analysts, designers and administrators.

Prerequisites
Familiarity with the Windows environment.

Topics covered

• Features and limitations: comparison with SQL Server
• Tables: creating tables and columns, primary keys, relationships, data validation
• Working with data pages: creating, formatting, sorting, filtering
• Importing and exporting, connecting to external data
• Design and use of queries: simple, multitable, action queries with parameters, calculation and pivot analysis
• Forms: types, use of wizards, modifying and adding controls
• Designing and printing reports
• Managing the database: analysis, documentation and compaction
• Overview of additional features of Access

Note: Versions of Access prior to Access 2007 are covered in the Course BD103.

Objective
To provide the knowledge and skills required to master the new features and functionality provided by SQL Server 2008.

Target audience
System administrators and IT professionals.

Prerequisites
None.

Topics covered

• The SQL date and time types (date, time, datetime2, datetimeoffset), support for the ISO week, and time zone management
• Data compression for the Data Warehouse command SQL MERGE (INSERT and UPDATE combined)
• Implementing security rules
• The spatial data module (GIS)
• Creating new server roles
• The new "hierarchy" type (trees)
• The new filestream type (similar to the SQL standard datalink), which permits a file reference to appear as a field in a table
• The table type
• Parameterizing the table name in requests (e.g. SELECT FROM @TableName)
• Compressed back-ups
• Support for LINQ (Language Integrated Query), which provides sophisticated queries
• GROUPING SETS for OLAP functions on OLTP databases
• Data traceability
• Improved integration with Office

Accredited course.
6 PDU

Objective
To provide the knowledge and skills required to install, configure and manage SQL Server 2008

Target audience
System administrators and technical support personnel.

Prerequisites
Experience with SQL Server 2005 administration (Course MS509).

Topics covered

• Description of new functionality in SQL Server 2008
• New features in Management Studio: IntelliSense
• Beyond relational: presenting new data types
• Migrating from SQL Server 2005 to SQL Server 2008
• Managing dates and times with the new data types
• Optimizing storage with compressed backups
• Detecting bottlenecks with extended event management
• Setting up CDC
• Restoring an encrypted database on another instance of SQL Server
• Optimizing SQL Server 2008
• Securing an SQL Server 2008 database
• Monitoring and fixing performance problems with Management Studio
• Using SQL Server 2008 Integration Services (SSIS)
• Using SQL Server 2008 Reporting Services (SSRS)
• Using SQL Server 2008 Analysis Services (SSAS)

Objective
To provide the knowledge and skills required to develop databases with SQL Server 2008

Target audience
Database programmers, analysts, designers and administrators

Prerequisites
Experience with SQL Server 2005 database programming (Course MS510)

Topics covered

• New functionality and new development tools in SQL Server 2008
• Introduction to dynamic development
• Using ADO.NET's Entity Framework
• Using LINQ (Language Integrated Query)
• New data types: hierarchy (trees), filestream, table
• SQL date and time types (date, time, datetime2, datetimeoffset)
• Using Integrated Full-Text Search (IFTS)
• Improvements in Transact-SQL
• Using the MERGE instruction (INSERT and UPDATE combined)
• Parameterizing the table name in requests (e.g. SELECT FROM @MyTableVariable)

Objective
To provide the knowledge and skills required to develop SQL Server 2005 databases.

Target audience
System administrators and developers.

Prerequisites
Knowledge of Windows 2000/2003 interface, conceptual data modeling (Course BD101) and SQL (Course BD102).

Topics covered

• Overview of SQL Server 2005 and Transact-SQL
• Creating data types and tables
• Data integrity: constraints, default values, etc.
• Managing and manipulating data
• Implementing views and stored procedures
• Multiserver programming: partitioned views, linked server requests, etc.
• Using XML with SQL Server 2005
• The architecture and use of Service Broker
• The use of HTTP native support
• Architecture and implementation of notification services
• Implementing the Microsoft .NET CLR
• Developing client applications
• Planning, creating and maintaining indexes
• Managing transactions and locks
• Planning and managing security
• Performance analysis and optimization

Objective
To provide the knowledge and skills required to install, configure, administer, and troubleshoot the client-server database management system of SQL Server 2005.

Target audience
System administrators and developers.

Prerequisites
Practical experience with Windows 2003 administration (Course MS304 or Course MS307) and basic knowledge of SQL (Course BD102).

Topics covered

• Overview of the architecture of Microsoft SQL Server 2005
• Planning an installation of SQL Server 2005
• SQL Server 2005 administration tools
• Creation and configuration of user and system databases
• Data transfer within the database
• Backing up and restoring databases
• Managing access, permissions and certificates
• Running and automating administrative procedures
• Managing and optimizing indexes
• Tracking and optimizing performance and load levels
• Implementing replication
• Maintaining high availability

Objective
To provide the knowledge and skills required to optimize SQL Server 2000/2005 to get the highest possible performance for complex and/or high-volume applications.

Target audience
Administrators and developers faced with challenging performance problems.

Prerequisites
Experience in SQL Server 2000 administration (Course MS502) or SQL Server 2005 administration (Course MS509).

Topics covered

• Introduction to performance optimization: physical design, load analysis
• SQL Server storage mechanisms: files, FILEGROUPS, extensions and pages
• Indexes: creation, options, structure, parameterization
• Indexed views, partitioned views and partitioned tables
• The role of the optimizer: how it works, query optimization, hints
• Refining queries: the query execution plan and execution statistics
• Constraining an execution plan with tags (locks, WITH and OPTION clauses)
• SQL Server with Windows: CPU utilization, virtual memory management, RAID disks
• Transactions and locks: exclusive and shared, range, page and table locks, isolation levels, hints
• Performance monitoring, stored procedures, perfmon, alerts, SQL Profiler
• Using the Index Tuning Advisor and the Tuning Wizard
• Maintaining indexes (defragmentation) and statistics

Objective
To provide the knowledge and skills required to make effective use of SQL Server Integration Services (SSIS).

Target audience
System administrators, analysts, developers and relational database programmers.

Prerequisites
Experience with SQL language (Course BD102).

Topics covered

• SSIS Architecture: the evolution from DTS to SSIS, the SSIS development environment
• Fundamental concepts of SSIS: containers, tasks, precedence constraints, data streams, connectivity, variables, and package configurations
• Creation of a control stream and package data
• Component connection and data streams
• Designing high-performance, extensible packages
• Debugging SSIS packages: executing a package, defining breakpoints, adding a data inspector
• Controlling the progress of data streams
• Configuring and deploying SSIS packages
• Using transformations: derived columns, slowing changing dimensions, fuzzy lookups and fuzzy grouping

Objective
To provide the knowledge and skills required to make effective use of SQL Server Reporting Services (SSRS).

Target audience
System administrators, business analysts, developers and relational database programmers.

Prerequisites
Experience with SQL (Course BD101 or course BD102).

Topics covered

• SSRS features: creating and publishing reports
• Designing ad hoc report models
• Fundamental concepts of SSRS: data sources, datasets, fields and filters
• Designing reports with Report Designer and Report Wizard
• Deploying and managing reports
• Deploying a report from Visual Studio, scripts and Report Manager
• Managing reports: configuring shared data sources and report properties, configuring subscriptions
• Planning subscriptions and report processing
• Using Report Model Designer
• Examples of report models
• Creating an ad hoc report using Report Builder

Objective
To provide the knowledge and skills required to make effective use of SQL Server Analysis Services (SSAS).

Target audience
System administrators, business analysts, developers and relational database programmers.

Prerequisites
Experience with SQL (Course BD101 or course BD102)

Topics covered

• Fundamental concepts of SSAS: data sources, dimensions, cubes, the UDM model
• New features of the SSAS architecture
• Designing and deploying a UDM model
• Generating a cube
• Implementing advanced cube functionality
• SSAS administration
• Upgrading and migrating to SSAS 2005
• Aspects of the upgrade to SSAS 2005
• Configuring roles, groups and permissions
• SSAS management: performance optimization
• Managing partitions and Business Intelligence in real time
• Generating scripts with MDX
• Using data exploration solutions in SSAS
• Using data exploration tools

Objective
To provide the knowledge and skills required to effectively use XML in database development with SQL Server.

Target audience
Architects, application designers, developers, project managers and IT planners.

Prerequisites
Knowledge of XML technologies.

Topics covered

• Introduction to XML in SQL Server 2005
• Server-side processing of XML in SQL Server 2005
• Using the XML data type
• Querying and updating XML data in SQL Server 2005
• Indexing XML data in SQL Server 2005
• XML schemas in SQL Server 2005
• Improvements in Transact-SQL to support FOR XML and OPENXML
• Client-side processing of XML in SQL Server 2005
• Creating and querying XML views
• Bulk load of XML data via an XML view
• Using XSLT in SQL Server 2005
• Support for Web Services (SOAP) in SQL Server 2005
• Client-side SOAP
• Web Services Description Language (WSDL)

Objective
To provide the knowledge and skills required to identify the main concepts of data warehousing and to make use of effective approaches in designing a data warehouse. This course introduces operating techniques and covers the main challenges encountered when implementing a data warehouse.

Target audience
Analysts, designers and managers.

Prerequisites
General knowledge of computer science.

Topics covered

• What are data warehouses and why do they exist?
• Architecture of data warehouses and data stores
• Business Intelligence: data mining, data warehousing and data visualization
• Referential/Universal set (metadata)
• Building a data warehouse: extracting, cleansing, transforming, consolidating, loading, implementing, operating and refreshing
• Multidimensional modeling, and the star, snowflake and galaxy models
• Normalization rules for multidimensional schemas
• Modeling approaches: ascending, descending, mixed
• OLAP techniques (OnLine Analytical Processing): roll-up, drill-down, slice, dice, pivot, drill-through and drill-cross
• ROLAP, MOLAP and HOLAP servers
• Data warehousing applications (ETL, OLAP, etc.)
• Case studies