## New Update

IGNOU BCA, MCA New, PGDCA New, M.com New, PGDIBO, DECE/ECCE solved assignments July-January 2024-25 & January -July 2024 now available. Note: If do not get solved assignments after payment then mail us. Thanks You...

## Tuesday 25 August 2020

IGNOU MCA MCS-043 4th semester term-end examination (ADVANCED DATABASE
MANAGEMENT SYSTEMS) books/block, term-end exam notes, upcoming guess paper, important questions, study materials, previous year papers download.

IGNOU BCA/MCA all semester new revised (1st semester,2nd semester,3rd semester,4th semester,5th semester and 6th semester) solved assignments,term-end exam notes, study materials, important questions, books/blocks (June-December) free download.

Notes-1

1. (a) Consider the following situation :
"A company has two employee named "A" and "B". The company has two projects P1 and P2 project P1 uses languages C and C++, whereas project P2 uses languages C and JAVA. Employee whose name is "A" works on both the projects P1 and P2,whereas employee named "B" works on project P2 only."
(i) Represent the information given above as a relation/ table S (employee name,project, language).
(ii) List the FDs and MVDs in the table above.
(iii) Normalise the table upto 4th Normal Form.
(b) Define the term - "view" with the help of an example.
(c) Consider the following relations.
Supplier (sid, Sname)
parts (pid, pname)
Supp-part (sid, pid, quantity)
(i) Write a relational algebraic expression for the following query :
"List the names of the supplier's who have supplied the part whose pname is "Bolt".
(ii) Draw the query tree for the query given above.
(d) How is a checkpoint useful in log based recovery ? Explain it with the help of an example/ diagram
(e) Represent the following using Object Definition language (ODL)

Please note that diagram indicates that a book is issued to a student who receives it.The attributes of Book objects are Book ID,Title and Author; and the attributes ofstudent objects are SID and SNAME.
(f) What is a data warehouse ? Why is it needed by an organisation ? Explain the process of
Data Extraction, Transformation and Loading (ETL) in the context of a data warehouse
(g) What is ODBC ? Why is it needed ? What are the various components needed to implement ODBC ?
(h) What are control files in Oracle ? What does a control file contain ? What is the role of control file in starting an instance of oracle.What is the purpose of redo log file during this time.

2. (a) Draw an EER diagram for the following situation : A vehicle can be owned by an Individual or an organisation. In case the vehicle is owned by an organisation then the database should store the name and designation of contact person in the organisation, the office address, and the name of the organisation along with name of the head. However, for an individual owner just name, address and
phonenumber of owner needs to be recorder. "You may assume that the basic information that is to be stored about the vehicle should be - vehicle registration number type, date of model, date of purchase."
(b) Convert the EER diagram created in part
(a) to equivalent RDBMS tables having proper keys and constraints.
(c) Define the term data mining. How data mining is different from Knowledge Discovery in data bases (KDD) ? Explain the steps of KDD with the help of an example.
(d) Explain any three common database security failures. Explain with the help of an example, how SQL can be used to enforce access control in a database.

3. (a) Assume the following three transactions :
T1 : "Increment the X account by an amount Rs. 1000/- and Decrement Y is account by an amount. Rs. 1000/-" T2 : Decrement X account by an amount of Rs. 2000/- and increment Z's account by Rs. 2000/- T3 : Add the amount present in the accounts X, Y and Z to Total amount use the shared (s) mode and exclusive (k) mode locks to write the pseudo code of transactions T1, T2 and T3, such that they can be executed concurrently. Explain your answer.
(b) What is embedded SQL ? Give an example of embedded SQL statement in 'C' language. What is the role of cursor in embedded SQL ? Explain with the help of an example.
(c) Differentiate between the following :
(i) JDBC versus ODBC
(ii) Distributed Database versus centralized Database
(iii) Redo versus undo

4. (a) Consider the following XML document :
<?xml version = "1.0"encity ="UTF — 8"?>
<Customer>
<name>
<fname>Lokesh </fname>
<lname> Jain </lname>
</name>
<type>Home</ type>
<phone>297511</ phone>
</customer>
<customer>
<name>
<fname> Ramesh</fname>
<lname>Nagesh</ lname>
</name>
<type> Company</type>
<phone>293575</phone>
<phone>33333</ phone>
</ customer>
Write the DTD for the XML document given above.
(b) What is the role of a trigger ? Explain with the help of an example. How is a trigger
different to a stored procedure ? Explain.
(c) Consider the following Relation schema R (Faculty, Dean, Department,Chairperson, professor, Rank, student) and the following set of functional dependencies :
Faculty → Dean
Dean → Faculty
Department → Chairperson
Professor → Rank, chairperson
Department → Faculty
Student → Department, Faculty, Dean
Professor, Rank → Department, Faculty
Obtain 3NF decomposition of the above relation schema and show each step explicitly

5. (a) Explain the following with the help of an example/diagram, if needed.
(i) Role of system catalogue
(ii) Nested loop join
(iii) Time stamp and its use
(v) Fragmentation and allocation schema
(vi) Snoflake schema in data warehouse
(b) Explain the multidimensional Data Modeling for a Datawarehouse with an example.

Notes-2

1. (a) Explain lossless decomposition and dependency preserving. Consider the
following relational scheme : R (A, B, C, D, E, F) and FDs
A → BC, C A, D → E, F → A, E → D Is the decomposition of R into R1 (A, C, D),R2 (B, C, D) and R3 (E, F, ID) lossless and dependency preserving.
(b) What is meant by a schedule in the context of concurrent transactions in Database ? Also explain serial and serializable schedules with the help of a suitable example.
(c) Define locking in concurrency control.Discuss the various types of locking techniques.
(d) How does Boyce-codd Normal form differ from 3NF ? Why is it considered stronger than 3NF, explain using a suitable example.

2. (a) Discuss the five basic operations of relational algebra with suitable example for each.
(b) Describe the architecture of distributed databases with the help of a diagram.

3. (a) Consider the following scheme for project database :
Project (PR_NO, PR_Name, PR_Manager)
Employee (Emp_NO, Emp_Name)
Assigned_To (PR_NO, Emp_No)
(i) Write the DDL statements for the Project Database. Clearly specify the primary and foreign keys.
(ii) Write the following queries in SQL :
• List the details of employees working on PR_NO "A34" and "B64". 3x2=6
• Delete the record of employee whose Emp_No is "E64221".
• List the name of employees who are working on a project for which "Ramesh" is a Project
Manager.
(b) Define weak entity set in ER diagram. How are keys of the weak entities identified ? Discuss the mapping of strong entity set and weak entity set into relations.

4. (a) Describe the term MVD (Multi-Valued Dependency) and JD (Join Dependency) in the context of relational DBMS by giving an example. Also, differentiate between 4 NF and 5 NF with an example.
(b) How are data marts different from data warehouse ? Explain the different types of data marts.
(c) Explain, Business Intelligence in context of Data warehousing.

5. Explain the following with the help of examples or illustration.
(a) Postgres SQL
(c) Semantic Query Optimization
(d) Spatial and Multimedia Databases

Notes-3

1. (a) What is multivalued dependency ? How is 4 NF related to multivalued dependency ? Is 4 NF decomposition dependency preserving ? Justify your answer.
(b) Differentiate between Assertions and Views. Discuss each with a suitable example.
(c) What is UML ? How does UML have an edge over the other database designing tools ? Describe database designing by using a UML class diagram.
(d) What do you mean by the term database transaction ? Briefly discuss the properties of database transactions performed in a concurrent environment.
(e) Describe the reference architecture of a distributed DBMS, with the help of a block diagram.
(f) How does database differ from a data warehouse ? How does processing in database differ from that in data warehouse ?
(g) What is a Datagrid ? Describe the structure of data grid, with the help of a block diagram. Why is the datagrid required ?
(h) What do you mean by tuning of SQL ? Briefly discuss each step involved in the tuning of SQL.

2. (a) Describe the phases of Query processing by using a block diagram. Discuss the process
of Query optimization, by using suitable example.
(b) Compare and contrast the following :
(i) Inclusion dependencies and Template dependencies
(ii) XML and HTML
(iii) Centralized 2PL and Distributed 2PL
(iv) K-Means clustering and Nearest Neighbour clustering

3. (a) Discuss classification as a tool in Data Mining. Describe the ID 3 algorithm for classifying data sets with a suitable example.
(b) What do you mean by Multiversioning ? What are the various schemes available for multiversioning ? Describe any two schemes in detail

4. (a) How does PostgreSQL perform storage and indexing of tables ? Briefly discuss the types of indexes involved in PostgreSQL.
(b) What is SQLJ ? What are the requirements of SQLJ ? Briefly describe the working of SQLJ. "Can SQLJ use dynamic SQL ?" If yes, then how ? Otherwise, specify the type of SQL it can use.

5. (a) Write short notes on any two of the following :
(i) JDBC
(ii) PJNF
(iii) Spatial Databases
(b) What do you mean by Multilevel Security ? Discuss the methods involved in the support of multilevel security.
(c) What is Audit trail ? Give four benefits provided by Audit trail in the context of DBMS.
(d) Discuss the constraints associated with Generalization in an E-R Model.

IGNOU MCA MCS-043 4th semester term-end examination (ADVANCED DATABASE

Also available:-