1 / 77

Introduction to Database Management Systems (DBMS)

Learn about the importance of databases, different types of database models, and the key characteristics of DBMS in this comprehensive guide by Dr. Mohamed Yagoub Mohamed.

cblackstone
Download Presentation

Introduction to Database Management Systems (DBMS)

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. Database Management System (DBMS) By: Dr. Mohamed Yagoub Mohamed E-mail: myagoub@hotmail.com URL: http://www.angelfire.com/mo/yagoub

  2. Overview Why we need database Database Concept Types of database models Relational database

  3. DBMS DBMS is a collection of data (database) and programs to access that data. The goal of DBMS is to store, retrieve, and display information Key characteristics of DBMS are: performance, store large volume of database, share data (access), provide security (authorization), remove redundancy (normalization) and provide concurrent access (different users at the same time).

  4. Why we need database? Without database GIS is cartography (electronic map) No database No spatial analysis

  5. Hardware Information GIS People Software Principles of GIS Why we need database?

  6. GIS database Attribute DBMS Spatial data location Text, Images Sound, movie Multimedia Database and GIS

  7. Data abstraction Physical level: Describe how the data are actually store (word or bytes) Conceptual level: Describe what data are actually stored in the database (Structure). it gives Schematic representation of phenomena

  8. Data abstraction-Continue • View level: Describe only a part of the entire database. Many users of the database may be concerned with a subset of information. The system may provide many views for the same database

  9. Data abstraction Different users for the same database View Level Conceptual level Logical Level

  10. Instance of schemes • The collection of database at a particular moment is called the instance of the database • The overall design of the database is called the database scheme

  11. Types of database models Data model is a collection of conceptual tools for describing data, data relationship, data semantics, and consistency constraints. There are mainly three types of models Object-based logical models Are used to describe data at the conceptual and view level. Example of these the Entity-Relationship model and object-oriented model

  12. Data models- Continue • Record-based logical models Are used to describe data at the conceptual and view level. Example of these are: Network model, Hierarchical model, and relational model. • Physical data models Are used to describe data at the physical level (bytes and words). It is mainly deal with hardware.

  13. Entity-Relationship (E-R) model It is based on simulation of the real world which consists of basic objects called entities and relationship among these objects The overall logical data structure of a database can be expressed graphically by an E-R diagram. Which consists of rectangle (entity), ellipse (attribute), diamond(relationship), and lines.

  14. Balance Street Number SSN Name Date Custom Acct Customer Account 3 1 2 3 Tables can represent the above relation E-R Diagram for customer CustomAcct relationship associate a customer with each account he has

  15. Aggregation Aggregation is an abstraction through which relationships are treated as higher-level entities i.e. express relationships among relationships

  16. Hours Name SSN Number Employee Project Work Users Type Machinery 5 tables can represent the diagram E-R diagram with aggregation

  17. Generalization In E-R diagram generalization is depicted through a triangle labeled ISA (is a). The attribute of higher level entity are said to be inherited by lower level entity. e.g. both saving and checking account inherit the attributes of account

  18. Balance Account number Account ISA Checking account Saving account Over draft Interest rate 3 tables can represent the diagram E-R diagram with generalization

  19. E-R diagram Table • An Entity (E) with attributes a1..an can be represented by a table called E with n columns for each attribute. • Each row in this table corresponds to one entity of the entity set E

  20. Account_no balance 255 3000 452 3222 560 34555 323 21000 215 456780 Let D1 ==> set of all account number D2===> set of all balance Any row consists of 2 tuples (v1,v2) e.g. (255,3000) The set of all possible rows is the Cartesian product of D1 and D2 i.e. = D1 X D2 For a table with n columns the total number of rows = D1 X D2 X ..X Dn-1 X Dn

  21. b1 b2 b3 a1 a2 Mapping constraints • Mapping cardinalities express the number of entities to which another entity can be associated via a relationship • For a binary relationship set R between entity set A and B the mapping can be one-one (1-1), one-many(1-M), many-one (M-1), and many-many(M-M) 1-M relation

  22. Object oriented Model The basic unit that an object-oriented (OO-DBMS) manages is the object. It is based on four basic concepts of abstraction: • Classification: Mapping of several objects (instances) to common class • Generalization: Group several classes which have the same properties in common (roads, railway)-transportation network

  23. Object oriented Model-Continue • Association: Relation between similar objects is considered a higher level set object • Aggregation: Objects which consist of several other objects (Composed objects)

  24. Object oriented Model-Continue • OO model uses objects rather than records to manage data • An object is a collection of data elements and operations that together are considered a single entity • An object has associated with it a set of variables that contain the data for the object, a set of messages to which the object respond, and a method which response to the message

  25. Object Oriented Model-Continue • Once the structure is setup, the details of it need not be user visible • This approach has the attraction that query is very natural • A geographic data handling systems employ this model are: TIGRIS, DAPLEX, and PROBE • It is application in GIS is recommended

  26. Object oriented Model-Continue • Objects are typed and the format and operations of an object instance are the same as some object prototype • Example of an object might be a lake: • List of border chain: C1, C2, C3, Cn • List of nodes: N1, N2, N3, Nn • Attribute: Depth, soil type

  27. Student Second year First year Object oriented Model-Continue For example student can be a superclass. First and second year student may represented by a classes that are specialization of a student class variables and methods specific to first year students are associated with fist year student class. Variables and methods that apply both to first and second year students are associated with student class. The variables associated with each class may be: Student: Name, ID, address First year student: Subject Second year student:Practical course

  28. Hierarchical Model • Based on Tree structure (child-parent) • No element can have more than one parent • Requires knowledge by the user of the actual storage scheme used by the DBMS • Examples of database are: System2000 and IMS • Not commonly applicable in GIS

  29. Network Model • Organized data in a network or plex structure (child-parent) • Children may have more than one parent • The query language is procedural • Examples of database are: DBMS-10, DMS1100, IDMS • Not commonly applicable in GIS

  30. Root (Parent) A1 Child Parent B2 B1 C3 • C2 C1 C5 C4 C6 Child Child Database tree structure (Hierarchical, Network)

  31. Relational model • A relational database consists of a collection of tables, each of which is assigned a unique name • The relational models differs from network and hierarchical models in that it does not use pointers or links. Instead , the relational model relate records by the value they contain.This freedom from the use of pointers allows formal mathematical foundation to be defined • Examples of RDBMS are Oracle, Informix, and Sybase

  32. Reasons to use Relational Model • Independence of the physical data storage and logical database structure. Results in users do not need to understand the underlying physical layout of the data to access data from a logical structure, such as a table • Variable and easy access to all data. Results in access to data is not predefined as in hierarchical databases in which users must understand and navigate through the hierarchy to retrieve data • Flexible in database design. i.e complex objects are expressed as simple tables and relationships • Applying relational design methods reduces data redundancy (Normalization) and storage requirements

  33. Relational DBMS • Aspects of an RDBMS • Structures: Well defined objects • Operations: Clearly defined actions • Integrity Rules: Rules that control which operations are allowed on the data and structures of the database

  34. Relational DBMS • Components of a Relational Database • Table: collection of rows all containing the same columns • Row: Horizontal components of a table. Consists of values for each column. Each row is equivalent to a record • Column: Vertical component of a table. Each column in the record is often referred to as a field

  35. Relational DBMS • Relational Database Rules • Each column in a table must be unique • The order of the rows in a table is not meaningful • The order of the columns in a table is not meaningful • All data in a column must be the same type • Every table has a primary key, each column in the primary key must have a value

  36. Relational DBMS • Primary Key and Foreign Key • Relational database use primary keys and foreign keys to allow mapping of information from one table to another • A foreign key is column or group of columns in a table whose value matches those of the primary key of another table • Values in primary key column must be unique e.g. social security number (SSN)

  37. Relational DBMS • Primary Key and Foreign Key • Referential Integrity refers to the integrity of the reference from the primary key in one table to a foreign key in another table.

  38. Relationships between Tables • One-to-One • One-to-Many • Many-to-One • Many-to-Many

  39. Weather table city_name measurement_dt avg_temp Washington 05-01-94 70 Amsterdam 05-01-94 47 Location table Warsaw 05-01-94 43 city_name country_name Tokyo 05-01-94 60 Seattle United States Washington 05-01-94 55 Amsterdam Neatherlands Warsaw Poland Tokyo Japan Washington 05-01-94 Foreign Key Primary Key Primary Key Relational DBMS • Relational Database Example (1-1)

  40. One-to-One

  41. Relational DBMS • Relational Database Example (1-M) Complexes table comp_name N.Shops N.Banks Kotraya 444 70 H.Plaza 555 47 Owner table Komtar 622 43 comp_name shop_owner Kotraya ALi Tan Kotraya Kotraya Lee Kotraya Raman Primary Key Kotraya Nora Foreign Key

  42. One-to-Many

  43. Many-to-One

  44. Data Definition Language (DDL)and Data Manipulation Language (DML) • DDL store files that contains data about data (metadata). For example storage of structure in data dictionary • DML enable users to access or manipulate data (retrieval, insertion, deletion). The part of DML that involves information retrieval is called a query language (QL)

  45. Types of DML • There are two types of DML, procedural and non procedural • Procedural DML: Require the user to specify what data is needed and how to get it • Non-procedural DML: Require the user to specify what data is needed without specify how to get it

  46. Query language (QL) • QL is the language in which a user requests information from the database. Example of QL are relational algebra (procedural) and tuple relational calculus (nonprocedural) • The most common query languages are Structured Query Language (SQL),Query By Example (QBE), and Quel • SQL has gain wide acceptance in commercial products

  47. Relational algebra (RA) • RA is a procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation as a result • The fundamental operations in RA are select(6), project(II), Cartesian product(X) , rename, union(U), and set difference(-) • Other operations include intersection, natural join, division, and assignment

  48. To select those tuples (rows) of the AccCust relation where customer name is “John” it could be written as: 6name = “John” (AccCust) The results may be one or more records or street = “ spring” 6street = “Spring” (AccCust) • Comparisons can done using =, >, <, >=,etc.

  49. Let E1 and E2 be relational algebra expressions. Then the following are all relational algebra expressions: • E1 U E2 union • E1 - E2 set difference(-) • E1 X E2 Cartesian product

  50. Structured Query Language (SQL) • SQL is the standard relational database language • SQL include commands not only restricted to query but to other functions such as defining relation, deleting relations, creating indices, and modifying relation scheme, access right, integrity, and transaction control • Basic structure of an SQL expression consists of three clauses: Select, from, and where

More Related