Data Modeling and Database Design - PowerPoint PPT Presentation

Loading...

PPT – Data Modeling and Database Design PowerPoint presentation | free to download - id: 982db-MGFiM



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Data Modeling and Database Design

Description:

Data Modeling and Database Design. Chapter 1: Database Systems: Architecture and Components ... Design-Specific ER Model. Coarse and fine level of granularity ... – PowerPoint PPT presentation

Number of Views:109
Avg rating:3.0/5.0
Slides: 36
Provided by: course221
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Data Modeling and Database Design


1
Data Modeling and Database Design
Chapter 1 Database Systems Architecture and
Components
2
Terminology
  • Data
  • Information
  • Metadata

Chapter 1 Database Systems Architecture and
Components
2
3
Information vs Data
  • Compareand

EID Salary
E1 10k E2 12k E3 10k E4 15k
EID
Salary
E1 E2 E3 E4
10k 12k 10k 15k
4
Data Management
  • Creation of data
  • Retrieval of data
  • Update or modification of data
  • Deletion of data
  • For that, data must be accessed and, for the
    ease of access, data must be organized.
  • Quickly, Reliably and Securely.

Chapter 1 Database Systems Architecture and
Components
4
5
Exercise
Assume you want to organize your DVD collection.
The only tool available is an Excel sheet. What
would your columns and rows in Excel look like?
Chapter 1 Database Systems Architecture and
Components
5
6
Exercise (continued)
Maybe like this?
Chapter 1 Database Systems Architecture and
Components
6
7
Data Management
  • Only two approaches for accessing data exist
  • Sequential access
  • Direct access
  • Important
  • A DBMS facilitates access of data without
    burdening the user with details of how the data
    is physically organized.
  • It does this by using a declarative query
    language.

Chapter 1 Database Systems Architecture and
Components
7
8
History of Data Management
Chapter 1 Database Systems Architecture and
Components
8
9
Limitations of File-Processing Systems
  • Lack of Data IntegrityData integrity (data
    values are correct, consistent, complete, and
    current) is often violated in isolated
    environments.
  • Lack of StandardsOrganizations find it hard to
    enforce standards for naming data items as well
    as for accessing, updating, and protecting data.
  • Lack of Flexibility/MaintainabilityFile-processin
    g systems are not amenable to structural changes
    in data and are therefore dependent upon a
    programmer who can either write or modify program
    code.

Chapter 1 Database Systems Architecture and
Components
9
10
Limitations of File-Processing Systems (continued)
  • The limitations to file-processing systems are
    due to
  • Lack of Data Integration Data are separated and
    isolated in a file-processing environment.
  • Lack of Program-Data IndependenceThe structure
    of each file is embedded in the application
    programs.

Chapter 1 Database Systems Architecture and
Components
10
11
Limitations of File-Processing Systems (continued)
Chapter 1 Database Systems Architecture and
Components
11
12
So, What Is Desirable?
  • Integrated data
  • Not data in isolation to be integrated by the
    application program/programmer
  • Data Independence
  • Application program(s) immune to changes in
    storage structure and access strategy
  • Independent user views of data

Chapter 1 Database Systems Architecture and
Components
12
13
History of Data Management
In the 1970s, the Standards Planning and
Requirements Committee (SPARC) of the American
National Standards Institute (ANSI) proposed what
came to be known as the ANSI/SPARC three-schema
architecture conceptual, internal and external
schema.
Chapter 1 Database Systems Architecture and
Components
13
14
Three Perspectives of Metadata in a Database
Chapter 1 Database Systems Architecture and
Components
14
15
Conceptual Schema
  • Core of the architecture
  • Represents the global view of the structure of
    the entire database for a community of users
  • Captures data specification (metadata)
  • Describes all data items and relationships
    between data together with integrity constraints
  • Separates data from the program (or views from
    the physical storage structure)
  • Technology independent

Chapter 1 Database Systems Architecture and
Components
15
16
Why is the Conceptual Model so Important to You?
  • The world wants T-shaped People

http//www.nytimes.com/2009/08/20/education/20INNO
V.html?hpw
17
Internal Schema
  • Describes the physical structure of the stored
    data (e.g., how the data is actually laid out on
    storage devices)
  • Describes the mechanism used to implement access
    strategies (e.g., indexes, hashed addresses,
    etc.)
  • Technology dependent
  • Concerned with the efficiency of data storage and
    access mechanisms

Chapter 1 Database Systems Architecture and
Components
17
18
External Schema
  • Represents different user views, each describing
    portions of the database
  • Technology independent
  • Views are generated exclusively by logical
    references

Chapter 1 Database Systems Architecture and
Components
18
19
Physical and Logical Data Independence
  • Physical Data Independence
  • Definition External views unaffected by changes
    to the internal structure
  • How? Introduction of conceptual schema between
    the external views and the internal (physical)
    schema

Chapter 1 Database Systems Architecture and
Components
19
20
Physical and Logical Data Independence (continued)
  • Logical Data Independence
  • Definition External views unaffected by design
    changes (growth or restructuring) in conceptual
    schema
  • How? External views generated exclusively
    through logical reference to elements in the
    conceptual schema
  • Consequence External views unaffected by
    changes to other external views

Chapter 1 Database Systems Architecture and
Components
20
21
What is a Database System?
  • A self-describing collection of integrated
    records
  • Self-describing
  • The structure of the database (metadata) is
    recorded within the database system not in the
    application programs.
  • Integrated
  • The responsibility for 'integrating' data items
    as needed is assumed by the DBMS instead of the
    programmer.

Chapter 1 Database Systems Architecture and
Components
21
22
Characteristics of a Database System
Database A single, integrated set of
files Database Management System (DBMS) A
collection of general-purpose software that
facilitates the process of defining,
constructing, and manipulating a database for
various applications
DDL vs DML
Chapter 1 Database Systems Architecture and
Components
22
23
An Early View of a Database System
Chapter 1 Database Systems Architecture and
Components
23
24
What is a Database?
  • A database is a self-describing collection of
    interrelated files.
  • Data consists of recorded facts that have
    implicit meaning.
  • Viewed through the lens of metadata, the meaning
    of recorded data becomes explicit.
  • A database is self-describing in that the
    metadata is recorded within the database not in
    applications programs.

Chapter 1 Database Systems Architecture and
Components
24
25
What is a Database Management System (DBMS)?
A DBMS is a collection of general-purpose
software that facilitates the processes of
defining, constructing, and manipulating a
database.
Chapter 1 Database Systems Architecture and
Components
25
26
Components of a DBMS
The major components of a DBMS include one or
more query languages tools for generating
reports facilities for providing security,
integrity, backup and recovery a data
manipulation language for accessing the database
and a data definition language used to define the
structure of data.
Chapter 1 Database Systems Architecture and
Components
26
27
Components of a Database System
Chapter 1 Database Systems Architecture and
Components
27
28
An Example of a Database System
Chapter 1 Database Systems Architecture and
Components
28
29
Types of Database Systems
  • Number of users
  • Single-user
  • Desktop database system
  • Multi-user
  • Workgroup database system
  • Enterprise database system
  • Scope
  • Desktop database system
  • Workgroup database system
  • Enterprise database system

Chapter 1 Database Systems Architecture and
Components
29
30
Some Commercial DBMS
  • IBM DB2 www-306.ibm.com/software/data/db2/
  • Oracle www.oracle.com/database/index.html
  • Microsoft SQL Server www.microsoft.com/sql/def
    ault.mspx

Chapter 1 Database Systems Architecture and
Components
30
31
Important Terms
  • Data Integrity (correct, consistent, complete
    and current)
  • Data Redundancy

Chapter 1 Database Systems Architecture and
Components
31
32
Data Models
  • A model is an expression of observed or
    unobservable reality.
  • Example chair versus department
  • A database represents some aspect of the real
    world that is called the Universe of Interest.
  • The initial step in the design process is the
    requirements specification activity (i.e.,
    business rules).

Chapter 1 Database Systems Architecture and
Components
32
33
Steps in Database Design
  • Conceptual design ---gt Conceptual schema
    capturing user-specified business rules
  • Tool e.g., ER modeling, NIAM modeling
  • Presentation Layer ER ModelER diagram and
    semantic integrity constraints
  • Design-Specific ER ModelCoarse and fine level of
    granularity

Chapter 1 Database Systems Architecture and
Components
33
34
Steps in Database Design (continued)
  • Logical Design ---gt Logical schema
  • Tool normalization
  • Architecture hierarchical, network, or
    relational
  • Physical Design
  • Specifying internal storage structure and access
    strategies

Chapter 1 Database Systems Architecture and
Components
34
35
Steps in Database Design (continued)
Chapter 1 Database Systems Architecture and
Components
35
About PowerShow.com