Chapter 3
Database Design:
Conceptual Design
Introduction
SYS. ANALYSIS
Information
Gathering
Analyze
WFD, AD, Project Mgt
Proces Information etc
s DFD
Model
Logical DFD
current s& new
system
Data Model
SYS. PLANNING
Decision table, E-R
decision trees, Diagram
etc Feasibilit
Process Design PDReport y
DFD
Output Design
SYS. IMPLEMENTATION
Input Design Program Design
E-R Database Design
SYS. DESIGN Diagram
Conceptual Design >
Logical Design > Physical DSpecRepor
Design
t
Database Design
Real World Conceptual
Conceptual
Data Design
Design
abstract
Mapping
Requirement Analysis Logical
Logical Make use of:
Design
Design abstraction
mechanism
design
methodologies
Design
Design Mapping
Physical
Physical
Implementation Design
Design
concreate
Phases of Database Design
Application Domain
Requirements Analysis
DBMS Database Requirements
Independent
Conceptual Design
Conceptual Schema
Logical Design
Implementation Schema
DBMS Physical Design
Dependent
Physical Schema
ANSI-SPARC Three-Level
Architecture
z DBA should be able to change
database storage structures
without affecting the users’
views.
z Internal structure of database
should be unaffected by
changes to physical aspects of
storage.
z DBA should be able to change
conceptual structure of
database without affecting all
users.
Differences between Three Levels
of ANSI-SPARC Architecture
Database Design & ANSI-
SPARC Architecture
View View View
User 1 User 2 ….. User n
Conceptual
Design
External Conceptual
Subschema 1 Subschema 2 Subschema n
Data
level Model
…..
Logical
Conceptual Design Logical Data
Conceptual
Model
level Schema
Physical
Design
Internal Internal Physical Data
level Schema Model
Steps For Designing
Database
Conceptual Design STEP 1
STEP 2
Logical Design
STEP 3
STEP 4 STEP 6
STEP 5 STEP 7
Physical Design
STEP 8
STEP 9
Methodology Overview -
Conceptual Database Design
z Step 1 Build local conceptual data
model for each user view
z Step 1.1 Identify entity types
z Step 1.2 Identify relationship types
z Step 1.3 Identify and associate attributes with entity or
relationship types
z Step 1.4 Determine attribute domains
z Step 1.5 Determine candidate and primary key
attributes
z Step 1.6 Consider use of enhanced modeling concepts
(optional step)
z Step 1.7 Check model for redundancy
z Step 1.8 Validate local conceptual model against user
transactions
z Step 1.9 Review local conceptual data model with user
Methodology Overview - Logical
Database Design for Relational
Model (cont)
z Step 2 Build and validate local logical
data model for each view
z Step 2.1 Remove features not compatible with the relational
model (optional step)
z Step 2.2 Derive relations for local logical data model
z Step 2.3 Validate relations using normalization
z Step 2.4 Validate relations against user transactions
z Step 2.5 Define integrity constraints
z Step 2.6 Review local logical data model with user
Methodology Overview - Logical
Database Design for Relational
Model (cont)
z Step 3 Build and validate global logical
data model
z Step 3.1 Merge local logical data models into global model
z Step 3.2 Validate global logical data model
z Step 3.3 Check for future growth
z Step 3.4 Review global logical data model with users
Methodology Overview - Physical
Database Design for Relational
Databases
z Step 4 Translate global logical data model for
target DBMS
z Step 4.1 Design base relations
z Step 4.2 Design representation of derived data
z Step 4.3 Design enterprise constraints
z Step 5 Design physical representation
z Step 5.1 Analyze transactions
z Step 5.2 Choose file organization
z Step 5.3 Choose indexes
z Step 5.4 Estimate disk space requirements
Methodology Overview - Physical
Database Design for Relational
Databases (cont)
z Step 6 Design user views
z Step 7 Design security mechanisms
z Step 8 Consider the introduction of
controlled redundancy
z Step 9 Monitor and tune the
operational system
The process of creating a design for a database that
Database will support the enterprise’s operations and
Design objectives.
Conceptual The process of constructing a model of the
Database information used in an enterprise, independent of
Design all physical considerations. It includes
identification of the important entities,
relationships, and attributes.
Logical The process of constructing a model of the
Database information used in an enterprise based on a
Design specific data model, but independent of a particular
DBMS and other physical considerations. It
translate the conceptual model to the logical
structure of the database, which includes designing
the relations.(e.g relational model)
Physical The process of constructing a model of the
Database information used in an enterprise based on a
Design specific data model, but independent of a particular
DBMS and other physical considerations. It decide
how the logical structure is to be physically
implemented (as relations) in the target DBMS.
Conceptual Design
z Similar to the analysis phase in software
development
z produce a description of the data
z capture the semantics of the data
z Description in a high-level model
z close to the user’s view of the world
z abstract concepts
z means of communication between the user and the
developer
Reasons for Conceptual
Design
z Independent of DBMS.
z Allows for easy communication between
end-users and developers.
z Has a clear method to convert from high-
level model to relational model.
z Conceptual schema is a permanent
description of the database
requirements.
Entity-Relationship
Diagram (E-R Diagram)
Entity-Relationship Model
z Most popular conceptual model for
database design.
z Basis for many other models.
z Describes the data in a system and how
that data is related.
z Describes data as entities, attributes
and relationships
Database Requirements
z We must convert the written database
requirements into an E-R diagram
z Need to determine the entities, attributes
and relationships.
z nouns = entities
z adjectives = attributes
z verbs = relationships
Entities
z Entity – basic object of the E-R model
z Represents a “thing” with an
independent existence
z Can exist physically or conceptually
z a professor, a student, a course
z Entity type – used to define a set of
entities with the same properties.
Entity Type
z Strong Entity Type
z Entity type that is not existence-dependent on
some other entity type.
z Weak Entity Type
z Entity type that is existence-dependent on
some other entity type.
Part of
Section Course
1..1 States 1..1
Client Preference
ER Diagram of Staff and
Branch Entity Types
Entity and Entity Types
Course
Entity Type Number
Name
Topic
Number: 1123
Entity Name: Computer Programming 2
Topic: Computer Programming
Cont…
Attributes
z Each entity has a set of associated properties
that describes the entity. These properties are
known as attributes.
z Attributes can be:
Course
z Simple or Composite
Number
z Single or Multi-valued Name
Topic
z Stored or Derived
z NULL
Cont…
Attributes
z Simple Attribute
z Attribute
z Attribute composed of a single component with an independent
z Property of an existence.
entity or a z Composite Attribute
relationship z Attribute composed of multiple components, each with an
type. independent existence.
z Single-valued Attribute
z Attribute z Attribute that holds a single value for each occurrence of an entity
type.
Domain
z Set of z Multi-valued Attribute
z Attribute that holds multiple values for each occurrence of an
allowable
entity type.
values for one z Derived Attribute
or more z Attribute that represents a value that is derivable from value of a
attributes. related attribute, or set of attributes, not necessarily in the same
entity type.
z NULL attributes have no value
z not 0 (zero)
z not a blank string
z Attributes can be “nullable” where a null value is allowed, or “not
nullable” where they must have a value.
Attributes
Professor Professor
Simple Composite
Start_Date Name
First
Last
Single Professor
EmployeeID#{PK}
Multi-Valued Professor
Tel_No [1…3]
Derived Professor
/Years_Teaching
Keys
z Candidate Key
z Minimal set of attributes that uniquely
identifies each occurrence of an entity type.
z Primary Key
z Candidate key selected to uniquely identify
each occurrence of an entity type.
z Composite Key
z A candidate key that consists of two or
more attributes.
ER Diagram of Staff and Branch
Entities and their Attributes
Relationships
z Defines a set of associations between various
entities
z Can have attributes to define them
z Are limited by (constraint in relationship):
z Participation
z Determines whether all or only some entity
occurrences participate in a relationship.
z Cardinality Ratio
z Describes maximum number of possible
relationship occurrences for an entity
participating in a given relationship type.
Cardinality
z The number of relationships that an entity may
participate in.
Multiplicity as Cardinality
and Participation Constraints
Multiplicity of Staff Manages Branch
(1:1) Relationship Type
Multiplicity of Staff Oversees
PropertyForRent (1:*) Relationship Type
Multiplicity of Newspaper Advertises
PropertyForRent (*:*) Relationship
Extended
Entity-Relationship
Diagram (EER Diagram)
Extended E-R Model
z Limitations of basic concepts of the ER model
and requirements to represent more complex
applications using additional data modeling
concepts.
z Most useful additional data modeling
concepts of Enhanced ER (EER) model called:
z specialization/generalization;
z aggregation;
z composition.
Extended E-R Model(cont)
z E-R model is sufficient for traditional
database applications
z Nontraditional applications (CAD,
multimedia) have more complex
requirements
z Can extend traditional E-R diagrams
with semantic data modeling
concepts
Specialization /
Generalization
z Specialization
z Process of maximizing differences
between members of an entity by
identifying their distinguishing
characteristics.
z Generalization
z Process of minimizing differences
between entities by identifying their
common characteristics.
Specialization Constraints
z Disjointness constraint –
z Describes relationship between members of the
subclasses and indicates whether member of a
superclass can be a member of one, or more than one,
subclass.
z May be disjoint or nondisjoint.
z Participation constraint –
z Determines whether every member in superclass must
participate as a member of a subclass.
z May be mandatory or optional.
Constraints on Specialization /
Generalization
z There are four categories of constraints of
specialization and generalization:
z mandatory and disjoint; {mandatory,
and }
z optional and disjoint; {optional, and }
z mandatory and nondisjoint;
{mandatory, or }
z optional and nondisjoint; {optional, or
}
Specialization/Generalization of Staff
Entity into Subclasses Representing
Job Roles
Total or
Partial ?
disjoint
or
overlap ?
Specialization/Generalization of Staff
Entity into Job Roles and Contracts of
Employment
EER Diagram with Shared Subclass
and Subclass with its own Subclass
DreamHome Worked Example - Staff
Superclass with Supervisor and
Manager Subclasses
DreamHome Worked Example -
Owner Superclass with PrivateOwner
and BusinessOwner Subclasses
DreamHome Worked Example - Person
Superclass with Staff, PrivateOwner,
and Client Subclasses
External Level,
External Schema,
Conceptual Design,
Conceptual Data Model
CASE 1
DreamHome Case Study – An Overview
Pls refer to Connolly, T., Begg,
C.(2002).“Database System: A Practical
Approach to Design, Implementation, and
Management”, Addision Wesley, USA
Chapter 10, Section 10.4.1, pp 309
First-cut ER diagram for Staff View
of DreamHome
Extract from Data Dictionary for
Staff View of DreamHome Showing
Description of Relationships
Extract from Data Dictionary for
Staff View of DreamHome
Showing Description of Attributes
ER Diagram for Staff View of
DreamHome with Primary Keys
Added
Revised ER Diagram for Staff View
of DreamHome with Specialization
/ Generalization
Example of a Non-Redundant
Relationship FatherOf
Using Pathways to Check that the
Conceptual Model Supports the
User Transactions
Questions
Acadia Teaching Database
Design an E-R schema for a database to store info about
professors, courses and course sections indicating the
following:
z The name and employee ID number of each professor
z The salary and email address(es) for each professor
z How long each professor has been at the university
z The course sections each professor teaches
z The name, number and topic for each course offered
z The section and room number for each course section
z Each course section must have only one professor
z Each course can have multiple sections
