Logical Database Design Steps

1
Logical Database Design Steps

• Material drawn from
• Hoffer, J.A., Prescott, M.B., and McFadden, F.R.,
• Modern Database Management, 6th edition.
• Upper Saddle River, NJ Pearson Education, 2002.

2
Transforming ER Diagrams into Relations

• Three types of entities
• Regular independent existence, generally
  represent real-world objects
• Weak cannot exist except with an identifying
  relationship
• Associative formed from MN relationships

3
Step 1 Map Regular Entities

• Each regular entity type is transformed into a
  relation
• simple attribute ? attribute
• identifier ? primary key
• composite attribute ? simple component attributes
• multivalued attribute ? two relations
• (1) attributes of entity except multivalued
  attribute
• (2) primary key multivalued attribute

4
Regular Entity, Simple Attributes
5
Regular Entity, Composite Attribute
6
Regular Entity, Multivalued Attribute
7
Step 2 Map Weak Entities

• simple attribute ? attribute
• composite attribute ? simple component attributes
• identifier of owner ? foreign key
• identifier of weak entity identifier of owner ?
  primary key

8
Weak Entity Diagram
9
Weak Entity Relation
NOTE the domain constraint for the foreign key
should NOT allow null value if DEPENDENT is a
weak entity
Foreign key
10
Step 3 Map Binary Relationships

• 1M
• primary key attribute(s) of one side becomes
  foreign key in many side
• MN
• associative entity
• primary keys of participating entities become
  foreign key of associative entity
• 11
• primary key of mandatory side becomes foreign key
  in optional side

11
1M Diagram
12
1M Relation
13
MN Diagram
14
MN Relation
New intersection relation
15
11 Diagram
16
11 Relation
17
Step 4 Map Associative Entities

• No identifiers assigned default primary key is
  primary keys of participating entities (as well
  as foreign key)
• Identifier assigned use as a surrogate key
• when natural identifier exists
• when default does not uniquely identify instances
  of the associative entity

18
Associative Entity Diagram
19
Associative Entity Relation
20
Step 5 Map Unary Relationships

• Recursive relationships
• 1M
• recursive foreign key, same domain as primary key
• MN
• create a new relation to represent the MN
  relationship
• primary key of new relation is primary key plus
  recursive key

21
Unary 1M
22
Unary MN
23
Step 6 Map Ternary Relationships

• Create new associative relation
• default primary key is primary keys of
  participating relations
• primary keys of participating relations are also
  foreign keys

24
Ternary Diagram
25
Ternary Relation
26
Step 7 Map Supertype/Subtype Relationships

• Create a separate relation for the supertype and
  each of its subtypes
• Assign the supertype relation common attributes,
  including primary key
• Assign each subtype relation attributes unique to
  that subtype and the primary key of the supertype
• Assign attribute(s) to supertype to serve as
  subtype discriminator

27
Supertype/Subtype Diagram
28
Supertype/Subtype Relation
29
A few points to ponder when you convert your
logical design to a physical design
30
Physical Database Design Process

• INPUTS
• Normalized relations
• Volume estimates
• Attribute definitions
• Response time expectations
• Data security needs
• Backup/recovery needs
• Integrity expectations
• DBMS technology used

• DECISIONS
• Attribute data types
• Physical record descriptions (dont always match
  logical design)
• File organizations
• Indexes and database architectures
• Query optimization

LEAD TO
31
Designing Physical Fields

• Data types
• minimize storage space
• represent all possible values
• improve data integrity
• support all data manipulations
• Coding and compression
• Data integrity
• default value
• range control
• null value control
• referential integrity

32
Denormalization (part of physical record design)

• Denormalization
• denormalize 11 when optional side usually
  present
• denormalize MN if frequent joins needed
• denormalize 1M if 1 side is reference data
• horizontal partitioning if processed by group
• vertical partitioning if certain fields processed
  together
• replicate to various locations

33
Indexes

• Primary key index (unique identifier)
• Secondary key index (clustering)
• When to index
• larger tables
• primary key (mandatory)
• fields used for selecting, grouping, or sorting
  data
• variety in attribute values
• dont exceed limit
• fields with null values may not work

34
General/Specific
35
Ledgers/Transactions
36
(No Transcript)