Modeling Your Data

1
Modeling Your Data

• Chapter 2

2
Part II

• Discussion of the Model
• Good Design/ Bad Design?

3
Design The Obvious !

• Use meaningful and descriptive names (its for
  the human after all)
• Keep as simple as possible, and relevant to the
  application at hand
• Avoid redundant constructs
• Express all constraints, if possible, as then the
  DBMS will help you to enforce them

4
Conceptual Design Using ER Model

• Design choices
• Should a concept be modeled as an entity or an
  attribute?
• Should a concept be modeled as an entity or a
  relationship?
• Identifying relationships Binary or ternary?
  Aggregation?
• Constraints in the ER Model
• A lot of data semantics can (and should) be
  captured.
• But some constraints cannot be captured in ER
  diagrams.

5
Entity vs. Attribute

• Should address be an attribute of Employees or an
  entity (connected to Employees by a relationship)?

6
Entity vs. Attribute

• Should address be an attribute of Employees or an
  entity (connected to Employees by a
  relationship)?
• Depends upon the use we want to make of address
  information and its semantics
• If we have several addresses per employee,
  address must be an entity
  (since attributes cannot be
  set-valued).
• If the structure (city, street, etc.) is
  important, e.g., we want to retrieve employees
  in a given city, address must be modeled as an
  entity (since attribute
  values are atomic).

7
Entity vs. Attribute

• Reminder
• Do not introduce un-necessary entities (and
  complexity) if not needed for your application !

8
Entity vs. Attribute
to
from
budget
Departments
Works_In4
9
Entity vs. Attribute

• Does Works_In4 allow an employee to work in a
  department for two or more periods???

to
from
budget
Departments
Works_In4
10
Entity vs. Attribute (Contd.)

• Works_In4 does not allow an employee to work in
  a department for two or more periods
• à no multi-valued attributes in ER !

to
from
budget
Departments
Works_In4
11
Entity vs. Attribute (Contd.)

• Works_In4 does not allow an employee to work in a
  department for two or more periods.

to
from
budget
Departments
Works_In4

• What do ?

• Similar to the problem of wanting to record
  several addresses for an employee We want to
  record several values of the descriptive
  attributes for each instance of this
  relationship.
• Accomplished by introducing new entity set,
  Duration.

12
Entity vs. Attribute
to
from
budget
Departments
Works_In4
name
ssn
lot
Works_In4
Departments
Employees
13
Entity vs. Relationship?
since
dbudget
name
dname
ssn
did
lot
budget
Employees
Departments
Manages2
14
Entity vs. Relationship
since
dbudget
name
dname
ssn
did
lot
budget
Departments
Employees
Manages2

• What if a manager gets a separate discretionary
  budget for each dept ?
• What if a manager gets a discretionary budget
  that covers all managed depts?

15
Entity vs. Relationship
since
dbudget
name
dname
ssn
did
lot
budget
Employees
Departments
Manages2

• ER diagram OK if a manager gets a separate
  discretionary budget for each dept.
• What if a manager gets a discretionary budget
  that covers all managed depts?
• Redundancy dbudget stored for each dept managed
  by manager.
• Misleading Suggests dbudget associated with
  department-mgr combination.

16
Entity vs. Relationship
since
dbudget
name
dname

• Discretion-ary budget of manager that covers
  all managed depts?

ssn
did
lot
budget
Employees
Departments
Manages2
name
ssn
lot
dname
since
did
budget
Employees
Departments
Manages2
ISA
This fixes the problem!
Managers
dbudget
17
Binary vs. Ternary Relationships
pname
age
Dependents
Covers
18
Binary vs. Ternary Relationships
pname
age
Dependents
Covers

• What if each policy is owned by just 1
  employee?
• What if each dependent should be tied to only 1
  covering policy?

19
Binary vs. Ternary Relationships
pname
age
Dependents
Covers

• What do additional constraints in 2nd diagram?

Bad design!
pname
age
Dependents
Purchaser
Better design?
20
Binary vs. Ternary Relationships
pname
age
Dependents
Covers
Bad design
pname
age
Dependents
Purchaser
Even Better Design
21
Binary vs. Ternary Relationships

• If each policy is owned by just 1 employee, and
  each dependent is tied to the covering policy,
  first diagram is inaccurate.
• What are the additional constraints in the 2nd
  diagram?

pname
age
Dependents
Covers
Bad design
pname
age
Dependents
Purchaser
Better design
22
Binary vs. Ternary Relationships

• Previous example illustrated when two binary
  relationships better than one ternary
  relationship.
• How about ternary relation Contracts
• entity sets Parts, Departments and Suppliers,
• and has descriptive attribute qty.

Qty
pnum
num
Parts
Suppliers
contract
Department
D-id
23
Binary vs. Ternary Relationships

• Ternary relation Contracts relates entity sets
  Parts, Departments and Suppliers, and has
  attribute qty.
• What about following binary relationships
• S can-supply P,
• D needs P, and
• D deals-with S
• No combination of binary relationships is an
  adequate substitute
• Together 3 binary relationships dont imply that
  D has agreed to buy P from S.
• Also, how could we record qty?

24
Summary of ER

• There are often many ways to model a given
  scenario! Not one correct ER design
• Analyzing alternatives can be tricky, especially
  for a large enterprise.
• Common choices include
• Entity vs. attribute, entity vs. relationship,
  binary or n-ary relationship, whether or not to
  use ISA hierarchies, and whether or not to use
  aggregation.
• Ensuring good database design - Resulting
  relational schema should be analyzed and refined
  further. FD information and
  normalization techniques useful.