Overview of Database Development
Data Modeling and Relational
Database Design
Objectives
• Describe the phases of the database
development process
• Describe the phases of the application
development process
• Explain conceptual data modeling and
database design
2-2 Overview of Database Development
Database Development Process
Business Requirements
Information Process
Cross-checking
ER model, Conceptual Function Function hierarchy,
entity definition Data Modeling Modeling function definition
Database Application Module (screen,
Table, index, Cross-checking report, menu,
view Design Design PL/SQL utility)
Database Application
Build Build
Database Application
Operational System
2-3 Overview of Database Development
Database Development Process
Business Information Requirements
Conceptual Data Business view
Modeling
Logical Systems view
Database Design
Physical
Database Build
Operational Database
2-4 Overview of Database Development
Terminology
CONCEPTUAL LOGICAL
(Business view) (Systems view)
ANALYSIS DESIGN
2-5 Overview of Database Development
Terminology
CONCEPTUAL LOGICAL
(Business view) (Systems view)
ANALYSIS DESIGN
ENTITY TABLE
2-6 Overview of Database Development
Terminology
CONCEPTUAL LOGICAL
(Business view) (Systems view)
ANALYSIS DESIGN
ENTITY TABLE
RELATIONSHIP FOREIGN KEY
2-7 Overview of Database Development
Terminology
CONCEPTUAL LOGICAL
(Business view) (Systems view)
ANALYSIS DESIGN
ENTITY TABLE
RELATIONSHIP FOREIGN KEY
ATTRIBUTE COLUMN
2-8 Overview of Database Development
Terminology
CONCEPTUAL LOGICAL
(Business view) (Systems view)
ANALYSIS DESIGN
ENTITY TABLE
RELATIONSHIP FOREIGN KEY
ATTRIBUTE COLUMN
UNIQUE PRIMARY KEY
IDENTIFIER UNIQUE KEY
2-9 Overview of Database Development
Information Models
• Organize thought processes
• Accurately model business data
• Communicate with stakeholders
• Analyze the scope
• Provide sound basis for system design
2-10 Overview of Database Development
Goals of Conceptual Data Modeling
• Robust syntax
• User communication
• Ease of development
• Definition of scope
• Integration of multiple applications
2-11 Overview of Database Development
Business Information Requirements
“I manage the Human Resources Department for a large
company. We need to keep information about each of our company’s
employees. We need to track each employee’s first name, last name,
job or position, hire date and salary. For any employees on commission,
we also need to track their potential commission. Each employee is
assigned a unique employee number.
Our company is divided into departments. Each employee is assigned
to a department, for example, accounting, sales or development. We
need to know the department responsible for each employee and the
department’s location. Each department has a unique number, for
example, accounting is 10 and sales is 30.
Some of the employees are managers. We need to know each
employee’s manager and the employees that each manager manages”.
2-12 Overview of Database Development
Business Information Requirements
“I manage the Human Resources Department for a large
company. We need to keep information about each of our company’s
employees. We need to track each employee’s first name, last name, job
or position, hire date and salary. For any employees on commission,
we also need to track their potential commission. Each employee is
assigned a unique employee number.
Our company is divided into departments. Each employee is assigned
to a department, for example, accounting, sales or development. We
need to know the department responsible for each employee and the
department’s location. Each department has a unique number, for
example, accounting is 10 and sales is 30.
Some of the employees are managers. We need to know each
employee’s manager and the employees that each manager manages”.
2-13 Overview of Database Development
Main Elements of an ERD
Relationship
CUSTOMER Unique
held # * customer number Identifier
Entity MEMBERSHIP by * first name
* last name Attributes
the holder
o other initials
of
COMPANY
held Super-type
by SUPPLIER
Sub-type
the holder
of
OTHER
composed COMPANY
of
part of Recursive
Relationship
Exclusive Arc
2-14 Overview of Database Development
Hardware and Software Independence
ENTITY RELATIONSHIP MODEL
Agreement
Term Product
Agreement
2-15 Overview of Database Development
Hardware and Software Independence
ENTITY RELATIONSHIP MODEL
Agreement
Term Product
HIERARCHICAL Agreement
DATABASE
Agreement
Item Item 2
1
Product X Product X
2-16 Overview of Database Development
Hardware and Software Independence
ENTITY RELATIONSHIP MODEL
Agreement
Term Product
NETWORK
HIERARCHICAL Agreement
DATABASE
DATABASE
Agreement Agreement Product
x
Item Item 2 Product
1 Item 1 y
Item 2
Product X Product X
2-17 Overview of Database Development
Hardware and Software Independence
ENTITY RELATIONSHIP MODEL
Agreement Product
Term
NETWORK
HIERARCHICAL Agreement
DATABASE
DATABASE
Agreement Agreement Product
x
Item Item 2 Product
1 Item 1 y
Item 2
Product X Product X RELATIONAL
DATABASE
Code Date Customer Code Description
Agreement Number Quantity Product
2-18 Overview of Database Development
Summary
• Database development process
• Terminology
• Entity relationship models
• Hardware and software independence
2-19 Overview of Database Development
