MCS-043 Advanced Database Management Systems

368 Pages·2021·6.933 MB·English

by  Dinesh Verma

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
[phone]
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 : [phone]
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.