Overview of Data Modeling and
Database Design
8
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder8Ć2
Overview of Data Modeling and Database Design 8Ć3
Objectives
Before you build your tables, you design your database. In this lesson, you
examine the data modeling process and relational database concepts, and define
normalization. You also translate an entity relationship model into a relational
database design.
At the end of this lesson, you should be able to
D
Describe the stages of system development.
D
List and define basic types of data relationships.
D
Define a relational database and its components.
D
Read an entity relationship model.
D
Translate an entity relationship model into a relational database design.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder8Ć4
Overview of Data Modeling and Database Design 8Ć5
Overview
When you create a database, you need to carefully consider its components. For
assistance with the design, you can follow the concepts outlined in this lesson.
System Development Cycle
In order to logically and successfully create the database objects in the Oracle7
Server, you complete the system development cycle. Each stage of the cycle contains
specific activities that you perform to achieve the best possible database design.
Database Design

D
Build the prototype system. Write and execute the commands to create the tables
and supporting objects for the database.
D
Develop user documentation, help-screen text, and operations manuals to support
the use and operation of the system.
Transition
D
Refine the prototype. Move an application into production with user acceptance
testing, conversion of existing data, and parallel operations. Make any
modifications required.
Production
D
Roll out the system to the users. Operate the production system. Monitor its
performance, and enhance and refine the system.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder8Ć8
Overview of Data Modeling and Database Design 8Ć9
Database Design
Designing a relational database system involves converting a model into a workable
software representation. The entities (or objects) perceived by the user are
transformed into tables in the database. All forms of design involve a mixture of
rules, judgements, and common sense, and relational design is no different.
During a design effort, your goal is to design reliable, high-performance systems
using the deliverables from the analysis effort. The following key factors describe in
detail why you should bother to design at all.
Performance
The initial design of a system has an enormous impact on its final performance.
Generally the impact is much greater than any remedial tuning.
Integrated Application
Application systems are typically developed by teams of developers. Without some

Purpose of Models
Models help communicate the concepts in people’s minds. They can be used for the
following purposes:
D
Communicate
D
Categorize
D
Describe
D
Specify
D
Investigate
D
Evolve
D
Analyze
D
Imitate
The objective is to produce a model that fits a multitude of these uses, can be
understood by an end user, but contains sufficient detail for a developer to build a
database system.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder8Ć12
Overview of Data Modeling and Database Design 8Ć13
Entity Relationship Modeling
Entity relationship models are derived from business specifications or narratives. This
model is a graphical representation of business information needs and rules.
Entity Relationship Models
Entity relationship models separate the information required by a business from the
activities performed within a business. Although businesses can change their

To represent an entity in a model, use the following conventions:
D
Soft box with any dimensions
D
Singular, unique entity name
D
Entity name in uppercase
D
Optional synonym names in uppercase within parentheses “()”
Attributes
An attribute describes entities and holds the specific information that should be
known about an entity. For example, for the customer entity, the attributes would be
customer number, name, phone number, and address.
If an entity does not have attributes that need to be known from the business
viewpoint, then it is not within the scope of the system requirements, and should not
appear in the model.
Each of the attributes is either required or optional. This state is called optionality.
To represent an entity in a model, use the following conventions:
D
Use singular names in lowercase.
D
Tag mandatory attributes, or values that must be known, with an asterisk “*”.
D
Tag optional attributes, or values that may be known, with an “o”.
Unique Identifiers
A unique identifier (UID) is any combination of attributes or relationships, or both,
that serves to distinguish occurrences of an entity. Each entity occurrence must be
uniquely identifiable.
D
Tag each attribute that is part of the UID with a number symbol (#).