- 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
- 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)
· 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.
· 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
· 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 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
- 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