INFO 2005 Personal Database Tools Primary Keys Week 13

1
INFO 2005Personal Database Tools- Primary Keys-
Week 13

• Data Modelling Week 7
• Simsion -Chapter 8

2
Learning Objectives

• To understand the nature of primary keys
• to be able to design suitable primary keys and
  surrogate keys

3
Basic Requirements Trade-offs

• 1. Applicable
• 2. Unique
• 3. Minimal
• 4. Stable

4
Applicability

• Meaningful in the sense there are no exceptions
• Special cases when real world entities dont have
  keys
• special mechanism to allocate keys in these cases
• exclude from entity definition altogether
• find another key
• Unavailable data - all components need to be
  available at the time of data entry
• Broadening of scope- scope of entity may change -
  be precise as possible with entity definition.

5
Uniqueness

• essential element of design
• checking uniqueness
• nature of the real world -rare
• designer controls key allocation - surrogates or
  tiebreaker -attribute that discerns clashes
• someone elses scheme e.g. car registrations
  -useful for outside world but be sure that owner
  will not change it
• be sure to eliminate the possibility of
  duplicates

6
Minimality

• No more attributes than necessary to achieve
  uniqueness
• should be picked up in normalisation
• not disputable

7
Stability

• real world entity keeps its key value for life
• why
• used elsewhere as foreign keys -changes wreak
  havoc
• need to match historical data with current data -
  stability over time
• key repesents real world entities
• stability is tied to identity -insurance policy
  example

8
Composite Keys Weak Entities

• Problems
• Weak entities
• Programming
• Performance issues

9
Composite Keys - Problems

• collection of attributes
• collection of attributes into 1 column - bad
  design
• often fail to meet stability - one of the
  components runs our of expansion space and so
  requires dummy values - e.g. agents with dummy
  offices

10
Composite Keys - Weak Entities

• entity that relies on another for identification
  - e.g.
• Enrolment (Student ID, Unit ID,)

11
Composite Keys - Programming

• Ususally simplify programming -fig 8.3 - if
  Employees can change Department then key of
  Employee is unstable

12
Composite Keys - Performance Issues

• Can reduce the number of tables needed for some
  transactions
• Can reduce the number of access mechanisms
• As attributes in key increases so does table size
  and index size - fig 8.4

13
Surrogate Keys

• Definition
• When to use
• Visibility
• subtypes - fig 8.5
• Singularity

14
Surrogate Keys - Definition

• a unique identifier independent of any attribute
  values

15
Surrogate Keys - When to use

• necessary to look-up natural identifier
• identifying next available number
• Use natural identifers when they are uniquely
  stable
• weak keys when the entity is nontransferable
• surrogates if there is no suitable natural
  identifer

16
Surrogate Keys - Visibility

• In theory no visibility because
• users will attribute meaning that is not stable
  requires discouragement and provision of
  necessary info
• may wish to change keys should not arise and is
  painful

17
Surrogate Keys - Subtypes

• Separate or same key for subtypes - fig 8.5
• use same key so vlaues are unique at supertype
  level

18
Surrogate Keys - Singularity

• Real world entities need to have one key
• violated by aliases
• multiple relationships student/teacher -
  employee/customer
• Quality control at point of data capture

19
Null Partially Null Keys

• sensible or not? Problem of interpreting NULL in
  FK
• Not possible to eliminate
• Problem arises with subtypes propagating a
  supertype key -fig 8.7
• Library loans -2 views - fig 8.8 processing
  efficiency for some queries
• loan with stages -borrow and return
• borrowing and return are different instances

20
Mulitple Candidate Keys

• Choosing a PK - FK needs to use the PK not just
  any candidate key
• does each candidate key repesent the same thing
  all the time
• Normalization issues -can be in 3NF but not BCNF
• confusion can arise in satgs of normaisations
• CUSTOMER(Cust, TaxFile, Address)
• TAxFile-gtCust but we would not decompose

21
(No Transcript)
22
INFO 2005Personal Database Tools- Attributes

• Simsion -Chapter 9

23
Learning Objectives
24
Attribute Definition
25
Disaggregation

• Simple aggregation
• Complex codes
• Meaningful ranges
• inappropriate generalisations

26
Generalisation

• Options Tradeoffs
• As a result of entity generalisation
• generalisation within entities

27
Domains, Constraints, Look-up Tables

• coding descriptive attributes
• simple look-up tables
• generalisation
• interdomain constraints

28
Attribute Names

• stanadradising names
• guidelines