MONASH
INFORMATION TECHNOLOGY
Database Design 1: Conceptual Modelling
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
A
B
Please note this diagram is incomplete
Q1. How many relationships are there in the above diagram?
What is the degree of the relationship/s (the number of entities participating in the relationship/s) ?
A. 1, unary
B. 2, binary
C. 1, binary
D. 3, ternary
15
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
16
17
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)
18
Weak vs Strong Entity
Note the Crow’s Foot model shown here has been modified from the text version
19
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
20
Student Activity
▪ Using the Monash Software Case Study, identify the
entities present and their key attribute/s
– draw a box for each entity, name it and add the key attribute/s
– For example:
21
Entities in the Monash Software Case Study
23
Student Activity
▪ Using the Monash Software Case Study, identify the relationship(s) and participation which exist between TEAM and EMPLOYEE
25
TEAM – EMPLOYEE relationships
27
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?
28
Q2. The employee details that will be recorded are:
Employee number, Full name, Address, Date of birth, Tax file number and Skill(s). Examples of skills are Java, Python, UNIX, Relational db, MongoDB, etc
Choose a TRUE statement.
A. ADDRESS is a multi-valued attribute and SKILL is a multi-valued attribute.
B. ADDRESS is a composite attribute and SKILL is a composite attribute.
C. ADDRESS is a composite attribute and SKILL is a multi-valued attribute.
D. ADDRESS is a multi-valued attribute and SKILL is a composite attribute.
30
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.
31
Resolving Multivalued Attributes
32
Student Activity
▪ Using the Monash Software Case Study, add attributes to your EMPLOYEE entity
33
35
Q3. The company provides several in-house training programs. The HR team needs to keep track of the details about who has done what. An employee can do several training programs. At the completion of a training, a certificate will be provided to the employee containing the training name and the completion date.
A. The relationship between EMPLOYEE and TRAINING is ternary.
B. The relationship between EMPLOYEE and TRAINING is 1:M.
C. The relationship between EMPLOYEE and TRAINING is M:1.
D. The relationship between EMPLOYEE and TRAINING is M:N.
37
Incomplete model
…”the company provides several in-house training programs. The HR team needs to keep track of the details about who has done what. An employee can do several training programs.
At the completion of a training, a certificate will be provided to the employee containing the training name and the completion date.”…
Incomplete model
38
Associative (or Composite) Entity
39
Student Activity
▪ Using the Monash Software Case Study, add attributes to TRAINING EMPLOYEE
40
42