Database Modeling and Design: Logical Design

4th Edition

Toby Teorey, Sam Lightstone, Tom Nadeau

Lecture Notes

Contents

I. Introduction ................................................................………...……2 Relational database life cycle 3 Characteristics of a good database design process 6

II. The Entity-Relationship (ER) Model …………...……………….7 Basic ER concepts 7 Ternary relationships 11

III. The Unified Modeling Language (UML)………...…………….13 Class diagrams 13 Activity diagrams 19 Rules of thumb for UML 21

IV. Requirements Analysis and Conceptual Data Modeling….…..22 Requirements analysis 22 Conceptual data modeling 24 View integration methods 25 Entity Clustering 30

V. Transforming the Conceptual Model to SQL…………...………32

VI. Normalization and normal forms ………………………………38 First normal form to third normal form (3NF) and BCNF 38 3NF synthesis algorithm (Bernstein) 43

VII. An Example of Logical Database Design………………………48

VIII. Business Intelligence………………………………..……….....52 Data warehousing 52 On-line analytical processing (OLAP) 58

IX. CASE Tools for Logical Database Design……………………….60
I. Introduction

Introductory Concepts

data—a fact, something upon which an inference is based (information or knowledge has value, data has cost)

data item—smallest named unit of data that has meaning in the real world (examples: last name, address, ssn, political party)

data aggregate (or group) -- a collection of related data items that form a whole concept; a simple group is a fixed collection, e.g. date (month, day, year); a repeating group is a variable length collection, e.g. a set of aliases.

record—group of related data items treated as a unit by an application program (examples: presidents, elections, congresses)

file—collection