DBMS Course Notes - 6 December 2023 PDF

Course – DBMS Course Instructor Dr. Umadevi V Department of CSE, BMSCE 1 6 December 2023 CSE, BMSCE Unit-1 Introduction to Databases: Introduction, An Example, Characteristics of Database approach, Advantages of...

Course – DBMS Course Instructor Dr. Umadevi V Department of CSE, BMSCE 1 6 December 2023 CSE, BMSCE Unit-1 Introduction to Databases: Introduction, An Example, Characteristics of Database approach, Advantages of using DBMS approach, When not to use a DBMS. Database System Concepts and Architecture: Data models, Schemas and instances, Three schema architecture. SQL: SQL Data Definition and Data Types specifying basic constraints in SQL, Basic retrieval queries in SQL, Insert, Delete and Update statements in SQL, Additional features of SQL, more complex SQL Queries, Specifying Constraints as Assertions and Triggers, Views (Virtual Tables) in SQL, Schema Change Statement in SQL. 6 December 2023 CSE, BMSCE 2 Basic Definitions □ Data: Known facts that can be recorded and have an implicit meaning. □ Database: A collection of related data. □ Database Management System (DBMS): A database management system (DBMS) is a collection of programs that enables users to create and maintain a database. DBMS is a general-purpose software system that facilitates the process of defining, constructing, manipulating and sharing databases among various users and applications. 6 December 2023 CSE, BMSCE 3 Database Management System □ A Database Management System (DBMS) is a piece of software that manages databases and lets you create, edit and delete databases, their tables and their data. Examples of a DBMS are MySQL, Oracle 6 December 2023 CSE, BMSCE 4 Simplified database system environment 6 December 2023 CSE, BMSCE 5 Main Characteristics of the Database Approach 1. Self-describing nature of a database system. 2. Insulation between programs and data, and data manipulation. 3. Support of multiple views of the data. 4. Sharing of data and multi-user transaction processing 6 December 2023 CSE, BMSCE 6  Main Characteristics of the Database Approach 1. Self-describing nature of a database system What will be your description of the data stored in the following tables ? 1BM14CS001 Aditya 3 WP 1BM14CS002 Bharath 3 DS 1BM14CS001 1000 1BM14CS002 1000 1BM14CS001 S 1BM14CS002 B 6 December 2023 CSE, BMSCE 7 Main Characteristics of the Database Approach 1. Self-describing nature of a database system. Student_Details USN Name Sem Sub 1BM14CS001 Aditya 3 WP 1BM14CS002 Bharath 3 DS Student_ExamFee_Details Student_Grade_Details USN Amount USN Grade 1BM14CS001 1000 1BM14CS001 S 1BM14CS002 1000 1BM14CS002 B 6 December 2023 CSE, BMSCE 8  Main Characteristics of the Database Approach 1. Self-describing nature of a database system. Column_name Data_Type Belongs_to USN Char(10) Student_Details Catalog Information stored in Name Char(30) Student_Details catalog is called Sem Integer Student_Details meta-data which Describes the structure Sub Char(2) Student_Details Of primary Amount Float Student_ExamFee_Details database Grade Char(1) Student_Grade_Details 6 December 2023 CSE, BMSCE 9 Main Characteristics of the Database Approach 2. Insulation between programs and data, and data manipulation. File Approach student.txt 1BM14CS001 Aditya LA,Java,DBMS,OS,DC 1BM14CS002 Baharth DBMS,OS,DC fp=fopen("student.txt","r"); while(fscanf(fp,"%s %s %s",USN,name,subjects)!=EOF) { printf("USN: %s Name: %s Subjects: %s",USN,name,subjects); } 6 December 2023 CSE, BMSCE 10  Main Characteristics of the Database Approach 2. Insulation between programs and data, and data manipulation. DBMS Approach student USN Name Subjects 1BM14CS001 Aditya LA, Java, DBMS,OS,DC 1BM14CS002 Bharath DBMS, OS, DC select USN, Name, Subjects from student; SQL query to retrieve and display table information 1BM14CS001 Aditya LA,Java,DBMS,OS,DC 1BM14CS002 Bharath DBMS,OS,DC 6 December 2023 CSE, BMSCE 11 Main Characteristics of the Database Approach 3. Support of multiple views of the data 6 December 2023 CSE, BMSCE 12 Main Characteristics of the Database Approach 4. Sharing of data and multi-user transaction processing Train Reservation Database Source Destination Train Start Time Start Date Number of Seats Name Availability Bangalore Mysore Chamundi 18:15 14-11- 2 Express 2022 Train Ticket Booking Software 6 December 2023 CSE, BMSCE 13 Main Characteristics of the Database Approach □ Self-describing nature of a database system: A DBMS catalog stores the description of a particular database (e.g. data structures, types, and constraints) The description is called meta-data. This allows the DBMS software to work with different database applications. □ Insulation between programs and data, and data manipulation: Called program-data independence. Allows changing data structures and storage organization without having to change the DBMS access programs. A data model is used to hide storage details and present the users with a conceptual view of the database. Programs refer to the data model constructs rather than data storage details 6 December 2023 CSE, BMSCE 14 Main Characteristics of the Database Approach ❑ Support of multiple views of the data: ❑ Each user may see a different view of the database, which describes only the data of interest to that user. □ Sharing of data and multi-user transaction processing: Allowing a set of concurrent users to retrieve from and to update the database. Concurrency control within the DBMS guarantees that each transaction is correctly executed or aborted Recovery subsystem ensures each completed transaction has its effect permanently recorded in the database OLTP (Online Transaction Processing) is a major part of database applications. This allows hundreds of concurrent transactions to execute per second. 6 December 2023 CSE, BMSCE 15 Advantages of Using the Database Approach 1. Controlling redundancy in data storage and in development and maintenance efforts. 2. Restricting unauthorized access to data. 3. Providing persistent storage for program Objects (data structures provided by DBMS & the programming languages were incompatible) 4. Providing Storage Structures (e.g. indexes) for efficient Query Processing 5. Providing backup and recovery services. 6. Providing multiple interfaces to different classes of users. 7. Representing complex relationships among data. 8. Enforcing integrity constraints on the database. 9. Drawing inferences and actions from the stored data using deductive and active rules 6 December 2023 CSE, BMSCE 16 Understanding integrity constraints on the database Database Catalog for following database tables Which of the following tables data storage is correct as per the above catalog definition ? Student_Grade_Details Student_Grade_Details USN Grade USN Grade 1BM14CS001 S 1BM14CS001 S 1BM14CS002 B 1BM14CS002 8 6 December 2023 CSE, BMSCE 17 Understanding integrity constraints on the database Student_Details USN Name Sem Sub 1BM14CS001 Aditya 3 WP 1BM14CS002 Bharath 3 DS What is wrong in the following table data as per the Student_Details table ? Student_Grade_Details USN Grade 1BM14CS001 S 1BM14CS002 B 1BM14CS003 C 6 December 2023 CSE, BMSCE 18 Understanding drawing of “inferences and actions” from the stored data Student_Grade_Details USN Grade 1BM14CS001 S 1BM14CS002 B 1BM14CS003 C 1BM14CS004 S 1BM14CS005 F 1BM14CS006 S Using the above table data, Can you infer How many Students have scored S grade and What are their USN’s? 6 December 2023 CSE, BMSCE 19 Understanding drawing of “inferences and actions” from the stored data Student_Grade_Details Catalog USN Grade 1BM14CS001 S Column_name Data_Type Constraint USN Char(10) 1BM14CS002 B Grade Char(1) Should not be 1BM14CS003 C empty 1BM14CS004 S 1BM14CS005 F 1BM14CS006 S Using the above Catalog information, Can you tell Whether the following insert action on Student_Grade_Details table is Right or Wrong ? insert into Student_Grade_Details values (1BM14CS007); 6 December 2023 CSE, BMSCE 20 Question The DBMS acts as an interface between what two components of an enterprise-class database system? i. Database application and the database ii. Data and the database iii. The user and the database application iv. Database application and SQL 6 December 2023 CSE, BMSCE 21 Question The DBMS acts as an interface between what two components of an enterprise-class database system? i. Database application and the database ii. Data and the database iii. The user and the database application iv. Database application and SQL 6 December 2023 CSE, BMSCE 22 Question An application where only one user accesses the database at a given time is an example of an ________. □ single-user database application □ multiuser database application □ e-commerce database application □ data mining database application 6 December 2023 CSE, BMSCE 23 Question An application where only one user accesses the database at a given time is an example of an ________. □ single-user database application □ multiuser database application □ e-commerce database application □ data mining database application 6 December 2023 CSE, BMSCE 24 Question An on-line commercial site such as Amazon.com is an example of an ________. □ single-user database application □ multiuser database application □ e-commerce database application □ data mining database application 6 December 2023 CSE, BMSCE 25 Question An on-line commercial site such as Amazon.com is an example of an ________. □ single-user database application □ multiuser database application □ e-commerce database application □ data mining database application 6 December 2023 CSE, BMSCE 26 Question Because it contains a description of its own structure, a database is considered to be ________. □ described □ metadata compatible □ self-describing □ an application program 6 December 2023 CSE, BMSCE 27 Question Because it contains a description of its own structure, a database is considered to be ________. □ described □ metadata compatible □ self-describing □ an application program 6 December 2023 CSE, BMSCE 28 Question You have run an SQL statement that asked the DBMS to display data in a table named USER_TABLES. The results include columns of data labeled "TableName," "NumberOfColumns" and “DataType." You are looking at ________. □ user data. □ metadata □ A report □ indexes 6 December 2023 CSE, BMSCE 29 Question You have run an SQL statement that asked the DBMS to display data in a table named USER_TABLES. The results include columns of data labeled "TableName," "NumberOfColumns" and “DataType." You are looking at ________. □ user data. □ metadata □ A report □ indexes 6 December 2023 CSE, BMSCE 30  When not to use a DBMS ? 6 December 2023 CSE, BMSCE 31  When not to use a DBMS □ Main inhibitors (costs) of using a DBMS: High initial investment and possible need for additional hardware. Overhead for providing generality, security, concurrency control, recovery, and integrity functions. □ When a DBMS may be unnecessary: If the database and applications are simple, well defined, and not expected to change. If access to data by multiple users is not required. 6 December 2023 CSE, BMSCE 32 Unit-1 Introduction to Databases: Introduction, An Example, Characteristics of Database approach, Advantages of using DBMS approach, When not to use a DBMS. Database System Concepts and Architecture: Data models, Schemas and instances, Three schema architecture. 6 December 2023 CSE, BMSCE 33  Case Study 1 For example Say Ramu, who is staying in Basvangudi area has a empty land plot of 30x40 feet area. He wants to construct a house in this site. He is going to discuss this issue with you. But you are “Civil Engineering” student. How you will guide Ramu to resolve his issue. 6 December 2023 CSE, BMSCE 34  Case Study 1 □ Low High level view of house Building Architecture Quantity of cement, brick, mud, iron, Middle level view wood….etc to be used. Civil Engineer Low level view Building Constructor 6 December 2023 CSE, BMSCE 35 Case Study 2 Principal B. M. S. College of Engineering Dear Student, I need an software application to keep track of student Information and Department information. You should build a software which will help me to carry out the operations, such as display, insertion, deletion and updating of Student and department information. For each Student USN, Name, Semester and Department name to be stored. For each Department, its name and HOD name to be stored. Provide your design plan for developing this application software. 6 December 2023 CSE, BMSCE 36  Objective of todays class Understanding the basic terminologies and definitions involved in building Architecture of Database Systems. In this regard, First we will understand Data Models, Schemas and Instances 6 December 2023 CSE, BMSCE 37 Data Models □ Data Abstraction generally refers to the suppression of details of data organization and storage and the highlighting of the essential features for improved understanding of data. □ Data Model: A set of concepts to describe the structure of a database, the operations for manipulating these structures, and certain constraints that the database should obey. Data Models provides the necessary means to achieve data abstraction. 6 December 2023 CSE, BMSCE 38 Categories of Data Models □ Conceptual (high-level, semantic) data models: Provide concepts that are close to the way many users perceive data. □ Also called entity-based or object-based data models. □ Implementation (representational) data models: Provide concepts that fall in between high and low level, used by many commercial DBMS implementations (e.g. relational data models used in many commercial systems). □ Physical (low-level, internal) data models: Provide concepts that describe details of how data is stored in the computer. These are usually specified in an ad-hoc manner through DBMS design and administration manuals 6 December 2023 CSE, BMSCE 39  Categories of Data Models Conceptual (high-level) Implementation (Representational) Physical (low-level) 6 December 2023 CSE, BMSCE 40 Schemas, Instance and Database State Database Schema: – The description of a database. – Includes descriptions of the database structure, data types, and the constraints on the database. Schema Diagram: – An illustrative display of (most aspects of) a database schema □ Database State: The actual data stored in a database at a particular moment in time. This includes the collection of all the data in the database. Also called database instance (or occurrence or snapshot). □ The term instance is also applied to individual database components, e.g. record instance, table instance, entity instance 6 December 2023 CSE, BMSCE 41  Schemas, Instance and Database State Schema Diagram Department Student USN Name Sem DepNo. DepNo. HOD Database State at time “X” Database State at time “Y” Database Instance Database Instance 6 December 2023 CSE, BMSCE 42 Three-Schema Architecture □ This framework is used to describe the structure of a specific database system. □ The goal of the three-schema architecture, is to separate the user applications from the physical database. □ Three-schema architecture defines DBMS schemas at three levels: Internal schema Conceptual schema External schemas 6 December 2023 CSE, BMSCE 43 The three-schema architecture 6 December 2023 CSE, BMSCE 44  The three-schema architecture Empno Ename Salary Deptno Post 6 December 2023 CSE, BMSCE 45 Three-Schema Architecture □ Three-schema architecture defines DBMS schemas at three levels: Internal schema at the internal level to describe physical storage structures and access paths (e.g indexes). □ Typically uses a physical data model. Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. □ Uses a conceptual or an implementation data model. External schemas at the external level to describe the various user views. □ Usually uses the same data model as the conceptual schema. 6 December 2023 CSE, BMSCE 46 Test Your Knowledge Data are stored for a particular time in database is called (A) schema (B) records (C) instance (D) sub schema 6 December 2023 CSE, BMSCE 47 Test Your Knowledge Data are stored for a particular time in database is called (A) schema (B) records (C) instance (D) sub schema 6 December 2023 CSE, BMSCE 48 Question The architecture of database in which the characteristics such as program insulations, multiple user support and the use of catalogs are achieved is classified as A) multiple-schema architecture B) single-schema architecture C) two-schema architecture D) three-schema architecture 6 December 2023 CSE, BMSCE 49 Question The architecture of database in which the characteristics such as program insulations, multiple user support and the use of catalogs are achieved is classified as A) multiple-schema architecture B) single-schema architecture C) two-schema architecture D) three-schema architecture 6 December 2023 CSE, BMSCE 50 QUestion A physical schema (A) describes the relationship between data (B) describes how data is actually stored on disk (C) is the entire database (D) All of the above 6 December 2023 CSE, BMSCE 51 Question A physical schema (A) describes the relationship between data (B) describes how data is actually stored on disk (C) is the entire database (D) All of the above 6 December 2023 CSE, BMSCE 52 Question The process of converting the requests into results between three-schema architecture internal, external and conceptual levels is called A) mapping B) pitching C) transforming D) dependence 6 December 2023 CSE, BMSCE 53 Question The process of converting the requests into results between three-schema architecture internal, external and conceptual levels is called A) mapping B) pitching C) transforming D) dependence 6 December 2023 CSE, BMSCE 54 Thanks for Listening 6 December 2023 CSE, BMSCE 55  Student database Student_Details USN Name Sem Sub 1BM14CS001 Aditya 3 WP 1BM14CS002 Bharath 3 DS Student_ExamFee_Details Student_Grade_Details USN Amount USN Grade 1BM14CS001 1000 1BM14CS001 S 1BM14CS002 1000 1BM14CS002 B Accounts Section Examination Section 1BM14CS001 Aditya 3 WP 1BM14CS001 Aditya 3 WP S 1000 1BM14CS002 Bahart 3 DS B 1BM14CS002 Bahart 3 DS 1000 6 December 2023 CSE, BMSCE 56  Categories of Data Models USN Name Name HOD Sem Student Belongs Department To USN Name Sem Dep 1BM14CS001 Aditya 3 CSE 1BM14IS002 Bharath 3 ISE Dep HOD CSE Dr. H S Guruprasad ISE Dr. Gowrishankar 6 December 2023 CSE, BMSCE 57  USN Name Sem Dep 1BM14CS001 Aditya 3 CSE 1BM14IS002 Bharath 3 ISE 1BM14CS002 Chandan 3 CSE 6 December 2023 CSE, BMSCE 58