MONASH
INFORMATION TECHNOLOGY
Database Design 1: Conceptual Modelling
Lindsay Smith
ANSI/SPARC architecture
External level
Conceptual level Internal level
View 1
View 2
Conceptual Schema
View n
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
Student view
5
Staff and Student View
6
Admin View
7
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
8
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.
9
Conceptual Level (ER Model)
10
Conceptual Level (ER Model) – Keys only
11
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.
12
Logical Level (Logical Model)
13
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
14
Physical Level – Starting point
15
ENTITY RELATIONSHIP DIAGRAM
ERD – Notation
Chen
Information Engineering/James Martin/Crows foot
17
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.
18
Entity, Attributes and Relationships
Please note this diagram is incomplete
Q1. How many entities are there in the above diagram?
A. 1 B. 2 C. 3 D. 4
A
B
mars.mu
Feed Code: HYT80D
20
A
B
Please note this diagram is incomplete
Q2. How many relationship are there on the above diagram?
What is the degree of the relationship (the number of entities participating in the relationship) ?
A. 1, unary
B. 2, binary
C. 1, binary
D. 3, ternary
21
Q3. “An employee is assigned to be a member of a team. A team with more than 5 members will have a team leader. The members of the team elect the team leader.”
List the entity(s) which you can identify in the above statement. Enter them one by one – select the tick for each and then use the X to add a second entity if required.
22
Q4. How many relationships connect TEAM and EMPLOYEE?
A. 1 B. 2 C. 3 D. 4
23
one to many
supports
one to one
supports
many to many
supports
A
B
A
B
A
B
24
Relationship Participation
Q5. “A Team with more than 5 members will have a team leader. The members of the team elect the team leader.”
What is the relationship participation of the relationship TEAM — lead_by — EMPLOYEE
A.
lead_by
lead_by
B.
C. lead_by
25
Q6. “The company also introduced a mentoring program, whereby a new employee will be paired with someone who has been in the company longer.”
How many entity/ies do you need to model the mentoring program?
A. 1 B. 2 C. 3
26
27
Q7. “To attract high calibre talent, the company provides generous remuneration package as well as health insurance support for the employees and their family. To do this, the HR team needs to know the details of the family members. The family information will be recorded”
Choose a TRUE statement.
A. EMPLOYEE entity is a strong entity and FAMILY is a strong entity.
B. EMPLOYEE entity is a weak entity and FAMILY is a strong entity.
C. EMPLOYEE entity is a strong entity and FAMILY is a weak entity.
D. EMPLOYEE entity is a strong entity and FAMILY is strong entity.
28
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)
29
Weak vs Strong Entity
30
Identifying vs Non-Identifying Relationship
▪ Identifying
▪ Identifier of A is part of identifier
of B.
supports
▪ 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. supports
▪ Shown with broken line
▪ Department no (identifier of department) is not part of Employee’s identifier.
A
B
A
B
31
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
32
Q8. 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, Mongo db, 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.
33
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.
34
Resolving Multivalued Attributes
35
36
…”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
37
Associative (or Composite) Entity
38
39