Introduction to Database Management System Database System Concepts

Introduction to Database Management System Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Outline n Database Introduction n Level of Abstraction n Data Models n Database Languages n Database Design Database System Concepts - 6 th Edition 1. 2 ©Silberschatz, Korth and Sudarshan

Database Management System (DBMS) n DBMS contains information about a particular enterprise l Collection of interrelated data l Set of programs to access the data l An environment that is both convenient and efficient to use n Database Applications: l Banking: transactions l Airlines: reservations, schedules l Universities: registration, grades l Sales: customers, products, purchases l Online retailers: order tracking, customized recommendations l Manufacturing: production, inventory, orders, supply chain l Human resources: employee records, salaries, tax deductions n Databases can be very large. n Databases touch all aspects of our lives Database System Concepts - 6 th Edition 1. 3 ©Silberschatz, Korth and Sudarshan

View of Data An architecture for a database system Database System Concepts - 6 th Edition 1. 4 ©Silberschatz, Korth and Sudarshan

Instances and Schemas n Similar to types and variables in programming languages n Logical Schema – the overall logical structure of the database l Example: The database consists of information about a set of customers and accounts in a bank and the relationship between them 4 Analogous to type information of a variable in a program n Physical schema– schema the overall physical structure of the database n Instance – the actual content of the database at a particular point in time l Analogous to the value of a variable n Physical Data Independence – the ability to modify the physical schema without changing the logical schema l Applications depend on the logical schema l In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others. Database System Concepts - 6 th Edition 1. 5 ©Silberschatz, Korth and Sudarshan

Data Models n A collection of tools for describing Data l Data relationships l Data semantics l Data constraints l n Relational model n Entity-Relationship data model (mainly for database design) n Object-based data models (Object-oriented and Object-relational) n Semistructured data model (XML) n Other older models: l l Network model Hierarchical model Database System Concepts - 6 th Edition 1. 6 ©Silberschatz, Korth and Sudarshan

Relational Model n All the data is stored in various tables. n Example of tabular data in the relational model Columns Rows Database System Concepts - 6 th Edition 1. 7 ©Silberschatz, Korth and Sudarshan

A Sample Relational Database System Concepts - 6 th Edition 1. 8 ©Silberschatz, Korth and Sudarshan

Data Definition Language (DDL) n DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. n Example: n Examples of DDL commands: n CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers). n DROP – is used to delete objects from the database. n ALTER-is used to alter the structure of the database. n TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed. n COMMENT –is used to add comments to the data dictionary. n RENAME –is used to rename an object existing in the database. Database System Concepts - 6 th Edition create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8, 2)) 1. 9 ©Silberschatz, Korth and Sudarshan

Data Manipulation Language (DML) n The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. n Examples of DML: n INSERT – is used to insert data into a table. n UPDATE – is used to update existing data within a table. n DELETE – is used to delete records from a database table. Database System Concepts - 6 th Edition 1. 10 ©Silberschatz, Korth and Sudarshan