Database Design 1: Conceptual
Modelling
MONASH
INFORMATION
TECHNOLOGY
2
ANSI/SPARC architecture
View 1 View 2 View n
Conceptual
Schema
Internal
Schema
External level
Conceptual level
Internal level
3
The Database Design Life Cycle
Requirements
Definition
Conceptual
Design
Logical
Design
Physical
Design
4
Requirements Definition
▪ Identify and analyse user views.
▪ A ‘user view’ may be a report to be produced or a
particular type of transaction that should be
supported.
▪ Corresponds to the external level of the
ANSI/SPARC architecture.
▪ Output is a statement of specifications which
describes the user views’ particular requirements
and constraints.
5
Student view
6
Staff and Student View
7
Admin View
8
ER Modeling
▪ ER (Entity-Relationship) model developed by Peter
Chen in 1976 to aid database design.
▪ May be used for conceptual (ERD)/logical design
(ERD like).
▪ ER diagrams give a visual indication of the design.
▪ Basic components:
– Entity
– Attribute
– Relationship
9
Conceptual Design
▪ Develop the enterprise data model.
▪ Corresponds to the conceptual level of the
ANSI/SPARC architecture.
▪ Independent of all physical implementation
considerations.
▪ Various design methodologies may be employed,
including the ER (Entity-Relationship) approach.
10
Conceptual Level (ER Model) – Keys only
Ass 1 Part A
11
Conceptual Level (ER Model) – All Attributes
Ass 1 Part B
12
Logical Design
▪ Develop a data model which targets a particular
database model (e.g. relational, hierarchical,
network, object-oriented).
▪ Independent of any implementation details which are
specific to any particular DBMS package.
▪ Normalisation technique (see week 5) is used to test
the correctness of the logical model.
▪ May also be considered to correspond to the
conceptual level of the ANSI/SPARC architecture.
13
Logical Level (Logical Model)
Ass 1 Part B
14
Physical Design
▪ Develop a strategy for the physical implementation
of the logical data model.
▪ Choose appropriate storage structures, indexes, file
organisations and access methods which will most
efficiently support the user requirements.
▪ Physical design phase is dependent on the particular
DBMS environment in use.
▪ ANSI/SPARC internal level.
▪ Shown in SQL Developer Data Modeller as the
Relational Model
15
Physical Level – Starting point
ENTITY RELATIONSHIP
DIAGRAM
17
ERD – Notation
Chen
Information
Engineering/James
Martin/Crows foot
18
ERD – Notation cont’d
Chen’s Notation
▪ Semantically rich.
▪ Complex diagram.
▪ ‘Pure’ conceptual level.
Information Engineering
▪ Less semantics.
▪ Simpler diagram.
▪ Mix between conceptual
and logical levels.
Entity, Attributes and
Relationships
24
A B
supports
A B
supports
A B
supports
one to many
one to one
many to many
27
29
Weak vs Strong Entity
▪ Strong entity
– Has a key which may be defined without reference to other entities.
– For example EMPLOYEE entity.
▪ Weak entity
– Has a key which requires the existence of one or more other entities.
– For example FAMILY entity – need to include the key of employee to
create a suitable key for family
▪ Database designer often determines whether an entity can be described
as weak based on business rules
– customer pays monthly account
• Key: cust_no, date_paid, or
• Key: payment_no (surrogate? – not at conceptual level)
30
Weak vs Strong Entity
31
Identifying vs Non-Identifying Relationship
▪ Identifying
▪ Identifier of A is part of identifier
of B.
▪ Shown with solid line
▪ Enrolment’s PK includes student
id, which is an identifier of
student.
▪ Non-identifying
▪ Identifier of A is NOT part of
identifier of B.
▪ Shown with broken line
▪ Department no (identifier of
department) is not part of
Employee’s identifier.
A B
supports
A B
supports
32
Types of Attributes
▪ Simple
– Cannot be subdivided
– Age, sex, marital status
▪ Composite
– Can be subdivided into
additional attributes
– Address into street, city, zip
▪ Single-valued
– Can have only a single
value
– Person has one social
security number
▪ Multi-valued
– Can have many values
– Person may have several
college degrees
▪ Derived
– Can be derived with
algorithm
– Age can be derived from
date of birth
34
Multivalued Attribute
▪ An attribute that has a
list of values.
▪ For example:
– Car colour may
consist of body
colour, trim colour,
bumper colour.
▪ Crow’s foot notation
does not support
multivalued attributes.
Values are listed as a
separate attribute.
35
Resolving Multivalued Attributes
38
Associative (or Composite) Entity