Database Design Methodology by akm33296

VIEWS: 14 PAGES: 23

									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 k


         application 2                                 database            application k+1
                                                        DBMS

                   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                                      discuss the order
                                                                                 in which these steps
                  • identify domains for attributes
                                                                                 are to be performed
            identify relationships
                  • 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

								
To top