Data Modeling: Logical Database Design

Request more details:

Description:
This course provides a solid basis from which you can successfully implement relational database, multidimensional data warehouse and business intelligence (BI) technologies. The principal objective of this initial course is to convey a practical and common sense guide to the theory and concepts of data modeling. Using these sophisticated techniques you can create an elegant logical design of a database.
Prerequisites:
No mandatory prerequisites exist for this course. However a basic knowledge of computer systems, business systems requirements and database technologies is helpful.
The methodologies discussed are applicable to any relational database environment, including IBM DB2, the Oracle database, Microsoft SQL Server, the open-source MySQL and PostgreSQL databases as well as other RDBMS platforms. They are also applicable to other database technologies, such as object databases and legacy IMS and IDMS databases.
While we use the free Oracle SQL Developer Data Modeler product as a demonstration modeling tool, you can complete the exercises of this course and apply the techniques learned using any other popular data model diagramming tool, such as IBM InfoSphere Data Architect, CA ErWin Data Modeler, Embarcadero ER/Studio and others.
Target Audience:
The primary target audiences for this course are:
Business analysts
Data modelers, data analysts and data architects
Senior application designers and developers
Database administrators
Objectives:
In the workshop exercises you will build an increasingly complex series of data models, and will critique and correct other models. A summary of the detailed objectives of this textbook are:
A review of model-based design, including process modeling, physical data modeling and other modeling techniques which relate to logical data modeling.
A comparison of data modeling concepts and theories, including top-down data modeling, bottom-up data modeling, data normalization, object-oriented and semantic modeling.
Hints, tips and guidelines in identifying entities, attributes and relationships which should appear within a data model.
Review the popular commercial data modeling tools commonly in use today.
The benefits of building a conceptual data model in advance of the logical model.
Learn to find and fix well-known mistakes which can exist in relationship definitions, finding missing attributes and correcting erroneous attribute definitions.
Review a recommended strategy for unique identifiers.
Using semantic modeling constructs and techniques such as supertypes, subtypes, generalization, specialization, constraints, lattices and arcs.
Using object-oriented modeling techniques such as domains, attribute classes, extended types and abstraction of attributes.
Time-dependency and state-dependency within a data model.
Explore classic structures and modeling patterns, including many-to-many recursion.
Steps and available options for engineering a physical data model from a logical model.
Reverse engineering and forward engineering of a physical data model into an implementation relational database.
DATA MODELING THEORY & CONCEPTS:
ABOUT MODEL-BASED DESIGN
ABOUT DATA MODELING
ABOUT DATA MODEL DIAGRAMS
ADVANCED MODELING METHODOLOGIES
BUILDING AN INITIAL DATA MODEL:
PRINCIPLES OF DATA MODELING
BUILDING THE MODEL
IDENTIFYING ENTITIES
IDENTIFYING ATTRIBUTES
IDENTIFYING RELATIONSHIPS
A SIMPLE MODELING SCENARIO
DRAWING A MODEL USING SOFTWARE ENGINEERING TOOLS:
ABOUT DATA MODELING TOOLS
DRAWING A DATA MODEL DIAGRAM
INCREASING THE ACCURACY OF THE MODEL:
STARTING WITH A CONCEPTUAL MODEL
SUPPLEMENTING THE REQUIREMENTS
REFINING THE RELATIONSHIP DEFINITIONS
FINDING & FIXING ATTRIBUTE MISTAKES:
CAPTURING MISSING ATTRIBUTE DETAILS
CHARACTER
NUMERIC
DATE
CORRECTING ATTRIBUTE DEFINITIONS
UNIQUE IDENTIFIERS
UNIT OF MEASURE ATTRIBUTES
SEMANTIC & OBJECT ORIENTED MODELING OF ENTITIES & RELATIONSHIPS:
DEFINING SUPERTYPES & SUBTYPES
ENTITY NAME PROBLEMS
NAMING STANDARDS
SPECIALIZATION & GENERALIZATION
SUBTYPE CONSTRAINTS
DEFINING RELATIONSHIP ARCS
SEMANTIC & OBJECT ORIENTED MODELING OF DOMAINS & TYPES:
DEFINING DOMAINS
DEFINING TYPES
COLLECTION TYPES
TIME-DEPENDENCY & STATE-DEPENDENCY:
ABOUT TIME & STATE
TIME-DEPENDENT SUB-MODEL
PERSON / INDIVIDUAL ROLES SUB-MODEL
CLASSIC STRUCTURES & PATTERNS:
MASTER-DETAIL-DETAIL
M:N RECURSION (BILL-OF-MATERIALS)
ORGANIZATION UNIT HIERARCHY
ENTITY LOCATIONS
ENTITY CONTACTS
LOGICAL / PHYSICAL MODEL TRANSFORMATION:
ABOUT PHYSICAL DATA MODELS
PHYSICAL RELATIONAL TRANSFORMATION
MODEL TRANSFORMATION EXAMPLE
AUTOMATIC TRANSFORMATION
SUPERTYPE TRANSFORMATION
RDBMS IMPLEMENTATION OF THE PHYSICAL MODEL:
REVERSE ENGINEER A PHYSICAL MODEL
ABOUT THE RELATIONAL DATABASE
RELATIONAL DATABASE OBJECTS
FORWARD ENGINEER A PHYSICAL MODEL