Overview of Data Modeling and
Database Design
8
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder8Ć2
Schedule: Timing Topic
40 minutes Lecture
40 minutes Total
Class Management Note:
Files required for this lesson are:
Demonstration: None
Practice: None
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
Class Management Note:
This lesson is an overview to the DDL lessons. Please remember that as an

identify the information requirements. Incorporate the enterprise and application
mission statements as well as any future system specifications.
D
Build models of the system. Transfer the business narrative developed in the
strategy and analysis phase into a graphical representation of business information
needs and rules. Confirm and refine the model with the analysts and experts.
Design
D
Design the database. The entity relationship model maps entities to tables,
attributes to columns, relationships to foreign keys, and business rules to
constraints.
Build and Document
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

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder8Ć10
Server
ÉÉ
ÉÉ
ÉÉ
ÉÉ
Overview of Data Modeling and Database Design 8Ć11
Data Model
Models are a cornerstone of design. Engineers build a model of a car before putting it
into production to work out any details. In the same manner, system designers
develop models to explore ideas and improve the understanding of the database
design.
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

Entity A thing of significance about which information needs to
be known.
Attribute Something that describes or qualifies an entity.
Relationship A named association between entities showing optionality
or degree.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder8Ć14
Overview of Data Modeling and Database Design 8Ć15
Entity Relationship Model Concepts
An entity relationship model is composed of entities, attributes, and relationships.
Entities
An entity represents a thing of significance about the business system, or a discrete
category or collection of related data. Examples are customer, orders, and employees.
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: