Audience
This course is intended for Data Analysts and Database Designers who wish to use formal and practical methods and techniques in the analysis of business data and the design of databases.
Prerequisites
There are no formal prerequisites for this course.
A basic understanding of information systems development would be helpful.
Duration
3 days. Lecture presentations are supported by examples and a realistic case study to reinforce learning and enhance understanding.
Course Objectives
The course includes industry standard techniques for data analysis and database design and places them in a modern day development environment. After an introductory session covering database design, sound theoretical concepts are linked to practical considerations. The course sequence moves from logical data analysis and data model development through to physical database design and optimisation. Environments such as the Internet and data warehouses are included as well as standard relational databases.
At all times the emphasis is on a pragmatic approach to the solution of everyday problems encountered in data analysis and design.
At the end of the course attendees will be able to:
- Describe the reasons for producing data models.
- Develop a logical data model, using a top-down approach, based on the analysis of a business scenario.
- Validate a data model to ensure data integrity.
- Develop a normalised data model using relational data analysis.
- Normalise an existing data model.
- Transform a logical to a physical data model based on a target database.
- Create a database schema from a physical data model and optimise the schema.
- Understand how to use data modelling techniques in a variety of situations including reverse engineering and the use of software packages.
Course Content
Data Design Concepts
Types of Database.
Relational Database Origins.
Relational Model.
Design Steps.
Why Build Models.
Introduction to Data Modelling
Entities and Attributes.
Primary Key Types.
Relationships.
Cardinality.
Many-to-Many Relationships.
Development Steps.
Data Model Refinement
Null Value Attributes.
Mutually Exclusive and Reflexive Relationships.
Entity Sub-types.
Partial and Complete Coverage.
Multiple Sub-type Groups.
Data Integrity
Keys.
Foreign Keys.
Referential Integrity.
Attribute Data Types.
Attribute Constraints and Default Values.
Relational Data Analysis
Properties of a Relation.
Normalisation Steps.
TNF Tests.
Key-Only TNF Relations.
Implied Data.
Repeating Groups.
Transitive Dependency.
TNF Data Models
Optimisation.
Key-only Relations.
Potential Problems.
TNF Data Model from Optimised Relations.
Data Model Validation
Normalising a Data Model.
Primary Keys.
TNF Tests.
Foreign Keys.
Data and Processing Cross-Reference.
Volumetrics.
Validation by Navigation.
Physical Design
Transforming to Physical.
Entity Sub-types.
Exclusive Relationships.
Denormalisation.
Designing Tables.
Naming Conventions.
Structured Query Language (SQL).
Creating Tables.
The SELECT Statement.
Modifying Data.
Distributed Data
Centralised Model.
Distributed Model.
Client/Server Configurations.
Web Environments.
Stored Procedures.
Triggers.
Data Model Partitioning.
Variations in Design
Database Reversal.
Merging Models.
Package Implementation.
Data Warehouse.
Structure of a Data Warehouse.
Facts and Dimensions.
Star Schema.