Database Design Methodology

1
Database Design Methodology

• Lecture 1, Term 2, 2004

2
Outline

• front end applications and back end databases
• methodology for database design overview
• requirements specification
• ER/EER modelling
• elements of EER modelling
• transformation of an ER/EER model into a
  relational model
• motivation for normalisation

3
Front-End Applications and Back-End Databases
application 1
application k
database DBMS
application 2
application k1
application 3
application n
application 4
4
Back-End Databases

• one database per information system
• central to the information system
• stores data in a consistent way
• mechanisms for data retrieval and update
• not responsible for complex calculations
• nowadays DBMSs provide means for server based
  computations
• trade-off between calculations performed on
  server and calculations performed in the front
  end
• not responsible for data formatting

5
Front-End Applications

• responsible for complex calculations
• responsible for data formatting

6
when designing a database

• must decide
• what is to be represented in the database
• what is to be represented in the front-end
• because
• many aspects may be represent-able at both ends
• give an example in class

7
Database Design Methodology - Overview

• what is a methodology?
• top down methodology
• requirements specification
• ER/EER modelling (conceptual design)
• refinements of higher level ER/EER models
• validation of ER/EER model
• construction of relational model (logical design)
• validation of relational model
• normalisation
• physical design
• monitoring and tuning

8
Requirements Specification

• requirements
• data
• transaction
• even by specifying the user interfaces
• specification
• informal
• English combined with diagrammatic
  representations
• elicitation
• for each view
• do you know the term database view?

9
Fact Finding Techniques

• techniques
• interviewing
• questionnaires
• examining documentation
• observing the enterprise in operation
• research
• you may need to uncover things for the user
• tell them what may be possible
• requirements identification is a two way process
• you will need to understand the operation of the
  enterprise at least to some considerable extent

10
Requirements Specification

• exercise in class

11
Requirements Specification

• be as comprehensive as possible
• define the scope clearly
• describe as clearly as possible what is to be
  part of the database
• specify clearly what is not to be included in the
  database
• (at this point it may be possible to decide, for
  certain aspects, whether they are to be part of
  the back-end or the front-end)
• infer requirements for future growth, if
  applicable
• if this step is not carried out correctly, the
  final database system is bound to fail

12
ER/EER modelling

• identify entities
• identify attributes
• the set of attributes defines an entity
• identify candidate keys
• identify domains for attributes
• identify relationships
• identify multiplicity
• identify attributes (if applicable)
• identify domains for attributes
• (possible) combine view models into unitary model
• validate model

discuss the order in which these steps are to be
performed
13
ER/EER modelling

• different people may produce different models
• they may all be correct
• could they express the same requirements?
• some may be better than others, though
• how do we measure good?
• it is a matter of experience
• however, if the methodology is correctly
  followed, the design cannot be too bad

14
Identify Entities

• very informal definition
• an entity (type) is something about which we want
  to store information in the database, and which
  has more than one instance
• in text (requirements) entities are nouns
• possible problems
• you may identify different entities that
  represent the same thing (information object)
• you may attempt to represent two information
  objects with the same entity

15
Identify Entities

• give example if needed

16
Identify Relationships

• very informal definition
• a relationship (type) is a link between entities
  that need to be recorded in the database
• identify multiplicity
• consider future growth
• potential problems
• unlinked entities
• un-represented relationships
• may be due to fan and chasm traps

17
Identify Attributes

• single/composite
• single/multi-valued
• derived
• identify candidate keys
• define domains
• potential problems
• entities with no attributes
• entities with the exactly the same set of
  attributes
• impossibility of associating an attribute with
  any of the existing entities

18
EER Modelling Concepts

• generalisation (sub-class / super-class)
• is-a relationship
• aggregation
• part-of relationship
• composition
• special type of aggregation
• the existence of the parts (instances) is not
  motivated (in the database) if the whole
  (instance) disappears (from the database)
• a part (instance) can only be part-of one single
  whole (instance) at any one time
• I am not so sure about this!
• notation and examples on white board

19
EER Modelling Concepts

• generalisation/specialisation
• participation mandatory or optional
• disjoint constraint disjoint or non-disjoint
• aggregations
• can be represented as a has relationship
• some people name aggregation relationships
• in this case, the distinction between an
  aggregation and a simple relationship is
  blurred
• composition vs aggregation
• the distinction may not be clear
• example of an aggregation
• disk ( format, location, ) and recording (
  actor, length, topic )

20
Validation of EER Model

• check for redundancy
• check for completion
• validate against requirements
• review model with user

21
ER/EER Model into Relational Model

• problems in class
• multi-valued, composite and derived attribute
• many-to-many relationship
• relationship with attributes
• recursive relationship
• ternary relationship
• difference between 1 and 0
• difference between 1(0..) and 1(1..)
• generalisation
• mandatory vs optional
• OR vs AND

22
Relational Model

• how do we know that the resulting relational
  model is good/correct?
• there is a formal way of checking whether a
  relation is in a good form or not
• this is through normal forms
• we shall study normal forms in the following two
  weeks

23
Summary

• Requirements Specification essential
• data and transactions
• be as thorough as possible
• ER/EER modelling
• it is possible to devise two or more correct
  models based on the same requirements
  specification
• experience leads to better design (obviously!)
• EER concepts
• translation of ER/EER structures into
  specifications within the relational model