Technische Universitt Berlin Department of Geoinformation Science GeoDatabases
Technische Universität Berlin Department of Geoinformation Science Geo-Databases: lecture 7 Database design Prof. Dr. Thomas H. Kolbe Institute for Geodesy and Geoinformation Science Technische Universität Berlin Credits: This material is mostly an english translation of the course module no. 8 (‘Geo-Datenbanksysteme‘) of the open e-content platform www. geoinformation. net. 12. 10. 2006
Database design Department of Geoinformation Science 2 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Motivation (1) Department of Geoinformation Science Desired: A suitable relational schema in order to describe a given application (mini-world) Role of the database design: Systematic development of database schemata, with regard to the specific information needs of the user. Starting with SQL right away has some disadvantages: Poor in structuring concepts Application semantics therefore hidden in tables, foreign keys, etc Effects of changes in the real world on the schema hardly traceable Instead, we may perform the design process on a conceptual level: 3 Usage of an abstract modelling language (e. g. Entity-Relationshipdiagrams, UML-models) Projectable onto the language of the DBMS, e. g. an SQL schema T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Database design– a miniature example Department of Geoinformation Science Task: Examination database Problem: Projection of an object-model into a relational schema? 4 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Logical design / Implementation Department of Geoinformation Science A conceptual model can be converted into an implementable database model. 5 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Translation into the relational model Department of Geoinformation Science There are some “cooking recipes“, that can help with the translation of a UML-diagram into a relational schema. Generally, the translation process can be carried out in several steps: 1. Each class, that does not take part in the inheritance hierarchy is projected onto a table 2. Each association, besides inheritance, is projected onto a table 3. Refinement: Some tables can be combined 4. Separate treatment of inheritance hierarchies 6 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Translation of classes Department of Geoinformation Science Each class is projected onto a table. Name of the table: name of the class Attributes: Attributes of the class in the UML-diagram Note: if necessary, UML-data types have to be adapted to the SQLdata types! Example: 7 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Translation of associations Department of Geoinformation Science Each association is projected onto a table. Name of the table: name of the association or, if existent, of the related association class; otherwise meaningful, new name Attributes: (if necessary attributes might have to be re-named!) Key attributes of the involved classes Attributes of the related association class (if existent) Example: 1: n-relationship between class room and class lecture 8 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Refinement: Combination of tables Department of Geoinformation Science Some of the tables, that emanate from associations, can be eliminated. Let A and B be the classes / tables of the association. The options for the combination are determined by the type of association. 1: 1 The key of B can be added to table A or vice versa. The association table can be eliminated 1: n The key of A can be added to the table B (“n-side“). The association table can be eliminated n: m The association table should persist. key of room for lecture 9 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Translation of inheritance hierarchies Department of Geoinformation Science When it comes to inheritance hierarchies only the concerned classes are translated but not the inheritance relationships. There are several reasonable alternatives: One table per class in the hierarchy One table for each partial tree of the hierarchy that emanates from the root A single table for the entire hierarchy Criteria for the selection of an alternative: Required space (tuple size and number of emerged tables) Support of important manipulation operations and queries for the given application 10 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
inheritance hierarchies, alternative I Department of Geoinformation Science Alternative I: One table per class in the hierarchy. For each table: Name of the table: name of the class Attributes: Attributes of the corresponding class in the UML-diagram Key attributes of the root in the hierarchy, that remain keys 11 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
inheritance hierarchies, alternative II (1) Department of Geoinformation Science Alternative II: A table for each partial tree of the hierarchy that emanates from the root. For each partial tree: Name of the table: meaningful, clear name Attributes: al attributes of the concerned classes, renamed if necessary Tables: §Persons, §Persons. Emp. Prof, §Persons. Emp. Ass, §Persons. Emp. Prof. Ass, §Persons. Stud, §Persons. Emp. Ass. Stud, §Persons. Emp. Prof. Ass. Stud Persons. Emp. Ass. Stud Street wage 12 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
inheritance hierarchies, alternative II (2) Department of Geoinformation Science Alternative II illustrates the subject of disjunctive / non-disjunctive inheritance. An inheritance hierarchy is called non-disjunctive, if an object of an upper class K may have the type of more than one lower class of K. In our Example: Person is employee and (phd-) student at the same time Non-disjuntive inheritance can be expressed in all of the presented translation alternatives Alternative II shows an analogy to object-oriented programming languages in terms of disjuntive inheritance: All attributes of an object are stored in a “coherent“ memory area. If an inheritance hierarchy was translated according to alternative II, it requires the following amendments: 13 Eliminate all tables that represent nonsensical combination in terms of the given application! In our Example: Persons. Emp. Prof. Stud T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
inheritance hierarchies, alternative III Department of Geoinformation Science Alternative III: A single table for the entire hierarchy. Name of the table: for example class name of the root Attributes: all class attributes that appear in the hierarchy, renamed if necessary. Not corresponding attributes are set to NULL! 14 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
inheritance hierarchies, comparison (1) Department of Geoinformation Science Comparison of the alternatives on the basis of the previously mentioned size parameters and the example query: “show name and rank of all professors!“ tuple size AI (one table per class) A II (one table per partial tree) A III (a single table) 15 compact besides foreign keys compact large # of tables Medium High minimal Example query SELECT Name, Rank FROM Professor, Person WHERE Professor. Per. ID =Person. Pers. ID SELECT Name, Rank FROM Persons. Emp. Prof UNION SELECT Name, Rank FROM Persons. Emp. Prof. Ass. Stud UNION SELECT Name, Rank FROM Persons. Emp. Prof. Stud SELECT Name, Rank FROM Person WHERE Person. Rank IS NOT NULL T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
inheritance hierarchies, comparison (2) Department of Geoinformation Science Comparison of the alternatives on the basis of the dominating query types in the given application. Dominating attribute queries of the application Attributes of an upper class Attributes of a derivative class Attributes on different levels of the inheritance hierarchy AI + + - A II - + +- A III + + / - : number of tables to be included low / high 16 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
Refinement of the design Department of Geoinformation Science The implementable database schema can be improved: 1. In terms of redundancy: Enforcement of the so-called normalisation of a relational database schema (later!) 2. In terms of performance: controlled redundancy by targeted denormalisation by specifyfing further physical characteristics of the database, such as index structures for efficient access (see also “B-tree“ in LE 8 and “R-tree“ in LE 11) 17 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
References Department of Geoinformation Science Overview: Hector Garcia-Molina, Jeffrey D. Ullman, Database Systems: The Complete Book, Prentice Hall, 2002 Bernd Oestereich, Objektorientierte Softwareentwicklung: Analyse und Design mit der Unified Modeling Language, Oldenbourg Verlag, München, 1998 Gottfried Vossen, Datenbankmodelle; Datenbanksprachen und Datenbankmanagement-Systeme, Oldenbourg Verlag, München, 1999 Original works: P. P. -S. Chen, The entity-relationship model: Toward a unified view of data, in: ACM Transactions on Database Systems, Vol. 1, Seite 9 -36, 1976 J. Rumbaugh, I. Jacobson, G. Booch, The Unified Modeling Language for Object-Oriented Development - Documentation Set 0. 9 Addendum, Rational Software Corporation, Santa Clara 1996 18 T. H. Kolbe – Geo-Databases: lecture 3 13/12/2021
- Slides: 18