Relational Database Design

Course Description

After an overview of relational concepts that are important for designing relational databases, learn about the different views of the data of the application domain and the steps included in this comprehensive relational database design methodology. These steps include:

• Analyzing a problem statement for an application domain
• Establishing an Entity Relationship (ER) model to visualize and structure the business object types of the application domain as entity types
• Illustrating the relationships between the business object types as relationship types
• Modeling the constraints for the entity types and relationship types imposed by the business rules
• Using the business information and the process inventories to produce a data inventory
• Establishing tuple types for entity types and relationship types of an ER model
• Transforming the tuple types for the application domain to the tables of the target database management system
• Defining implementation techniques for the various types of integrity
• Recommending the necessary indexes from a database design perspective
• Transitioning from the storage view to the logical view by creating logical data structures

IACET Continuing Education Units: 3.2

Case Study Labs ?

Six team-based lab exercises use a comprehensive case study to practice each major step and sub-step of the methodology.

Skills Gained

Relational concepts

• identify the components of tables
• explain the rules defined by the relational data model regarding: ?uniqueness of rows and columns, physical ordering of rows and columns, and linkage of tables

Views and results during database design

• explain the different views assumed for the data during database design: the conceptual view, the storage view, and the logical view
• summarize the steps performed during database design and their results
• relate the steps and results to the data views

Problem statement

• explain the purpose of the problem statement for database design
• discuss who has the responsibility for the creation of the problem statement
• describe the role of the database designer in the creation of the problem statement
• describe the contents of a problem statement

ER model

• define the entity types for an application domain based on a problem statement
• define the relationship types for an application domain based on a problem statement
• define the supertypes and subtypes for an application domain
• identify the constraints for the entity types and relationship types of an application domain
• establish an ER model for an application domain

Data and process inventories

• explain the purpose of data and process inventories
• explain the significance of data inventories for database design
• discuss who has the responsibility the creation of data and process inventories
• describe the content data and process inventories should have for database design
• summarize some methods for establishing data and process inventories

tuple types

• explain the purpose of tuple types and position them in the design process.
• identify the objects of an ER model for which tuple types are established
• establish the tuple types for the appropriate objects of an ER model
• explain the purpose and rules for the normalization of tuple types
• normalize the tuple types for an application domain

From tuple types to tables

• combine tuple types to reduce the number of tables required
• split tuple types to cope with database limitations or performance degradations
• denormalize tuple types as required for performance reasons
• establish the tables for the tuple types including the translation of abstract data types for attributes, the definition of data types and column attributes for the columns of the tables, and the documentation of the necessary database objects

Integrity rules

• describe the different types of integrity to be enforced for a database
• explain the integrity rules for referential integrity
• establish the referential constraints for the tables of an application domain
• draw the referential structure for the tables of an application domain
• relate how to ensure the integrity of redundant information
• implement business constraints
• describe the columns that should have indexes ?

Logical data structures

• explain the purpose of logical data structures
• discuss who has the responsibility for the establishment of logical data structures
• describe the components of logical data structures and their representation
• explain the relationship between business processes and logical data structures
• describe the interrelationship between logical data structures and views

Daily Agenda

Day 1

• relational concepts
• views and results during design
• problem statement
• exercises: problem statement
• review exercises: problem statement
• ER model (part 1)

Day 2

• ER model (part 2)
• exercises: ER model
• review exercises: ER model
• data and process inventories

Day 3

• exercises: data and process inventories
• review exercises: data and process inventories
• tuple types
• exercises: tuple types
• review exercises: tuple types
• from tuple types to tables (part 1)

Day 4

• from tuple types to tables (part 2)
• exercises: from tuple types to tables
• review exercises: from tuple types to tables
• integrity rules
• exercises: integrity rules
• review exercises: integrity rules
• logical data structures

Who Can Benefit

This is a intermediate course for programmer analysts, database administrators, and other individuals, who design relational databases and need an in-depth understanding of data modeling.

Prerequisites

None.

What's Next

Use the link to the Training Path above for easy access to prerequisite courses, and other courses that might be of interest to you.

Syllabus

Daily Agenda

Day 1

• relational concepts
• views and results during design
• problem statement
• exercises: problem statement
• review exercises: problem statement
• ER model (part 1)

Day 2

• ER model (part 2)
• exercises: ER model
• review exercises: ER model
• data and process inventories

Day 3

• exercises: data and process inventories
• review exercises: data and process inventories
• tuple types
• exercises: tuple types
• review exercises: tuple types
• from tuple types to tables (part 1)

Day 4

• from tuple types to tables (part 2)
• exercises: from tuple types to tables
• review exercises: from tuple types to tables
• integrity rules
• exercises: integrity rules
• review exercises: integrity rules
• logical data structures