overview dbms
Post on 21-Jul-2016
48 Views
Preview:
DESCRIPTION
TRANSCRIPT
Database Management Database Management SystemsSystems
ByBy
Dinesha LDinesha LLecturer, Dept. Of CSELecturer, Dept. Of CSE
SSIT, TumkurSSIT, Tumkur
Chapter-1Chapter-1
ObjectivesObjectives An Overview of Database Management An Overview of Database Management Database Database DBMS DBMS Database Systems Database Systems Why Use Database Why Use Database Database Architecture Database Architecture An Example of the Three Levels An Example of the Three Levels Schema Schema Data Independence Data Independence Types Of Database Models Types Of Database Models Database Design Phases Database Design Phases
Definitions:Definitions:
Data: Data: Known facts that can be recorded Known facts that can be recorded and that have implicit meaningand that have implicit meaning
Database:Database: Collection of related data Collection of related data– Ex. the names, telephone numbers and Ex. the names, telephone numbers and
addresses of all the people you knowaddresses of all the people you know Database Management System:Database Management System: A A
computerized record-keeping systemcomputerized record-keeping system
Goals of a Database Management System:Goals of a Database Management System:– To provide an efficient as well as a convenient To provide an efficient as well as a convenient
environment for accessing data in a databaseenvironment for accessing data in a database– Enforce information security: database security, Enforce information security: database security,
concurrency control, crash recoveryconcurrency control, crash recovery
It is a general purpose facility for:It is a general purpose facility for:– Defining Defining database database
– ConstructingConstructing database database
– Manipulating Manipulating databasedatabase
History of DBMSHistory of DBMS 19601960 – First DBMS designed by Charles – First DBMS designed by Charles
Bachman at GE. IBMs Information Management Bachman at GE. IBMs Information Management System (IMS)System (IMS)
19701970 – Codd introduced the RDBMS – Codd introduced the RDBMS 19801980 – Relational model became popular and – Relational model became popular and
accepted as the main database paradigm. SQL, accepted as the main database paradigm. SQL, ANSI SQL, etc.ANSI SQL, etc.
1980 to 19901980 to 1990 – New data models, powerful – New data models, powerful query languages, etc. Popular vendors are query languages, etc. Popular vendors are Oracle, SQL Server, IBMs DB2, Informix, etc.Oracle, SQL Server, IBMs DB2, Informix, etc.
Various types of data: Images, Text, Various types of data: Images, Text, complex queries, Data Mining, etc.complex queries, Data Mining, etc.
Enterprise Resource Planning (ERP)Enterprise Resource Planning (ERP) Management Resource Planning (MRP)Management Resource Planning (MRP) Database in Web technologiesDatabase in Web technologiesCurrent Database trends:Current Database trends: Multimedia databasesMultimedia databases Interactive videoInteractive video Streaming dataStreaming data Digital LibrariesDigital Libraries
DBMS FunctionsDBMS Functions
Data DefinitionData Definition Data ManipulationData Manipulation Data Security and IntegrityData Security and Integrity Data Recovery and ConcurrencyData Recovery and Concurrency Data DictionaryData Dictionary Performance Performance
Early File Systems Vs DBMSEarly File Systems Vs DBMS
Catalog in DBMS. Data definition in file Catalog in DBMS. Data definition in file systems is part of application programs.systems is part of application programs.
Program-Data independenceProgram-Data independence
ViewsViews
Sharing and Transaction processingSharing and Transaction processing
program-1
data description-1
program-2
data description-2
program-3
data description-3
File-1
File-2
File-3
Application program-1 with data semantics
Application program-2 with data semantics
Application program-3 with data semantics
Description
Manipulation
Control
… . . .
Database
File System approach
DBMS approach
Redundancy can be reducedRedundancy can be reduced Inconsistency can be avoided Inconsistency can be avoided Data can be sharedData can be shared Standards can be enforcedStandards can be enforced Security restrictions can be appliedSecurity restrictions can be applied Integrity can be maintainedIntegrity can be maintained Data independence can be providedData independence can be provided Backup and RecoveryBackup and Recovery
Benefits of database approachBenefits of database approach
Describing and Storing Data Describing and Storing Data in DBMSin DBMS
Data ModelData ModelA data model is a collection of high-level A data model is a collection of high-level data description constructs that hide many data description constructs that hide many low-level storage details.low-level storage details.
Relational Data ModelRelational Data Model Semantic Data Model – ER ModelSemantic Data Model – ER Model
Relational ModelRelational Model
A A RelationRelation is a set of records and attributes. is a set of records and attributes. Also known as tuples and columns.Also known as tuples and columns.
A A SchemaSchema is the description of data in terms is the description of data in terms of a data model.of a data model.Eg. Student(Eg. Student(RegNoRegNo : String, : String, NameName : String, : String, SemSem : Integer, : Integer, BranchBranch : String) : String)
Student RelationStudent Relation
RegNo RegNo NameName Sem Sem Branch Branch 00160016 N. DeepakN. Deepak 33 CSE CSE 06740674 K. GopalK. Gopal 55 MECH MECH 29012901 KanchanaKanchana 22 CSE CSE
Other Data ModelsOther Data Models
Relational Model – DB2, Oracle, Informix, Relational Model – DB2, Oracle, Informix, Sybase, MS-Access, Foxbase, Paradox, etc.Sybase, MS-Access, Foxbase, Paradox, etc.
Hierarchical Model – IMS DBMSHierarchical Model – IMS DBMS Network Model – IDS & IDMSNetwork Model – IDS & IDMS Object-Oriented Model – ObjectStore & Object-Oriented Model – ObjectStore &
VersantVersant Object-Relational Model – Products from Object-Relational Model – Products from
IBM, Oracle, ObjectStore, Versant.IBM, Oracle, ObjectStore, Versant.
Three-Layer AbstractionThree-Layer Abstraction
Physical Schema
Conceptual Schema
External Schema - 2External Schema - 1 External Schema - 3
Disk
Conceptual SchemaConceptual Schema Describes the stored data in terms of the data model Describes the stored data in terms of the data model
of the DBMS. This leads to of the DBMS. This leads to conceptual database conceptual database designdesign..
Example: Example: StudentStudent(RegNo:Integer, Name:String, (RegNo:Integer, Name:String,
Sem:Integer, Branch:String)Sem:Integer, Branch:String)FacultyFaculty(Fid:Integer, FName:String, Salary:Float)(Fid:Integer, FName:String, Salary:Float)CourseCourse(CourseNo:Integer, CName:String, (CourseNo:Integer, CName:String,
Credit:Integere, Dept:String)Credit:Integere, Dept:String)SectionSection(SecId:Integer, CourseNo:Integer, (SecId:Integer, CourseNo:Integer,
Sem:Integer, Year:Integer, Instructor:String)Sem:Integer, Year:Integer, Instructor:String)GradeReportGradeReport(RegNo:Integer, SecId:Integer, (RegNo:Integer, SecId:Integer,
Grade:Char)Grade:Char)
Physical SchemaPhysical Schema
Describes the actual storage details of the Describes the actual storage details of the relations described in conceptual schema.relations described in conceptual schema.
Primary indexing, sequential, binary, Primary indexing, sequential, binary, secondary indexing, etc.secondary indexing, etc.
This leads to the This leads to the physical database designphysical database design..
External SchemaExternal Schema
Describes several views of the database Describes several views of the database based on the database model.based on the database model.
Several external schemas are possible for a Several external schemas are possible for a single database.single database.
Each view is based upon the user Each view is based upon the user requirements.requirements.
Example:Example:StdGrade(RegNo:Integer, Name:String, StdGrade(RegNo:Integer, Name:String, Sem:Integer, Grade:Char)Sem:Integer, Grade:Char)
Program-Data IndependenceProgram-Data Independence The data independence is the ability to change
the schema at one level of a database system with out changing the schema at a higher level.
Logical data IndependenceLogical data IndependenceIt is the ability to change the schema at one level of a database system without changing the external schema or application programs, is called as the logical data independence. With out changing the application programs, one can change the logical schema.
ExampleExampleSuppose the Faculty relation is modified Suppose the Faculty relation is modified as:as:Faculty_PublicFaculty_Public(Fid:Integer, FName:String, (Fid:Integer, FName:String, Office:Integer)Office:Integer)
Faculty_PrivateFaculty_Private(Fid:Integer, Salary:Float)(Fid:Integer, Salary:Float)
Any view designed before this modification Any view designed before this modification can still retrieve the data with little can still retrieve the data with little modification (relation name) and obtain the modification (relation name) and obtain the same answer.same answer.
Physical data independencePhysical data independence
There are occasions for changing the There are occasions for changing the internal structures for improved internal structures for improved performance of the retrieval of data.performance of the retrieval of data.
Any change introduced to the internal Any change introduced to the internal schema or physical schema will not affect schema or physical schema will not affect the other schemas.the other schemas.
Database LanguagesDatabase Languages
DDL – Data Manipulation LanguageDDL – Data Manipulation Language SDL – Storage Definition LanguageSDL – Storage Definition Language VDL – View Definition LanguageVDL – View Definition Language DML – Data Manipulation LanguageDML – Data Manipulation Language
(For data manipulations like (For data manipulations like insertion, deletion, update, insertion, deletion, update, retrieval, etc.) retrieval, etc.)
Transaction ManagementTransaction Management
Atomic operation – Handling concurrent Atomic operation – Handling concurrent execution of transactions from several execution of transactions from several users.users.
Example: Reservation systems, Banking Example: Reservation systems, Banking systems.systems.
Transaction failures and recovery.Transaction failures and recovery. Locking protocols.Locking protocols. Log (WAL – Write Ahead Log)Log (WAL – Write Ahead Log)
DBMS ArchitectureDBMS Architecture
Plan Executor
OptimizerOperator Evaluator
ParserSQL Engine
Recovery ManagerLock
Manager
TxManager
Files & Access
Buffer Manager
Disk Space Manager DBMS
CatalogData File Database
Web Forms Front-End SQL I/f
SQL Commands
People Who Work with People Who Work with DatabasesDatabases
–Database ImplementersDatabase Implementers
–End UsersEnd Users
–Application ProgrammersApplication Programmers
–DBADBA
End UsersEnd Users Casual usersCasual users
These are people who use the database occasionally.These are people who use the database occasionally. Naive usersNaive users
These are users who constantly querying and updating These are users who constantly querying and updating the database.the database.Eg. Reservation Clerks of Airline, Railway, Hotel, etc.Eg. Reservation Clerks of Airline, Railway, Hotel, etc.Clerks at receiving station of Courier service, Insurance Clerks at receiving station of Courier service, Insurance agencies, etc. agencies, etc.
Sophisticated UsersSophisticated UsersPeople who use for their complex requirements.People who use for their complex requirements.Eg. Engineers, Scientists, Business analysts… Eg. Engineers, Scientists, Business analysts…
Standalone UsersStandalone UsersWho maintain database for personal use.Who maintain database for personal use.
DBADBA Managing resourcesManaging resources
Creation of user accountsCreation of user accounts
Providing security and authorizationProviding security and authorization
Managing poor system response timeManaging poor system response time
System RecoverySystem Recovery
Tuning the DatabaseTuning the Database
End of Chapter - 1End of Chapter - 1
top related