Introduction to DBMS

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