Database management systems isys 464
Download
1 / 38

Database Management Systems ISYS 464 - PowerPoint PPT Presentation


  • 98 Views
  • Uploaded on

Database Management Systems ISYS 464. Fall 200 6 David Chao. 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 Strategic plans. Definitions.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Database Management Systems ISYS 464 ' - tasya


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Database management systems isys 464

Database Management Systems ISYS 464

Fall 2006

David Chao


Introduction to databases
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

    • Strategic plans


Definitions
Definitions

  • Database: organized collection of logically related data

    • A group of related files

  • Data: stored representations of meaningful objects and events

    • Structured: 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



Traditional file based systems
Traditional File-Based Systems

  • A collection of application programs that perform services for the end-users. Each program defines and manages its own data.



Limitations of the file based approach
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


Problems with program data dependency
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


Example comma delimited file
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?


Sequentially accessing the student file to compute average gpa
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)


Database approach
Database Approach GPA

  • 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


Advantages of the database approach
Advantages of the Database Approach GPA

  • 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


Database management system dbms
Database Management System (DBMS) GPA

  • 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, code generator, report writer, etc.

  • Support Ad Hoc queries


Database management system
Database Management System GPA

  • 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



Database schema ansi sparc architecture
Database Schema GPAANSI-SPARC Architecture

  • 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.

    • E-R models

  • Internal schema

    • Logical schema

      • Underlying implementation and design

        • Relational table design

    • Physical Schema

      • File organizations, indexes


Figure 2-7 Three-schema architecture GPA

Different people have different views of the database…these are the external schema

The internal schema is the underlying design and implementation


Data independence
Data Independence GPA

  • 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.


Three level example employee entity
Three-Level Example GPAEmployee 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, Age=Year(Today()) – Year(DateOfBirth)

    • EmpSalaryView: EmpID, EmpName, Salary


Benefits of using views
Benefits of Using Views GPA

  • 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.


Database application
Database Application GPA

  • 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


The range of database applications
The Range of Database Applications GPA

  • 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


The three components in a database application
The three components in a database application GPA

1. Presentation – user interface

  • Menus, forms, reports, etc

    2. Processing logic

  • Business rules

    3. Database


Categories of database applications
Categories of Database Applications GPA

  • One-Tier

    • Legacy online transaction processing

    • PC database application

  • Two-Tier client/server

    • Client-based presentation.

    • Processing logic is buried either inside the user interface on the client or within the database on the server, or both.

  • Three-Tier, N- tier

    • Processing logic is separated from the interface and database.


SQL queries GPA

Client

Database Server

Results

Database Server: A high processing power computer with advanced DBMS.

Client: A PC that runs database applications.

SQL interface.


Client functions
Client Functions GPA

  • 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.


Database server functions
Database Server Functions GPA

  • 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.


The web as a database application platform
The Web as a Database Application Platform GPA

  • Three-tier architecture

    • Browser, web server, database server, processing logic

  • Advantages:

    • Cross-platform support

    • Graphical user interface



Major datab a se management activities
Major Datab GPAase Management Activities

  • Creating database

  • Updating database

  • Querying database


Creating database
Creating Database GPA

  • 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


Two approaches to database and is development
Two Approaches to Database and IS Development GPA

  • SDLC

    • System Development Life Cycle

    • Detailed, well-planned development process

    • Time-consuming, but comprehensive

    • Long development cycle

  • Prototyping

    • Rapid application development (RAD)

    • Cursory attempt at conceptual data modeling

    • Define database during development of initial prototype

    • Repeat implementation and maintenance activities with new prototype versions


Systems development life cycle see also figures 2 4 2 5

Planning GPA

Analysis

Logical Design

Physical Design

Implementation

Maintenance

Systems Development Life Cycle(see also Figures 2.4, 2.5)



Updating database
Updating Database GPA

  • Insertions, deletions, modifications

    • Insertion only, no deletion

  • Concurrent processing

    • Read/Write

  • Transaction management


Querying database
Querying Database GPA

  • Relational algebra

  • SQL

  • QBE


New developments in database
New Developments in Database GPA

  • Object-Oriented database

  • Object-Relational database

  • Decision support with data warehouse

  • Web based database applications

  • XML database


Course overview
Course Overview GPA

  • 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


ad