1 / 18

Database Design Principles for Geo-Databases

Learn the systematic development of database schemata tailored to user information needs, using conceptual modeling languages like Entity-Relationship diagrams. Explore relational schema translation and inheritance hierarchy representations in database design.

shrum
Download Presentation

Database Design Principles for Geo-Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Geo-Databases: lecture 7Database 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.

  2. Database design

  3. Motivation (1) • 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: • Usage of an abstract modelling language (e.g. Entity-Relationship-diagrams, UML-models) • Projectable onto the language of the DBMS, e.g. an SQL schema

  4. Database design– a miniature example • Task: Examination database Problem: Projection of an object-model into a relational schema?

  5. Logical design / Implementation • A conceptual model can be converted into an implementable database model.

  6. Translation into the relational model 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: • Each class, that does not take part in the inheritance hierarchy is projected onto a table • Each association, besides inheritance, is projected onto a table • Refinement: Some tables can be combined • Separate treatment of inheritance hierarchies

  7. Translation of classes • 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 SQL-data types! • Example:

  8. Translation of associations • 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

  9. key of room for lecture Refinement: Combination of tables • 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.

  10. Translation of inheritance hierarchies • 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

  11. inheritance hierarchies, alternative I • 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

  12. PersonsEmpAssStud Street wage inheritance hierarchies, alternative II (1) • 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, • PersonsEmp, • PersonsEmpProf, • PersonsEmpAss, • PersonsEmpProfAss, • PersonsStud, • PersonsEmpStud, • PersonsEmpAssStud, • PersonsEmpProfStud, • PersonsEmpProfAssStud

  13. inheritance hierarchies, alternative II (2) • 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: • Eliminate all tables that represent nonsensical combination in terms of the given application!In our Example: PersonsEmpProfStud

  14. inheritance hierarchies, alternative III • 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!

  15. inheritance hierarchies, comparison (1) • Comparison of the alternatives on the basis of the previously mentioned size parameters and the example query: “show name and rank of all professors!“

  16. inheritance hierarchies, comparison (2) • Comparison of the alternatives on the basis of the dominating query types in the given application. Dominating attribute queries of the application + / - : number of tables to be included low / high

  17. Refinement of the design • The implementable database schema can be improved: • In terms of redundancy: • Enforcement of the so-called normalisation of a relational database schema (later!) • 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)

  18. References 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

More Related