Advanced Data Models
and Logical
Design Stages
Week 2
Mcfadden, F, Hoffer J,and Prescott M Modern Database Management
Connolly, T and Begg, C Database Systems
Carter, P, The Relational Database
LEARNING OUTCOMES
After this week’s lecture and tutorial you will be able to:
Revisit Data modelling
Advanced Data modelling
Identify the purpose of Logical design
Derive a set of relations from an ER model
Revise Relational Model rules
DATABASE DEVELOPMENT LIFE
CYCLE
Project Initiation
and Planning
Conceptual
Modelling
Logical Design
Physical
PhysicalDesign
Design
Database development Implementation
activities during the
development life cycle Maintenance
PART 1 - ADVANCED DATA
MODELS
4
WHAT MAKES UP A CONCEPTUAL
MODEL?
Entity-Relationship Diagram (ERD)
Relationships, Cardinality, Optionality
Entities, Attributes, Identifiers
Entity and Attributes definitions
Occurrences
Modeling requirements (To-be model)
Meant to be a ‘pure’ model
– Irrespective of implementation
– e.g. type of database, who/what, etc
EXAMPLE OF AN EERD
VEHICLE
manages
EMPLOYEE uses
assigned to
VAN PARKING SPACE
used by used by
CAR
has
DEPARTMENT
6
ADVANCED OR EXTENDED MODELLING
STRUCTURES
Recursive
Relationships
7
Exclusive Arcs Relationships
Subtypes
Identification
Dependence
RECURSIVE RELATIONSHIPS
Sometimes need to model relationships with other
occurrences of same entity
8
The “manages” relationship between occurrences of STAFF
RECURSIVE RELATIONSHIPS manages
is managed by
Can be shown on ERD as STAFF
9
Do all staff have a manager and do all members
of staff act as managers?
No, Vice-Chancellor not managed by other staff
member so…
manages
is managed by
STAFF
DATA VIEW - STAFF
Staff id Staff Name Manager Id
S123 H Gould S167
S345 M Soosay S167
10
S167 C Pattinson S761
S761 M Dastbaz
Who is H Gould’s manager?
S167 C Pattinson
SUB TYPES
May need to show categories of an entity
E.g. VEHICLE
WHY?
11
Because we may hold different attributes for each sub-type
CAR or VAN
May also want to show a relationship to a sub_type not the
whole entity type
WARNING Don’t confuse sub_types with different entity
occurrences
SUB TYPES
VEHICLE
attributes registration_no, make, model
VAN sub type
12
attributes registration_no,no._of wheels, load_capacity
CAR sub type
attributes registration_no,no._of seats, extras
Note: Sub_types use same
identifier as their Entity
VEHICLE
e.g. registration_no
CAR
VAN
EXCLUSIVE ARC RELATIONSHIP
Sometimes two or more relationships are
mutually exclusive
13
E.g. a bank ACCOUNT is either for a PERSON
or a COMPANY but not both
We can show this on an ERD as an exclusive arc
PERSON
is f or
ACCOUNT
is f or
COM PANY
Identification Dependence
An apartment will have a number, eg 102.
That is not a unique, identifying attribute
Why not?
14
What is the solution?
alt.1 create a new attribute to act as the identifier
OR
alt.2 recognise that the apartment number is unique for
a particular building
BUILDING has APPARTM ENT
Identification Dependence
Consider the example below (suggestion in alt. 1):
BUILDING has APPARTMENT
Building Apartment
id name address id floor
PR Priestley Headingly 100 1
JG James Graham Headingly 101 1
CA Ceadmon Headingly 102 1
CV Cavendish Headingly 200 2
201 2
202 2
100 1
15
101 1
etc…..
ALTERNATIVE MODELLING
Some believe UML will replace ERD’s*
*http://www.dulcian.com/papers/ECO/
1999/1999_ECO_MakingTransitionERDtoUML.htm
UML Class Diagram looks similar but not
16
same:-
Meaning
0..1 Zero or one
1 One only
0..* Zero or more
1..* One or more
Ref www.agilemodelling.com
n Only n where n>1
0..n Zero to n where
n>1
1..n One to n where n
>1
PART 2 - LOGICAL DESIGN
17 Relational Data Model Rules
RDM DOCUMENT CONTAINS
EXAMPLES OF ALL RULES
18
LOGICAL DESIGN - STAGE 1
Derive a set of relations from an ER model
Entity Relation/Table
PATIENT Patient (patient_id, name, d-o-b)
1. Map Entities
(a) each entity becomes a relation with the same name
(b) the identifying attribute becomes the primary key
(c) other attributes provide non-key fields, names unchanged
A RELATION (TABLE)
is a logical definition of a row (implemented as a table)
can be seen eventually as a set of named columns and
a number of rows of data
has a primary key, which is an attribute (or attributes)
that uniquely identifies each row in a relation
may have foreign keys, which are the primary key of
another relation
has a structure which is expressed using a shorthand
form: Employee(empno, name, ….., deptno),
primary key underlined, foreign key(s) in italics
Relation/Table Keys
primary key underlined, foreign key(s) in italics
Employee(empno, name, ….., deptno),
An attribute, or combination
PRIMARY KEY of attributes that uniquely
identifies each row
An attribute, or combination
of attributes, that is the FOREIGN KEY
primary key of another Defined by looking at the relationship
cardinality and applying Relational
relation Data Model rules!
HOW MANY TABLES WILL WE
HAVE?
VEHICLE
manages
EMPLOYEE uses
assigned to
VAN PARKING SPACE
used by used by
CAR
has
DEPARTMENT
22
Logical Design - Stage 2
Applying Relational Data Model Rules
RELATIONSHIPS from the ER model
are represented by Foreign Keys
WARD PATIENT
Ward (WardName, Location
Patient (Patient_id, Name, DateOfBirth, Address
Note : this is a ‘translation process’ checking each entity &
relationship, to produce the relations(tables), with the
appropriate keys
…We take Ward No PK from ward into
Patient table as FK?…
WardNo Ward Location Ward Type
(Primary Key) Name
1125 Tudor Main Block General medical for Elderly
2133 Glyndwr Dead Hero Annex Male surgical
………..
Patient No Patient Wardno
(Primary Name
Key)
23 Joan Allen 1125
117 Manjit Bhopal 2133
216 Kevin O'Shea 2133
Foreign Keys must contain a value, they can’t contain an empty value (NULL). This means
that we have ‘rules’ which specifically deal with optionality from the ERM
Example : 1:M + optionality
In the example below, patients may not be in a ward…...
WARD O PATIENT
An extra relation
is required, to
Ward(wardno,......) represent this
Patient(patno,.........) relationship in
Occupancy(patno,wardno) order to avoid
Null Foreign Keys
WARD O O PATIENT
What relations are derived from here?
The data in the tables ... 1:M with optionality on 1 end
Ward
WardNo Ward Location Ward Type
Primary Key Name
1125 Tudor Main Block General medical for Elderly
2133 Glyndwr Dead Hero Annex Male surgical
………..
Patient No Patient Patient Wardno
No
Which ward is
Primary Key Name Foreign
Primary Key Manjit Bhopal
Key in?
23 Joan Allen 23 1125
117 Manjit Bhopal 117 2133 Which ward is
216 Kevin O'Shea 216 2133 Fred Smith in?
111 Fred Smith
Patient Occupancy
Task 1
Derive relations and key’s
CUSTOMER INVOICE
Identify Relation/Table, PK and FK(s)
CUSTOMER (CustNo, Name, Address……
INVOICE (InvoiceNo, Date….
Example M:M relationship
contains
COURSE MODULE
COURSE(courseno,....) MODULE(modcode,....)
Course_Module(courseno,modcode)
An additional relation, with a compound primary key, made up of the
primary keys from each relation is needed.
Would optionality change this? NO
Task 2
Derive relations and key’s
CUSTOMER ACCOUNT
Identify Relation/Table, PK and FK(s)
CUSTOMER (CustNo, Name, Address….)
ACCOUNT (AccountNo, BranchNo….)
Example: Recursion 1:M
O manages Treated very much as for
previous examples
Employee(empno,.....mgrempno)
Employee
O manages Need to use a
unique name
for the FK
O
Employee
Not all employees Employee(empno,.....)
have a manager …
need an additional Manages(empno,mgrempno)
relation
Example: Identification Dependence
BUILDING has APPARTM ENT
BUILDING (building_id, name, address,…..)
APARTMENT (building_id ,apartment_id, floor,…)
Building Apartment
Bid name address Bid Aid floor
PR Priestley Headingly PR 100 1
James
JG Graham Headingly PR 101 1
CA Ceadmon Headingly PR 102 1
CV Cavendish Headingly PR 200 2
PR 201 2
PR 202 2
JG 100 1
JG 101 1
etc…..
Example: Super/Sub type Entities
EMPLOYEE (empno, Name,
EMPLOYEE Address, Payroll, number, Job title,
Department,NI number, Age,Date
of birth)
ACADEMIC Academic (empno, publications)
Technical (empno, allocations)
Support (empno, part time, full
TECHNICAL time)
You will implement 4 tables
SUPPORT
Example: Exclusive Arcs
We can’t have null values in foreign keys, so that dictates
which relation(s) has the foreign key when you have a 1:1
FLIGHT
HOLIDAY Uses
COACH
Holiday(holiday-code,…..)
Flight(flight-id,holiday_code….)
Coach(coach-id,holiday_code….) Note the relationship
Cardinality is 1:1
ANSWER THE FOLLOWING:
Does each table have to have a PK?
Does each table have to have a FK?
How many PK’s does a table normally have?
PK for super and sub entities is the same?
PK is sub entity is also a FK?
Exclusive arc is “ignored” when deriving a relationship?
Recursive relationship rule 1:M is applied the same way
as if you had 1:M between two entities.
Which of these is most likely to be a FK…
”it support dept” or “ 123”?
IMPORTANT NOTICE!
Make sure you can log into your Apex account
before next weeks tutorial!
Read instructions carefully!
Reset your password if you are not sure what it is, so
you avoid locking your account.
If you have problem with your account and
password immediately seek help from the help
desk JG212 or email
aet.helpdesk@leedsbeckett.ac.uk
Important Note: tutor can not help you with
unlocking your account in the class.
NEXT WEEK
Physical Design and Normalisation
36
SOURCES
Mcfadden, F, Hoffer J,and Prescott
M Modern Database
Management
Connolly, T and Begg, C Database
Systems
Carter, P, The Relational
Database
37
THE END
? Questions ?
? ?
?
? ? ? ?
38
