Chapter 6 Data Base PDF

UNIT6 SPATIAL DATABASE 1. Introduction 12/25/2020 1 Fundamental database concepts The Database Management System (DBMS) is now the underlying framework of the information system and has fundamentally changed the way in which many organ...

UNIT6 SPATIAL DATABASE 1. Introduction 12/25/2020 1 Fundamental database concepts The Database Management System (DBMS) is now the underlying framework of the information system and has fundamentally changed the way in which many organizations operate. The database system remains a very active research area and many significant problems remain. 12/25/2020 2 Fundamental database concepts - file based system The predecessor to the DBMS was the file based system, which is a collection of application programs that perform services for the end-users, usually the production of reports. 12/25/2020 3 Fundamental database concepts - file based system Each program defines and manages its own data. Although the file-based system was a great improvement over the manual filing system, it still has significant problems, mainly the amount of data redundancy present and program data dependence. 12/25/2020 4 Fundamental database concepts - database approach The database approach emerged to resolve the problems with the file based approach. A database is a shared collection of logically related data and a description of this data, designed to meet the information needs of an organization. A DBMS is a software system that enables users to define, create, maintain, and control access to the database. 12/25/2020 5 Fundamental database concepts - application program An application program is a computer program that interacts with the database by issuing an appropriate request (typically a SQL statement) to the DBMS. The database system is used to define a collection of application programs that interact with the database along with the DBMS and database itself. 12/25/2020 6 Fundamental database concepts - DBMS provides DBMS provides a Data Definition Language (DDL), which allows users to define the database, and DBMS provides a Data Manipulation Language (DML), which allows users to insert, update, delete, and retrieve data from the database. DBMS provides controlled access to the database. It provides security, integrity, concurrency and recovery control, and a user- accessible catalog. It also provides a view mechanism to simplify the data that users have to deal with. 12/25/2020 7 Fundamental database concepts – components The DBMS environment consists of hardware (the computer), software (the DBMS, operating system, and appli- cations programs), data, procedures, and people. The people include data and database administrators, database designers, application developers, and end-users. 12/25/2020 8 DBMS vs. Flat File DBMS Flat File Management System Multi-user access It does not support multi- user access Design to fulfill the need for small It is only limited to smaller and large businesses DBMS system. Remove redundancy and Integrity Redundancy and Integrity issues Expensive. But in the long term It's cheaper Total Cost of Ownership is cheap Easy to implement complicated No support for complicated transactions transactions 12/25/2020 9 Fundamental database concepts - advantages & dis advantages Some advantages of the database approach include control of data redundancy, data consistency, sharing of data, and improved security and integrity. Some disadvantages include complexity, cost, reduced performance, and higher impact of a failure. 12/25/2020 10  Additional info DBMS – Advantages DBMS offers various techniques and powerful functions to efficiently store & retrieve data DBMS serves as an efficient handler to balance the needs of multiple applications using the same data Provides Uniform administration procedures for data management Application programmers never exposed to details of data representation and storage Offers Data Integrity and Security DBMS implies integrity constraints to get a high level of protection against prohibited access to data DBMS schedules concurrent access to the data in such a manner that only one user can access the same data at a time Reduces Application Development Time 12/25/2020 11  Additional info DBMS – disadvantages Hardware and Software cost of a DBMS is quite high which increases the budget of your organization Most database management systems are often complex systems, so the training for users to use the DBMS is required In some organizations, all data is integrated into a single database which may cause the damage because of electric failure or database can be corrupted on the storage media Use of the same program at a time by many users sometimes lead to the loss of some data DBMS can't perform sophisticated calculations 12/25/2020 12 Spatial database A spatial database includes collections of information about the spatial location, relationship and shape of topological geographic features and, the data in the form of attributes. 12/25/2020 13 A spatial database – characteristics A spatial database system is a database system. It offers spatial data types (SDTs) in its data model and query language. It supports spatial data types in its implementation, providing at least spatial indexing and efficient algorithms for spatial join. 12/25/2020 14 Geographic database - types and functions There have been several attempts to define a superset of geographic data types that can represent and process geographic data in databases. International Standards Organization (ISO) and the Open Geospatial Consortium (OGC) standards. The GIS community working under the auspices of ISO and OGC has defined the core geographic types and functions to be used in a DBMS and accessed using the SQL language. 12/25/2020 15 Geographic database – Geometry The Geometry class is the root class. It has an associated spatial reference (coordinate system and projection, for example, UTM Z 37 N; WGS1984). The Point, Curve, Surface, and GeometryCollection classes are all subtypes of Geometry. Referring the next slide, other classes and relationships show how geometries of one type are aggregated from others (e.g., a LineString is a collection of Points). 12/25/2020 16 Geometry - class hierarchy 12/25/2020 17 (Source: after OGC Boolean operations – test spatial relationships b/n geometries The full set of Boolean operators to test the spatial relationships between geometries is Equals Disjoint Intersects Touches Crosses Within Contains Overlaps Relate 12/25/2020 18 Boolean operations – test spatial relationships b/n geometries Equals – are the geometries the same? Disjoint – do the geometries share a common point? Intersects – do the geometries intersect? Touches – do the geometries intersect at their boundaries? Crosses – do the geometries overlap (can be geometries of different dimensions, for example, lines and polygons)? 12/25/2020 19 Boolean operations – test spatial relationships b/n geometries Within – is one geometry within another? Contains – does one geometry completely contain another? Overlaps – do the geometries overlap (must be geometries of the same dimension)? Relate – are there intersections between the interior, boundary, or exterior of the geometries? 12/25/2020 20  Boolean operations – test spatial relationships b/n geometries (e.g.) (A) Contains 12/25/2020 21  Boolean operations – test spatial relationships b/n geometries (e.g.) (B) Touches 12/25/2020 22 Advantages of spatial database vs. traditional file system Assembling all data at a single location reduces redundancy. Maintenance costs decrease because of better organization and reduced data duplication. Applications become data independent so that multiple applications can use the same data and can evolve separately over time. User knowledge can be transferred between applications more easily because the database remains constant. Data sharing is facilitated and a corporate view of data can be provided to all managers and users. Security and standards for data and data access can be established and enforced. DBMS are better suited to managing large numbers of concurrent users working with vast amounts of data. 12/25/2020 23 Disadvantages of spatial database vs. traditional file system The cost of acquiring and maintaining DBMS software can be quite high. A DBMS adds complexity to the problem of managing data, especially in small projects. Single user performance will often be better for files, especially for more complex data types and structures where specialist indexes and access algorithms can be implemented. In recent years geographic databases have become increasingly large and complex. 12/25/2020 24 Database management systems A DBMS is a software application designed to organize the efficient and effective storage and access of data. Small, simple databases that are used by a small number of people can be stored on computer disk in standard files. However, larger, more complex databases with many tens, hundreds, or thousands of users require specialist database management system (DBMS) software to ensure database integrity and longevity. 12/25/2020 25 DBMS provide: A data model A data load capability Indexes A query language Security Controlled update Backup and recovery Database administration tools Applications Application programming interfaces (APIs) 12/25/2020 26 DBMS provide A data model. A data model is the mechanism used to represent real- world objects digitally in a computer system. All DBMS include standard general-purpose core data models suitable for representing several types of object (e.g., integer and floating-point numbers, dates, and text). In most cases DBMS can be extended to support geographic object types. A data load capability. DBMS provide tools to load data into databases. Simple tools are available to load standard supported data types (e.g., character, number, and date) in well-structured formats. Other non-standard data formats can be loaded by writing custom software programs that convert the data into a structure that can be read by the standard loaders. 12/25/2020 27 DBMS provide Indexes. An index is a data structure used to speed up searching. All databases include tools to index standard database data types. A query language. One of the major advantages of DBMS is that they support a standard data query/manipulation language called SQL (Structured/Standard Query Language). Security. A key characteristic of DBMS is that they provide controlled access to data. This includes restricting user access to all or part of a database. For example, a casual GIS user might have read-only access to just part of a database, but a specialist user might have read and write (create, update, and delete) access to the entire database. 12/25/2020 28 DBMS provide Controlled update. Updates to databases are controlled through a transaction manager responsible for managing multi-user access and ensuring that updates affecting more than one part of the database are coordinated. Backup and recovery. It is important that the valuable data in a database are protected from system failure and incorrect (accidental or deliberate) update. Software utilities are provided to back up all or part of a database and to recover the database in the event of a problem. Database administration tools. The task of setting up the structure of a database (the schema), creating and maintaining indexes, tuning to improve performance, backing up and recovering, and allocating user access rights is performed by a database administrator (DBA). A specialized collection of tools and a user interface are provided for this purpose. 12/25/2020 29 DBMS provide Applications. Modern DBMS are equipped with standard, general- purpose tools for creating, using, and maintaining databases. These include applications for designing databases (CASE tools) and for building user interfaces for data access and presentations (forms and reports). Application programming interfaces (APIs). Although most DBMS have good general-purpose applications for standard use, most large, specialist applications will require further customization using a commercial off-the-shelf programming language and a DBMS programmable API. 12/25/2020 30 DBMS provide This list of DBMS capabilities is very attractive to GIS users and so, not surprisingly, virtually all large GIS databases are based on DBMS technology. Indeed, most GIS software vendors include DBMS software within their GIS software products, or provide an interface that supports very close coupling to a DBMS. 12/25/2020 31 Types of DBMS DBMS can be classified according to the way they store and manipulate data. Three main types of DBMS are available to GIS users today: relational (RDBMS), object (ODBMS), and object-relational (ORDBMS). 12/25/2020 32 Types of DBMS – A relational database (RDBMS) A relational database comprises a set of tables, each a two-dimensional list (or array) of records containing attributes about the objects under study. This apparently simple structure has proven to be remarkably flexible and useful in a wide range of application areas, such that today over 95% of the data in DBMS are stored in RDBMS. 12/25/2020 33 Types of DBMS – A relational database (RDBMS) A further difficulty is the poor performance of RDBMS for many types of geographic query. These problems are compounded by the difficulty of extending RDBMS to support geographic data types and processing functions, which obviously limits their adoption for geographic applications. 12/25/2020 34 Types of DBMS – Object database management systems Object database management systems (ODBMS) were initially designed to address several of the weaknesses of RDBMS. These include the inability to store complete objects directly in the database. Because RDBMS were focused primarily on business applications such as banking, human resource management, and stock control and inventory, they were never designed to deal with rich data types, such as geographic objects, sound, and video. 12/25/2020 35 Types of DBMS – Object-relational DBMS (ORDBMS) In spite of the technical elegance of ODBMS, they have not proven to be as commercially successful as some predicted. This is largely because of the massive installed base of RDBMS and the fact that RDBMS vendors have now added many of the important ODBMS capabilities to their standard RDBMS software systems to create hybrid object- relational DBMS (ORDBMS). An ORDBMS can be thought of as an RDBMS engine with an extensibility framework for handling objects. 12/25/2020 36 Types of DBMS – Object-relational DBMS (ORDBMS) They can handle both the data describing what an object is (object attributes such as color, size, and age) and the behavior that determines what an object does (object methods or functions such as drawing instructions, query interfaces, and interpolation algorithms). They can manage and store the data and the behavior together as an integrated whole. 12/25/2020 37 Types of DBMS – Object-relational DBMS (ORDBMS) Examples of ORDBMS software include IBM DB2 and Informix Dynamic Server, Microsoft SQL Server, Oracle, and PostgreSQL. As ORDBMS and the underlying relational model are so important in GIS. 12/25/2020 38 Geographic ORDBMS The ideal geographic ORDBMS is one that has been extended to support geographic object types and functions through the addition of the functions in the following slides 12/25/2020 39 Geographic ORDBMS - Supports and functions Query parser Query optimizer Query language Indexing services Storage management Transaction services Replication 12/25/2020 40 Geographic ORDBMS - Supports and functions A query parser – the engine used to interpret SQL queries is extended to deal with geographic types and functions. A query optimizer – the software query optimizer is able to handle geographic queries efficiently A query language – the query language is able to handle geographic types (e.g., points and polygons) and functions (e.g., select polygons that touch each other). 12/25/2020 41 Geographic ORDBMS - Supports and functions Indexing services – the standard unidimensional DBMS data index service is extended to support multidimensional (i.e., x, y, z coordinates) geographic data types. Storage management – the large volume of geographic records with different sizes (especially geometric and topological relationships) is accommodated through specialized storage structures. 12/25/2020 42 Geographic ORDBMS - Supports and functions Transaction services – standard DBMS are designed to handle short (sub-second) transactions and are extended to deal with the long transactions common in many geographic applications. Replication – services for replicating databases are extended to deal with geographic types, and problems of reconciling changes made by distributed users. 12/25/2020 43 Geographic ORDBMS - Supports and functions (e.g. to a query optimizer) Consider a query to find all potential users of a new brand of premier wine to be marketed to wealthy households from a network of retail stores. The objective is to select all households within 3 km of a store that have an income greater than $110 000. This could be carried out in two ways: Select all households with an income greater than $110 000; from this selected set, select all households within 3 km of a store. Select all households within 3 km of a store; from this selected set select all households with an income greater than $110 000. Selecting households with an income greater than $110 000 is an attribute query that can be performed very quickly. Selecting households within 3 km of a store is a geometric query that takes much longer. Executing the attribute query first (option 1 above) will result in fewer tests for store proximity and therefore the whole query will be completed much more quickly. 12/25/2020 44 Applications of database managements Banking Land Manufacturing administration and Finance management Mistry of water Universities resources Airlines Cadaster 12/25/2020 45 Users in a DBMS environment Component Task Name Application write programs in various Programmers programming languages to interact with databases. Database is responsible for managing the entire Administrator DBMS system. s End-Users are the people who interact with the database management system. conduct various operations on You ? database like retrieving, updating, 46 deleting, etc. 12/25/2020 Popular DBMS Software Microsoft Access PostgreSQL MySQL SQLite Oracle dBASE FoxPro IBM DB2 LibreOffice Base MariaDB Microsoft SQL Server etc. 12/25/2020 47 When not to use a DBMS system? Although, DBMS system is useful. It is still not suited for specific task mentioned below: Not recommended when you do not have the budget or the expertise to operate a DBMS. In such cases, Excel/CSV/Flat Files could do just fine. 12/25/2020 48