Database design with a relational model

1
Database design with a relational model

• Design methodology

2
Database Design Methodology

• Definition A structured approach that uses
  procedures, techniques, tools and documentation
  to support and facilitate the process of design
  for the data used by an enterprise
• Conceptual database design - independent of all
  physical considerations
• Logical database design - based on a specific
  data model
• Physical database design includes base
  relations, file organizations, access paths,
  integrity constraints and security measures

3
Database Design Methodology
C O N C E P T U A L

• Identify entity types

Identify relationship types
Identify and associate attributes
Determine domains
Determine keys
Consider enhanced modeling
Check for redundancy
Validate with transactions
Review with user
L O G I C A L
Derive relations
Normalize to validate
Run transactions to validate
Check constraints
Review with user
Merge to global model
P H Y S I C A L
Implement base relations
Implement derived data
Implement constraints
Analyze transactions
Implement files
Implement access paths
Implement user views
Implement security
Monitor and tune
4
Database Design Methodology

• Identity the entity types
• Review tabular and narrative documentation
• Get a copy of every report, spreadsheet,
  repetitive memo
• Get a copy of every form in three formats
• Blank
• Filled out in full
• Filled out as used in practice (from several
  users)
• In narrative, looking at the grammar helps
• An heir search attempts to identify all
  potential heirs and devisees of the decedent.
  Heirs are spouses, siblings and lineal
  descendants (or ascendants) that are enrolled or
  eligible to become enrolled members of a
  federally recognized Tribe. Devisees are
  individuals that are named in a will or are
  otherwise eligible to benefit from the estate.

5
Database Design Methodology

• Looking at the grammar helps
• An heir search attempts to identify all
  potential heirs and devisees of the decedent.
  Heirs are spouses, siblings and lineal
  descendants (or ascendants) that are enrolled or
  eligible to become enrolled members of a
  federally recognized Tribe. Devisees are
  individuals that are named in a will or are
  otherwise eligible to benefit from the estate.
• Identify nouns (entities?), verbs
  (relationships?), adjectives and adverbs
  (attributes?)

6
Database Design Methodology

• Logical to Physical
• Identify what the dbms allows datatypes, keys,
  constraints, triggers, stored procedures,
  computed columns, functions, nested queries
• These affect your data-flow design
• Analyze transactions
• Check ACID properties Atomic, Consistent,
  Isolated and Durable
• Check frequency
• Check criticality
• Check relations involved

7
Database Design Methodology

• Create transaction-relation matrix

• Project transaction frequency, query byte size
• Identify access path use

8
Database Design Methodology

• Indexes
• The most common
• Heap
• Hash
• Indexed Sequential Access Method (ISAM)
• B tree
• Clusters
• Order data in a relation
• Primary index (the key)
• Clustering index (non-key attributes)
• Secondary index (non-key attributes)
• Primary and clustering are mutually exclusive

9
Database Design Methodology

• Index selection guidelines (rules of thumb)
• Do index
• Primary key (hard to find a vendor that doesnt
  do this)
• Frequently used foreign key (often automatic by
  the vendor)
• Unique constraint (particularly if used as a key)
• SELECT, JOIN, WHERE, GROUP BY, ORDER BY (i.e,
  anything involving sorting or exact match)
  attributes in queries
• Combined GROUP BY and Aggregate Function (an
  index-only plan)
• Dont index
• Small relations (if they fit in a page or so of
  memory, no need for an extra structure)
• Frequent updates, inserts or deletes (i.e.,
  requiring multiple rights to disk for both the
  data and the access path)
• Combined WHEREOR attributes unless all are
  indexed
• Columns with few values (if more than 10 of
  records have the same value, benefits reduce
  rapidly)

10
Database Design Methodology

• Project storage requirements
• Blocks required for a record
• Number of records
• Access path overhead