ebook img

MCS-043 Advanced Database Management Systems PDF

368 Pages·2021·6.933 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview MCS-043 Advanced Database Management Systems

ADVANCED DATABASE MANAGEMENT SYSTEMS MCS - 043 For Masters In Computer Applications [MCA] Dinesh Verma S. Roy Useful For IGNOU, KSOU (Karnataka), Bihar University (Muzaffarpur), Nalanda University, Jamia Millia Islamia, Vardhman Mahaveer Open University (Kota), Uttarakhand Open University, Kurukshetra University, Seva Sadan’s College of Education (Maharashtra), Lalit Narayan Mithila University, Andhra University, Pt. Sunderlal Sharma (Open) University (Bilaspur), Annamalai University, Bangalore University, Bharathiar University, Bharathidasan University, HP University, Centre for distance and open learning, Kakatiya University (Andhra Pradesh), KOU (Rajasthan), MPBOU (MP), MDU (Haryana), Punjab University, Tamilnadu Open University, Sri Padmavati Mahila Visvavidyalayam (Andhra Pradesh), Sri Venkateswara University (Andhra Pradesh), UCSDE (Kerala), University of Jammu, YCMOU, Rajasthan University, UPRTOU, Kalyani University, Banaras Hindu University (BHU) and all other Indian Universities. Closer to Nature We use Recycled Paper ® GULLYBABA PUBLISHING HOUSE PVT LTD. ISO 9001 & ISO 14001 CERTIFIED CO. Published by: GullyBaba Publishing House Pvt. Ltd. Regd. Office: Branch Office: 2525/193, 1st Floor, Onkar Nagar-A, 1A/2A, 20, Hari Sadan, Tri Nagar, Delhi-110035 Ansari Road, Daryaganj, (From Kanhaiya Nagar Metro Station Towards New Delhi-110002 Old Bus Stand) Ph. 011-45794768 011-27387998, 27384836, 27385249 +919350849407 E-mail: [email protected], Website:GullyBaba.com, GPHbook.com New Edition Price: `129/- ISBN: 978-93-81638-09-5 Copyright© with Publisher All rights are reserved. No part of this publication may be reproduced or stored in a retrieval system or transmitted in any form or by any means; electronic, mechanical, photocopying, recording or otherwise, without the written permission of the copyright holder. Disclaimer: Although the author and publisher have made every effort to ensure that the information in this book is correct, the author and publisher do not assume and hereby disclaim any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from negligence, accident, or any other cause. If you find any kind of error, please let us know and get reward and or the new book free of cost. The book is based on IGNOU syllabus. This is only a sample. The book/author/ publisher does not impose any guarantee or claim for full marks or to be passed in exam. You are advised only to understand the contents with the help of this book and answer in your words. All disputes with respect to this publication shall be subject to the jurisdiction of the Courts, Tribunals and Forums of New Delhi, India only. Home Delivery of GPH Books You can get GPH books by VPP/COD/Speed Post/Courier. You can order books by Email/SMS/WhatsApp/Call. For more details, visit gullybaba.com/faq-books.html Our packaging department usually dispatches the books within 2 days after receiving your order and it takes nearly 5-6 days in postal/courier services to reach your destination. Note: Selling this book on any online platform like Amazon, Flipkart, Shopclues, Rediff, etc. without prior written permission of the publisher is prohibited and hence any sales by the SELLER will be termed as ILLEGAL SALE of GPH Books which will attract strict legal action against the offender. Preface This book (MCS-043) is mainly targeted for the exam of ‘Advanced Database Management Systems’ for all Universities. This topic has been in demand and is the need of current scenario. Providing well researched material to the learners, with focus on examination and learning has been a constant pursuit for us. We at GPH, believe in the power of education and respect the growth and positive contribution that education and respect contribution that education can contribute to a society. The unavailability of material in the market is a demotivating factor for the students and searching and collecting it, becomes a tough task for the student community. We, at GPH wish to strive for excellence as we perceive it not only as earning a degree but a step towards the growth and enrichment. GPH Books are the pioneers in the field and an effort to provide a Strategy and a unique methodology so as to give you excellent performance in the examination. Your goal of attaining a higher grade and securing your future can be made accessible by the use of our published material. Your goal can thus be achievable and realistic! Hoping for your better, brighter and a promising and successful future! Our website is www.gullybaba.com. It is a vital resource for your exams and can have manifold effect on our meticulous preparation. Now you can access us on the net thorough www.doeacconline.com, www.ignounline.com, and www.astrologyeverywhere.com. We greatefully acknowledge the significant contributions of our experts in bringing out this publication. Dear Reader, Welcome in the world of GullyBaba Publishing House (P) Ltd. Profound, in-depth study and research can guarantee you the most reliable, latest & accurate information on the subject. However, as the saying goes, nothing is perfect, but we still believe that there is always a scope for improvement. And we wish to be nothing less but aim for the best. You, the reader can be our best guide in making this book more interesting and user friendly. Your valuable suggestions are welcome! Feedback about the book can be sent at [email protected]. Publisher. TOPICS COVERED Block—1 Data Base Design and Implementation Unit—1 Relational Database Design Unit—2 Database Implementation and Tools Unit—3 Advanced SQL Unit—4 Database System Catalogue Block—2 DBMS Advanced Features and Distributed Database Unit—5 Query Processing and Evaluation Unit—6 Transaction Management and Recovery Unit—7 Database Security and Authorisation Unit—8 Distributed Database Block—3 Enhanced Database Models Unit—9 Object Oriented Database Unit—10 Database and XML Unit—11 Introduction to Data Warehousing Unit—12 Introduction and Data Mining Block—4 Emerging Trends and Example DBMS Architectures Unit—13 Emerging Database Models, Technologies and Applications-I Unit—14 Emerging Database Models, Technologies and Applications-II Unit—15 PostgreSQL Unit—16 Oracle Contents Chapter—1 Database Design..................................................................1-13 Chapter—2 Database Implementation...............................................14-34 Chapter—3 Important ER Diagrams..................................................35-47 Chapter—4 Advanced SQL and System Catalogue......................48-67 Chapter—5 DBMS Advanced Features and Distributed Database......................................................68-112 Chapter—6 Enhanced Database Models....................................113-139 Chapter—7 Emerging Trends and Example DBMS Architectures..................................................................140-150 Chapter—8 ORACLE..........................................................................151-159 Question Papers (1) Dec-2006 (Solved)................................................................................161-180 (2) June- 2007 (Solved).............................................................................181-199 (3) Dec-2007.................................................................................................200-202 (4) Dec-2007 (Solved)................................................................................203-211 (5) June- 2008 (Solved).............................................................................212-226 (6) Dec-2008 (Solved)................................................................................227-233 (7) June-2009 (Solved)..............................................................................234-242 (8) Dec-2009 (Solved)................................................................................243-255 (9) June- 2010 (Solved).............................................................................256-266 (10) Dec-2010 (Solved).............................................................................267-276 (11) June- 2011 (Solved)..........................................................................277-285 (12) Dec-2011 (Solved).............................................................................286-289 (13) June- 2012 (Solved)..........................................................................290-293 (14) Dec-2012 (Solved).............................................................................295-301 (15) June- 2013 (Solved)..........................................................................302-314 (16) Dec-2013...............................................................................................315-317 (17) June- 2014 (Solved)..........................................................................318-332 (18) Dec-2014...............................................................................................333-334 (19) June- 2015............................................................................................335-336 (20) Dec-2015...............................................................................................337-339 (21) June- 2016............................................................................................340-341 (22) Dec-2016...............................................................................................342-343 (23) June- 2017............................................................................................344-345 (24) Dec- 2017 (Solved)............................................................................346-349 (25) June- 2018............................................................................................350-351 (26) Dec- 2018 .........................................................................................352-353 (27) June- 2019 (Solved)..........................................................................354-358 (28) Dec- 2019 .........................................................................................359-360 (29) June- 2020 ..........................................................................................361-362 GPH Books Also Available for : BA(History, Sociology, Public Administration, Political Science, English, Hindi), B.Ed., BCA, MCA, CIC, MBA, B.Com., B.Sc., MA(English, History, Hindi), and for Tourism (DTS, BTS, MTM). Read GPH Books FOR IGNOU AND ALL INDIAN UNIVERSITIES GullyBaba Publishing House 2525/193, Ist Floor, Kanhaiya Nagar Metro Station, Tota Ram Bazar, Onkar Nagar-A, Tri Nagar, Delhi-110035 Phone : 011-27387998, 9350849407, 9312235086 Chapter-1 D D ATABASE ESIGN Q1. What is the need of EER model? Ans. The ER modeling concepts which are sufficient for representing many database schemas for “traditional” database applications, which mainly include data-processing applications in business and industry. Since the late 1970s, however, newer applications of database technology have become commonplace; these include databases for engineering design and manufacturing (CAD/CAM), telecommunications, images and graphics, multimedia, data mining, data warehousing, geographic information systems (GIS), and databases for indexing the World Wide Web, among many other applications. These types of database have more complex requirements than do the more traditional applications. To represent these requirements as accurately and clearly as possible, designers of database applications must use additional semantic data modeling concepts. Features that have been proposed for semantic data models, leading to the enhanced-ER or EER model. The EER(Enhance-ER) model includes all the modeling concepts of the ER model. In addition, it includes the concepts of subclass and superclass and the related concepts of specialization and generalization. Another concept included in EER model is that of category which is used to represent a collection of objects that is the union of objects of different entity types. Q2. What is the use of EER diagram? Ans. The EER diagrams are used to model advanced data model requiring inheritance, specialisation and generalisation. Q3. Define Specialisation & Generalisation. Represent them in an example of diagram. Also discuss their constraints. Ans. The process of defining the subclasses of an entity type is called specialization, where the entity type is called the super class of the specialization. Generalisation is the reverse process of specialization; in other words, it is a process of suppressing the differences between several entity types, identifying their common features into a single super class. For example, the entity type 2 GullyBaba Publishing House (P) Ltd. CAR and TRUCK can be generalized into entity type VEHICLE. Therefore, CAR and TRUCK can now be subclasses of the super class generalized class VEHICLE. Type Number Owner G S E P Vehicle N E E C R I A A L L d I I S S E Car Scooter Truck E D D Mileage Stock Capacity Generalisation and specialisation Constraints and Characteristics of Specialisation and Generalisation A super class may either have a single subclass or many subclasses in specialization. In case of only one subclass we do not use circle notation to show the relationship of subclass / super class. Sometimes in specialization, the subclass becomes the member of the super class after satisfying a condition on the value of some attributes of the super class. Such subclasses are called condition defined subclasses or predicate defined subclasses. For example, vehicle entity type has an attribute vehicle “type”, as shown in the above figure. Disjointness is also the constraints to a specialisation. It means that an entity can be a member of at most one of the subclasses of the specialisation. In an attribute-defined specialization the disjointness constraint means that an entity can be a member of a single sub-class only. In the above figure, the symbol ‘d ’in circle stands for disjoint. But if the real world entity is not disjoint their set of entities may overlap; that is an entity may be a member of more than one subclass of the specialization. This is represented by an (o) in the circle. For example, if we classify cars as luxury cars and cars then they will overlap. MCS-043 GPH Book 3 In some cases, a single class has a similar relationship with more than one class. For example, the sub class ‘car’ may be owned by two different types of owners: INDIVIDUAL or ORGANISATION. Both these types of owners are different classes thus such a situation can be modeled with the help of a Union (u). Q4. What are the constraints used in EER diagrams? Ans. The basic constraints used in EER diagrams are disjointness, overlapping and unions. Q5. How is an EER diagram converted into a table? Ans. For disjointness and union constraints the chances are that we create separate tables for the subclasses and no table for super class. For overlapping constraints it is advisable to have a table of super class. For such cases the tables of subclasses will have only those attributes that are not common to super class except for the primary key. Q6. Explain the EER diagram with example of an institution. Ans. The INSTITUTE Database Example: Consider a INSTITUTE database that keeps track students and their majors, transcripts and registration as well as of the institute’s course offerings. The database also keeps track of the sponsored research projects of faculty and graduate students. A discussion of the requirements that led to this schema follows. For each person, the database maintains information on the person’s Name Name, social security number Ssn, address Address, sex Sex, and birth date BDate. Two subclasses of the PERSON entity type were identified: FACULTY and STUDENT. Specific attributes of FACULTY are rank Rank (assistant, associate, adjunct, research, visiting, etc.), office FOffice, office phone FPhone, and salary Salary, and all faculty members are related to the academic department(s) with which they are affiliated BELONGS (a faculty member can be associated with several departments, so the relationship is M:N). A specific attribute of STUDENT is [Class] (freshman = 1, sophomore = 2, … , graduate student = 5). Each student is also related to his or her major and minor departments, if known (MAJOR and MINOR), to the course sections he or she is currently attending REGISTERED, and to the courses completed TRANSCRIPT. Each transcript instance includes the grade the student received Grade in the course section. GRAD_STUDENT is a subclass of STUDENT, with the defining predicate Class = 5. For each graduate student, we keep a list of previous degrees in a composite, multivalued attribute Degrees. We also relate the graduate student to a faculty advisor ADVISOR and to a thesis committee COMMITTEE if 4 GullyBaba Publishing House (P) Ltd. one exists. An academic department has the attributes name DName, telephone DPhone, and office number Office and is related to the faculty member who is its chairperson CHAIRS and to the college to which it belongs CD. Each college has attributes college name CName, office number COffice, and the name of its dean Dean. A course has attributes course number C#; course name Cname, and course description CDesc. Several sections of each course are offered, with each section having the attributes section number Sec# and the year and quarter in which the section was offered (Year and Qtr). Section numbers uniquely identify each section. The sections being offered during the current semester are in a subclass CURRENT_SECTION of SECTION, with the defining predicate Qtr = CurrentQtr and Year = CurrentYear. Each section is related to the instructor who taught or is teaching it (TEACH, if that instructor is in the database). The category INSTRUCTOR_RESEARCHER is a subset of the union of FACULTY and GRAD_STUDENT and includes all faculty, as well as graduate students who are supported by teaching or research. Finally, the entity type GRANT keeps track of research grants and contracts awarded to the university. Each grant has attributes grant title Title, grant number No, the awarding agency Agency, and the starting date StDate. A grant is related to one principal investigator PI and to all the researchers it supports SUPPORT. Each instance of support has an attributes the starting date of support Start, the ending date of the support (if known) End, and the percentage of time being spent on the project Time by the researcher being supported.

See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.