Tuesday, August 12, 2008

Introduction to DBMS

© Moreniche

Managing Data

  • Data is an Important organizational asset
  • Organizations require tools for effective management of their large and complex datasets
  • Badly managed data is a liability - the cost of acquiring & managing it far exceeding the value derived from it

What is DBMS?

  • A very large, integrated collection of data.
  • Models real-world enterprise.
  • Entities (e.g., students, courses)
  • Relationships (e.g., Madonna is taking CS564)
  • A Database Management System (DBMS) is a software package designed to assist in maintaining & utilizing large collections of data.

File System Vs DBMS

  • Application using files need to swap large datasets between main memory and secondary storage
  • Special programs would be needed to identify all data items
  • Special code for different queries
  • Multiple concurrent users could result in inconsistency
  • Crash recovery
  • Security and access control

Advantages of DBMS

  • Data Independence
  • Efficient data access
  • Data integrity and security
  • Data administration
  • Concurrent access and Crash recovery
  • Reduced application development time

Describing & storing data in DBMS

  • A DBMS allows a user to define the data to be stored in terms of a data model
  • A data model is a collection of high-level data description constructs that hide several lowlevel storage details
  • The relational model of data is the most widely used model today.

Main concept: relation, basically a table with rows and columns.

  • A description and storage of data in terms of the data model is called a schema
  • In the relational model, the schema for a relation specifies its name, name & type of each field

Students(sid: string, name: string, login: string,age: integer, gpa:real)

Data Independence

· Application programs are insulated from changes in the way the data is structured and stored

· Data independence is achieved through use of three levels of data abstraction

Levels of abstraction

The data in a DBMS is described at three levels of abstraction

Levels of abstraction

  • Views describe how users see the data.
  • Conceptual(logical) schema defines logical structure
  • Physical schema describes the files and indexes used.

Ø *Schemas are defined using DDL

Ø *Data is modified/queried using DML.

Conceptual Schema

· In relational DBMS, the conceptual schema describes all relations that are stored in the database

Ø Students(sid: string, name: string, login: string, age: integer, gpa:real)

Ø Courses(cid: string, cname:string, credits:integer)

Ø Enrolled(sid:string, cid:string, grade:string)

· These relations contain information about entities, such as Students & courses and about relationships, such as Enrollment

Physical Schema

· The physical schema summarizes how relations described in the conceptual schema are actually stored on secondary storage devices such as disks and tapes

· This schema decides :

Ø The file organizations to be used to store the relations

Ø Indexes needed to speed up data retrieval operations

External schema

· External schema allows data access to be customized at the level of individual users or group of users

· Any given database has exactly one conceptual & physical schemas but it may have a number of external schemas

· Each external schema consists of a collection of one or more Views and relations from the conceptual schema

Data Independence

  • Changes to Conceptual schema need not affect the users view of data - the definition
  • of the view relation can be modified so that users get the same view as before
  • Users can be shielded from changes in the logical structure of the data
  • Logical data independence: Protection from changes in logical structure of data.
  • The conceptual schema hides details such as how the data is actually laid out on the disk, the file structure, and the choice of indexes
  • Physical data independence: Protection from changes in physical structure of data.

Architecture of DBMS

People who work with databases

  • Database Implementors
  • End users
  • Database application developers
  • Database administrator(DBA)
    • DBA is responsible for:

Ø Design of the conceptual &physical schemas

Ø Security & authorization

Ø Data availability & recovery from failures

Ø Database tuning