Database Management System (DBMS) (2022)

About This Presentation

Title:

Description:

... need to understand the underlying physical layout of the data to access data ... The creator of the table becomes the owner ... – PowerPoint PPT presentation

Number of Views:14076

Avg rating:3.0/5.0

Slides: 78

Provided by: fakul2

Category:

Tags:dbms | creator | database | layout | management | system

(Video) Introduction to DBMS | Database Management System

less

Transcript and Presenter's Notes

Title: Database Management System (DBMS)

1

  • Database Management System (DBMS)
  • By Dr. Mohamed Yagoub Mohamed
  • E-mail myagoub_at_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
Why we need database?

(Video) Introduction to Database Management Systems

Principles of GIS
6
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
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
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
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

  • Let D1 gt set of all account number
  • D2gt 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
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
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 studentPractical 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

(Video) What Is Database Management System ? | What Is DBMS ?

  • 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
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
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 , gt, lt, gt,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

51

(Video) Database Management System (DBMS) - Introduction

  • A typical SQL query has the form select A1, A2,
    ..., An from r1, r2, .., rm where
    P Each Ai represents an attribute and each
    ri a relation. P is a predicate (selection). The
    list of attributes A1..An can be replaced by ()
    to select all attributes
  • The result of an SQL query is a relation (table)

52

  • The keyword distinct is used after select to
    force the elimination of duplicates. For example,
    find all customer names having a balance equal
    6000
  • select distinct customer-name from
    AccCust where balance 6000
  • SQL supports union, intersect, and minus

53

  • SQL uses logical connectives and, or, and not and
    operators (, -, , /) on values from tuples
  • e.g.
  • select distinct customer-name from
    AccCust where balance between 600 and 700

54
Setting up User Accounts and Privileges

  • Typical Roles and Privileges

55
Creating Tables

  • In a relational database, data is stored in
    tables
  • Users with connect privileges can create tables
  • The creator of the table becomes the owner
  • Users cannot access the tables unless permission
    is explicitly given
  • Users can modify the database by deletion i.e.
    remove selected tuples, insertion i.e. insert
    tuples, or update i.e change a value in a tuple
    without changing all values in the tuple

56
Field types

  • INTEGERS e.g. number of population, age
  • REAL (DECIMAL) e.g. income, salary
  • CHARACTER e.g. names, description
  • DATES e.g. date of flood
  • IMAGES (Multimedia) e.g. image of flood (.tif)
  • SOUND e.g. Sound of thunder (.wav)
  • MOVIE e.g. recording film (.avi, .mov)

57

  • Granting Object Privileges
  • Only owners of a database object can grant object
    privileges to other users
  • Only users with DBA privileges can grant object
    privileges to roles

58
Creating and Displaying Indexes

  • Indexes are optional structures that can be
    created on any column or set of columns in a
    table to speed up viewing and retrieval of data
    rows.
  • An index is a list of keywords with the location
    of the keyword information
  • Users can create indexes

59
Index Concept

  • Index is conceptually similar to an index at the
    end of a book or cataloging books in a library
  • Database index is not a physical structure but a
    logical structure. The RDBMS, not the user uses
    the database index. The DBMS chooses the best
    index to use in the table.
  • Index can dramatically speed up data access but
    will require additional space and may slow down
    certain SQL operations

60
Exporting and Importing Data

  • Export and Import utilities allows data to be
    moved in and out of the native database. For
    Example, converting database tables to other
    format such Comma Delimited Format (CDF), Space
    Delimited Format (SDF), .dbf, and ASCII flat
    files (Text)

61
Relational Database design

  • In the database design considerations must be
    paid to
  • Repetition of information (Normalization)
  • Loss of information
  • Number of fields and records required, some
    RDBMS supports limited number of fields and
    records (e.g. 255 fields)
  • Nature of database, some RDMBS supports only
    textual data, some supports multimedia (sound,
    image, etc.)

62
Database quality

  • Check accuracy of attribute database by
    Performing quality assurance / quality control
    (QA/QC) on the database by
  • Check correctness of data
  • Check mis-typing errors

63
Storage Capacity Terminology
NCGIA
64
Boolean expressions

  • AND
  • OR
  • NOT

65
AND
66
Boolean Operators
both expressions are true
Elevation gt 100 AND Veg Type conif
67
OR
68
Boolean Operators
at least one expression is true
Rainfall gt 2 OR Veg type decid
69
NOT
Negates the Boolean value. Returns TRUE if the
Boolean is FALSE Returns FALSE if the Boolean is
TRUE
70
Boolean Operators
Excludes information
NOT pH lt 6.5
71
Operators
- equals - greater than - less than - not equal to
72
Operators
- greater than or equal to - less than or equal
to - expressions enclosed in parentheses are
evaluated first
73
Query Syntax
around field names _ in place of spaces in
field names around string values ( ) grouping
and evaluation order multiple character
wildcard ? Single character wildcard Date
format yyyymmdd
74
Query Syntax Examples
(Roof_type "tile) (Owner_age gt 65 and
Income lt 20000 ) (Type_use "res" and
Const_date lt 1955) (State_name
A) (Name ?athy) (Date 19991027)
75
Common Query Syntax Errors
Roof_type "tile) (Owner_age gt 65) and
Income lt 20000 ) (Type_use
res) (Const_date lt 1955)
76
Query Syntax
Salisbury Princess Anne Hebron Willards
Correct Query Syntax (City Salisbury OR
City Princess Anne OR City Hebron OR
City Willards)
Incorrect Query Syntax (City Salisbury OR
Princess Anne OR Hebron OR Willards)
77
References

  • Bailey, T. C. (1994). A review of statistical
    spatial analysis in geographical information
    systems. In Fotheringham, A. S. and P. A.
    Rogerson (eds.). Spatial analysis and GIS. Taylor
    Francis Ltd., London, UK.
  • Batini, C. S. and S. B. Navathe (1992).
    Conceptual database design. The Benjamin/
    Cummings Publishing, California, USA.
  • Hoffer, J. A., J. S. Valacich and J. F. George
    (1996). Modern systems analysis and design. The
    Benjamin/Cummings Publishing, California, USA.
  • Michael, A. and C. Smith (1996). Teach yourself
    database programming with Visual Basic 4 in 21
    days. Sams Publishing, Indiana, USA

User Comments ()

FAQs

What is database management system in DBMS? ›

Database Management Systems (DBMS) are software systems used to store, retrieve, and run queries on data. A DBMS serves as an interface between an end-user and a database, allowing users to create, read, update, and delete data in the database.

What are the 4 types of DBMS? ›

Types of DBMS
  • Relational database.
  • Object oriented database.
  • Hierarchical database.
  • Network database.
Jul 3, 2021

What is DBMS with example? ›

The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs. Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.

What is DBMS and its types? ›

There are three main types of DBMS data models: relational, network, and hierarchical. Relational data model: Data is organized as logically independent tables. Network data model: All entities are organized in graphical representations. Hierarchical data model: Data is organized into a tree-like structure.

What is DBMS and its features? ›

Database Management System can be defined as a software that is used to enter, store, manipulate and retrieve data that is organised in the form of information in the databases. Database Management System, also known as DBMS, is a software that helps in creation and maintenance of databases.

What is DBMS application? ›

DBMS is most useful to these companies to store the call details and monthly postpaid bills. Credit card transactions − The purchase of items and transactions of credit cards are made possible only by DBMS. A credit card holder has to know the importance of their information that all are secured through DBMS.

What are 3 database examples? ›

MySQL, SQL Server, MongoDB, Oracle Database, PostgreSQL, Informix, Sybase, etc. are all examples of different databases. These modern databases are managed by DBMS. Structured Query Language, or SQL as it is more widely known, is used to operate on the data in a database.

What is the need of DBMS? ›

A DBMS provides mechanisms to deal with this kind of data inconsistency while allowing users to access data concurrently. A DBMS implements ACID(atomicity, durability, isolation,consistency) properties to ensure efficient transaction management without data corruption.

What is difference between database and DBMS? ›

A database is a collection of connected information about people, locations, or things. A database management system (DBMS) is a collection of programs that allow you to create, manage, and operate a database.

What is a primary key in DBMS? ›

A primary key is the column or columns that contain values that uniquely identify each row in a table. A database table must have a primary key for Optim to insert, update, restore, or delete data from a database table.

What are components of DBMS? ›

Hardware, Software, Data, Database Access Language, Procedures and Users all together form the components of a DBMS.

What is difference between DBMS and Rdbms? ›

What is the Difference between DBMS and RDBMS? DBMS stands for Database Management System, and RDBMS is the acronym for the Relational Database Management system. In DBMS, the data is stored as a file, whereas in RDBMS, data is stored in the form of tables.

What is database management system PDF? ›

A database management system (DBMS) is a software system that allows access. to data contained in a database. The objective of the DBMS is to provide a convenient. and effective method of defining, storing and retrieving the information contained in the. database.

Videos

1. Introduction To DBMS - Database Management System | What Is DBMS? | DBMS Explanation | Simplilearn
(Simplilearn)
2. Introduction to Database Management Systems (DBMS)
(Neso Academy)
3. 1.0: Database Management Systems (DBMS) Full Course!
(Coding Coach)
4. What is Database & SQL?
(Guru99)
5. Database Management System (DBMS)
(MathAddiction)
6. 7. Database Management Systems DBMS
(Audit Academy)

You might also like

Latest Posts

Article information

Author: Ms. Lucile Johns

Last Updated: 08/28/2022

Views: 6422

Rating: 4 / 5 (61 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Ms. Lucile Johns

Birthday: 1999-11-16

Address: Suite 237 56046 Walsh Coves, West Enid, VT 46557

Phone: +59115435987187

Job: Education Supervisor

Hobby: Genealogy, Stone skipping, Skydiving, Nordic skating, Couponing, Coloring, Gardening

Introduction: My name is Ms. Lucile Johns, I am a successful, friendly, friendly, homely, adventurous, handsome, delightful person who loves writing and wants to share my knowledge and understanding with you.