Database%20Management%20Systems%20ISYS%20464 - PowerPoint PPT Presentation

View by Category
About This Presentation
Title:

Database%20Management%20Systems%20ISYS%20464

Description:

Database Management Systems ISYS 464 David Chao – PowerPoint PPT presentation

Number of Views:69
Avg rating:3.0/5.0
Slides: 35
Provided by: COB84
Learn more at: http://online.sfsu.edu
Category:

less

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

Title: Database%20Management%20Systems%20ISYS%20464


1
Database Management Systems ISYS 464
  • David Chao

2
Introduction to Databases
  • The most important component in an information
    system
  • Created to support all levels of business
    operations
  • Day-to-day operations
  • TPS, CRM, ERP
  • Decision-makings
  • DSS, data warehouse
  • Strategic plans

3
Definitions
  • Database organized collection of logically
    related data
  • A group of related files
  • Data stored representations of meaningful
    objects and events
  • Structured Fixed format record
  • numbers, text, dates
  • Unstructured images, video, documents
  • Information data processed to increase knowledge
    in the person using the data
  • Metadata data that describes the properties and
    context of user data
  • data about data

4
Example of Metadata
5
Traditional File-Based Systems
  • A collection of application programs that perform
    services for the end-users. Each program defines
    and manages its own data.

6
Example
7
Limitations of the File-Based Approach
  • Duplication of data
  • Data inconsistency
  • Limited data sharing
  • Program-data dependence
  • When file structure changed, all programs that
    access the file must be modified to conform to
    the new file structure.
  • The definition of the data is embedded in the
    program.
  • Fixed queries
  • No facilities for asking unplanned, ad hoc queries

8
Problems with Program-Data Dependency
  • Each application program needs to include code
    for the metadata of each file
  • Each application program must have its own
    processing routines for reading, inserting,
    updating, and deleting data

9
Example Comma-Delimited File
  • It stores each data item with a comma separating
    each item and places double quotes around string
    fields.
  • Student file with fields SID, Sname, and GPA
  • S5, Peter, 3.0
  • S1, Paul, 2.5
  • Questions
  • How many students?
  • What is average GPA?

10
Sequentially Accessing the Student File to
Compute Average GPA
Dim fileNumber, stCounter As Integer Dim
SID, SNAME As String Dim gpa, sumGpa As
Double fileNumber FreeFile()
FileOpen(fileNumber, "c\stdata.txt",
OpenMode.Input) Do While Not
EOF(fileNumber) Input(fileNumber,
SID) Input(fileNumber, SNAME)
Input(fileNumber, gpa) sumGpa
gpa stCounter 1 Loop
MessageBox.Show(sumGpa / stCounter.ToString)
11
Database Approach
  • Central repository of shared data
  • The database holds not only the data but also a
    description of the data.
  • System catalog (repository , data dictionary, or
    metadata)
  • A central location where data descriptions are
    stored.
  • Data about data
  • Program-data independence

12
Advantages of the Database Approach
  • Program-data independence
  • The separation of data descriptions from the
    application programs that use the data.
  • Allows the data to change without changing the
    application programs.
  • Planned data redundancy
  • Improved data consistency
  • Improved data sharing
  • Enforcement of standards

13
Database Management System (DBMS)
  • A software that enables users to define, create,
    maintain, and control access to the database.
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Control access
  • Security, integrity, concurrent access, recovery,
    support for data communication, etc.
  • Utility services
  • File import/export, monitoring facilities, etc.
  • Support Ad Hoc queries

14
Database Management System
  • A software system that is used to create,
    maintain, and provide controlled access to user
    databases

Order Filing System
Central database Contains employee, order,
inventory, pricing, and customer data
Invoicing System
DBMS
Payroll System
DBMS manages data resources like an operating
system manages hardware resources
15
Evolution of DB Systems
16
Database Schema
  • External Schema
  • User Views
  • Subsets of Conceptual Schema
  • Conceptual Schema
  • This level describes what data is stored in the
    database and the relationships among the data.
  • View of the data administrator
  • E-R models
  • Internal schema
  • Logical schema
  • Underlying implementation and design
  • Relational table design
  • Physical Schema
  • File organizations, indexes

17
Figure 2-7 Three-schema architecture
Different people have different views of the
databasethese are the external schema
The internal schema is the underlying design and
implementation
18
Data Independence
  • Data independence means that upper levels are
    unaffected by changes to lower levels.
  • Logical data independence
  • Changes to the conceptual level, such as the
    addition of new entities, attributes, or
    relationships, should be possible without having
    to change the existing external level design.
  • Physical data independence
  • Changes to the physical level, such as using a
    different file organization, indexes, should be
    possible without having to change the conceptual
    level design.

19
Three-Level Example Employee Entity
  • Conceptual design
  • Employee entity with attributes EmpID, EmpName,
    DateOfBirth, Salary, and Sex.
  • Internal level
  • Logical schema
  • EmpID 4 characters
  • EmpName 30 characters
  • DateOfBirth Date field 8 bytes
  • Salary Number(7,2)
  • Sex 1 character
  • Physical schema
  • Record size 4 30 8 7 1 50 bytes
  • Sequential file with index on EmpID field
  • External level
  • EmpAgeView
  • EmpID, EmpName, AgeYear(Today())
    Year(DateOfBirth)
  • EmpSalaryView EmpID, EmpName, Salary

20
Benefits of Using Views
  • Views provide a level of security.
  • Views provide a mechanism to customize the
    appearance of the database.
  • Views provide a consistent, unchanging picture of
    the database, even if the underlying database is
    changed.

21
Database Application
  • It is a program that interacts with the database
    at some point in its execution by issuing an
    appropriate request (typically an SQL statement)
    to the DBMS.
  • Database programming

22
The Range of Database Applications
  • Personal databases
  • Desktop, PDA/Smart Phone
  • Workgroup databases
  • Departmental/divisional databases
  • Enterprise database
  • Enterprise Resource Planning (ERP)
  • Integrate all enterprise functions
    (manufacturing, finance, sales, marketing,
    inventory, accounting, human resources)
  • Data Warehouse
  • Integrated decision support system derived from
    various operational databases

23
The three components in a database application
  • 1. Presentation user interface
  • Menus, forms, reports, etc
  • 2. Processing logic
  • Business rules
  • 3. Database

24
SQL queries
Client
Database Server
Results
Database Server A high processing power computer
with advanced DBMS. Client A PC that runs
database applications. SQL interface.
25
Client Functions
  • Manages the user interface.
  • Accepts and checks syntax of user input.
  • Implements business rules.
  • Generates database requests and transmits to
    server.
  • Passes response back to user.

26
Database Server Functions
  • Checks authorization.
  • Accepts and processes database requests from
    clients.
  • Ensures integrity constraints not violated.
  • Performs query/update processing and transmits
    response to client.
  • Provides concurrent database access, transaction
    management, and recovery control.

27
The Web as a Database Application Platform
  • Three-tier architecture
  • Browser, web server, database server, processing
    logic
  • Advantages
  • Cross-platform support
  • Graphical user interface

28
Figure 2-9 Three-tiered client/server database
architecture
29
Major Database Management Activities
  • Creating database
  • Updating database
  • Querying database

30
Creating Database
  • Analysis
  • System analysis
  • Data Flow Diagram, UML
  • Data modeling
  • ERD
  • Design
  • Maps the data model on to a target database
    model.
  • Implementation Efficiently store and retrieve
    data
  • File organization and index

31
Updating Database
  • Insertions, deletions, modifications
  • Update pattern
  • Insertion only, no modification
  • Concurrent processing
  • Read/Write
  • Transaction management

32
Querying Database
  • Relational algebra
  • SQL
  • QBE

33
New Developments in Database
  • Object-Oriented database
  • Object-Relational database
  • Decision support with data warehouse
  • Web based database applications
  • XML database

34
Course Overview
  • An introduction to the three-level database
  • Conceptual level
  • Data modeling, ERD, Normalization
  • Physical level
  • File organizations and index
  • External level
  • Relational algebra, SQL, QBE
  • Other database management technologies
About PowerShow.com