Database Management Systems

1. Database Management Systems

2. TEXT BOOKS • 1. Database Management Systems, Raghurama Krishnan, Johannes Gehrke, TATA McGrawHill 3rd edition • 2. Database System Concepts, Silberschatz, Korth, McGraw hill, 5 th edition. • REFERENCES : • 1. Database Systems design, Implementation, and Management, Peter Rob & Carlos Coronel 7 th Edition. • 2. Fundamentals of Database Systems, Elmasri Navate, Pearson Education • 3. Introduction to Database Systems, C.J.Date, Pearson Education

3. Chapter 1: Introduction • Purpose of Database Systems • View of Data • Data Models • Data Definition Language • Data Manipulation Language • Transaction Management • Storage Management • Database Administrator • Database Users • Overall System Structure

4. Database Management System (DBMS) • Collection of interrelated data • Set of programs to access the data • DBMS contains information about a particular enterprise • DBMS provides an environment that is both convenient and efficient to use. • Database 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

5. Some Issues • Creation of definitions/structures • Various views • Various types of users • Storing • Retrieving -- Indexes • Manipulating the data • Designing a good database --Normalization • Support for transactions • Crash recovery • Concurrent execution • Two clerks trying to reserve the same berth in a railway reservation system! • Two concurrent programs trying to add Rs 50 and Rs 100 to the same account. • Security

6. Purpose of Database System • In the early days, database applications were built on top of file systems • Drawbacks of using file systems to store data: • Data redundancy and inconsistency • Multiple file formats, duplication of information in different files • Difficulty in accessing data • Need to write a new program to carry out each new task • Data isolation — multiple files and formats. • It should be possible for you to write your program without worrying about other programs. Thinks that you alone is using the data. • Integrity problems • Integrity constraints (e.g. account balance > 0) become part of program code • Hard to add new constraints or change existing ones

7. Purpose of Database Systems (Cont.) • Drawbacks of using file systems (cont.) • Atomicity of updates • Failures may leave database in an inconsistent state with partial updates carried out • E.g. transfer of funds from one account to another should either complete or not happen at all • Concurrent access by multiple users • Concurrent accessed needed for performance • Uncontrolled concurrent accesses can lead to inconsistencies • E.g. two people reading a balance and updating it at the same time • Security problems • Database systems offer solutions to all the above problems

8. 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. • There are customers and accounts. Relationship is a customer can have one or many accounts. • Similarly a constraint saying that account-balance is non-negative is also part of the logical level description. • View level: There are several categories of users of a DBMS. They want to see the data in a particular form because of ease or clarity. Sometimes only part of a database is allowed to be accessed by a particular user. • There is only one Physical and Logical level schema, but there can be many view level schemas.

9. View of Data An architecture for a database system

10. Instances and Schemas • Similar to types and variables in programming languages • Schema – the description about the data -- Meta data (data about data) • Schema is also data ! • A program which wants to access data, should first access the schema to know the things likes format, type of the data, etc. • This is a powerful concept which allows us to see the data in a more abstract way. • Physical schema: database description at the physical level • Logical schema: database description at the logical level • Instance – the actual content of the database at a particular point in time • Analogous to the value of a variable • Physical Data Independence – the ability to modify the physical schema without changing the logical schema • Applications depend on the logical schema • 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.

11. Data Models • A collection of conceptual tools for describing data items along with relationship between them. It can also describe other information like constraints, etc. • Entity-Relationship model: A high level conceptual model which can be used to describe an enterprise without worrying about the technicalities of DBMS or any other system. • Good to do the requirements analysis. • Later on this can be converted easily into other models which are more technical. • Relational model: DB is a collection of relations. Relations(tables) are used to describe both data and relationships. • Other models: object-oriented model, semi-structured data models. Older models: network model and hierarchical model

12. Entity-Relationship Model An Example: ( we will see in detail later-on)

13. 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 • Widely used for database design at a very high abstract level • Database design in E-R model usually converted to design in the relational model (coming up next) which is used for storage and processing

14. Relational Model Attributes • Example of tabular data in the relational model customer- street customer- city account- number customer- name Customer-id Johnson Smith Johnson Jones Smith 192-83-7465 019-28-3746 192-83-7465 321-12-3123 019-28-3746 Alma North Alma Main North A-101 A-215 A-201 A-217 A-201 Palo Alto Rye Palo Alto Harrison Rye

15. A Sample Relational Database

16. DDL and 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

17. DDL and DML : SQL • Data definition language is used to define schema (mostly at logical level) of a relation. • E.g. create tableaccount (account-numberchar(10),balanceinteger) • DDL compiler generates a table (relation) called account which is ready to be populated. • To insert a row (a data item) into the account table • insertinto account values (‘A-9732’,1200)

18. SQL • SQL: widely used non-procedural language which contains both DDL and DML. • E.g. find the name of the customer with customer-id 192-83-7465selectcustomer.customer-namefromcustomerwherecustomer.customer-id = ‘192-83-7465’ • E.g. find the balances of all accounts held by the customer with customer-id 192-83-7465selectaccount.balancefromdepositor, accountwheredepositor.customer-id = ‘192-83-7465’ anddepositor.account-number = account.account-number • Application programs generally access databases through one of • Language extensions to allow embedded SQL • Application program interface (e.g. ODBC/JDBC) which allow SQL queries to be sent to a database

19. Database Users • Users are differentiated by the way they expect to interact with the system • Application programmers – Write applications which uses a database. They should know regarding how to access the database by using a host language, etc. • Sophisticated users – form requests in a database query language • Naïve users – Use already developed tools to access the data. E.g. people accessing database over the web, bank tellers, clerical staff

20. Database Administrator • Coordinates all the activities of the database system; the database administrator has a good understanding of the enterprise’s information resources and needs. • 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 • Acting as liaison with users • Monitoring performance and responding to changes in requirements

21. 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.

22. 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

23. Overall System Structure

24. Application Architectures • 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”

25. History • It is closely related to the history of storage mediums from punched cards to hard disks; also related to data models from early network or hierarchical model to object oriented models. • 1950s and early 1960s: Magnetic tapes, punched cards are used as input medium. Output could be tape or punched card or printer. • Tapes are sequential, often main memory size is for lesser than the tape size. You need to write restricted programs which process at a time only a small quantity of data. • Late 1960s and 1970s: Hard disks. Random access. Allowed data structures to be stored on a disk. Network and hierarchical model of early databases is possible because of this. • A landmark paper by Codd [1970] defined the relational model and non-procedural ways of querying data in the relational model. • Codd won the prestigious Association of Computing Machinery Turing Award.

26. History • 1980s: Hierarchical and network models dominated in late 1970s, even though relational model is academically insteresting. • This is because, hierarchical and network models are efficient (speed) than relational models • Situation changed with System R, a ground breaking project at IBM Research that developed techniques for construction of an efficient relational database system. • Initial commercial RDBMS came into picture: IBM DB2, Oracle, Ingres, and DEC Rdb • 1980s saw much research on parallel and distributed databases, as well as initial work on object-oriented databases.

27. History • Early 1990s: Transaction processing capabilities • Tools for analyzing large amounts of data • Late 1990s: World Wide Web • High transaction processing rates • 24X7 working systems – no downtime even for maintenance. • Early 2000s: • XML, XQuery  new database technology. • Data warehousing, data mining tools