DBMS

DATABASE MANAGEMENT SYSTEMS(DBMS) : 

DATABASE MANAGEMENT SYSTEMS(DBMS) N.KANAGARAJ BSC[FORESTRY] BSF-06-007

What is DBMS? : 

What is DBMS? A collection of programs and tools to create & maintain a database (interrelated data) DBMS contains information about a particular information Defining specifying types of data Constructing storing & populating Manipulating querying, updating, reporting

File Management Systems: a physical interface : 

File Management Systems: a physical interface Student Admin Scheduler Payroll Timetable Year Lists Cheques Student Data Lecturer Data Course Data

DBMS: A Logical Interface : 

University Database Metadata DBMS: A Logical Interface student course lecturer Lab Timetable Teaching Schedule Tutorials Database Management System University Database Data Data Dictionary or System Catalog

File Management Systems-Drawbacks : 

File Management Systems-Drawbacks Uncontrolled redundancy Inconsistent data Inflexibility Limited data sharing Poor enforcement of standards Low programmer productivity Excessive program maintenance Excessive data maintenance

Database Management System Approach : 

Database Management System Approach Controlled redundancy consistency of data & integrity constraints Integration of data self-contained & represents semantics of application Data and operation sharing multiple interfaces Services & Controls security & privacy controls backup & recovery enforcement of standards Flexibility data independence data accessibility reduced program maintenance Ease of application development

DBMS- Applications : 

DBMS- Applications Banking: all transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax deductions Databases touch all aspects of our lives

View of Data : 

View of Data An architecture for a database system

Levels of Abstraction : 

Levels of Abstraction Physical level: describes how a record (e.g., customer) is stored. Logical level: describes data stored in database, and the relationships among the data. type customer = record name : string; street : string; city : integer; end; View level: application programs hide details of data types. Views can also hide information (e.g., salary) for security purposes.

Instances and Schemas : 

Instances and Schemas Schema – the logical structure of the Database e.g., the database consists of information about a set of customers and accounts and the relationship between them) Physical schema: database design at the physical level Logical schema: database design at the logical level Instance – the actual content of the database at a particular point in time

Data Models : 

Data Models A collection of tools for describing data data relationships data semantics data constraints Entity-Relationship model Relational model Other models: object-oriented model semi-structured data models Older models: network model and hierarchical model

Entity-Relationship Model : 

Entity-Relationship Model Example of schema in the entity-relationship model

Entity Relationship Model (Cont.) : 

Entity Relationship Model (Cont.) E-R model of real world Entities (objects) E.g. customers, accounts, bank branch Relationships between entities E.g. Account A-101 is held by customer Johnson Relationship set depositor associates customers with accounts

Relational Model : 

Relational Model

A Sample Relational Database : 

A Sample Relational Database

Data Definition Language (DDL) : 

Data Definition Language (DDL) Specification notation for defining the database schema E.g. create table account ( account-number char(10), balance integer) DDL compiler generates a set of tables stored in a data dictionary Data dictionary contains metadata (i.e., data about data)

Data Manipulation Language (DML) : 

Data Manipulation Language (DML) Language for accessing and manipulating the data organized by the appropriate data model DML also known as query language Two classes of languages Procedural – user specifies what data is required and how to get those data Nonprocedural – user specifies what data is required without specifying how to get those data SQL is the most widely used query language

SQL-structured query lang. : 

SQL-structured query lang. SQL: widely used non-procedural language E.g. find the name of the customer with customer-id 192-83-7465 select customer.customer-name from customer where customer.customer-id = ‘192-83-7465’ E.g. find the balances of all accounts held by the customer with customer-id 192-83-7465 select account.balance from depositor, account where depositor.customer-id = ‘192-83-7465’ and depositor.account-number = account.account-number

Database Users : 

Database Users Users are differentiated by the way they expect to interact with the system Application programmers – interact with system through DML calls Naïve users – invoke one of the permanent application programs that have been written previously E.g. people accessing database over the web, bank tellers, clerical staff

Database Administrator : 

Database Administrator Database administrator's duties include: Schema definition Storage structure and access method definition Schema and physical organization modification Granting user authority to access the database Specifying integrity constraints Monitoring performance and responding to changes in requirements

Transaction Management : 

Transaction Management A transaction is a collection of operations that performs a single logical function in a database application Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures. Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database.

Storage Management : 

Storage Management Storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. The storage manager is responsible to the following tasks: interaction with the file manager efficient storing, retrieving and updating of data

Slide 23: 

Two-tier architecture: E.g. client programs using ODBC/JDBC to communicate with a database Three-tier architecture: E.g. web-based applications, and applications built using “middleware”

Three-Schema Architecture : 

Three-Schema Architecture The goal is to separate user applications and physical database Schemas defined at External (or View) Level includes external schemas or user views Conceptual Level Schema describes database structure Internal Level Schema describes physical storage of database Mapping: Process of transforming requests and results between levels