Data Modeling

1
Data Modeling
2
Data Modeling

• A database can model a world which is seen
  as
• a collection of entities,
• relationships among entities.
• An entity(-instance) is an individual object
  that exists and is distinguishable from other
  individuals.
• Example specific person, company, event,
  plant
• Entities have attributes Example people have
  names and addresses
• An entity set (also entity type) is a set of
  entities of the same type that share the same
  properties.
• Example set of all persons, companies, trees,
  holidays

3
Entity Sets customer and loan
customer-id customer- customer- customer-
loan- amount
name street city
number
4
Attributes

• An entity is represented by a set of attributes,
• i.e. descriptive properties possessed by all
  members of an entity set.
• Example
• customer (customer-id, customer-name,
  customer-street, customer-city) loan
  (loan-number, amount)
• Domain the set of permitted values for each
  attribute
• Attribute types
• Simple and composite attributes.
• Single-valued and multi-valued attributes
• E.g. multivalued attribute phone-numbers
• Derived attributes
• Can be computed from other attributes
• E.g. age, given date of birth

5
Composite Attributes
6
Relationship Sets

• A relationship (-instance) is an association
  among several entitiesExample Hayes
  depositor
  A-102 customer entity relationship set
  account entity
• A relationship set is a mathematical relation
  among n ? 2 entities, each taken from entity
  sets (e1, e2, en) e1 ? E1, e2 ? E2, ,
  en ? Enwhere (e1, e2, , en) is a
  relationship
• Example
• (Hayes, A-102) ? depositor

7
Relationship Set borrower
8
Relationship Sets (Cont.)

• An attribute can also be property of a
  relationship set.
• For instance, the depositor relationship set
  between entity sets customer and account may have
  the attribute access-date

9
Degree of a Relationship Set

• Refers to number of entity sets that participate
  in a relationship set.
• Relationship sets that involve two entity sets
  are binary (or degree two). Generally, most
  relationship sets in an E-R schema are binary.
• Relationship sets may involve more than two
  entity sets.
• E.g. Suppose employees of a bank may have jobs
  (responsibilities) at multiple branches, with
  different jobs at different branches. Then there
  is a ternary relationship set between entity sets
  employee, job and branch
• Relationships between more than two entity sets
  are relatively rare. Most relationships are
  binary.

10
Mapping Cardinalities

• Express the number of entities to which another
  entity can be associated via a relationship set.
• Most useful for binary relationship sets.
• For a binary relationship set, the mapping
  cardinality must be one of the following types
• One-to-one
• One-to-many
• Many-to-one
• Many-to-many

11
One-to-one
One-to-many
Note Some elements in A and B may not be mapped
to any elements in the other set
12
Many-to-one
Many-to-many
Note Some elements in A and B may not be mapped
to any elements in the other set
13
Cardinalities affect ER Design

• Can make access-date an attribute of account,
  instead of a relationship attribute, if each
  account can have only one customer
• I.e., the relationship from account to customer
  is many-to-one, or equivalently, customer to
  account is one-to-many

14
E-R Diagrams

• Rectangles represent entity sets.
• Diamonds represent relationship sets.
• Lines link attributes to entity sets and entity
  sets to relationship sets.
• Ellipses represent attributes
• Double ellipses represent multivalued attributes.
• Dashed ellipses denote derived attributes.
• Underline indicates primary key attributes

15
E-R Diagram With Composite, Multivalued, and
Derived Attributes
primary key
composite
multi-valued
derived
16
Relationship Sets with Attributes
17
Roles

• Roles are indicated in E-R diagrams by labeling
  the lines that connect diamonds to rectangles.
• Role labels are optional, and are used to clarify
  semantics of the relationship

18
Cardinality Constraints

• We express cardinality constraints by drawing
  either a directed line (?), signifying one, or
  an undirected line (), signifying many,
  between the relationship set and the entity set.
• E.g. One-to-one relationship
• A customer is associated with at most one loan
  via the relationship borrower
• A loan is associated with at most one customer
  via borrower

19
One-To-Many Relationship

• In the one-to-many relationship
• a loan is associated with at most one customer
  via borrower,
• a customer is associated with several (gt 0)
  loans via borrower

20
Many-To-Many Relationship

• A customer is associated with several (possibly
  0) loans via borrower
• A loan is associated with several (possibly 0)
  customers via borrower

21
Participation

• Total participation (indicated by double line)
  every entity in the entity set participates in at
  least one relationship in the relationship set
• E.g. participation of loan in borrower is total
• every loan must have a customer associated to it
  via borrower
• Partial participation some entities may not
  participate in any relationship in the set
• E.g. participation of customer in borrower is
  partial

22
Alternative Notation for Cardinality Limits

• Cardinality limits can also express participation
  constraints

23
Specialization Generalization

• Similar concept to inheritance
• Designated by the ISA relationship
• If A ISA B then every A entity is also considered
  to be a B entity
• Depicted by a triangle labeled with ISA
• Can be done top-down (specialization) or
  bottom-up (generalization)
• Specialization and Generalization are simple
  inverses the the design process

24
(No Transcript)
25
ER Design

• ER design is subjective. There are often many
  ways to model a given scenario!
• Analyzing alternatives can be tricky, especially
  for a large enterprise.
• Ensuring good database design
• resulting relational schema should be analyzed
  and refined further (normalization).

26
A Sample ER-Diagram
27
Heartland Properties

• Heartland Properties, Inc. (HPI) is a property
  management company that manages rental properties
  on behalf of owners.   The company offers a
  comprehensive collection of services to these
  owners, including advertising the property,
  interviewing potential renters, negotiating
  leases, inspecting and maintaining the property,
  and collecting rent from the renters.  The
  following is a brief description of the kinds of
  data Heartland Properties is concerned with.
• Offices
• HPI has offices in several cities throughout the
  Heartland.   Each office is identified by an
  Office Number.   Each office has an address
  (Street, City, State, Zipcode), and telephone and
  fax numbers.  Each office also has a year in
  which it was opened.

28
Heartland Properties

• Employees
• Employees of HPI are assigned to work at branch
  offices.  Each employee has an employee
  identifier, first and last names, a
  social-security number, as well as a home address
  and telephone number.  For each employee, HPI
  keeps track of when that employee was hired, the
  employee's gender, and job title.  For each
  employee, HPI keeps track of a single next-of-kin
  who may be notified in the event of an emergency.
   
• Employees are divided into three categories
   Managers, Associates, and Administration
  Assistants.  Managers are salaries employees and
  a given a monthly allowance for maintaining a
  company car.   Associates and administrative
  assistances are both hourly employees, and HPI
  tracks their hourly rate.  
• Each office has one senior manager, and several
  regular managers.  Each manager manages up to 10
  associates and administrative assistants.

29
Heartland Properties

• Owners
• Owners own the properties HPI manages.  Owners
  may be either business or private owners.  In
  either case, an owner is given a unique owner
  number, and HPI records the owner's name,
  address, and telephone number.  For owners that
  are businesses, HPI also must record the name of
  a contact person.
• Properties
• Each property has a unique property number, as
  well as an address.  Moreover, the property is
  managed by a particularly HPI office.  Each
  property has a type (house, apartment), a
  particularly number of rooms, and a particular
  number of baths.
• Renters
• In order for the business to work effectively,
  HPI must be able to identify quickly the
  properties that are of potential interest to a
  client.  Consequently, when a client approaches
  HPI, the company records the client's maximum
  monthly rent, the type of property (apartment,
  house), and desired number of rooms. The basic
  client data include a unique client number, the
  client's name, address, and phone number.   HPI
  also records the employee who initially
  interviews the renter.

30
Heartland Properties

• The Rental Process
• When a client, a potential renter, approaches
  HPI, a process begins which continues until HPI's
  association with that renter has ended.
   Following the preliminary interview, the
  following stages may be followed
• Viewing properties. A client may wish to see one
  or more properties before deciding to lease.  HPI
  must keep track of which property was viewed, the
  date it was viewed, the HPI employee who
  accompanied the client, and any comments the
  client made about the property.
• Drawing up of a lease.   If a client agrees to
  lease a particularly property, HPI draws up a
  lease.  The lease has a unique lease identifier,
  and includes details of the lease, including the
  property to be rented, the monthly rental, the
  rental deposit, the start and end dates of the
  lease, and the manager who signs off on the
  lease.
• Collection of rent.  When a client rents a
  property, he or she is responsible for monthly
  payments.  Each payment is recorded by noting the
  day on which payment was received, the amount of
  the payment, the property for which payment was
  made, the renter making the payment, and the
  number on the check used to make the payment.
• Property Management
• One of the services HPI provides to owners is the
  regular inspection and maintenance of the
  properties.  Each property is inspected no less
  frequently than every six months.  Whenever a
  lease ends, the property is also inspected.  For
  each inspection, HPI keeps track of the date,
  property, and HPI employee making the inspection.
   The employee's comments following the inspection
  are also recorded.

31
Heartland Properties

• Management Reports
• Rental listings
• Each day, each HPI office prints out a list of
  all of the properties currently available for
  rent.  An example of such a report is found
  below.
• Property Inspection Report
• Periodically, management wishes to see a record
  of the inspections performed on a particular
  property.  One such listing appears as follows

32
Heartland Properties ER-Diagram
33
ER Design to DB Schema

• Entities become tables with the given attributes
• Relationships become foreign keys
• Many to One and One to Many
• Foreign key to the One goes in the Many table
• Many to Many
• Create a relationship table with foreign keys to
  both sides of the relationship
• Also add in any attributes of the relationship

34
ER Design to DB Schema

• 3 tables
• customer - id, name, street, city
• account - number, balance
• depositor
• primarykey, customer_id, account_id, access-date

35
ER Design to DB Schema

• Composite Attributes
• typically flattened out to use the specific info
• Multi-valued Attributes
• build a separate schema
• new schema contains the information and the
  primary key for the entry they belong to.

36
Heartland Properties Schema
37
Program Data Modeling

• ER Design works great when you are designing the
  database right from the start.
• However, sometimes it may be easier to map Object
  - Oriented program data
• Map Objects and Object hierarchy to DB
• Many ways to do it. What are the choices?

38
Object/RDBMS

• How do we map the following Class Model to an
  RDBMS

One to Many Relationship between Owner and
Account
InterestBearingAccount and CheckingAccount
inherit from Account
39
Horizontal Partitioning

• Each concrete class is mapped to a table

40
Vertical Partitioning

• Each class is mapped to a table

41
Unification

• Each sub-class is mapped to the same table

42
RDBMS Mapping

• Horizontal Partitioning
• entire object within one table
• only one table required to activate object
• no unnecessary fields in the table
• must search over multiple tables for common
  properties
• Vertical Partitioning
• object spread across different tables
• must join several tables to activate object

• Vertical Partitioning (cont.)
• no unnecessary fields in each table
• only need to search over parent tables for common
  properties
• Unification
• entire object within one table
• only one table required to activate object
• unnecessary fields in the table
• all sub-types will be located in a search of the
  common table