Logical Data Modelling

1
Logical Data Modelling

• The sole purpose of an Information System is to
  support or automate business activities by
  storing and processing relevant business
  information or data.
• It is therefore critical to the success of any IS
  development that the meaning, structure and
  business rules of the required data are fully
  analysed, understood and modelled.
• During the Investigation phase we are concerned
  with understanding the underlying (i.e. logical)
  data requirement rather than making decisions
  about its physical implementation.

2
Current and Required Data

• Most of the data required for the future system
  will be the same in content or meaning as that
  used currently.
• The other form of data analysis concerns
  requirements for new business data.
• The approach of starting with an analysis of
  existing data (or even re-using existing data
  models) will provide the most rigorous and
  efficient approach.
• This approach will also help in driving out
  restrictions in data support arising from
  existing technical constraints.

3
Physical vs Logical Data Structures

• An organisations data will be physically stored
  in many different places, e.g. paper files,
  computer files.
• This data will almost inevitably contain
  duplications and compromises due to the physical
  restrictions of storage, processing or
  practicality.
• Example
• A physical purchase order form will hold
  information about products (product name, product
  number, product price), suppliers (supplier name,
  supplier address), the orders heading (purchase
  order number, purchase order date) as well as the
  quantity of each product ordered (quantity
  ordered).
• While we may have a single physical grouping of
  data on one purchase order form, what we actually
  have is information about several different
  things - products, suppliers, and purchase
  orders.
• In other words the underlying logical view is of
  a number of separate data groupings, each
  describing a different business concept or
  object.
• We will also find that information on, for
  example, products is physically held in many
  other places, such as on customer orders,
  invoices and despatch notes.
• This all leads to a confusing mess of duplication
  and interconnecting information, which in turn
  leads to problems in maintaining data consistency
  and integrity.

4
The LDM

• In SSADM the vehicle for analysing the logical
  structure of an organisations information is the
  Logical Data Model (LDM).
• A Logical Data Model is a way of graphically
  representing what that information is really all
  about, how it relates to other information and
  business concepts, and how business rules are
  applied to its use in the system.
• The LDM is possibly the most important and
  ultimately the most rigorous product of an entire
  SSADM project.
• Logical Data Models consist of two parts
• a diagram called the Logical Data Structure
  (LDS)
• a set of associated textual descriptions that
  explain each part of the diagram.

5
Entities

• Any object or concept about which a system needs
  to hold information is known as an Entity Type
  (or entity for short).
• To be a valid entity we must wish to hold
  information on more than one occurrence of it.
  Entity occurrences are real world instances of an
  entity type.
• For example the entity type Supplier will have
  occurrences such as

6
Entities (continued)

• The symbol for an entity in an LDS is a round
  cornered rectangle containing the entitys name
  (which must be unique)

• An entity must have a number of properties to
  qualify as such
• - There must be more than one occurrence of the
  entity.
• - Each occurrence should be uniquely
  identifiable.
• - There must be data that we want to hold about
  the entity.
• - It should be of direct interest to the system.

7
Attributes

• Each item of information (or data) that we hold
  about an entity is known as an attribute or data
  item.
• Examples of attributes for Supplier might be
  supplier number, supplier name, supplier address,
  and supplier telephone no.
• The detail of an entitys attributes is not
  formally included on the LDS itself. This is held
  in separate textual descriptions, which will be
  discussed later.

8
Relationships

• Entities do not exist in isolation, but are
  related to other entities.
• In physical data structures these relationships
  are signified by physical links such as pointers
  or placement in the same file or document.
• In logical models relationships represent
  business associations or rules and not physical
  links.
• Any entities that are related are linked by a
  line on the LDS.
• The line is labelled with the name of the
  relationship, and is named in both directions.

9
Degree

• The number of occurrences of each entity type
  participating in a given relationship is denoted
  by the degree or cardinality of that
  relationship, and illustrated on the LDS by
  adding crows feet to the relationships line.

• There are three types of degree
• Many to Many (mn). This tells us that each
  occurrence of A is related to one or more
  occurrences of B, and each occurrence of B is
  related to one or more occurrences of A.
• One to Many (1m). This tells us that each
  occurrence of A is related to one or more
  occurrences of B, but each occurrence of B is
  related to only one occurrence of A.
• One to One (11). This tells us that each
  occurrence of A is related to only one occurrence
  of B, and each occurrence of B is related to only
  one occurrence of A.

10
Optionality

• Each relationship is further annotated to show if
  it must exist for all occurrences of the
  participating entity types.
• If there can be occurrences of one entity that
  are not related to at least one occurrence of the
  other, then the relationship is said to be
  optional for that entity.
• The relationship line is then converted to a
  dashed line at its optional end (which could mean
  both ends if both entities are optional
  participants).

11
Developing the LDS

• To start with we are only interested in producing
  a high level model of the current systems
  underlying data structure.
• Due to its largely conceptual nature Logical Data
  Modelling can be one of the most intense
  activities of an SSADM project.
• In many projects development of the LDM is
  started by holding brainstorming sessions with
  small groups of analysts and users.
• With a little practice analysts often find that
  the best method of data modelling is to draw up
  possible LDSs almost instinctively
• Relationships are added as each entity is
  identified and then checked with users on the
  spot.
• This approach has a lot to recommend it,
  particularly at this level of detail or for small
  systems, as diagrams are produced and verified
  quickly.

12
Identifying Entities

• To identify entities in the current environment
  we can begin by looking at our physical data
  stores to find out exactly what it is that they
  hold information about.
• If we take the customer order file and discuss it
  with users, we find that it not only contains
  details of each individual order, but of the
  customers themselves,
• i.e. customer address, customer telephone number
  etc., and so encompasses at least two entities,
  namely Customer and Customer Order.

13
Verification

• Once the list has been drawn up we should verify
  it with key users during preliminary scoping
  interviews.
• The key questions to ask of each entity are
• Are any of the candidates merely attributes of
  another entity?
• Do any of the candidates represent a subset of
  occurrences of another entity?
• Do all of the entities have a unique identifier?
• During this process we may discover new entities,
  merge existing entities or discard candidates as
  being outside the area of investigation.

Note There will often be relationships between
entities that exist in the real world, but which
are not of relevance to the system under
discussion. E.g a customer of ZigZag may well be
employed by one of its suppliers. This is NOT
something that ZigZag will be interested in
recording!  
14
Adding Relationships

• We now examine each entity to see if it is
  directly related, in a way that is of interest to
  the system, to any of the other entities.
• The best way to do this is in discussion with
  users, either taking each entity in turn, or
  starting with a key entity and moving around the
  LDS network as the relationships are
  identified.
• Having identified where we think relationships
  exist, we now consider their degree, optionality
  and names.
• We do this by identifying the business rules that
  apply to each entity pairing.
• The basic process is the same for all pairings,
  so we will look at just one example.

15
Stock - Delivery

• We first consider the relationship from the Stock
  perspective
• Each Stock occurrence will consist of a quantity
  of a single product, all of which was delivered
  on the same delivery.
• If within the depot we have a quantity of a given
  product, some of which was delivered in one
  delivery and some in another, then we will have
  more than one Stock.
• This is an example of one of ZigZags business
  rules, and one that will continue in the new
  system.
• Thus each Stock occurrence is related to just one
  Delivery.
• Each delivery may contain a number of different
  products, each of which will be stored as a
  separate stock (remember that each Stock
  occurrence is a quantity of a single product).
• Thus each Delivery is related to one or more
  Stock occurrences.

16
Stock Delivery (continued)

• We now consider the optionality of the
  relationship
• Each Stock must have been delivered by a
  Delivery.
• So the relationship at the Stock end is
  mandatory.
• However a Delivery could be rejected for quality
  reasons by the depot, in which case the delivery
  would be recorded but would not be related to any
  subsequent Stock occurrences.
• So the relationship is optional at the Delivery
  end.

• Choosing a name is often the hardest part of the
  procedure.
• It is important to name a relationship in both
  directions as it forces us to examine the true
  nature of the relationship, sometimes leading to
  the discovery of additional relationships or even
  entities.
• We should always try to choose phrases that
  accurately reflect the users view of the
  relationship. In our example it is not too
  difficult to find reasonable names delivery of
  and delivered by.

17
Overview LDS

• Continuing this process for all of the
  relationships identified on the matrix gives us a
  first-cut overview LDS for the current system

18
Drilling Down.

• The overview LDS provides us with a good basis
  for building a more complete model of current
  data.
• We begin the process of creating a detailed model
  by looking at this model and discussing it with
  users to check our understanding of the scope of
  current data and to uncover lower level entities
  which can be added immediately.

19
Masters and Details

• Most relationships are 1m.
• The entity at the 1 end is known as the master
  and the entity at the m end as the detail.

• The terms master and detail refer only to an
  entitys role in a particular relationship.
• It is quite possible for an entity to be the
  master in one relationship and the detail in
  another.

20
Keys

• We should be able to select at least one
  identifier for each entity type,
• i.e. an attribute that enables each occurrence of
  an entity to be uniquely identified,
• e.g. for Customer we could use customer number.
• Any attribute or set of attributes which together
  uniquely identify an entity is known as a
  candidate key.
• One of these candidates (there will often only be
  one) should be selected as the primary key.
• Whenever we require direct access to an entity,
  the primary key is used to identify which
  occurrence we are interested in.
• For example, if we needed to access the Supplier
  entity to find out a suppliers address, we would
  use the primary key of supplier number to
  identify the correct occurrence.

21
Foreign Keys

• If we have a relationship between two entities we
  need to be able to associate the occurrences at
  one end with the related occurrences at the
  other.
• In a relational model (such as the LDM) we do
  this by including the primary key of the master
  in the set of attributes of the detail.
• The copy of the masters primary key in the
  detail entity is known as a foreign key.

22
Key Navigation

• Supplier attributes
• Supplier No. (Primary Key), e.g 271
• Supplier Address etc.
• Purchase Order attributes
• P.O. Number (Primary Key), e.g 5001
• P.O. Date etc.
• Supplier Number (Foreign Key), e.g 271
• To access all purchase orders placed with
  supplier number 271, we look for all occurrences
  of Purchase Order with a supplier number
  attribute value of 271.
• Coming in the opposite direction, to access the
  supplier for purchase order 5001, we look for the
  single occurrence of the Supplier entity whose
  primary key is equal to the supplier number given
  in the foreign key of purchase order number 5001,
  i.e. supplier number 271.

23
Types and Notation

• Primary Keys belong to one of three types
• A Simple Key, consisting of a single attribute
• A Compound Key, consisting of two or more foreign
  keys
• A Hierarchic or Composite Key, consisting of one
  or more foreign keys and a qualifying non-foreign
  key attribute.
• Notation
• The primary key is underlined and the foreign key
  preceded by an asterisk to show the contents of
  each entity
• Supplier (supplier number, supplier address,
  supplier tel. no.)
• Purchase Order (P.O. number, P.O. date,
  supplier number)

24
Resolving Many-to-Many Relationships

• Many design techniques can only be carried out on
  hierarchical (i.e. master-detail) relationships
  which are hidden by mn relationships.
• mn relationships make navigation around the
  model very difficult or even impossible (and,
  although we are not really concerned with
  technical issues at this point, they cannot be
  implemented).
• mn relationships very often hide information
  about the participating entities or the
  relationships themselves.

25
Resolving Many-to-Many Relationships - example

• Each Product may be ordered by one or more
  Purchase Orders.
• Each Purchase Order must be an order for one or
  more Products.
• So where do we place the quantity ordered?

26
Resolving Many-to-Many Relationships - example

• If we look at a sample purchase order of ZigZag,
  we will discover that details of quantities and
  products are held in individual purchase order
  lines.

27
Resolving Many-to-Many Relationships - example

• So in this case we can choose a natural link
  entity, which we will call Purchase Order Item.
• Purchase Order Line sounds a bit too similar to
  the physical printed line on the order form.
• The key for Purchase Order Item will be Purchase
  Order Number plus Product Number a compound
  key.

28
Relationships in MN Resolutions

• Whenever we introduce a link entity we need to
  ensure that the relationships we recorded
  previously with its master entities are still
  valid.
• For example, in our overview LDS we recorded a
  many to many relationship between Despatch and
  Customer Order.
• This may at a high level appear reasonable as it
  is common for some items in an order to go into
  one van load (Despatch) and some into another.
• However, the contents of each item within the
  order is always despatched in its entirety in the
  same van load (i.e. if 3 copies of Puccinis
  Tosca are ordered within a single customer order,
  they will all be delivered together).
• Therefore, each Despatch is actually related to
  many Customer Order Items, rather than to whole
  Customer Orders.

29
Link Entities

• Depot Zone and Product Type provide another more
  complex illustration of many to many
  relationships
• Each Depot Zone may store one or more Product
  Types.
• Each Product Type must be storable in one or more
  Depot Zones.
• The attributes that make up Depot Zone are Depot
  Zone Number, Shelf Height, and Depot Zone
  Description etc. Depot Zone Number is a unique
  identifier that is assigned to each Depot Zone,
  and is the label attached to the end of each row
  of shelving in the zone. Depot Zone Description
  would include values such as CD and DVD, Videos
  and Books, and Tape etc, which describe the sorts
  of products that the shelving in each zone can
  accommodate.
• The attributes of Product Type include Product
  type code and Product type name, where the
  Product type code is an abbreviation of the
  Product type name, e.g. BV for Blank Video,
  DVD for DVD etc.
• So, for example, we might have the following
  cases
• Depot Zones 101 and 105 store DVD and CD
  product types
• Depot Zone 102 stores VHS, BV and SPB
  (small paperback book) product types.

30
Link Entities

• To make these associations we would have to set
  up lists of foreign keys in both entities, of
  arbitrary length.
• Significant maintenance overhead
• Navigation around the model very difficult
• Against the rules of relational data modelling
• Solution A link entity
• Each occurrence will store a valid association or
  pairing of a Depot Zone occurrence with a Product
  Type occurrence, such as
• Depot Zone Product Type
• 101 DVD
• 101 CD
• 105 DVD
• 105 CD
• 102 VHS
• 102 BV
• 102 SPB

31
Pigs Ear
32
Resolving One-to-One Relationships

• The problems associated with 11 relationships
  are less clear-cut than with mn relationships
• 11 relationships often obscure an underlying
  single entity.
• There may be a missing link entity.
• Later design techniques may require all
  relationships to be master-detail.
• In the ZigZag overview LDS there are two 11
  relationships - between Delivery and Purchase
  Order and between Supplier Invoice and Delivery .
• Deliveries are identified by the purchase order
  they are satisfying
• The only information currently held about them
  details which parts of the purchase order they
  have successfully delivered.
• It is quite easy in this case to view Delivery as
  a logical extension (or conclusion) of a Purchase
  Order, so we will merge the two entities and
  transfer all of Deliverys relationships to
  Purchase Order.
• To do this successfully, Purchase Order will
  contain attributes delivery date and suppliers
  delivery reference while Purchase Order Item will
  contain quantity delivered.

33
Resolving One-to-One Relationships (continued)
34
Resolving One-to-One Relationships (continued)
35
Removing Redundant Relationships

• One of our aims when drawing up an LDS should be
  to include only the minimum number of
  relationships needed to apply all of the business
  rules relating to data.
• Any unnecessary relationships are termed
  redundant, and will involve us in a maintenance
  overhead if implemented.

• The major difference between relationships and a
  route map is that each relationship carries with
  it a meaning, and so different routes between
  entities will often have different meanings, or
  enforce different rules.

36
Removing Redundant Relationships

• Each Purchase Order may be related to a number of
  Supplier Invoice, each of which is related to a
  PO Item.
• Each PO Item may relate to just one Supplier
  Invoice, which relates to just one Purchase
  Order.
• HOWEVER Each Purchase Order MUST contain at
  least one PO Item.
• If the Invoice is not present then removing the
  direct relationship would mean that a
  relationship could not be established between
  Purchase Order and PO Item.

37


Product Type
Depot Zone


Supplier

Depot Zone


Allocation
Product

Product

Substitute

Purchase Order

Customer

Despatch

Supplier
Invoice

Customer Order

Purchase
Order
Item

Customer
Order
Item

Stock

38
Selected ZigZag Entities and Attributes
39
Completing the Documentation
40
Small Projects
Entity Description Table and Data Catalogue Table
for Small Projects
41
Validating the LDM

• we need to check that the LDM can provide access
  to all of the data items required by each update
  or enquiry process.
• Most processes will need to access a number of
  data items, which will be specified by some
  selection criteria.
• These items will often be represented by the
  attributes of more than one entity.
• navigate around the relationships of the LDS,
  applying the selection criteria to filter out the
  entity occurrences we need to provide all of the
  necessary data. These navigations are called
  Access Paths.

42
Validating the LDM

• For example, when allocating a zone in which to
  store the stock of a particular product received
  in a delivery (a process called Allocate Stock
  Zone), we will need to find out which depot
  zones have been designated for the storage of
  that type of product.
• The entry point to the LDS is via the product
  number in the entity Product.
• We can then access its product type, and then the
  possible zones in which this product can be
  stored by reading through all the occurrences of
  Depot Zone Allocation for that Product Type.

43
(No Transcript)