Database lectures MSBC....pptx

1. What is a database?  Database management systems organise and structure data for easy retrieval and manipulation  The data structures and access techniques provided by a particular DBMS are called its data model  The structured Query Language (SQL) is a database language for relational databases and it uses the relational data model

2. RELATIONAL DATABASES

3. Introduction  A relational database is a database where all data visible to the user is organised strictly as tables of data values and where all database operations work on these tables

4. Tables  The organisational principle of the relational database is a table  A table is a rectangular row/column arrangement of data values  Each table in a database has a unique table name that identifies its contents.

5. Tables in sample database  We shall deal with a sample database with five tables   Landoffices: which stores data about the lands offices   Beneficiaries: which stores data on beneficiaries of products   Officers: which stores data of each officer   MachineOrders: Which stores data on orders placed by each beneficiary   MachineProducts: Which stores data on each product available for sale to beneficiaries

6. Tables Each horizontal row represents a single physical entity In this example single lands offices Together five rows represent 5 offices The vertical column represents one item of data that is stored in the database for each office

7. Tables Each row of a table contains exactly one data value in each column All the data values in the column should be one data type e.g.. Numeric, text, currency etc. A table with zero rows is perfectly legal

8. Primary key  Because rows of a relational table are unordered you cannot select a specific row by its position in the table  Thus in a well designed relational database each table must have a column or a combination of columns whose values uniquely identifies each row in the table.  This is the primary key

9. Relationships  One of major differences between the relational model and earlier models is the explicit pointers and parent/child relationships are banned from the relational database  Relationships exist in the relational database.

10. Relationships The relationships are represented by common data values stored in two tables One of the goals of SQL is to let you retrieve data from these tables by manipulating these relationships

11. Foreign keys A column in a table whose value matches the primary key in some other table is called a foreign key Foreign keys are an important part of the relational model because they create relationships among tables in the database

12. Summary  Each table has a name which uniquely identifies it  Each table has one or more named columns which are arranged in left to right order  Each table has zero or more rows  All data values in a given column are of same data type and are drawn from a domain of the column  A primary key is a column of a combination of columns whose value(s) uniquely identify the row  A foreign key is a column or combination of columns whose values are a primary key of some other table

13. Summary  A primary key/foreign key combination creates a parent/child relationship between tables that contain them

14. SIMPLE QUERIES IN RELATIONAL DATABASES Dr. A. Murwira University of Zimbab, Department of Geography and Environmental Science

15. The SELECT statement  The SELECT statement retrieves data from a database and returns to you query results

16. The SELECT statement SELECT LANDOFFICES.CITY, LANDOFFICES.TARGET, LAND OFFICES.SALES FROM LANDOFFICES;

17. The SELECT statement SELECT LANDSOFFICES.CITY, LANDSOFFICES.TARGET, LANDSOFFICES.SALES, LANDSOFFICES.REGION FROM LANDSOFFICES WHERE (((LANDSOFFICES.REGION)="eastern"));

18. The SELECT statement SELECT LANDSOFFICES.CITY, LANDSOFFICES.TARGET, LANDSOFFICES.SALES, LANDSOFFICES.REGION, [SALES]>[TARGET] AS Expr1 FROM LANDSOFFICES WHERE (((LANDSOFFICES.REGION)="Eastern"));

19. DATA INTEGRITY Dr. A. University of Zimbabwe, Department ofphy and Environmental Science

20. DATA INTEGRITY  Data integrity refers to the correctness and completeness of the data in a database  Data integrity can be lost in different ways:   Invalid data may be added to the database such as an order that specifies a non-existent product   Existing data may be modified to an incorrect value   Changes in database may be lost due to system error or power failure   Changes may be partially applied

21. WHAT IS DATA INTEGRITY?  DBMS normally implement data integrity constrains as a way to preserve consistency and correctness. These are of different types:  Required data: some columns in the database must contain valid data value in every row. They are not allowed to contain null values  Validity checking: Every column in the database has a set of values that are legal to that column called a domain  Entity integrity: The primary key of a table must contain a unique value in each row. Duplicate values are not allowed

22. WHAT IS DATA INTEGRITY?  Referential integrity: A foreign key in a relational database links each row in a child table containing the foreign key to the row of the parent table containing the primary key value.  Business rules: Updates to a database may be constrained by business rules governing real world business.  Consistency: Many real world transactions cause multiple updates to a database.

23. FORMS

24. WHAT IS FORM  A form is the interface between the user and the database  Forms display data from the database in a familiar, attarctive and convenient layout

25. LINKING DBMS TO A SPATIAL DATABASE: LAND DATABASE EXAMPLE

26. Vector data model: GIS database structure Vector GIS data structure consists of the Coordinate database and the Attribute tables connected via the unique Identifier common to both the coordinate table and attribute data table

27. The spatial database Create a spatial database of farms Make the attribute table have a foreign key that will serve as a link to your DBMS In this case it is CUST_NUM

28. Connecting the spatial database with DBMS In ArcView GIS you can connect your spatial database with the DBMS though SQL connect

29. Connecting the spatial database with DBMS You then select the appropriate DBMS to connect to and it will expose all the tables You then select the table which has the primary key which will then enable you to link to the Spatial database with the use of the foreign key

30. Connecting the spatial database with DBMS You then select the primary key in the DBMS table as well as the foreign key in the spatial database This is followed by the join command which joins the two tables

31. Connecting the spatial database with DBMS This is followed by the join command which joins the two tables This means the DBMS data can be used to query the spatial database

34. Spatial Data 2 Spatial data, which means data related tospace. Data that pertains to the space occupied by objects. Data that define alocation. These are in the form of graphic primitives that usually either points, lines, polygons orpixels. Spatial data includes location, shape, size and orientation. For example: consider a particularsquare: • Its center ( the intersection of its diagonals ) specifiesits location • Its shape issquare • The length of one of its sides specifies itssize • The angle of its diagonals make with, say, x-axis specifies itsorientation.

35. Spatial Data 3 The space of interest can be, • For example, the two-dimensional abstraction of (parts of) the surface of the earth – that is, geographic space, • The most prominent example – a man-made space like the layout of a VLSI design, • A volume containing a model of the human brain, or another 3d-space representing the arrangement of chains of protein molecules.

36. Non-Spatial Data 4 Non-spatial data ( also called attribute or characteristic data) is that information which is independent of all geometric considerations. For example: a person's height, mass, and age are non-spatial data because they are independent of person's location. It's interesting to note that, while mass isnon-spatial data, weight is spatial data in the sense that something's weight is very much dependent on its location.

37. Spatial Data 5 Tw otypesof spatialdataareparticularlyimportant: • Computer-aided-design (CAD) data, which includes spatial informationabouthowobjectssuchasbuildings,cars,or aircraft, areconstructed.Other important examplesof computer-aided- design databases are integrated-circuit and electronic-device layouts. • Geographic data such as road maps, land-usage maps, topographic elevationmaps,political mapsshowingboundaries, land-ownership maps, and so on. Geographic information systems are special-purpose geographicdata. databases tailored for storing

38. How Are Spatial Data Organized? 6 Coordinates are used to specify location of geographic objects in either two or three dimensional space. The coordinates can be specified as(x,y) in 2D or (x,y,z) in 3D or spherical coordinates (latitude,longitude). Discrete geographic features like points, lines and polygons can beused to represent different types objects. Points might be a house address, a line might be a road and a polygon might be a land parts or buildingfoot- prints. These are also known as vector datatypes. Continuous geographic features describe phenomena that exist continuously in landscape. Examples include: elevation, temperature, relative humidity, gravity, wind, atmospheric pressure and so on. These are considered as raster data types. The features can also be summarized by a geographic area. Examples include population, socio-economic characteristics and other demographic information.

39. Properties of Spatial Data 7 There are four main properties of the spatial data that set it apart from traditional relational data. Geometry Distribution of Objects in Space Temporal Changes Data Volume i. ii. iii. iv.

40. Geometry 8 Geometry deals with the mathematical properties of an object. These properties include measurement (metric), relationships of points, lines, angles, surfaces, and solids (topology), and order. A simple geometry is usually constructed fromgeometric primitives such as points, lines, curves, and areas. Complex geometries are constructed from collectionsof simple geometries. In addition, there are a number of geometric relationships between geometries that are important in handling spatial data.

41. Distribution of Objects in Space 9 Usually spatial objects are very irregularly distributed in space. Consider the case where we model the town halls of all the cities in the United States as spatial objects (points). The distribution of cities on the east coast is very dense compared to the distribution of cities in Arizona and Nevada, which is sparse. In addition, different objects have largely varying extents.

42. Temporal Changes 10 Spatial data often has an associated temporal property. An example is a navigation system that helps travelers find directions from place A to B in amajor city. If there is an accident and some road is temporarily closed, the system has to incorporate this new data and recompute a suitable path from point A toB.

43. Data Volume 11 Several GIS applications deal with very large databases of the order of terabytes. For example, remote sensing applications gather terabytes of data from satellites every day. Similarly, data warehousing applications and NASA’s Earth Observation System are other examples of systems with terabytes of spatial data.

44. 12

45. Spatial Data Types 13 Spatial data types are special data types necessary to model geometry and to suitably represent geometric data in database systems. These datatypesare: point, line, and region but also include more complex types like partitions (maps) and graphs (networks). Conceptually, points, lines, rectangles, surfaces, volumes and etc. Physically, cities, rivers, roads, states, crop coverage, mountain ranges etc. Spatial data types provide a fundamental abstraction for modeling the geometric structure of objects in space, their relationships, properties and operations.

46. Spatial Database 14 A spatial database system is a full-fledged database system with additional capabilities for handling spatial data. Spatial database system is a database systemwith: • Offersspatialdatatypesinitsmodelandquerylanguage. • Supports spatial data types in its implementation providing at least spatial indexing and efficient algorithms for spatialjoin.  Spatial data types, e.g. POINT, LINE, REGION, provide a fundamental abstraction for modeling the structure of geometric entities in space as well as their relationships (l intersects r), properties (area(r) > 1000), and operations (intersection(l, r) – the part of l lying within r). •

47. Spatial databases 15 In general, a spatial database stores objects that have spatial characteristics that describe them and that have spatial relationshipsamongthem. The spatial relationships among the objects are important, and theyareoftenneededwhenqueryingthedatabase. Aspatialdatabaseisoptimized to storeand query datarelated t o objectsinspace,includingpoints,linesand polygons. Whereas typical databases process numeric and character data, additional functionality needs to be processspatialdatatypes. added for databases to

48. Spatial databases 16 Queries posed on thesespatial data,wherepredicatesfor selection dealwithspatialparameters,arecalledspatial queries. For example, aquery suchas“List all the customers located within twentymilesof companyheadquarters”willrequiretheprocessing of spatialdata types. Effectively , each customer will be longitude>position. Atraditional B+-tree indexbasedon customers’zip codesor other non-spatial attributes cannot be used to process this query since traditional indexes are not capable of ordering multidimensional coordinatedata. associated to a <latitude,

49. Why Spatial databases? 17 Applications of spatial data initially stored data as files in a file system,asdidearly-generationbusinessapplications. But asthe complexity and volume of the data, and the number of users, have grown, ad hoc approaches to storing and retrieving datainafilesystemhaveprovedinsufficientfortheneedsof many applicationsthatusespatialdata. Spatial-data applications require facilities offered by a database system—inparticular,theabilitytostoreandquerylarge amounts of data efficiently .

50. Why Spatial databases? 18 Therefore,thereisaspecialneedfor databasestailoredfor handlingspatialdataandspatialqueries. Spatial data support in databases is important for efficiently storing, indexing, and querying of data on the basis of spatial locations. Efficient processing of the above query would require special- purposeindexstructures,suchasR-treesforthe task.