Slides edited by Sudhanshu Kulshrestha Lecturer, Dept. of Computer Science & Engineering

1. Jaypee Institute of Information TechnologyDeclared Deemed to be University u/s 3 of UGC Act Database Management SystemsIntroductionIn accordance with Silberschatz, Korth, Sudharshan, Fifth Edition Slides are adapted from the companion web site of the book Slides edited by Sudhanshu Kulshrestha Lecturer, Dept. of Computer Science & Engineering

2. Reference Books • Henry F Korth, Abraham Silberschatz, S. Sudurshan, Database system concepts, 5th Edition, McGraw-Hill,2006 • RamezElmasri , Shamkant B. Navathe , Fundamentals of Database Systems, 4th Edition, Pearson Education, 2006. • Ramakrishnan, Gehrke, Database Management Systems, Mcgraw-Hill, 3rd Edition, Addison-Wesley,2006. • Thomas Connolly, Carolyn Begg, Database Systems-A Practical Approach to design, Implementation and Management, 3rd Edition, Addison-Wesley,2002. Jaypee Institute of Information Technology

3. Database - Definition A database is a collection of related data. Example : A student database with following data. Name, telephone numbers, place, address Other Definitions : • A database represents some aspects of the real world , sometimes called the miniworld. Changes to the miniworld are reflected in the data base • A database is a logically coherent collection of data with some inherent meaning. • A data base is designed , built and populated with data for a specific purpose . It has an intended group of users and some preconceived applications in which these users are interested.

4. Database Management System • A DBMS is a general purpose software system that facilitates the following process among the various users and application. • Defining- Specification the data types , structures, and constraints. • Constructing -The process of storing the data itself on some media. • Manipulating - Includes functions such as querying, updating and generating reports. • Sharing - Concurrently accessing the database by multiple users and applications. • Other functions include • Protection(system protection and security protection) • maintaining (allowing the system to evolve)

5. Database Management System • The database and DBMS are together called database system

6. Database Management System • DBMS contains information about a particular enterprise • Collection of interrelated data • Set of programs to access the data • 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 • Online retailers: order tracking, customized recommendations • Manufacturing: production, inventory, orders, supply chain • Human resources: employee records, salaries, tax deductions

7. File System Databases In the early days, database applications were built directly 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 — changes made by one operation could not become visible to other concurrent operations. • Integrity problems • Integrity constraints (e.g. account balance > 0) become “buried” in program code rather than being stated explicitly • Hard to add new constraints or change existing ones

8. File System Databases • Drawbacks of using file systems (cont.) • Atomicity of updates • Failures may leave database in an inconsistent state with partial updates carried out • Example: 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 • Example: Two people reading a balance and updating it at the same time • Security problems • Hard to provide user access to some, but not all, data • Database systems offer solutions to all the above problems

9. Advantages of using the DBMS Approach • Controlling Redundancy • Restricting Unauthorized access • Proving Persistent storage for Program objects • Providing Storage Structures for efficient Query Processing • Providing backup and Recovery. • Providing multiple user interfaces • Representing Complex Relationships among data • Enforcing Integrity Constraints • Permitting Interfacing and Actions using rules • Additional advantages

10. Advantages of using the DBMS Approach • Controlling Redundancy • Redundancy means storing the same data multiple times. Problems of redundancy : • Leads to Duplication of efforts • Wastage of storage space. • Inconsistency. • In the database approach , these redundancy problems can be removed by storing logical data items in only one place. • In some cases controlled redundancy is required for improving the query performance .

11. Advantages of using the DBMS Approach • Restricting Unauthorized Access • A DBMS allows only authorized users to access the database to provide the security to the database. • A DBMS controls type of access operation (retrieve and update). • A DBMS provides security and authorization subsystem for the DBA to create accounts and specify account restriction. • Providing Persistent Storage for Program Objects. • An object – oriented DBMS provides persistent storage for program objects and data structures. e.g. : C++ and java objects can be stored permanently so that later they can be read whenever necessary.

12. Advantages of using the DBMS Approach • Providing Storage for Structures for Efficient Query Processing. • Since database is typically stored on disk , DBMS uses index files to speed up the searching process to increase the efficiency of query processing . • It uses buffering module to maintain the records in the main memory. • The query of the DBMS is responsible for choosing an processing and optimization module efficient query execution plan for each query based on the existing storage structures.

13. Advantages of using the DBMS Approach • Providing Backup and Recovery. • The back up and recovery subsystem of the DBMS is responsible for recovery. • Providing Multiple User Interface. Theses include • query languages for users • APIs (Application Programming Interface) for application programmers • Forms and command codes for parametric users. • Menu Driven interfaces and natural language for stand-alone programmers

14. Advantages of using the DBMS Approach • Representing complex Relationship among Data. • A DBMS has the capability to represent a variety of complex relationships among the data as well as to retrieve and update related data easily and efficiently. • Enforcing Integrity Constraints. • A DBMS provides capabilities for defining and enforcing Integrity constraints. • Two types of constraints : • Simple type of integrity constraints e.g : month must be an integer between 1 - 12 • complex type of integrity constraints e.g. a section record must be related to course record.

15. Advantages of using the DBMS Approach • Permitting Inferencing and Actions Using Rules : • Some database systems provide capabilities for defining deduction rules for inferencing new information from the stored database facts. Such systems are called deductive database systems. • Active database systems provide active rules that can automatically initiate actions when certain events and conditions occur. • Additional advantages : • Potential for enforcing standards • Reduced Application Development Time • Flexibility • Availability of up-to-Date information. • Economics of scale

16. Characteristics of DBMS Approach Self-describing Nature of a Database • A database has complete description of the database structure and constraints. These descriptions are stored in the DBMS catalog/Data dictionary. • The information stored in the catalog is called meta – data. • Traditional file processing software can access only specific databases, DBMS software can access diverse databases.

17. Characteristics of DBMS Approach Insulation between Programs and Data, and Data Abstraction : • Program–operation independence in which a user program (sequence of instruction) can operate on data regardless of how these operations are implemented. • Program-data independence: Changes to data file do not require the changes to the program. The structure of the data files is stored in the DBMS catalog separately from the program access.

18. Characteristics of DBMS Approach Insulation between Programs and Data, and Data Abstraction • A characteristics that allows program-data independence and Program – operation independence is called data abstraction. • Data abstraction - Provides users with a conceptual representationof data Support of multiple views of data • A multi-user DBMS must provide facilities for defining multiple views for a variety of users. • A view may be a subset of the database or it may contain virtual data

19. Characteristics of DBMS Approach Sharing of Data and Multi-user Transaction Processing • A DBMS allows multiple users to access the database at the same time. • A DBMS includes concurrency control software to control several users trying to update the same data. • Each transaction must be executed in isolation from other transaction. (isolation property) • Either all the operations in a transaction are executed or none at all. (Atomicity property)

20. Levels of Abstraction Abstraction captures only those details about an object that are relevant to the current perspective. OR Hiding the unwanted details. • Physical level:describes how a record (e.g., customer) is stored. • Logical level:describes what data stored in database, and the relationships among the data. Example: typecustomer = record customer_id : string; customer_name : string;customer_street : string;customer_city : string; end; • View level:application programs hide details of data types. Views can also hide information (such as an employee’s salary) for security purposes.

21. Levels of Abstraction An architecture for a database system

22. Instances and Schemas • Similar to types and variables in programming languages • Schema – the logical structure of the database • Example: The database consists of information about a set of customers and accounts and the relationship between them) • Analogous to type information of a variable in a program • 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 • 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.

23. Three Schema Architecture Describes 3 important characteristic of database approach : • Program - data and program – operation independence • Support of multiple views • Use of catalog to store schema

24. Three Schema Architecture Schemas can be defined at 3 levels • Internal level • Describes the physical storage of the structure • Uses physical data model to describe internal schema • Describes complete details of data storage and access paths for the database. • Conceptual level • Describes entities , data types , relationships , user operations and constraints. • Uses representational data model to describe the conceptual schema. • Hides details of physical storage • External (view) level • Describes a part of the database that a particular user group is interested and hides rest of the database. • Uses representational model to describe each external schema

25. Three Schema Architecture - Example Conceptual level or Conceptual schema : Students(sid: string, name: string, login: string, age: integer, gpa:real) Courses(cid: string, cname:string, credits:integer) Enrolled(sid:string, cid:string, grade:string) Internal level or Physical schema : Relations stored as unordered files. Index on first column of Students. External level (View): Course_info1(cid:string,enrollment:integer) Course_info2(cid:string, sid:string , grade:string) Student_info 1(name:string, age: integer) Student_info 2(name:string, cid:string, grade:string))

26. Three Schema Architecture – Data Independence Data Independence • The Capacity to change the schema at one level of a data base system without having to change the schema at the next higher level. • Logical data independence: • The Capacity to change conceptual schema without having to change external or application programs. • We may change conceptual schema to expand database, to change constraints or to reduce the database. • After change to conceptual schema the application program that reference the external schema construct must work as before. • Only view definition and the mapping need to be changed in a DBMS that supports logical independence.

27. Three Schema Architecture – Data Independence • Data Independence 2. Physical data independence: • The Capacity to change internal schema without having to change the conceptual schema. • Changes to the internal schema may be needed because some physical files had to be reorganized to improve the performance of retrieval and updates. • If the same files remains as before then we should not have to change the conceptual schema.

28. Data Models • Data Abstraction is provided by a Data Model. It is a collection of tools for describing following: • Data • Data relationships • Data semantics (study of meaning) • Data constraints (restrictions/rules) OR • A collection of concepts that can be used to describe the structure of database. • Structure of database means  the data types , relationships and constraints on the data. • A data model may also include operations for retrieval and updates on the database. • A set of valid user defined operation can also be specified in a Data Model

29. Data Models - Categorization • HIGH – LEVEL OR CONCEPTUAL DATA MODEL : • Provide concepts that are close to the way many users perceive data. • It uses concepts such as entities , attributes , and relationships. • An entity represents real world concepts such as an employee or a project • An attributerepresents some property of interest such as the employee’s name or salary • A relationship represents associations among two or more entities e.g. An employee works on some project. Relationship between employee and project entities

30. Data Models - Categorization • REPRESENTATIONAL DATA MODELS : • Provide concepts that may be understood by end users. Hide the details of data storage. • Frequently used in traditional commercial DBMSs. These include • Relational data model • Network data models • Hierarchical data models • Represents data by using record structures and sometimes called record – based data models.

31. Data Models - Categorization • LOW – LEVEL OR PHYSICAL DATA MODELS : • Physical data models represent describe how data is stored as files by representing information such as record formats , record ordering and access paths • Generally meant for computer specialists.

32. Data Models - Types • Relational model • Entity-Relationship data model (mainly for database design) • Object-based data models (Object-oriented and Object-relational) • Semi-structured data model (XML) • Other older models: • Network model • Hierarchical model *NOTE: Read the respective differences between above mentioned data-models and their examples. Refer Navathe 5th edition, topic 2.6.

33. Database Users: Types • Database Administrators (DBA) : • Responsible for administering the database and DBMS software . • Responsible for authorizing access to the database. • Acquiring software and hardware resources as needed. • Accountable for problems such as security and poor system response time. • Database designers : • Responsible for identifying the data to be stored in the database • Choosing appropriate structures to represent and store this data. • Understanding and collecting user requirements and to come up with desired design • Interacting with each potential group of users to develop views of the database.

34. Database Users: Types • Software Engineers (System analysts and Application Programmers) • System analysts determine the requirement of end users and develop specification for canned transactions that meet these requirements. • Application programmers implement theses specifications as programs. Then they test, debug , document and maintain these canned transaction. 4. End Users : End users access the database for querying , updating and generating reports. • Casual End Users : • Occasionally access the database with sophisticated database query language. • They are typically middle or high level managers or other occasional browsers

35. Database Users: Types • Naive or parametric end users • Constantly querying and updating the database using standard types of queries and updates called - canned transactions • Examples: Bank tellers, Reservation clerks for airlines and hotels • Sophisticated end users : • Include engineers, scientists and business analysts and others who thoroughly familiarize themselves with the DBMS so as to implement their applications. • Stand – alone users • Maintaining personal databases by using ready made program packages that provides menu based interfaces. • Example: The user of a tax package