Database Design

In the course Database Design participants learn the techniques and considerations for creating a well-structured database.

Intro Database Management Systems

The course Database Design starts with a discussion of the basic architecture of Database Management Systems. Attention is paid to the Database Schema and the differences between the conceptual, logical and physical model. The role of SQL, Data Definition Language (DDL) and Data Manipulation Language (DML) is also discussed.

Database Design

Subsequently the phases of Database Design and the components of a database are treated. The ERD Model and the UML Model are covered here. Possible design errors and the application of constraints are also reviewed.

ER Modeling

In the ER Modeling section participants learn how to discover the entities and their relationships and map them to tables. They learn the principles of Entity Relationship Modeling. Also treated is how to find and model attribute domains.

Table Mapping

Then attention is paid to how entities and their relations can be translated into tables in a relational database. The different mapping strategies for hierarchies of entities are also covered such as table per class, table per hierarchy and the use of discriminator columns.

UML Modeling

Next to the use of Unified Modeling Language UML for database design is treated. The UML syntax is discussed as well as UML elements such as interfaces, associations, composition, generalization and dependencies.

Normalization and Optimization

The process of normalization, the different normal forms and the removal of duplicate data are explained by means of practical examples. Finally, a number of optimization techniques, such as the use of indexes, that can improve the speed of databases are discussed.

Audience Course Database Design

The course Database Design in intended for Web developers, web application developers, database administrator, webmasters and web project managers.

Prerequisites Course Database Design

To join the course Database Design no specific skills or knowledge is required. General knowledge of system design is helpful to a proper understanding.

Realization Training Database Design

The theory is treated using presentation slides. Demos are used to clarify the theory. There is ample opportunity to practice. The course material is in English. The course times are from 9.30 up and to 16.30.

Certification Database Design

Participants receive an official certificate Database Design after successful completion of the course.

Modules

Module 1 : Intro DBMS

• What is a DBMS?
• DBMS Abstraction Levels
• Data Independence
• Database Model
• Types of Databases
• Database Schema
• Conceptual Model
• Logical Model
• Physical Model
• SQL Language
• DDL and DML Language
• Application Interfaces
• Transactions
• DBMS Architecture

Module 2 : Database Design

• What is Database Design?
• Database Design Phases
• Benefits of Phases
• Conceptual Data Model
• Entity Relationship Model
• UML Model
• Structuring the Model
• Design Errors
• Data Errors
• Constraints
• Database Constraints
• Naming Schema Elements
• Data Interpretation
• CASE Tools

Module 3 : Entity Relationship Modeling

• E-R Model Components
• Identification Guidelines
• Entities versus Entity Classes
• Attributes
• Entities versus Attributes
• Classification of Attributes
• Attribute Domains
• Relationships
• Degree of relationships
• Relationship Cardinalities
• Notation of Cardinalities
• Removing M:N relations
• Requirement Analysis
• Resulting ER Diagram

Module 4 : Advanced Er Modeling

• Weak Entity Set
• Generalization and Specialization
• Design Constraints
• Total and Partial Participation
• Disjoint Constraints
• Overlapping Constraints
• Aggregation
• ER Design Decisions
• Mapping ERD to Tables
• Composite Attributes
• Multivalued Attributes
• Redundancy As Tables

Module 5 : Mapping ERD to Tables

• Entity Set Table Translation
• Relationship Table Translation
• Mapping Key Constraints
• Map Relationship Set to Table
• Combine Relationship and Entity Set
• Weak Entity Sets
• Mapping Weak Entity Sets
• Mapping Subclasses
• Table per Subclass
• Table per Hierarchy
• Discriminator Columns
• Joining Tables

Module 6 : UML Modeling

• What is UML?
• Structural Modeling?
• Core Elements
• Core Relationships
• Structural Diagrams
• Classes and Objects
• Class Diagrams
• Interfaces
• Associations
• Composition
• Generalization
• Dependencies

Module 7: Normalization

• What is Normalization?
• Unnormalized form
• Moving towards 1NF
• First Normal Form
• Moving to 2NF
• Second Normal Form
• Third Normal Form
• Other Normal Forms
• Benefit of Normalization
• Relationship Cross Tables

Module 8: Database Optimization

• Optimization Process
• Use Ranges
• Denormalize
• Denormalization Issues
• Combine Tables
• Store Derived Data
• Add Indexes
• Index Operation
• Sorting
• Clustered Indexes