Post on 26-Jan-2017
transcript
DATABASE MANAGEMENT DATABASE MANAGEMENT SYSTEMS SYSTEMS
MJS-15
BYAbhishek & Sai Kumar
Introduction to DBMSWhat is Database?Purpose of Database SystemsFile System vs. DBMSView of DataData ModelsRDBMSDDLDMLDCLTCL
MJS-15 Index
MJS-15 Introduction1
• A software used to manage data.• A very large, integrated collection of data.• A collection of information stored in a computer in
a systematic way.
Databases• MySql• Postgresql• Oracle
What is Database..?
Database Management System (DMBS)
Collection of interrelated dataDMBS contains information about a particular
enterpriseDBMS provides an environment that it both
convenient and efficient to use
MJS-15 Introduction2
File System vs. DBMSadvantages disadvantages
FMS • Simpler to use• Less expensive
• Typically no multi-user access• Limited to smaller databases• Limited functionality• Decentralization of data• Redundancy and integrity issues
DBMS • Greater flexibility• Greater processing power• Ensures data integrity• Supports simultaneous access• Provides backup and recovery controls• Advanced security
• Difficult to learn• Packaged separately from the OS• Slower processing speeds• Requires skilled administrators• Expensive
MJS-15 Introduction 3
Purpose of Database Systems
Data redundancy and inconsistencyDifficulty in accessing dataData isolation – multiple files and formatsIntegrity problemsConcurrent access by multiple usersSecurity problems
MJS-15 Introduction 4
Database management systems were developed to handle the following difficulties of typical file-processing systems supported by conventional operating systems:
View of DataAn architecture for a database system
MJS-15 Introduction 5
Physicallevel
Logical level
View level
Levels of AbstractionPhysical 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.
MJS-15 Introduction 6
Instances and Schemas
Similar to types and variables in programming languagesSchema – the logical structure of the database (e.g., set
of customers and accounts and the relationship between them)
Instance – the actual content of the database at a particular point in time
MJS-15 Introduction 7
Data ModelsA collection of tools for describing:
Data relationshipsData constraints
Object-based logical modelsEntity-relationship model
Record-based logical modelsRelational model (e.g., SQL/DS, DB2)
MJS-15 Introduction 8
Entity-Relationship Model
Example of entity-relationship model
MJS-15 Introduction 9
customer accountdepositor
social-security customer-street
customer-name
account-number
balancecustomer-city
What is RDBMS..?
MJS-15 Introduction 10
• RDBMS stands for Relational Database Management System.
• RDBMS data is structured in database tables, fields and records.
• In this data will be stored in the from rows and columns.
• RDBMS also provide relational operators to manipulate the data stored into the database tables.
Relational Model
Example of tabular data in the relational model:
MJS-15 Introduction 11
name ssn street city account-numberJohnson 192-83-7465 Alma Palo Alto A-101Smith 019-28-3746 North Rye A-215Johnson 192-83-7465 Alma Palo Alto A-201Jones 321-12-3123 Main Harrison A-217Smith 019-28-3746 North Rye A-201
account-number balanceA-101 500A-201 900A-215 700A-217 750
MJS-15 Introduction 12
There are four types of query languages as follows.
•Data Definition Language (DDL)•Data Manipulation Language (DML)•Data Control Language (DCL)•Transaction Control (TCL)
RDBMS Query Languages.
Data Definition Language (DDL)A DDL is a language used to define data structures and modify data.DDL Commands:•Create•CREATE TABLE employee(empID int, Ename char(20), Esalary int, Eadd varchar(25));
•Alter•ALTER TABLE employee ADD Ecountry varchar (50);
•Drop•DROP TABLE employee;
•Truncate• TRUNCATE TABLE employee;
•Rename•SELECT Ename AS Emp_name FROM employee;
MJS-15 Introduction 13
Data Manipulation Language (DML)
A DML is a language used for managing data in database.DML Commands:•Select• SELECT * FROM employee;
•Insert•INSERT INTO employee(EmpId, Ename, Esalary, Ecity) VALUES (502 ,‘sandeep',‘25000',hyderabad' );
•Update•UPDATE employee SET Ename=‘bharath', where Ecity=hyderabd';
•Delete•DELETE FROM Employee WHERE Ename=‘sandeep‘;
MJS-15 Introduction 14
Data Control Language (DCL)
A DCL is a language used to control privilege in Database
DCL Commands:•Grant•Syntax: REVOKE [permission] ON database.table FROM 'user‘@'localhost'; Example: GRANT [write] ON [company_details].[employee] TO ‘[sandeep]’@'localhost’;
•Revoke•Syntax: REVOKE [permission ]ON database.table FROM 'user‘@'localhost'; Example: REVOKE [write] ON Company_details.Employee FROM ‘Sandeep'@'localhost';
MJS-15 Introduction 15
Transaction Control Language (TML)
A TCL is a language used to manage transactions in database
TCL Commands:•Commit : save work done•Syntax: commit
•Rollback : restore database to original since the last COMMIT•Syntax: rollback;
MJS-15 Introduction 16
Any Queries…?MJS-15 12