Modeling Your Data

Chapter 2

Part II

Discussion of the Model:

Good Design/ Bad Design?

Design : The Obvious !

– Use meaningful and descriptive names (it’s 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

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.

Entity vs. Attribute

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

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).

Entity vs. Attribute

Reminder : Do not introduce un-necessary entities (and

complexity) if not needed for your application !

Entity vs. Attribute

name

Employees

ssn lot

Works_In4

from todname

budgetdid

Departments

Entity vs. Attribute

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

name

Employees

ssn lot

Works_In4

from todname

budgetdid

Departments

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 !

name

Employees

ssn lot

Works_In4

from todname

budgetdid

Departments

Entity vs. Attribute (Contd.)

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.

name

Employees

ssn lot

Works_In4

from todname

budgetdid

Departments

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

What do ?

Entity vs. Attribute

name

Employees

ssn lot

Works_In4

from todname

budgetdid

Departments

dnamebudgetdid

name

Departments

ssn lot

Employees Works_In4

Durationfrom to

Entity vs. Relationship?

Manages2

name dnamebudgetdid

Employees Departments

ssn lot

dbudgetsince

Entity vs. Relationship

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?

Manages2

name dnamebudgetdid

Employees Departments

ssn lot

dbudgetsince

Entity vs. Relationship

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.

Manages2

name dnamebudgetdid

Employees Departments

ssn lot

dbudgetsince

Entity vs. Relationship

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

Manages2

name dnamebudgetdid

Employees Departments

ssn lot

dbudgetsince

dnamebudgetdid

DepartmentsManages2

Employees

namessn lot

since

Managers dbudget

ISA

This fixes theproblem!

Binary vs. Ternary Relationships

agepname

DependentsCovers

name

Employees

ssn lot

Policies

policyid cost

Binary vs. Ternary Relationships

agepname

DependentsCovers

name

Employees

ssn lot

Policies

policyid cost

What if each policy is owned by just 1 employee?

What if each dependent should be tied to only 1 covering policy?

Binary vs. Ternary Relationships

What do additional constraints in 2nd diagram?

agepname

DependentsCovers

name

Employees

ssn lot

Policies

policyid cost

Beneficiary

agepname

Dependents

policyid cost

Policies

Purchaser

name

Employees

ssn lot

Bad design!

Better design?

Binary vs. Ternary Relationshipsagepname

DependentsCovers

name

Employees

ssn lot

Policies

policyid cost

Beneficiary

agepname

Dependents

policyid cost

Policies

Purchaser

name

Employees

ssn lot

Bad design

Even Better Design

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.

pnum

PartscontractSuppliers

num

Department

D-id

Qty

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 don’t imply that D

has agreed to buy P from S.– Also, how could we record qty?

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.