Introduction to the Database Systems

1
Introduction to the Database Systems

• Chapters 1 and 2

2
Basic Terminology

• Data raw facts
• Field a character or group of characters
  (alphanumeric or numeric) that has a specific
  meaning
• Record a set of one or more logically related
  fields
• File a set of related records

3
File Systems
Input
Application program
Data file
Output
4
Disadvantages of File Approach

• Data and application dependence
• Data isolation
• Data redundancy
• Redundant storage
• Anomalies
• Data inconsistency
• Violation of data integrity

5
Database Definition

• Database
• is a collection of data, typically describing the
  activities of one or more related organizations
• is a collection of logically related files
• a collection of data and information describing
  items of interest to an organization
• DBMS is a software designed to assist in
  maintaining and utilizing large collection of data

6
DBMS Classification Criteria

• Number of supported users
• Database site location
• Type of database use
• Data model

7
Single-user vs. Multi-user DBMS

• Single-user DBMS
• On PC Desktop database
• Multi-user DBMS
• Workgroup database (lt50 users)
• Enterprise database (gt 50 users)

8
Centralized vs. Distributed DBMS

• Centralized DBMS
• Distributed
• Replicated DBMS
• Partitioned DBMS

9
According to the type of use

• Transactional DBMSs
• Support all levels of management decisions
• Optimized for insert and update operations
• Data warehouses
• Support mainly strategic decisions
• Optimized to retrieve large volume of data

10
DBMS Functions

• Data management
• storage management
• integrity management
• manipulation
• Maintaining data independence
• Security management
• Concurrency control
• Recovery management
• Database communication support

11
DBMS Components

• Data model
• Data dictionary (system catalog)
• Meta data
• Database languages
• Data definition language
• Data manipulation language

12
Data Model

• Data model is a collection of high level data
  description constructs that hide many low level
  storage details
• Conceptual model presents logical nature of data
• Implementation models describe how data are
  presented in a given database
• Logical (internal) model
• Physical model
• Schema a description of data in terms of a data
  model

13
Relationships among Data

• Associations among sets of fields
• Types
• 11 (one-to-one)
• 1N (one-to-many)
• NM (many-to-many)

14
Levels of Abstraction in a DBMS
External Schema 2
External Schema 3
External Schema 1
Conceptual (logical) Schema
Physical Schema
Secondary Storage
15
Physical Schema

• Storage space allocation for data and indexes
• Record description for storage (sizes for data
  items)
• Record placement
• Data compression and data encryption techniques

16
Conceptual (Logical) Schema

• Example
• Book (bid char(2), title char(50), author
  char(20), price decimal(5,2), av_q integer)
• Customer (cid char(2), l_n char(25), f_n
  char(25), a_c char(30), phone char(10))
• Order (i_n char(2), bid char(2), cid char(2),
  n_i integer, d_of_sale date)

17
External Schema

• Multiple external schemas
• In relational DBMS, External Schema contains
  views and relations from conceptual schema
• Reduce complexity of DBMS for users
• Support data security
• Support data independence

18
Data Independence
External Schema 2
External Schema 3
External Schema 1
independence
Logical data
Conceptual Schema
Physical Schema
Physical data
independence
Secondary Storage
19
Data Independence (cont)

• Logical data independence the immunity of the
  external schemas to changes in the conceptual
  schema
• Physical data independence the immunity of the
  conceptual schema to changes in the physical
  schema

20
Data Dictionary

• Meta-data
• Descriptions of data items and relationships
  among them
• Integrity constraints
• Data about users and authorization
• Used by DBMS to implement data processing

21
Database Language

• Data Definition Language (DDL)
• External and conceptual schemas
• Data Dictionary (System catalog) with meta-data
• Data Manipulation Language (DML)
• Insert new data
• Modify stored data
• Retrieve stored data
• Query language
• Delete data from database

22
DBMS History

• Generalized Update Access Method (at the
  beginning of 60s)
• Integrated Data Store (at the beginning of 60s)
• GUAM is transformed into Information Management
  System (in the middle of 60s)
• Data Base Task Group (1965)

23
DBMS History (continued)

• Relational data model had been introduced by Codd
  (1970)
• Object DBMSs Object-Oriented and
  Object-Relational
• Multidimensional data model
• Semistructured data model

24
GUAM

• Apollo moon-landing project by North American
  Aviation
• The basic concept smaller components come
  together as parts of larger components, and so
  on, until final product is assembled
  (hierarchical structure)

25
Hierarchical Database Model

• A segment represents one or a few fields
• Segments type determines the list and types of
  fields in the segment
• Segment instance particular data corresponding
  to the segment type
• A parent-child relationship 1N relationship
  between two segment types.

26
Hierarchical Database Model (continued)

• A segment called parent may have several
  subordinate segments called children and
  ordered from left to right
• A child segment has only one parent
• A segment with no parent is called the root
• A segment with no children is called a leaf

27
Hierarchical Schema

• Has only one root
• Every segment except the root participates in
  parent-child relationship as a child
• Hierarchical path of a segment is a sequence of
  segments starting from the root and following
  top-down and left-to right.
• Hierarchical path for the last segment describes
  hierarchical schema
• Hierarchical database comprises one or more
  hierarchical schemas

28
Advantages of Hierarchical Database Model

• Conceptual simplicity
• Efficiently describes large volume of data with
  1N relationships which are fixed over time
• All advantages associated with DBMS

29
Integrated Data Store

• The first general purpose DBMS
• Represents more complex data relationships
• Network Data model

30
Data Base Task Group

• Main DBMSs components (1971)
• The network schema
• The subschema
• A data management language
• Standards for data management languages
• A schema Data Definition Language
• A subschema Data Definition Language
• A Data Manipulation Language

31
Network Database Model

• Two data structures records and sets
• Records
• are classified into record types
• Two record types participate in owner-member 1N
  relationships
• Each record type is assigned
• Type field which distinguishes each record type
• Pointer field for each set type in which it
  participates as an owner or a member

32
Network Database Model (continued)

• Each member may have a few owners
• The relationships are described by set types
• Name of set type
• An owner record type
• A member record type
• To describe NM relationships link record types
  are introduced

33
Advantages of Network DBMSs

• Conceptual simplicity
• NM relationships are easier to implement
• Data access flexibility
• All advantages associated with DBMSs
• Conformance to standards

34
Disadvantages of Hierarchical and Network DBMSs

• Complex implementation
• Complex data manipulation
• Lack of data independence
• Lack of standards (hierarchical model)
• No widely accepted theoretical foundations for
  the models

35
Relational Data Model

• Commercial products appeared in the late 70s -
  earlier 80s
• SQL standard data management language for
  relational DBMSs
• Data manipulation is supported by theory of sets,
  relational algebra and calculus

36
Relational Data Model (continued)

• Basic constructs
• A relation
• A record
• A field (or a column)
• Integrity constraints
• Supports all types of relationships between two
  records which may change over time
• 11
• 1N
• NM

37
Relation (Example)

• Schema
• Book (Bid char(2), Title char(50), Author
  char(20), Price decimal(5,2))
• Instance

38
Advantages of Relational DBMSs

• Improved conceptual simplicity
• Ad hoc query capability
• Ability to describe complex relationships among
  data
• All advantages associated with DBMS
• Theoretical background

39
Object-database Systems

• Object-Relational DBMS an extension of RDBMS to
  support new data types
• Object-oriented DBMS an extension of OO
  programming languages to support complex
  persistent objects

40
Object-relational DBMSs

• Support
• User-defined data types and methods
• Object identity
• Manipulate new data types
• SQL3

41
Object-Oriented DBMSs

• Main components according to Object Data
  management Group
• Object Model
• Object Definition Language (ODL)
• Object Query Language
• OOP language bindings

42
Advantages of DBMS

• Data independence
• Efficient data access
• Data integrity and security
• Data administration
• Reduce application development time

43
Structure of DBMS
Query evaluation Engine
Database
Index Files
Data Dictionary
Data Files
44
Disadvantages of DBMS

• A DBMS is a complex software
• Requires the large amount of primary memory and
  secondary storage
• High cost
• The centralization of all data resources
  increases vulnerability of the system