1 / 45

Geog 495 GIS Database Design

Geog 495 GIS Database Design. Midterm review. Outlines. Database Concepts Relational Database Object-oriented Database Entity-Relationship Diagram Unified Modeling Language Normalization. 1. Database concepts. Data vs. Information Data vs. Database DBMS vs. Database system

sextonj
Download Presentation

Geog 495 GIS Database Design

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. Geog 495GIS Database Design Midterm review

  2. Outlines • Database Concepts • Relational Database • Object-oriented Database • Entity-Relationship Diagram • Unified Modeling Language • Normalization

  3. 1. Database concepts • Data vs. Information • Data vs. Database • DBMS vs. Database system • Level of abstraction • Data independence • Different database models • Architecture of database system

  4. Data vs. Information • Data: raw fact • Information: data processed to reveal meaning • Database system transforms data into information through queries

  5. Data vs. Database • Database is a collection of related data • Database stores data in an organized manner (i.e. minimal data redundancy) • Database exists, first and the foremost, to serve users’ requirements

  6. DBMS vs. DB system • DBMS allows users to access data (i.e. interface between users and database) • DB system is composed of DBMS, people, database, and procedures

  7. Different types of DB system • Number of users • Single-user • Multi-user • Workgroup • Enterprise • Database site location • Centralized • Distributed • Database use • Transactional (production) • Data warehouse

  8. Level of abstraction • Conceptual: independent of s/w and h/w • How humans see the world • Logical: s/w dependent • How programs see the world • Physical: h/w dependent • How machines see the world Real-world view Abstract representation detail Machine code Concrete

  9. Data independence • Separation of program from data • Program’s ability to retrieve data without changing the structure of code • Logical data independence • Program’s ability to retrieve data without changing the structure of s/w-specific code • When a system uses 4GL language (or non-procedural language) • Physical data independence • Program’s ability to retrieve data without changing the structure of h/w-specific code • When a system uses <= 3GL language (or procedural language)

  10. Database models • Hierarchical • Network • Relational • Object-oriented

  11. Hierarchical DB model • The world is represented with tree-like structure • Only one-to-many relationships (i.e. parent-child relationship) are allowed • Relationships between entities are built through reference point (e.g. pointer) • Logical data independence (yes) • Physical data independence (no)

  12. Network DB model • The world is represented with web-like structure • Many-to-many relationships are allowed • Relationships between entities are built through reference point (e.g. pointer) • Logical data independence (yes) • Physical data independence (no)

  13. Relational DB model • The world is represented with entities and relationships • M:N relationships are conceptually allowed, but implemented through transformation into 1:M relationship (e.g. composite entities (a.k.a. bridge entities)) • Relationships are built through key • Logical data independence (yes) • Physical data independence (yes)

  14. Object-oriented DB model • The world is represented with a collection of objects • Object embeds attribute, operation and relationships • Complex objects can be represented through abstract data type • Embody OO concepts such as encapsulation, inheritance and polymorphism

  15. Architecture of DB system • External view user’s view, local (incomplete) • Conceptual/logical view designer’s view, global • Internal view Programmer’s view • Physical view Implementation view, contains most details • Organized by level of abstraction • Data independence is embodied by the proper separation between four layers

  16. 2. Relational DB • Representation • Table • Key • Integrity rules • Relationships

  17. Representation • The world are viewed as entities and relationships • Entities are modeled as table • Relationships are built through common attributes between entities

  18. Table • Row represents a single entity • Column represents attribute • Cell represents a single value in the intersection between row and column

  19. Key • One or more attributes (columns) that determines other attributes • Primary key • Uniquely identifies entity (should be unique) • Should be Not Null (non-empty) • Foreign key • Common attributes that link one table to another tables • Placed in M side table in reference to primary key to 1 side table

  20. Integrity rules • Entity integrity • Each entity (record) must be uniquely identified (by primary key: PK) • PK should be Not Null for entity integrity to be enforced • Referential integrity • One table must reference another table properly (by foreign key: FK) • FK should be Not Null for referential integrity to be enforced

  21. Relationships • M:N • Yields data redundancy • Composite (or bridge) entities are needed to transform into 1:M

  22. 3. Object-oriented DB • Object • Difference between object in OODB and entity in RDB • Object and class • OO concepts

  23. Object • Object has • OID (identity): system-generated • Instance variables (attributes): ADT allowed, thus the representation of complex entities are possible • Methods (operations): make objects act upon them, thus entities become autonomous

  24. Objects (OODB) vs. Entities (RDB) Objects, unlike entities • Identity is not state-dependent • Because OID is system-generated • Relationship is embedded in the object • Because objects store the reference to other objects in themselves • Autonomous • Because objects can use methods stored in class

  25. Object and Class • Class is a collection of objects with similar attributes and behaviors • Object is an instance of class from conceptual point of view • Class is an instantiation of objects from implementation point of view (i.e. Object is implemented through class: e.g. object uses the methods stored in class) • Objects are organized by class hierarchy

  26. OO Concepts • Encapsulation • information can be selectively hidden • enhances integrity • Inheritance • subclass can inherit common properties from superclass • enhances modularity • Polymorphism • operation can take many forms depending on characteristics (through method overriding) • enhances flexibility

  27. 4. Entity-Relationship Diagram • Attributes • Relationships • Connectivity • Cardinality • Participation (optionality) • Strength • Degree • Entities • Composite • Weak • Subtype/supertype

  28. Attribute • Simple/Composite • Simple: cannot be subdivided (e.g. Sex) • Composite: can be subdivided (e.g. Name = First Name + Last Name; Address = street + city + state + zipcode) • Single-valued/Multi-valued • Single-valued: can have a single value (e.g. age) • Multi-valued: can have multiple values (e.g. educational attainment: # degree can differ by persons; address: you can live in many different places such as permanent address, local address, vacation home, and so on)

  29. Relationships • Connectivity: 1:1, 1:M, M:N • Cardinalities: the number of entity occurrence associated with another entity • Participation: optional/mandatory, determined by cardinalities • Strength: existence-dependency + PK derived from other table • Degree: the number of entities associated with the relationship

  30. Entity • Composite (bridge) • entity that represents relationship between entities (e.g. enrollment) • Weak • when the relationship is strong (e.g. dependent) • Supertype/subtype • characteristics of subtype entities are generalizable from supertype entities (e.g. employee/secretary)

  31. ERD notations

  32. 5. UML • What is UML? • Why UML? • UML Diagrams • Class Diagram

  33. What is UML? • Standardized modeling language for OO system design & analysis • UML notation 1.0 was formed in 1996, version 2.0 as of 2005 • Graphic notations: in between natural language (too imprecise) and programming language (too precise thus too much details) • Use different diagrams depending on different perspectives (conceptual, logical, physical)

  34. Why UML? • Let’s make OO system design unified • Let’s make OO system design visual and easy-to-learn • Let’s make OO system design independent of different programming languages • Let’s promote good things about OO principles • Modularity/code reusability • Let’s make system extensible • Stereotype, tagged value, constraint • Let’s make model interchange easier • XMI (XML Modeling Interchange)

  35. UML Diagrams • Behavior diagram • Describe behavioral aspect of system • Use case diagram, activity diagram • Structure diagram • Show the static structure of the model • Class diagram, package diagram • Component diagram, deployment diagram • Interaction diagram • Represents different aspects of interaction • Sequence diagram, collaboration diagram

  36. Class Diagram (overview) • Shows the static structure of object-oriented database or database that is implemented in OO system • Equivalent to ERD with some differences such as operation and more semantics on relationships • Can be seen from three different perspectives (conceptual, specification, implementation)

  37. Class Diagram: class • Class is represented as three-part compartments (name, attribute, operation) • Naming notation of attribute • [visibility] name: data type = [initial-value] • Naming notation of operations • [visibility] name (parameter-list: data type): [return value type]

  38. Class Diagram: relationship • Association • Aggregation: part-whole relation • Composition: strong form of aggregation • Generalization: general/unique properties • Dependency: implementation of one class is dependent on another class • Multiplicities: # participants associated with relationship • Navigability: shows the direction of navigation between classes

  39. Class Diagram: relationship: notation

  40. 6. Normalization • What is normalization? • 1NF • 2NF • 3NF

  41. What is normalization? • Process for correcting table structure to minimize data redundancies • Usually follows three-step procedures: conversion 1NF  2NF  3NF • Operated by functional dependency between attributes • Two types of functional dependencies • Partial dependency: nonkey attributes are dependent on a part of composite PK • Transitive dependency: nonkey attributes are dependent on another nonkey attributes

  42. First Normal Form (1NF) • No repeating groups • PK is defined

  43. Second Normal Form (2NF) • Table is in 1NF • No partial dependency

  44. Third Normal Form (3NF) • Table is in 2NF • No transitive dependency

  45. Questions & concerns? • Please talk to me • Take advantage of office hours (Wed 2:30-4:30): tutorial is the best way to communicate I believe. • Please do some reading before you show up in the class.

More Related