22C:144 Database Management Systems

1
22C144 Database Management Systems

• Jun Ni. Ph.D. M.E.
• Department of Computer Science
• The University of Iowa

2
Database Concepts

• File Systems and Databases

3
Outline

• What a database is, what it does, and why
  database design is important
• How modern databases evolved from files and file
  systems
• About flaws in file system data management
• What a DBMS is, what it does, and how it fits
  into the database system
• About types of database systems and database
  models

4
Introducing the Database

• Data versus Information
• Data raw facts
• Being stored and retrieved
• Not be processed to reveal their meaning to the
  user
• For example
• Robcor company has two divisions and the two
  division has 1,380,456 and 1,453,907 invoices,
  respectively.
• Each invoice has invoice number, date, and amount
• The period is from the first quarter of 1997 to
  first quarter of 2002.
• Total 2,834,363 records

5
Data
6
Introducing the Database

• Data versus Information
• Information processed data
• Key to good decision making

7
(No Transcript)
8
Introducing the Database

• Data versus Information
• Data constitute building blocks of information
• Information produced by processing data
• Information reveals meaning of data
• Good, timely, relevant information key to
  decision making
• Good decision making key to organizational
  survival

9
Database Management

• Database is shared, integrated computer structure
  housing related data
• End user data (raw data)
• Metadata (data about data, it contains data
  characteristics and relationships)

10
Database Management

• Database Management System (DBMS) software
  system (collect of software) help to manage the
  data contents
• Manages Database structure
• Controls access to data
• Contains query language

Application software
DBMS
Database
11
Importance of DBMS

• Makes data management more efficient and
  effective
• Query language allows quick answers to ad hoc
  queries
• Provides better access to more and better-managed
  data
• Promotes integrated view of organizations
  operations
• Reduces the probability of inconsistent data

12
DBMS Manages Interaction
13
Database Design

• Importance of Good Design
• Poor design results in unwanted data redundancy
  unnecessary duplication of data
• Poor design generates errors leading to bad
  decisions
• Practical Approach
• Focus on principles and concepts of database
  design
• Importance of logical design

14
Historical Roots of Database Files and File
Systems

• First applications focused on clerical tasks
• Requests for information quickly followed
• File systems developed to address needs
• Data organized according to expected use
• Data Processing (DP) specialists computerized
  manual file systems

15
Historical Roots of Database Files and File
Systems

• Why we need to study files and file system?
• Historically handling data
• Help to understand database design

16
File Terminology

• Data
• Raw Facts
• Field
• Group of characters with specific meaning
• Record
• Logically connected fields that describe a
  person, place, or thing
• File and file folder
• Collection of related records

17
record
field
18

19
Simple File System
Figure 1.5
20
File System Critique

• File System Data Management
• Requires extensive programming in
  third-generation language (3GL) COBOL, Basic,
  and Fortran (what must be done and how it is to
  be done)
• Time consuming
• depends on physically store data
• Makes ad hoc queries impossible
• Make difficult to modify file system (each file
  has its own system)
• Leads to islands of information

21
File System Critique (cont.)

• Data Dependence
• Change in files data characteristics requires
  modification of data access programs
• Must tell program what to do and how to do
• Makes file systems cumbersome from programming
  and data management views
• Structural Dependence
• Change in file structure requires modification of
  related programs

22
File System Critique (cont.)

• Field Definitions and Naming Conventions
• Flexible record definition anticipates reporting
  requirements
• Selection of proper field names important
• Attention to length of field names
• Use of unique record identifiers

23
File System Critique (cont.)

• Data Redundancy
• Different and conflicting versions of same data
• Results of uncontrolled data redundancy
• Data anomalies
• Modification
• Insertion
• Deletion
• Data inconsistency
• Lack of data integrity

24
Database Systems

• Database consists of logically related data
  stored in a single repository
• Provides advantages over file system management
  approach
• Eliminates data inconsistency (lack of data
  integrity), data anomalies, data dependency, and
  structural dependency problems
• Stores data structures, relationships, and access
  paths

25
Database vs. File Systems
26
Database System Environment
27
Database System Environment

• Hardware
• Systems Physical devices
• Computers
• Peripherals
• Network

28
Database System Environment

• Software
• Operating system manages hardware components
• DBMS manages database
• MS Access, SQL Server, Oracle, DB2
• Application and utility software support access
  and manipulate data
• Generate information for decision making
• Help to manage database system

29
Database System Environment

• People (five users)
• System administrator hardware system support
• Database administrator manage DBMS use
• Database designer design database structure
• System analysis and programmers implement
  application programs
• End users

30
Database System Environment

• Procedures
• Instruction and rule that govern the design and
  use of the database system
• Data

31
Database System Types

• Single-user vs. Multiuser Database (user number)
• Desktop database
• Workgroup database
• Enterprise database
• Centralized vs. Distributed (location)
• Use
• Production or transactional
• Decision support or data warehouse (obtain
  information)

32
DBMS Functions

• Objective Guarantee the integrity and
  consistency of data. It has several functions
• Data dictionary management (the definition of
  the data elements and their relationships are
  stored in a data dictionary). It remove data and
  structure dependencies.
• Data storage management structures required for
  data storage
• Data transformation and presentation relieving
  us from the distinct between logical data format
  and physical data format
• Security management
• Multiuser access control (concurrency)

33
DBMS Functions

• Backup and recovery management
• Data integrity management
• Database access language and application
  programming interfaces
• Query language (DDL and DML)
• Database communication interfaces

34
Database Models

• Definition collection of logical constructs used
  to represent data structure and relationships
  within the database
• Conceptual models logical nature of data
  representation if emphasizes on what entity is
  presented it is used for database design as
  blueprint
• Implementation models emphasis on how the data
  are represented in the database

35
Database Models

• Conceptual models include
• Entity-relationship database model (ERDBD)
• Object-oriented model (OODBM)
• Implementation models include
• Hierachical database model (HDBM)
• Network database model (NDBM)
• Relational database model (RDBM)
• Object-oriented database model (ODBM)

36
Database Models (cont.)

• Relationships in Conceptual Models
• One-to-one (11)
• One-to-many (1M)
• Many-to-many (MN)
• Implementation Database Models
• Hierarchical
• Network
• Relational
• Object-Oriented

37
Hierarchical Database Model (HDBM)

• Logically represented by an upside down tree
• Each parent can have many children (segment
  linkage)
• Each child has only one parent

38
Hierarchical Database Model

• Logically represented by an upside down tree
• 1M relationship

39
Hierarchical Database Model

• Hierarchical path (beginning from left)
• Left-list hierarchical path, or preorder
  traversal, or hierarchical sequence
• Re-list sequence, if the segment is frequently
  accessed
• Bank systems commonly use HD model

Final assembly-gtComponent A-gtAssembly A-gt -gt Part
A -gtPart B -gt Component B -gt Component C
Assembly B -gt Part C -gtPart D
40
Hierarchical Database Model

• Bank systems commonly use the HDBM
• customer account can be subject to many
  transactions (1M relationship)
• Relationship is fixed (debiting and crediting)
• Frequently access large amount of transactions

41
Hierarchical Database Model

• Advantages
• Conceptual simplicity relationship between
  layers is logically simple design process is
  simple
• Database security enforced uniformly through the
  system
• Data integrity
• Data independence
• Efficiency in 1M relationships and when uses
  require large numbers of transactions
• Dominant in 1970s , when we used mainframe system
  with large databases

42
Hierarchical Database Model

• Disadvantages
• Complex implementation physical data storage
  characteristics database design is complicated
• Difficult to manage and lack of standards
• Lacks structural independence
• Applications programming and use complexity
  (pointer based)
• Implementation limitations, i.e. especially it
  only handle 1M type of model

43
Network Database Model (NDBM)

• Each record can have multiple parents
• Called by Database Task Group (DBTG) to define
  standards
• Three crucial database components
• Network schema conceptual organization of the
  entire database
• Subschema portion of database as information for
  application programs
• Database management language defining data
  characteristics and data structure
• Schema Data definition language (DDL) define
  schema components
• Subschema Data definition language
• Data manipulating language manipulate data
  content

44
Network Database Model

• Each record can have multiple parents
• Introduce set to describe relationship
• Each set has owner record and member record,
  parallel to parent and child in HDM
• Member may have several owners
• One-ownership

45
Network Database Model

• Member may have several owners

46
Network Database Model

• Advantages
• Conceptual simplicity, just lime HDM
• Handles more relationship types (but all 1M
  relationship)
• Data access flexibility
• Promotes database integrity
• Data independence
• Conformance to standards

47
Network Database Model

• Disadvantages
• System complexity
• Lack of structural independence

48
Relational Database Model (RDBM)

• Lets user or database designer to operate human
  logical environment
• Perceived by user as a collection of tables for
  data storage, while let RDBMS handles the
  physical details.
• Tables are a series of row/column intersections
• Tables related by sharing common entity
  characteristics
• It allows 11, 1M, MN relationships

49
Relational Database Model
50
(No Transcript)
51
Relational Database Model

• Advantages
• Structural independence data access path is is
  irrelevant to database design change structure
  will not affect the database
• Improved conceptual simplicity
• Easier database design, implementation,
  management, and use
• Ad hoc query capability with SQL (4GL is added)
• Powerful database management system

52
Relational Database Model

• Disadvantages
• Substantial hardware and system software overhead
• Poor design and implementation is made easy
• May promote islands of information problems

53
Entity Relationship Database Model (ERDBM)

• Complements the relational data model concepts
• ERDBM introduces a relational graphic
  representation
• ERDBM is based on several components
• Entity, tabled entity (in RDM)
• Entity and entity set, a collection of like
  entities
• Each entity has attributes to describe the
  entity, which is similar to field in table
• Relationship and connection

54
Entity Relationship Database Model (ERDBM)

• Represented in an entity relationship diagram
  (ERD) Chens ERD model and Crows Foot ERD
• Based on entities, attributes, and relationships

55
Entity Relationship Database Model
connection
entity
relationship
56
(No Transcript)
57
Entity Relationship Database Model

• Advantages
• Exceptional conceptual simplicity
• Visual representation
• Effective communication tool
• Integrated with the relational database model

58
Entity Relationship Database Model

• Disadvantages
• Limited constraint representation
• Limited relationship representation (internal
  relationship can not be depicted multiple
  relationships)
• No data manipulation language (no complete)
• Loss of information content
• What will be the next one?

59
Object-Oriented Database Model (OODBM)

• Semantic Data model (SDM)-gtObject-oriented Data
  Model (OODM)
• Object-oriented concept
• Objects or abstractions of real-world entities
  are stored
• Attributes describe properties
• Collection of similar objects is a class, similar
  to entity set but contains procedure methods
• Methods represent real world actions of classes
• Classes are organized in a class hierarchy
• Inheritance is the ability of object to inherit
  attributes and methods of classes above it

60
Object-Oriented Database Model (OODBM)

• Contains implementation and procedure operation
  information for more complicated data such as
  graphics, video, and other metadata
• Support transaction and information
• Reusability
• Portable to powered computing system

61
Object-Oriented Database Model
62
OO Database Model

• Advantages
• Adds semantic content
• Visual presentation includes semantic content
• Database integrity
• Both structural and data independence

63
OO Database Model

• Disadvantages
• Lack of OODM
• Complex navigational data access
• Steep learning curve
• High system overhead slows transactions

64
(No Transcript)
65
Database Models and the Internet

• Characteristics of Internet age databases
• Flexible, efficient, and secure Internet access
• Easily used, developed, and supported
• Supports complex data types and relationships
• Seamless interfaces with multiple data sources
  and structures

66
Database Models and the Internet

• Simplicity of conceptual database model
• Many database design, implementation, and
  application development tools
• Powerful DBMS GUI make DBA job easier