Database management system chapter13

DATABASE MANAGEMENT SYSTEM ORACLE SQL AND PL/SQL PART III : Advanced Concepts & Technologies Teaching Aid Material (www.phindia.com/gupta) Authors invite your valuable feedback and suggestions on the book pkdasgupta@gmail.com RadhaKrishna_P@infosys.com

DATABASE MANAGEMENT SYSTEM ORACLE SQL AND PL/SQL PART III : Advanced Concepts & Technologies CHAPTER 13 Query Processing File Organization Distributed Processing

CHAPTER 13 Query Processing 1 of 3 Page reference in the book: 321-325

CHAPTER 13 Query Processing 2 of 3 Major components of Query Processing Engine     Query Parser Query Optimizer Query Code Generator Run-time Database Processor Query Processing is supported by    System catalogs Database statistics Main database Page reference in the book: 321-325

CHAPTER 13 Query Processing 3 of 3 SELECT C.CNAME, P.PNAME FROM CUSTOMER C, PRODUCT P, SALE S WHERE C.CID=S.CID AND P.PID=S.PID AND P.PCOST>10; Two example plans for the above query are: Plan (a) Use the indexed/hashed CUSTOMER, PRODUCT and SALE tables. Find PNAME whose cost is greater than 10 and then join the three tables. Plan (b) Take cross product of tables CUSTOMER, PRODUCT and SALE, which are neither sorted nor hashed. Join rows based on CID and PID and after that select display CNAME and PNAME based on PCOST greater than 10. In case of Plan (a) execution time will be in terms of seconds Plan (b) execution time will be in terms of minutes Page reference in the book: 321-325

CHAPTER 13 File Organization 1 of 17 Sequential  Records are stored in sequence based on the primary key value Indexed     Index Part Data Part Dense Index Sparse Index Page reference in the book: 325 -328

CHAPTER 13 File Organization 2 of 17 Page reference in the book: 325 -328

CHAPTER 13 File Organization 3 of 17 Binary Tree Node: Parent of a node: Child: Root: Leaf Node: Height’ of a tree: Stores the actual data and links to the other node Immediate predecessor of a node An immediate successor of a parent node Designated node which has no parent Node which is at the bottom most end and does not have any child Maximum number of nodes in a path starting from the root node to a leaf node Degree’ of a node: Maximum number of children that is possible for a node A binary tree is empty or consists of a node (called root) together with two binary trees called the left subtree and right subtree of the root. A binary tree is a full binary tree if it contains the maximum possible number of nodes at all levels. Page reference in the book: 328-333

CHAPTER 13 File Organization 4 of 17 Page reference in the book: 328-333

CHAPTER 13 File Organization 10 of 17 Hashing Provides key-to-Address transformation using some function known as hashing function Types of Hashing: Static Hashing Dynamic Hashing Page reference in the book: 333-339

CHAPTER 13 File Organization 11 of 17 Static Hashing Page reference in the book: 333-339

CHAPTER 13 File Organization 12 of 17 Static Hashing Page reference in the book: 333-339

CHAPTER 13 File Organization 13 of 17 Dynamic Hashing Page reference in the book: 333-339

CHAPTER 13 Distributed Processing 1 of 5 Client/Server Architecture (or Two-Tier Arch)  Client program initiates communication and  server program cater the requests Three-Tier Architecture  Data tier (back-end)  Logic tier (middle tier)  Presentation tier (front tier) Page reference in the book: 339-345

CHAPTER 13 Distributed Processing 2 of 5 Client/Server Architecture  Payroll software running on Oracle database server  Payroll application is developed using Visual Basic software  Three computers are used:  One for database (server side or back-end)  Two for clients (or front-ends)  TCP/IP networking protocol is used to establish the network Page reference in the book: 339-345

CHAPTER 13 Distributed Processing 3 of 5 Page reference in the book: 339-345

CHAPTER 13 Distributed Processing 4 of 5 Three-tier Architecture Message Board Oracle database server is used in the data tier (or back-end) In the logic-tier (or middletier) Dot net server is used In the front-end, Internet Explorer is used Page reference in the book: 339-345

CHAPTER 13 Distributed Processing 5 of 5 Page reference in the book: 339-345

CHAPTER 13 Short/Objective Type Questions 1 of 4 Q1. What is the correlation between ‘execution plan’ and ‘query optimization’ in query processing? Q2. Describe optimization technique used in Oracle database server. Q3. Briefly describe ISAM. Q4. What is the difference between B+ Tree and B– Tree? Q5. Illustrates advantages of dynamic hashing over static hashing. Q6. Describe advantages of two-tier and three-tier architectures. Q7. Describe ETL, OLTP and OLAP. Q8. Describe classification and association in data mining with the help of an example. Q9. Write two software suitable for middle-tier of 3-tier architecture. Q10. Elaborate query processing with the help of an appropriate diagram.

CHAPTER 13 Short/Objective Type Questions 2 of 4 Q11. Major component(s) of query processing is/are (a) Query parser (b) Query optimizer (c) Run-time database processor (d) Query file organizer Q12. In dense index, the number of index record is (a) More than the number of records in the data file (b) Less than the number of records in the data file (c) Equal to the number of records in the data file (d) None of the above Q13. Index part and data part are related to (a) B+ tree (b) ISAM (c) B– tree (d) Hashing

CHAPTER 13 Short/Objective Type Questions 3 of 4 Q14. B+ tree and B– tree are form of a (a) Binary tree (b) Balanced tree (c) Basic tree (d) Bi-variant tree Q15. The situation when static hash function generates the same bucket number for several search key values is known as (a) Overflow (b) Underflow (c) Collision (d) Spitting Q16. Two-tier architecture is known as (a) Web based architecture (b) Client server architecture (c) Multi-tier architecture (d) Master–slave architecture

CHAPTER 13 Short/Objective Type Questions 4 of 4 Q17. Example of presentation tier (or front-tier) in 3-tier is/are (a) Oracle database server (b) Internet explorer (c) Mozila firefox (d) Visual basic Q18. Primary job ETL is/are (a) Finding interesting patterns (b) Moving data from source to destination repositories (c) Online transaction processing (d) Online analytical processing Q19. Keyword related to data mining is/are (a) Extraction of non-trivial information (b) Data preprocessing (c) Online analytical processing (d) Revealing unexpected information

CHAPTER 13 Home Assignment WORKOUT SECTION

DATABASE MANAGEMENT SYSTEM ORACLE SQL AND PL/SQL PART III : Advanced Concepts & Technologies Periods Proposed – 12 to 18 Chapter 13 Query Processing, File Organization & Distributed Processing 2-3 Periods [Theory] Chapter 14 Transaction Processing, Concurrency Control, Oracle Architecture, Backup & Recovery 2-3 Periods [Theory] Chapter 15 Data Warehousing 2-3 Period [Theory] Chapter 16 Online Analytical Processing and Materialized Views 2-3 Period [Theory] Chapter 17 Data Mining Overview 2-3 Period [Theory] Chapter 18 Data Mining techniques 2-3 Period [Theory]

THE BOOK The book is organized into three parts to introduce the theoretical and programming concepts of DBMS. Part I (Basic Concepts and Oracle SQL) deals with DBMS basic, software analysis and design, data flow diagram, ER model, relational algebra, normal forms, SQL queries, functions, sub‐queries, different types of joins, DCL, DDL, DML, object constraints and security in Oracle. Part II (Application Using Oracle PL/SQL) explains PL/SQL basics, functions, procedures, packages, exception handling, triggers, implicit, explicit and advanced cursors using suitable examples. This part also covers advanced concepts related to PL/SQL, such as collection, records, objects, dynamic SQL and performance tuning. Part III (Advanced Concepts and Technologies) elaborates on advanced database concepts such as query processing, file organization, distributed architecture, backup, recovery, data warehousing, online analytical processing and data mining concepts and their techniques. KEY FEATURES  Includes about 300 examples to illustrate the concepts.  Offers about 400 objective type questions.  Provides about 100 challenging workouts. TEACHING AID MATERIAL Teaching Aid Material for all the chapters is provided on the website of PHI Learning. Visit www.phindia.com/gupta to explore the contents.

Visit PHI Learning Center www.phindia.com/gupta and Click on Instructor Resources to access Teaching Aid Material for all the 18 chapters of the book.

Database management system chapter13

More Related Content

What's hot

Viewers also liked

Similar to Database management system chapter13

Recently uploaded

Database management system chapter13