Database Concepts - MSDIS

2y ago
25 Views
2 Downloads
848.54 KB
62 Pages
Last View : 17d ago
Last Download : 2m ago
Upload by : Asher Boatman
Transcription

DatabaseConceptspresented by:Tim HaithcoatUniversity of MissouriColumbia

IntroductionVery early attempts to build GIS began from scratch,using limited tools like operating systems & compilersMore recently, GIS have been built around existingdatabase management systems (DBMS)– purchase or lease of the DBMS is a major part of the system’ssoftware cost– the DBMS handles many functions which would otherwisehave to be programmed into the GISAny DBMS makes assumptions about the data which ithandles– to make effective use of a DBMS it is necessary to fit thoseassumptions– certain types of DBMS are more suitable for GIS than othersbecause their assumptions fit spatial data better2

Two ways to use DBMS within a GIS:Total DBMS solution– all data are accessed through the DBMS, so must fitthe assumptions imposed by the DBMS designerMixed solution– some data (usually attribute tables andrelationships) are accessed through the DBMSbecause they fit the model well– some data (usually locational) are accessed directlybecause they do not fit the DBMS model3

GIS as a Database ProblemSome areas of application, notable facilitiesmanagement:– deal with very large volumes of data– often have a DBMS solution installed before the GISis consideredThe GIS adds geographical access to existingmethods of search and querySuch systems require very fast response to alimited number of queries, little analysisIn these areas it is often said that GIS is a“database problem” rather than an algorithm,analysis, data input or data display problem4

DefinitionA database is a collection of non-redundant datawhich can be shared by different application systems– stresses the importance of multiple applications, datasharing– the spatial database becomes a common resource foran agencyImplies separation of physical storage from use ofthe data by an application program, i.e. program/dataindependence– the user or programmer or application specialist neednot know the details of how the data are stored– such details are “transparent to the user”5

Definition (continued)Changes can be made to data without affectingother components of the system, e.g.– change format of data items (real to integer,arithmetic operations)– change file structure (reorganize data internally orchange mode of access)– relocate from one device to another, e.g. fromoptical to magnetic storage, from tape to disk6

Advantages of a Database ApproachReduction in data redundancy– shared rather than independent databases reduces problem of inconsistencies in stored information,e.g. different addresses in different departments for thesame customerMaintenance of data integrity and qualityData are self-documented or self-descriptive– information on the meaning or interpretation of thedata can be stored in the database, e.g. names ofitems, metadataAvoidance of inconsistencies data must follow prescribed models, rules, standards7

Advantages of a Database Approach(continued)Reduced cost of software development– many fundamental operations taken care of,however, DBMS software can be expensive toinstall and maintainSecurity restrictions– database includes security tools to control access,particularly for writing8

Views of the DatabaseINTERNAL VIEW– Normally not seen by the user or applicationsdeveloperCONCEPTUAL VIEW– Primary means by which the databaseadministrator builds and manages the databaseEXTERNAL VIEW (or Schemas)– what the user or programmer sees - can bedifferent to different users and applications9

Views of the DatabaseAdapted from: Date, G.J. 1987. An Introduction to Database Systems,Addison-Wesley. Reading, MA, p. 32User A1User A2User B1External View AConceptual ViewUser B2User B3External View BDatabaseManagementSystem(DBMS)Stored Database (Internal View)10

Database Management Systems:ComponentsData types integer (whole numbers only)real (decimal)character (alphabetic & numeric characters)date– more advanced systems may include pictures &images as data types Example: a database of buildings for the fire departmentwhich stores a picture as well as address,number of floors, etc.Standard Operations– Examples: sort, delete, edit, select records11

Database Management Systems:Components (Continued)Data definition Language (DDL)– The language used to describe the contents of thedatabase Examples: attribute names, data types - “Metadata”Data manipulation & Query Language– The language used to form commands for input,edit, analysis, output, reformatting, etc.– Some degree of standardization has been achievedwith SQL (Standard Query Language)12

Database Management Systems:Components (Continued)Programming tools– Besides commands and queries, the databaseshould be accessible directly from applicationprograms through e.g. subroutine callsFile Structures– The internal structures used to organize the data13

Database Management SystemsTypes of Database SystemsSeveral models for databases:– Tabular (“flat tire”) - data in single table– Hierarchical– Network– RelationalThe hierarchical, network & relational models all tryto deal with the same problem with tabular data:– inability to deal with more than one type of object, orwith relationships between objects Example: database may need to handle information onaircraft, crew, flights, and passengers - four types ofrecords with different attributes, but with relationshipsbetween them (“is booked on” between passenger & flight)14

Database Management SystemsTypes of Database Systems (Continued)Database systems originated in the late 1950s andearly 1960s largely by research and developmentof IBM CorporationMost developments were responses to needs ofbusiness, military, government and educationalinstitutions - complex organizations withcomplex data and information needsTrend through time has been increasingseparation between the user and the physicalrepresentation of the data - increasing“transparency”15

Hierarchical ModelEarly 1960s, IBM saw business worldorganizing data in the form of a hierarchyRather than one record type (flat file), abusiness has to deal with several types whichare hierarchically related to each otherLet’s look at anexample16

Hierarchical ModelExample: company has several departments, eachwith attributes: name of director, number of staff,address– Each department requires several parts to make itsproduct, with attributes: part number, number in stock– Each part may have several suppliers, with attributes:address, priceDDPDPSPSS17

Hierarchical Model - ContinuedCertain types of geographic data may fit thehierarchical model wellS– Example: census data organized by state,within state by city,within city by census tract:CCTThe database keeps track of different recordtypes, their attributes, and the hierarchicalrelationships between themThe attribute which assigns records to levels inthe database structure is called the key– Example: Is record a department, part or supplier?18

Summary of FeaturesA set of record “types”– Examples: Supplier record type, departmentrecord type, part record typeA set of links connecting all record types inone data structure diagram (tree)At most one link between two record types,hence links need not be named– For every record, there is only one parent record atthe next level up in the tree Example: every county has exactly one state, every parthas exactly one department19

Summary of Features (continued)No connections between occurrences of thesame record typecannot go between records at the samelevel unless they share the same parentDx20

Advantages & DisadvantagesData must possess a tree structure– Tree structure is natural for geographical dataData access is easy via the key attribute, butdifficult for other attributes– In the business case, easy to find record given itstype (department, part or supplier)– In geographical case, easy to find record given itsgeographical level (state, county, city, censustract), but difficult to find it given any otherattribute Example: find the records with population 5,000 or less21

Advantages & Disadvantages (continued)Tree structure is inflexible– Cannot define new linkages between records once thetree is established Example: in the geographical case, new relationshipsbetween objects– Cannot define linkages laterally or diagonally in thetree, only vertically– The only geographical relationships which can becoded easily are “is contained in” or “belongs to”DBMSs based on the hierarchical model (i.e., System2000) have often been used to store spatial data, buthave not been very successful as bases for GIS22

Network ModelDeveloped in mid 1960s as part of work ofCODASYL (Conference on Data SystemsLanguages) which proposed programminglanguage COBOL (1966) and then networkmodel (1971)– Other aspects of database systems also proposedat this time include database administrator, datasecurity, audit trailObjective of network model is to separate datastructure from physical storage, eliminateunnecessary duplication of data withassociated errors & costs23

Network Model (continued)Uses concept of a data definition language,data manipulation languageUses concept of man linkages or relationships– An owner record can have many member records– A member record can have several owners Hierarchical model allows only 1:nNetwork DBMSs include methods forbuilding and redefining linkages,– Example: when patient is assigned to ward24

Network Model (continued)Example of a network database– A hospital database has three record types: Patient: name, date of admission, etc. Doctor: name, etc. Ward: number of beds, name of staff nurse, etc.– Need to link patients to doctor, also to ward– Doctor record can own many patientrecords– Patient record can be owned by bothdoctor and ward records25

Network Model RestrictionsLinks between record of the same type are notallowedWhile a record can be owned by severalrecords of different types, it cannot be ownedby more than one record of the same type– Example: patient can have only onedoctor, only one ward26

Network Model SummaryThe network model has greater flexibilitythan the hierarchical model for handlingcomplex spatial relationshipsIt has not had widespread use as a basis forGIS because of the greater flexibility of therelational model27

Relational ModelThe most popular DBMS model for GIS– The INFO in ARC/INFO– EMPRESS in System/9– Several GIS use ORACLE– Several PC-based GIS use Dbase IIIFlexible approach to linkages between recordscomes closes to modeling the complexity ofspatial relationships between objectsProposed by IBM researcher E.F. Codd (1970)More of a concept than a data structure– Internal architecture varies substantially from oneRDBMS to another28

Relational Model TerminologyEach record has a set of attributes– The range of possible values (domain) is definedfor each attributeRecords of each type form a table or relation– Each row is a record or tuple– Each column is an attributeNote the potential confusion: a “relation” is atable of records, not a linkage between records29

Relational Model Terminology (continued)The degree of a relation is the number ofattributes in the table– 1 attribute is a unary relation– 2 attributes is a binary relation– N attributes is an n-ary relationExamples of relations:– Unary: COURSES (Subject)– Binary: PERSONS (Name, Address)OWNER (Person name, house address)– Ternary: HOUSES (address, price, size)30

Relational Model KeysA key of a relation is a subset of attributes with thefollowing properties:– Unique identification the value of the key is unique for each tuple– Non-redundancy No attribute in the key can be discarded without destroying thekey’s uniqueness record type (class)39

Databases for Spatial Data (continued)There are so many possible relationshipsbetween spatial objects, that not all can bestored explicitly– However, some relationships must be storedexplicitly as they cannot be computed from thegeometry of the objects Example: existence of grade separationThe integrity rules of geographical data are toocomplex– Example: the arcs forming a polygon must link intoa complete boundary– Example: lines cannot cross without forming a node40

Databases for Spatial Data (continued)Effective use of non-spatial database managementsolutions requires a high level of knowledge ofinternal structure on the part of the user– Example: user may need to be aware that polygonsare composed of arcs, and stored as are records,cannot treat them simply as objects and let thesystem take care of the internal structure– users are required to have too much knowledge ofthe database model, cannot concentrate onknowledge of the problem– Users may have to use complex commands toexecute processes which are conceptually simple41

Data SecurityMany systems for small computers, andsystems specializing in geometric andgeographical data, do not providefunctionality necessary to maintain dataintegrity over long periods of time.42

Data SecurityIntegrity ConstraintsIntegrity constraints: rules which the databasemust obey in order to be meaningful– Attribute values must lie within prescribeddomains– Relationships between objects must not conflict Example: “Flows into” relationship betweenriver segments must agree with“is fed by” relationship– Locational data must not violaterules of planar enforcement, contoursmust not cross each other, etc.43

Data SecurityTransactionsTransactions may include:– Modifications to individual data items– Addition or deletion of entire records– Addition or deletion of attributes– Changes in schema (external views of the database) Example: addition of new tables or relations, redefinitionof access keysAll of the updates or modifications made by auser are temporary until confirmed– System checks integrity before permanentlymodifying the database (“posting” the changes to the database)– Updates and changes can be abandoned at any timeprior to final confirmation44

Concurrent UsersIn many cases more than one user will need toaccess the database at any one time– This is a major advantage of multi-user systems &networksHowever, if the database is being modified byseveral users at once, it is easy for integrityconstraints to be violated unless adequatepreventative measures exist45

Concurrent Users (continued)Changes may interfere and produce loss of integrity– Example: user B may change an object while user A isprocessing it The results will not be valid for either the old or the newversion of the object– Example: a dispatching system Operator A receives a fire call, sends a request to firestation 1 to dispatch a vehicle, waits for fire station toconfirm Operator B receives a fire call after A’s call, but before Aconfirms the dispatch Result: both A & B request a dispatch of the same fire truck Solution: “lock” the first request until confirmed46

Concurrent Users (continued)Automatic control of concurrent use is based onthe transaction concept– The database is modified only at the end of atransaction– Concurrent users number see the effects of anincomplete transaction– Interference between two concurrent users is resolvedat the transaction level47

3 Types of Concurrent AccessUnprotected:Applications mayretrieve & modifyconcurrentlyIn practice, no systemallows this, but if onedid, system shouldprovide a warning thatother users areaccessing the dataProtected:Any application mayretrieve data, but only onemay modify itExample: User B should beable to query the status of firetrucks even after user A hasplaced a “hold” on oneExclusive:Only one application mayaccess the data48

Check-out/check-inIn GIS applications, digitizing and updatingspatial objects may require intensive work onone part of the database for long periods oftime– Example: digitizer operator may spend an entireshift working on one may sheet

key’s uniqueness – Example: phone number is a unique key in a phone directory In a normal phone directory the key attributes are last name, first name, street address If street address is dropped from this key, the key is no longer unique (many Smith, John’s) A prime attribute of a relation is an attribute which

Related Documents:

General Database Concepts, page 1: Database Table Details, page 2: General Database Concepts: This section provides an overview of some basic database concepts. Tables, Columns, and Rows: A database contains one or more tables of data. Each table in a database define s a set of columns, which are called :

Database Applications and SQL 12 The DBMS 15 The Database 16 Personal Versus Enterprise-Class Database Systems 18 What Is Microsoft Access? 18 What Is an Enterprise-Class Database System? 19 Database Design 21 Database Design from Existing Data 21 Database Design for New Systems Development 23 Database Redesign 23

Getting Started with Database Classic Cloud Service. About Oracle Database Classic Cloud Service1-1. About Database Classic Cloud Service Database Deployments1-2. Oracle Database Software Release1-3. Oracle Database Software Edition1-3. Oracle Database Type1-4. Computing Power1-5. Database Storage1-5. Automatic Backup Configuration1-6

The term database is correctly applied to the data and their supporting data structures, and not to the database management system. The database along with DBMS is collectively called Database System. A Cloud Database is a database that typically runs on a Cloud Computing platform, such as Windows Azure, Amazon EC2, GoGrid and Rackspace.

Creating a new database To create a new database, choose File New Database from the menu bar, or click the arrow next to the New icon on the Standard toolbar and select Database from the drop-down menu. Both methods open the Database Wizard. On the first page of the Database Wizard, select Create a new database and then click Next. The second page has two questions.

real world about which data is stored in a database. Database Management System (DBMS): A collection of programs to facilitate the creation and maintenance of a database. Database System DBMS Database A database system contains information about a particular enterprise. A database system provides an environment that is both

Concepts of Database Management Seventh Edition Chapter 6 Database Design 2: Design Method. Objectives Discuss the general process and goals of database design Define user views and explain their function Define Database Design Language (DBDL) and use it to document database designs

Changes in Oracle Providers for ASP.NET in ODAC 12c Release 4 xiv Changes in Oracle Providers for ASP.NET Release 11.2.0.2 xiv Changes in Oracle Providers for ASP.NET Release 11.2.0.1.2 xv 1 Introduction to Oracle Providers for ASP.NET 1.4 Connecting to Oracle Database Cloud Service 1-1 1.1 Overview of Oracle Providers for ASP.NET 1-1 1.2 Oracle Providers for ASP.NET Assembly 1-4 1.3 System .