ORGANIZING DATA AND INFORMATION-II

1
ORGANIZING DATA AND INFORMATION-II (Business
Data modeling) Chandra S. Amaravadi
2
IN THIS PRESENTATION..

• Quick Review
• entity, entity class, attribute
• database file, record, attribute, attribute value
• schema, primary key
• dbms activities
• Database planning
• Database requirements (modeling)

3
DATABASE PLANNING
4
PLANNING OBJECTIVES
The objective of planning is to get an overall
understanding of organizations data and resource
requirements

• assess size of database
• preliminary understanding of data requirements
• resource requirements
• scope

What is a systematic way of obtaining an overview
of data?
5
PLANNING METHOD
Planning method is called Business systems
planning

• Understand background of the organization
• Study functions and processes
• Preliminary understanding of data-function
  relationships. What function creates what data?
• Develop EA (Planning) matrices Enterprise Data
  Model
• Use for planning/scope/schedule/resource
  requirements

6
DATABASE ANALYSIS USING ENTITY-RELATIONSHIP
(ER) APPROACH
7
DATABASE ANALYSIS

• Next step after planning
• Develop an integrated model of data reqmts.
• ER Developed in 1976 by Chen to model data
  relationships
• Main concepts are
• entity classes,
• attributes and
• Relationships

How are entity classes and attributes identified?
8
MODELLING ENTITY CLASSES
Entity Class Collection of related entities.
AVIS
FLIGHTS
PRODUCTION
ord
BOOKING
PROPERTY

• Common nouns (singular or plural doesnt matter)
• Customers, Products, Students etc.
• Cannot be attributes or proper nouns (e.g.
  IBM)
• Cannot be database concepts

9
MODELLING ATTRIBUTES
Attribute Property or characteristic of an
entity class

• Generally on top of eclass
• or below eclass
• Use ovals
• Pkey is leftmost attribute
• underlined
• attr. not duplicated except for
• pkey

from
to
flt
FLIGHTS
Pkey An attribute whose values are unique
within a table
10
FOR DISCUSSION
For a real estate organization, identify which of
the following are

• Eclasses
• attributes
• relevant and not relevant?

• listing
• sale id
• advertisement
• owner
• employee bonuses
• refrigerator s.no
• bedrooms
• newsletter
• square footage

11
MULTI-VALUED ATTRIBUTES..
Sometimes attributes have more than one set of
values per entity instance.
to
from
fare
flt
FLIGHTS
12
MODELLING RELATIONSHIPS
Relationships logical and meaningful
connections between two or more entity classes.
to
pname
addr
from
flt
reserved
FLIGHTS
PASSENGERS

• Use diamonds, label
• L-R, T-B order
• Indicate cardinality (11, 1M, MN)

13
CARDINALITY
CARDINALITY -- Number of entities that
participate in a relationship. Can be 11, 1M
or MN Between two entity classes A and B, these
are defined as follows 11 -- Each instance of
A is associated with one instance of B
and vice versa. 1M -- Each instance of A is
associated with many instances of B.
Each instance of B is associated with one
instance of A. MN -- Each instance of A is
associated with many instances of B
and vice versa.
Max cardinality rule in 3-way above, use max
cardinality
14
THREE WAY RELATIONSHIPS
Agencies book flights for passengers
book
AGENCIES
FLIGHTS
PASSENGERS
in 3-way above, use max cardinality
15
RELATIONSHIP ATTRIBUTES
Attributes that belong with a relationship rather
than with an eclass. E.g. agencies book flights
for passengers on a certain date
date
booked
FLIGHTS
PASSENGERS
AGENCIES
Include airlines
16
CLASS/SUBCLASS RELATIONSHIPS..
Class/subclass Entity classes can be grouped
into parent/child or superclass/class or
class/subclass relationships based on common
characteristics.
PASSENGERS
Class/Superclass
Is-a
BUSINESS
Sub -classes
LEISURE
17
DRAWING THE ER CHART

• List eclasses and attributes, use format below
• Identify relationships among eclasses
• Decide which relationships to show
• Verbalize relationships
• Use rectangles for eclasses, diamonds
• for relationships and ovals for attributes
• Underline pkey
• Use only horizontal and vertical connectors

Prod prod, descr., price, qty Cust cust,
name, dt. joined
18
SOME GUIDELINES..

• Label eclasses, attributes and relationships
• Pkey should be left most attribute and underlined
• Depict the cardinalities
• Use max cardinality for 3-way and above
• Do not repeat attributes do not show
  cross-reference keys
• If developing for an organization, do not include
  name of organization or organization e.g. IRS, DMV

19
ER CHECK LIST

• all entity classes depicted?
• no unnecessary duplication?
• all attributes included/labelled?
• multivalued attr. shown (if any)?
• derived/system attr. omitted?
• Pkeys marked? fkeys omitted?
• Relationships correct/labelled?
• Cardinalities shown?
• Cycles avoided?

20
DISCUSSION
Draw Data Models (ER models) corresponding to the
following Situations
A property can be business or residential
property. It can have one or more owners. An
owner can own more than one property. A
professional publication can have single author
or multiple authors. The author could be
affiliated with a company or with a university. A
publication has the author names, journal title,
volume, year, and page. A person(s) may bid for
one or more items at an auction. The bid price
is specified at the time of bidding. This has to
be more than the start price. There are multiple
auctions at any given time. Persons could bid for
multiple items in multiple auctions.
21
DISCUSSION..
Draw Data Models (ER models) corresponding to the
following situations
A person makes a reservations for flights. A
reservation can consist of one or more flights.
A flight is operated by one or more airlines. A
flight has a flt, origination, destination,
departure time, arrival time and of passengers.
Airlines can be domestic or international. They
have a code, a full name, terminal and contact
information.
Legal situation A case can be a civil or
criminal case and is tried in a particular court
(such as the 9th judicial circuit court). The
party pressing the lawsuit is called the
plaintiff and the accused is called the
defendant. The defendant can have an attorney or
a public defender if they cannot afford one. The
attorney for the plaintiff is called the
prosecuting attorney. Attorneys present evidence
for or against the case (no need to distinguish
between these two) and could use objects or
witnesses. A judge decides admissible evidence
and presides over the case. A jury evaluates the
case and passes a verdict of guilty or not
guilty
22
DISCUSSION..
Develop a data model for an order fulfillment
center with the following conditions (make
relevant assumptions)

• Customer places one or more orders
• Each order is for one or more of the companys
  products
• An order can have multiple invoices
• Orders are entered by sales representatives
• Orders are shipped by carriers
• Each shipment has a bill of lading.
• Products and shipments are stored in warehouses
• A shipment contains more than one order
• A carrier can deliver multiple shipments
• A shipment is delivered by one carrier only

23
Entity classes
Relationships
Attributes
Multivalued attributes
Is-a
Class/ subclass
1 1
1 M
M N
24
SUMMARY OF CONCEPTS
Term Definition
Enterprise Data Model The data model created during the planning process.
Enterprise Analysis Matrix (EA matrix) This is also called the planning matrix. It shows relationships between processes or activities and entity classes (information classes)
Entity Individual example of person, place or thing.
Entity Class Collection of related entities.
Attributes Properties of entity classes about which we would like to collect information.
Relationship Logical and meaningful connection between two or more entity classes.
Class/subclass Grouping of entity classes based on common characteristics.
Cardinality Number of entities participating in a relationship.
Max cardinality In 3 way relationships and above use max cardinality.
25
(No Transcript)