Database Modeling And Design

Topics: Data modeling, Entity-relationship model, Database normalization Pages: 35 (5427 words) Published: January 7, 2015
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 of records of a single type (examples: president, election)

database—collection of interrelated stored data that serves the needs of multiple users within one or more organizations; a collection of tables in the relational model.

database management system (DBMS) -- a generalized software system for storing and manipulating databases. Includes logical view (schema, sub-schema), physical view (access methods, clustering), data manipulation language, data definition language, utilities - security, recovery, integrity, etc.

database administrator (DBA) -- person or group responsible for the effective use of database technology in an organization or enterprise.

Objectives of Database Management

1. Data availability—make an integrated collection of data available to a wide variety of users * at reasonable cost—performance in query update, eliminate or control data redundancy
* in meaningful format—data definition language, data dictionary
* easy access—query language (4GL, SQL, forms, windows, menus); embedded SQL, etc.; utilities for editing, report generation, sorting

2. Data integrity—insure correctness and validity
* checkpoint/restart/recovery
* concurrency control and multi-user updates
* accounting, audit trail (financial, legal)

3. Privacy (the goal) and security (the means)
* schema/sub-schema, passwords

4. Management control—DBA: lifecycle control, training, maintenance

5. Data independence (a relative term) -- avoids reprogramming of applications, allows easier conversion and reorganization

* physical data independence—program unaffected by changes in the storage structure or access methods

* logical data independence—program unaffected by changes in the schema

* Social Security Administration example
- changed benefit checks from $999.99 to $9999.99 format
- had to change 600 application programs
- 20,000 work hours needed to make the changes (10 work years)

*Y2K (year 2000) problem—many systems store 2-digit years (e.g. ‘02-OCT-98’) in their programs and databases, that give incorrect results...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Database Design Essay
  • Database Design Essay
  • Essay about Database Design
  • Learning Design Database Essay
  • Database Systems Design Implementati Essay
  • Database Design: Notes Essay
  • Database Essay
  • Database Design and Development Essay

Become a StudyMode Member

Sign Up - It's Free