Entegra Consultancy

Home / Training / Networking & Security / D102

D102

D102: Relational Database Concepts and Design – 3 Days

Who Should Attend:

Software engineers, systems analysts, applications programmers, computer science students, and those interested in acquiring technical skills required to administer a database environment.

Prerequisites:

SQL Programming (D101) module or familiarity with SQL syntax.

Objectives:

This module aims to introduce participants to the fundamental concepts involved in designing relational databases for DBMS such as Oracle, Sybase, and Informix. Database design is the process of developing database structures from user requirements. This process includes requirement analysis and translates these user requirements into first a conceptual, and then a physical design of the database. The resulting design must satisfy user needs in terms of completeness, integrity, performance, constraints and other factors. Topics covered include the entity-relationship model, relational database theory, file structure, indexing and hashing, query processing, crash recovery, concurrency control/transaction processing security and integrity, requirements formulation and analysis, conceptual design, implementation design, physical design, and stepwise refinement.

Contents:

Relational database structure and terminology: The relational database model. Relational database structure. Linking relational database tables. Structured Query Language (SQL). The relational database management system (RDBMS).

Design strategy, tools, and the database life cycle: Database design strategy. The subject approach to database design. The application approach to database design. Schema architecture. Design stages in the database life cycle. Post-design stages in the database life cycle. Database design tools.

Requirements Analysis: Business rules. Users of data. The data flow diagram. User views. Calculated fields. Requirements Analysis documentation.

Entities and attributes: Entity identifiers. Instances of entities. Attribute domains. Multi-valued attributes. Constraints.

Entity relationships: Types of relationships. Problems with many-to-many relationships. Resolving many-to-many relationships. Mandatory and optional participation.

Entity-relationship diagrams: Types of entity-relationship diagrams. Entities and attributes. Types of relationships. Types of participation. Verbalizing an ER diagram

Relational Constructs: Characteristics of tables. Rules for columns and rows. Calculations and domain types. Null values. Keys for tables. All-key relations. Foreign keys. Referential integrity.

Normalization: First, second, and third normal forms. Achieving 1NF and Limitations of 1NF. Achieving 2NF and Limitations of 2NF. Achieving 3NF. Normalization and Codd's 12 criteria. Denormalization.

Table joins and user views: Types of joins. User views. Permissions and requirements analysis.

Physical design: Indexing. Clustering. Partitioning. Using SQL to create a database and tables. Data dictionary tables.

Common design bugs: Columns. Constraints and keys. Primary and foreign keys. Relationships and referential integrity. Evaluating a relational database.

--------