Transcript Database Design Methodology

Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
Database Design Methodology
Lecture 1, Term 2, 2004
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
Front-End Applications and Back-End Databases
application 1
application 2
application k
database
DBMS
application k+1
application 3
application n
application 4
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
Front-End Applications
 responsible for complex calculations
 responsible for data formatting
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
… 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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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”?
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
Requirements Specification
 exercise in class …
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
ER/EER modelling
 identify entities
• identify attributes
• the set of attributes defines an entity
• identify candidate keys
• identify domains for attributes
 identify relationships
discuss the order
in which these steps
are to be performed
• identify multiplicity
• identify attributes (if applicable)
• identify domains for attributes
 (possible) combine view models into unitary model
 validate model
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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 …
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
Identify Entities
 give example if needed
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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 …)
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
Validation of EER Model
 check for redundancy
 check for completion
• validate against requirements
• review model with user
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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
Term 2, 2004, Lecture 1, Database Design Methodology
Marian Ursu, Department of Computing, Goldsmiths College
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