MONASH
INFORMATION TECHNOLOGY
Database Design 1: Conceptual Modelling
FIT9132
ANSI/SPARC architecture – proposed 1975
View 1
View 2
External level
(Users View)
Conceptual level
(All Data and Relationships)
Internal level
(Physical representation – hash, btree, isam …)
Physical Storage
View n
Conceptual Schema
Internal Schema
2
The Database Design Life Cycle
Requirements Definition
Conceptual Design
Logical Design
Physical Design
3
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.
4
Different views of the underlying data
Student
Staff & Student
Admin
5
ER Modeling
▪ ER (Entity-Relationship) model developed by Peter Chen in 1976 to aid database design.
▪ Used for conceptual model (ERD).
▪ ER diagrams give a visual indication of the design.
▪ Basic components: – Entity
– Attribute
– Relationship
6
Conceptual Design
▪ Develop the enterprise data model.
▪ Corresponds to the conceptual level of the
ANSI/SPARC architecture.
▪ Independent of all physical implementation considerations (the type of database to be used).
▪ Various design methodologies may be employed such as UML, ER (Entity-Relationship).
▪ ER consists of ENTITIES and RELATIONSHIPS between entities
–An ENTITY will have attributes (things we wish to record), one or more of which will identify an entity instance (called the KEY)
7
Conceptual Level (ER Model)
ENTITY
Collection of “Customer(s)”
RELATIONSHIP
Relates entities
KEY ATTRIBUTE(S)
Instance identifier
NON KEY ATTRIBUTE
Other non-key attributes
Assignment 1A
8
Logical Design
▪ Develop a data model which targets a particular database model (e.g. relational, hierarchical, network, object-oriented, noSQL).
▪ Independent of any implementation details which are specific to any particular vendors DBMS package.
▪ Normalisation technique (see week 4) is used to test the correctness of a relational logical model.
9
Logical Level (Logical Model – Relational)
Assignment 1B
10
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 (not part of unit).
▪ Physical design phase is dependent on the particular DBMS environment in use.
▪ ANSI/SPARC internal level.
11
Physical Level – Starting point
12
ERD – Notation
Chen
Information Engineering/James Martin/Crows foot
* This is what we will be using
13
Using FLUX
▪ Visit https://flux.qa presenter/dashboard on your internet enabled device
▪ Log in using your Authcate details
▪ Touch the + symbol
▪ Enter the code for your workshop
▪ Answer questions when they pop up.
14
A
B
A
B
In general for Crows Foot notation specific cardinalities are not shown as above eg. (1,4), instead participation is depicted via min and max participation using the standard symbols (Inside symbol = min, outside symbol = max)
CONNECTIVITY one to one
supports
one to many
supports
many to many
supports
A
B
18
19
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)
20
Weak vs Strong Entity
Note the Crow’s Foot model shown here has been modified from the text version
21
Identifying vs Non-Identifying Relationship
▪ Identifying
▪ Identifier of A is part of identifier
of B.
supports
▪ Shown with solid line
▪ ENROLMENT – STUDENT Enrolment key includes student id, which is an identifier of student.
▪ Non-identifying
▪ Identifier of A is NOT part of
identifier of B. supports
▪ Shown with broken line
▪ Department no (identifier of department) is not part of Employee’s identifier.
A
B
A
B
22
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
▪ Attribute classification is driven by Client requirements
– Phone Number?
27
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.
29
Resolving Multivalued Attributes
30
Associative (or Composite) Entity
35