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 k+1 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