mgs-043 : advanced database design - paraman.in · mgs-043 : advanced database design time : 3...

MCA (Revised)

Term-End Enamination

December, 2OO7

MGS-043 : ADVANCED DATABASE DESIGN

Time : 3 hours Maximum Marks : 100

Note : Question number 7 is compulsory. Answer anythree quesf ions t'rom the rest.

l, (a) ' Why is the functional dependency called so ?

Consider the following functional dependency :

l if you study -+ you will pass

Create instanies where this functional dependency

will hold,/not hold. 12

MCS-043 P . T : O .1

Downloaded from www.paraman.in

(b) .;Create and explain

the following UML

and functions.

an object oriented'database for

diagram. Assume your attributes

I

(c) Consider the following table :

Identify the multivalued dependencies in the above

table and write an SQL code to check whether the

table satisfies the multivalued dependency identified

by you

Explain the Apriori algorithm for finding frequent

itemsets using an example.

7 0

(d)

MCS-O43

Employee name Project_name Dependgnt-name

Mohan X Shyam

Mohan Y Ram

Mohan X Ram

Mohan Y Shyam

7 0


2. (a) What are assertions ? What is the syntax fordeclaration of an assertion ? Also, give an exampleof assertion 10

(b) Explain any two examples of vendor-specific security. 10

3. (a) Given the relational schemas : '

ENROLL (S#, C#, Section) S# represents studentnumber.

TEACH (Prof, C#, Sectionl C# r:eprgsents coursenumber

ADVISE (Prof, S*) Prof is a thesis advisor of S#

. GRADES (S*, C#, Grade, Year)

STUDENT (S#, Sname) Sname is a student name

Write queries expressed in relational algebra.

(i) .List all students taking courses with Mohan orShyam.

. (ii) Ust all students taking at least one course that

their advisor teaches.

(iiil List those professors who teach more than onesection of the same course. 12

(b) Explain the architecture of a Data warehouse withthe help of a figure. I.

4. (a) Explain the tasks in the KDD process with the helpof a figure. 70

(b) Explain ihe architeciure of Oracle 10g with the helpof a figure. 1 0

IMCS-O43 3 p .T .O;


Jl and a set of

of DDBMS with

" Consider the universal relation

R : {A, B, C, D, E, F, G, H, I ,functional dependencies.

[1'TlF : l o - o t f

l F - cH l[ o -+ t J )

Decompose R into BCNF.

Explaim the component architecture

the help of a figure.

5. (a)

(b)

1 0

1 0

8,000MCS-043


MCA (Revised)

Term-End Examination

June,2OO7

MCS-043 : ADVANCED DATABASE DESIGN


Nofe : Ques tion number 1 is compulsory. Ans wer anyz - L - ^ ^ t l tthree questions from the rest.

l. (a) What is rnultivalued dependency ? How is 4NF''related

to multivalued dependency ? Is 4NF'dependency preserving in nature ? Justify your

answer. s

What are Assertions ? What is the utility ofassertions ? How are Assertions different fromViews ? Describe both Assertions and Views with thehelp of example.

What is UML ? How does UML have an edge overother database designing tools ? With the help of anexample, describe the designing of database by usinga UML class diagram.

(b)

(c)

MCS_043 P.T O.


(d) With the help of a block diagram describe the phases

of Query processing. How do we optimize a Queryunder consideration ? Does Query optimisationcontribute to the measurement of Query cost ?Support your answer with suitable explanation.

What do you mean by transaction ? What are theproperties of a transaction ? Violation of whichproperty causes Lost Update problem ? Justify youranswer with suitable example.

Access control is the feature of which category oflanguage (DSDL, DDL,' DML, DCL) ? At whichlevel of ANSI-SPARC 3 level architecture doesAccess control work ? Describe the commands usedfor Access control. Give an example in support ofoperation of each command

Describe the reference architecture of a distributedDBMS, with the help of a block diagram.

;.

Comp are and contrast the following :

Inclusion dependencies and Template

dependencies

XML and HTML

Centralised zPLand Distributed zPL

Partitioning Clustering and Nearest NeighbourClustering

(e)

(0

(g)

2. (a) I

(i)

(ii1

(iii)

(iv)

MCS-043


(b)

(c)

(a)

(b)

(b)

(c)

(c)

Describe the architecture of Datawarehouse with thehelp of a block diagram. Briefly discuss the role oI,each component of the datawarehouse architecture.

What do you maan by Data mining ? How doesDatabase processing differ from Data miningprocessing ?

What do you mean by clustering ? Briefly describethe concept of Hierarchicai clustering. How isclustering related to Data mining ? I

Write short notes on the following : 6

(i) JDBC

(ii) SPATIAL DATABASES

(iii) PJNF

What is Datagrid ? What is the requirement ofdatagrid (give at least 3 requirements) ? Describe thestructure of datagrid with the help of a blockdiagram.

How does PostgreSQL perform storage andindexing of table's ? Briefly discuss the type of

7indexes involved in PostgreSQl.

What do you mean by tuning of SQL ? What are thesteps involved in tuning of SQL ? Discuss each step

6briefly.

What is SQLJ ? What are the requirements ofSQLJ ? Briefly describe the working of SQLJ. CanSQLJ use dynamic SQL ? If yes, then how ?

Otherwise specify what type of SQL it can use 7

4. (a)

MCS-043 P T O .


(a)5 .

(b)

(c)

(d)

What do you mean by Multiversioning ? What are

the various schemes available for multiversioning ?

f)escribe any one scheme in detail.

What is Audit trail ? Give four benefits provided by

Audit trail to DBMS.

What do you mean by Multilevel Security ? Discuss

the techniques involved in support of multilevel

security.

What are Distributed databases ? How is data

distribution performed in DDBMS ? Give two

advantages and two disadvantages of ,DDBMS.

MCS_043 6,000


MCA (Revised)


December, 2OO8

MCS-04it : ADVANGED DATABASE DESIGN

Time : 3 hours Maximum Marks: 700

Note : Question number 7 is compulsory. Answerany three questions from the rest.

1. (a) Consider the following relation R (A, B, C) :

R

Which of the following dependencies does not

hold ? Give reasons.

( i ) A + B

( i i ) A + + B

(b) What is a data warehouse ? Describe the

process of ETL for a data warehouse. 5

MCS-043 1 P.T.O.

A B c1 2 34 2 35 3 35 3 4


(c) What is timestamp ordering ? Explain

timestamp based protocol for serializable

schedule.

Consider the following requirements :

A car or truck can be a registered-vehicte. A

person, bank or company can own a registered

vehicle. Model this requirement using EER

diagram.

Consider the following relations':

EMPLOYEE (Eno, Ename, Address, Phone,

Salary, Dnum)

DEPT (Dnum, Dlocation, Dname)

PROJECT (Pno, Dname, Dnum)

Find the names of employees using relational

algebra who work on all projects controlled by

Department Number 5.

How will you enforce Referential Integrity

Constraint in Oracle ? Explain with the help of

one example.

Create an object-oriented database using ODL

for' the following schema. Make suitable

assumptions about attributes.


(h) What are the common database security

failures ? What are the SQL commands for

granting permission ? Why are statistical

databases more prone to disclosure ? Explain

with the help of an example.

2. (a) What is the significance of cursors in embedded

SQL ? Explain with the help of an example.

(b) What are mobile databases ? Explain the

characteristics of mobile databases.

(c) What do you mean by deadlock ? How can we

prevent them ? Write an algorithm that checks

. whether the concurrently executing transactions

are in deadlock 10

3. (a) What is data mining ? How is it different from

OLTP ? What is classification in context of data

mining ?

(b) What are data-maits ? What is the significance

of creating them ?

(c) What is XML ? How is it different from

HTML ? What are the advantages of XML ?

Create an XML schema for list of students and

their marks 70

MCS-043 P . T . O .


4. (a) Compare , and contrast Relational database

management systems, Object-relational

database management systems, Object-oriented

database management systems. Suggest one

application for each of these database

management systems.

(b) Explain Join Dependency with the helpof an

example. To which normal form does it

correspond ? Functional dependencies and

multivalued dependencies are special type of

Join dependencies. Justify.

5. (a) Explain the centralized Two-phase Commit

Protocol in Distributed Environment. Give the

algorithm for both coordinaior and participants. 12

(b) Develop a query plan for the following query

and compute its cost :

,SALARY > 4OOOO (EMPLOYEE X DEPARTMEM'

Make suitable assumptions of your own about

the relation schema as well as the database

statistics. I

7 0

1 0

MC5-043


rlcr)(oO

MCA (Revised)


December,2008

MCS-051 : ADVANCED INTERNETTECHNOLOGIES

Time': 3 hours Maximum Marks : LA0

Note : Question number f. is compulsory. Attempt any threequestions from the rest.

Write a MDB (Message Driven Bean) for

news agency that has to capture the data

from various news sources. The newly

written MDB should accept the XML format

of the news. The XML data needs to be

parsed and stored in the database. The

news format is as follows :

<news id> </news-id>

<source> </source>

<date> </ date>

<type-of-news > < / type -of.-news>

<priority> </ priority>

(news-content> </news-content>

What is Java Servlet ? Draw, to represent

the different phases of servlet life cycle.

(u)t .

(b)


(a)

(b)

(a)

z .

D. t .

(c) Explain the different types of Enterprisebeans.

(d) Explain Session Beans and Define their lifecycle.

(e) \A/hat is a custom tags in ISP ? What arethe components that make up a tag libraryin ISP ?

(0 What are the different recovery proceduresin security implementatron ?

What do you mean by session tracking ?Also, explain in brief different ways tohandle session tracking in servlet.

Explain SSL and TLS with their workingand .security measures.

Differentiate between the following :

(i) GET and POST

(tl) Context init Parameter and Servlet

init Parameter

(rit) Servlet Engines and Servlet Chaining

(iv) Data Integrity and System Integrity

Create a custom JSP tags named as " SortedIterate Tags' that can iterate or process anyjava collection. For example it shouldaccept the list of telephone numbers andprint them in sorted manner.

1 0

1 0

(b) t0


1 0(a)4. Write an application to create a XMLdocument from a telephone directorydatabase. The XML document shouldcontain the name of a customer, address,telephone number and the last twelvemonths bill payment summary.

Explain database handling in JSP using

Vpe 2 and type 4 drivers.

What are the different types of implicitobjects used in JSP ? Explain all in brief.

What are the different design anddevelopment Goals for XML and explain theusage of XSD/DTD in XMl.documents.

- o O o -

(u)

(b)

c .

1 0(b)

1 0

1 0


Mcs-o43@

MCA (Revised)


June, 2OO8

MCS-O4i|O : AEVANCED DATABASEDESIGN

TIme : 3 hours Moxlmum Morks : I00

Note : Queslion number 7 is compulsory' Answer any

lhree questlons from the rest'

1. (a) Give an example of MVD. Prove how your MVD can

act as a functional dePendencY' 6

(b) What is views ? Whai are the SQL command5 6

- to specifY a view

- for uPdating of views

- Ior deleHng of views

Illustrate with an o<amPle.

(c) Why was there a need oI Obiect Oriented

Databases ? Compare OODBMS with Relational

DBMs. 6

1Mcs-o43@ P.T .O .


What do you understand bV Qu"ry Processing andQuery Optimisation ? What are Query Trees andQuery Graphs ? Explain with an example of yourchoice.

Consider the follo,r,ing SQL query on theEMPLOYEE relation :

SELECT LNAME, FNAME

FROM EMPLOYEE,

WHERE SATARY > (SFI FCT MAX(SAIARY)FROM EMPLOYEEWHERE DNO = 5)

Is it a correlated nested query ? Suggest a goodexecution plan for the query optimizer. Give ameasure o{ query cost.

Explain the Apriori Algorithm for association rulemining.

2. (a) Compare the following :

(i) Bnbedded SQL and Dgnamic SQL

(ii) XML and HTML

(iii) Deductive Database and Semantic Daiabase

(iv) Centralised and Non-Centralised Database

(b) What is Data warehousing ? Where in industry candata warehouse find its uses ? How can one gaincompetitive edge in business by using datawarehouse ?

(d)

(e)

Mcs-o4so


(c) What are the advantages of distributed database ?

Gve an example of heterogeneous database from

the real-world.

What is Clustering ? How is Clustering and

Segmentation related to daia mining ? Explain

partitioning clustering algorithms.

Explain the signlficance of creating a datadictionary

in a DBMS.

What is Datagrid ? Show typical structure of

Datagrid. What are the application areas of

Datagrid ? Explain with an example.

Create a relational database from the following class

diagram:

What are the goals of Tuning in relational systems ?

Why is Query Tuning needed and what are its

indications ?

What is SQLJ ? Explain the slmtax of Declarations

and Executable Statements. Also show that SQLJ

uses Embedded SQL.

3. (a)

4. (a)

(b)

(c)

(b)

(c)

Code : Sting

Name : Sting

Details : Sting

Name : Sting

Type : Sbing

ID: lnteger

Mcs-043o P . T . O .


5. (a) What ls mulflversion concurrency control ? Explain

how multiversion concurrency control can be

achieved based on Timestamp ordering.

What is mulblevel security ? What are typical security

classes ?

How is daia distribdion performed in DDBMS ? List

some advantages and disaduantages of DDBMS.

4Mcs-043@ 4,000


t'c$o4t-lMCA (Revised)


June, 2OO8

MCS-04it : ADVANCED DATABASE DESIGN

Time : 3 hours Moximum Marks : 7OO

Nole; Question number 7 is conpulsory. Answer any

three questions lrom the rest.

l. (a) What are cursors, stored procedures and triggers ?

Explain with an example of your choice. 5

(b) The concept of "marriage" is identified as a class

in first view, as a relationship in second view and

as an attribute ln third view, How will you solve

this using Viewlntegration problem ? Suggest one

good measure. 4

(c) Differentiate between Logical and Physical database

design. How can UML Diagrams help in database

design ? 5

MCS-043 P . T . O .


(d)

(e)

Explain how Hash Join is applicable to Equi Joinand Natural Joins. Explain the Algorithm and Costcalculation for Simple Hash Join.

How is a database management system differentfrom a data warehouse ? When we have sulficienttools and concepts to develop a DBMS, then why dowe still design warehouses ?

How does granularity of data item affect theperformance of concurrency control ? How aregranularity and database securiiy related ?

What are the 'u:arious problems that aris€ indistributed DBMS environment that are notencountered in a centralised DBMS environment ?

Distinguish between the following :(i) Embedded SQL and Dynamic SeL(it) XML and HTML(iit 2 PC and 3 PC(iv) Data warehousing and Data mining

List all the functional dependencies satisfied bv thefollowing relation :

Write an SQL-Code to identify whether a givenfunctional dependency holds.

(s)

I2. (a)

(b)

A B

a1 b 1 c 1

a 1 b 1 c2

a2 b 1

a2 b1 ca

MCS-O43


What are Semantic Databases ? List {eahrres

Semantic Databases. Explain the process

searching the knowledge in these databases

3. (a) What is Data Mining ? How is Data Mining a part of

Knowledge Discovery process ? What are the goals

of Data Mining and lfuowledge Discovery ?

(b) Create an object-orienied database using ODL for

the following figure :

(c) What is ODBC ? How is ODBC implemented ? How

is access provided to Database on World Wide Web(www ? 6

of

of

ENROLMENT_No.NAMEMARKSCOURSE

ISB_No.TITLEPRICEPUBUSHEBAUTHORS

SUPPUER_IDSUPPUER_NAMESUPPUER-ADDRESSSUPPUER CITY

MCS-043 P . T . O .


4. (a)

5. (a)

(b)

(c)

What do you understand by Open SourceTechnologies ? List some open source DBMS.Explain the working and features of PostgreSQl.

Explain the overuiew of OMCLE architecture. Whatare the main ORACLE tools available for applicaiiondevelopment ? Can mobile use of data centricapplications be performed b9 ORACLE ?

What is data dictionary ? List some features of datadictionary. What are the various approaches toimplement a Distributed Database Catalogue ?

What is shadow paging ? Illustrate with an example.What are the advantages and disadvantages ofshadow paging ?

What are the various reasons for a transaction to failin the middle of execution ?

What is statistical database ? Discuss the problem o{statistical database security.

What is Distributed DBMS ? Explain itsarchitecture. What are the advantaqes of DDBMSover centralised databases ?

(b)

(c)

(d)

MCS-043 9,OOO


I MCS-043 I

MCA (Revised)r--cf) Term-End ExaminationC•1CC) December, 2009CD



Note : Question number / is compulsory. Answer any three

questions from the rest.

1. (a) Consider the following query :

SELECT ENAME, PNAME

FROM EMP, ASG, PROJ

WHERE

PROJ. TITLE = "Comp Engg"

AND ASG. ENO = EMP. ENO

AND ASG .PNO = PROJ.PNO

Assuming that the size of EMP relation is400 tuples, PROJ has 100tuples and ASGhas 20,000 tuples. There are no indexes.Derive a query execution plan for above.Make suitable assumptions.

I-

MCS-043 1 P.T.O.


Two-phase locking protocol uses waiting, 4time stamping and optimistic methods userestacting, to avoid nonserializableexecution. Justify the statement.

What is the difference between document 5type definition and XML schema ? Explainusing an example.

What are the differences between 5datamining and knowledge discovery indatabase (KDD) ? Can these twotechniques be used alternatively. Justify.

What is a join dependency ? Explain with 5

an example. When a join dependency isreferred as trivial ?

(f) Consider a small institute in which students 5register for programmes run by the institute.A program can be a full or a part timeprogram or both. Every student necessarilyregisters in at least one programme andat most three programme. Assumingsuitable attributes, design an EER diagramfor the same.

MCS-043 2


What are the different types of index 5implementations available in POST gre SQL.Explain each one of them.

What are triggers and what is their use ? 5Explain with the help of an example.

2. (a) Why is query expressed in relational algebra 6preferred over query expressed in SQL inquery optimization. Explain this by takinga suitable example

Determine all 4NF violations for the relation 6schema R(X, Y, Z, W) with multivalueddependencies X > >Y and X > > Z.Decompose the relation into 4NF.

The 3-phase commit protocol increases the 5system's availability and doesn't allowtransaction to remain blocked until a failureis repaired. Justify the statement.

(d) What are cursors. Explain with an example. 3

3. (a) Create an object oriented database using 10ODL for the following scheme. Makesuitable assumptions about the attributes

MCS-043 3


Answer the following query using OQL list all theaccount of a customer whose name is 'Q' "XYZ".

"A linear join help to exhibit parallelism inquery execution". Is the statementcorrect ? Justify your answer.Explain the "Deferred databasemodification" approach of log-basedrecovery.

(d) Define Multi-Version two-phase locking.

4

3

3

MCS-043

4 P.T.O.


4. (a) What is semi-structured data, explain with 5example. What is the difference between awell formed XML document and a validXML document ?

What are views and what is their 5significance. How views are created in SQLexplain using one example.

What is data warehousing ? Discuss various 5characteristics of Data warehousing ?

(d) Define multimedia databases and 5challenges in designing them.

5. (a) Differentiate between star schema and 5snowflake scheme using the same example.

(b) Differentiate between : 6

Data-mining queries and data basequery

Clustering and classificationapproaches in data-mining.

(c) How does Oracle manage database 4security ?

(d) What are deadlocks ? How are they 5detected ? Explain with the help of anexample.

- o 0 o -

MCS -043


MCA (Revised)


lune/ 2009



Note : Question number 1- is compulsory. Anstaer any three

questions fro* the rest.

L. (a) Consider the following three transactions

read (X) read (X) read (X)

X=X- 1000 disPtaY (X) Y : = X

Write (X) disPlaY (X)

Insert shared and exclusive locks in T1, T2

and T3 such that the transactions when

executed concurrently do not encounter any

concurrency related Problem.

CD()@c\ls"{

T3T2T1

MCS-043 P.T.O.


(b) Consider the following EER Diagram

Derive relations from the above EER

diagram.

Define simple Hash-Join and explain the

process and cost calculation of Hash-Join

with the help of example.

Make suitable assumptions of your own

about the relation schema as well as the

database statistics.

Expla in the character is t ics of mobi le

databases. Give an application of mobile

databases.

(c)

(d)

MCS-043


(b)

(")

How OLAP support query processing in

dataware house ?

Differentiate between embedded SQL and

dynamic SQL. Give an example of

embedded SQL.

Explain the following with the help of an example 20

diagram, if any :

(u) Semantic databases

(b) Applications of data grid

(") Security features of oracle

(d) Challenges in design of multimedia-database

(e) Benefits of data dictionary

(0 Steps of database design

(g) Clustering in data rnining

(h) Application of data mining

- o O o -

5 .

MCS-043


(")

(0

The decision regarding indexing is a trade

off between read-only queries and update

queries. Elaborate the statement with the

help of example.

Consider the following relational schema

emp (e-no, e-name, title)

pay ( title, Sal)

Let P 1: Sal < 3000 and

P 2: Sal > 3000 be 2 predicates

Perform a horizontal fragmentation of

re lat ion pay, wi th respect to these

predicates, to obtain pay l and pay 2. Using

this fragmentation of pay,perform further

derived horizontal fragmentation of Emp

based on title "Dr" .

(g) What is ETL ? What are diff erent

transformations that are needed during the

ETL Process ?

MCS.043 P.T.O.


(h) Given the following semi structured data in XMLcreate the DTD (Document Type Declaration) forit:

(document>

<employee>

<Name> Ramesh Jain </Name>

<Address> H-1, 25, Delhi </Address)

<Address> B-1, New office, Delhi </Address)

</employee>

<employee>

(u)2.

(b)

<Name> Anuj

<Address> 25,

</employee>

</document>

</Name>

Curgoan, Hanyana< / Address)

IuVhat is data mail and how it is differentfrom dataware house.

Consider the following query :

Select student-id, student-name, subject,marks form STUDENT, RESULT

WHERE STUDENT. Student-id =RESULT.,Student-id

AND RESULT, MARKS>60

Assume suitable relation and statistics.

Create a query evaluation plan for the above..

MCS-043 P.T.O.


No. of Printed Pages : 4 MCS-043

re) MCA (Revised)(NI Term-End Examination



Note : Question number 1 is compulsory. Answer any three

questions from the. rest.

1. (a) The ABC. Bank offers five types of 10

Accounts : loan, checking, savings, dailyinterest saving and money market. Itoperates a number of branches within thecountry. A client of the bank can have anynumber of accounts. Accounts can be self

or a joint account.

Draw an EER diagram for the ABC

bank identifying various entities,attributes and cordinality. Showmeaningful relationships that existamong the entities.

Translate the EER diagram to schemaRelational Model.

1.'•••1

June, 2010

MCS-043

1 P.T.O.


(b) Explain the following protocols for 10concurrency control in transactions with thehelp of an illustration for each :

Tree - protocol

Timestamp - Based Protocol.

(c) With the help of a process diagram, explain 10the various tasks involved in the KnowledgeDiscovery in Databases (KDD) process.

Explain the role of ODBC and JDBC with 6the help of an example.

Is the following XML document well 4formed ? Justify your answer :

?xml version = "1.0" standalone ="yess" ? >

employee >

name > Amit </name >

position > Professor </position >

</employee >

employee >

name > Sumit </name >

position > Reader </position >

</employee>

. (a) What are multimedia databases (MMDBs) ? 10List some of the applications of MMDBs.Describe various contents of MMDBs. Also,mention the challenges in designing ofMMDBs.

MCS-043 2


Define Multi - valued dependencies and Join 6dependencies. Give an example of each.State fourth and fifth normal form.

Consider a relation R (A, B, C) with 4

functional dependencies AB - C and

C --> A . Decompose the relation R into

BCNF relations.

3. (a) With the help of a diagram, explain the 10reference architecture of Distributed DBMS.How is this different from componentArchitecture of DDBMS ?

(b) Explain the following two ways to 10implement the object - oriented concepts inDBMS:

To extend the existing RDBMS toinclude object orientation.

To create a new DBMS that isexclusively devoted to OODBMS.

What is a (DW) Data Ware house ? Explainthe basic components of a DW.

Consider a Supply Data of an organizationhaving three dimensions as Supplier, Partand Project. Draw a star schema withsupply as the fact table. Make suitableassumptions.

6

6

MCS-043

3 P.T.O.


(c) Explain the following in the context of 8ORACLE/ POSTGRESQL :

Triggers

Security

Data Dictionary

Indexing

5. (a) With reference to special Databases and GIS

explain the following :

Application of Geographic Databases 3

Requirements of a GIS 4(iii) Operations on the data captured in 3

GIS

Consider the following query : 5

SELECT Empld, EmpName, DeptName,DeptLve

FROM Employee, Department

WHERE Employee. DeptNo = Department.DeptNo AND Employee, Salary > 10000

Create a query evaluation plan for the query

given above. Make suitable assumptionsabout the relation and statistics.

Explain Cursors. Explain the role of cursors 5in Embedded SQL with the help of anexample.

MCS-043


MCS-043 No. of Printed Pages : 4

C\I CO If) 0.. CD

MCA (Revised)


June, 2011



Question number one is compulsory. Answer any three

questions from the rest.

1. (a) Which MVDs (multivalued dependency) 5 hold for the following table :

P-No. Colour Size

Pi Ci S1 P1 C2 S1 P1 C1 S2

P1 C2 S2

P1 C1 S3

P1 C2 S3

P2 C3 S1

P2 C3 S3

Each product (P) comes in a range of colours (C) and sizes (S)

MCS-043

1

P.T.O.


(b) The organization called ABC undertakes 10 several kinds of projects. Each employee can move on one or more projects. Each project is undertaken on the request of a client. A client can request for several projects. Each project has only one client. A project can use a number of items from different manufacturers and an item may be used by several projects. Before delivery of items to a client, it is tested by testing group in the organization.

Draw an E-R diagram and convert it into a relational schema. Also identify primary key in each relation.

(c) Discuss the shadow paging recovery 5 scheme.

(d) Describe object definition language with the 4 help of an example.

(e) How does embedded SQL differ from 6 Dynamic SQL ? With the help of an example, describe the implementation of cursors and triggers.

(f) How does oracle manage database security ? 5

(g) When is it useful to have replication or 5 fragmentation of data in distributed system ? Explain.

MCS-043 2


2. (a) Distinguish between the followings with 8

examples.

(i) Time stamping and Two-Phase

locking.

(ii) Data mining queries and database

queries.

(b) Consider the following relations : 6

Teacher (T#, T Name)

Practical-Paper (P#, P-Name, Tname)

Conducts (T#, P#)

Write the relational algebra expression for

the following queries.

(i) Get those teacher numbers (T#) who

are not conducting practical number

P2.

(ii) Get details of those teachers who are

conducting practical numbers P1 to P4.

(c) What problems occur in the database system 6 when transactions do not satisfy ACID

properties ? Explain explicitly using suitable

examples.

3. (a) What is ODBC ? What are requirements of 7

ODBC ? Describe the components required

for implementation of ODBC.

MCS-043 3 P.T.O.


(b) What are the different types of index in 7

PostgreSQL ? Explain each one of them.

(c) What is the difference between document 6 type definition and XML schema ? Explain

with an example.

4. (a) What do you understand by query 7 optimization ? What are query trees ?

Explain with an example.

(b) What is multiversion concurrency control ? 8

Explain how multiversion concurrencey

control can be achieved based on time

stamp ordering ?

(c) List steps involved in building of Dataware 5 house.

5. (a) What are views ? How are they 6 implemented can views be used for data

manipulation ? Explain with help of an

example.

(b) Describe normalization using join 6 dependency with the help of an example.

(c) Explain the following terms in the context 8 of DBMS :

(i) Multilevel Security

(ii) Auditing and Control

(iii) Redo log files

(iv) Characteristics of DBMS

MCS-043 4


No. of Printed Pages : 4

MCS-043

MCA (Revised) Term-End Examination

1■4 December, 2011

IN- C1 MCS-043 : ADVANCED DATABASE DESIGN O


Note : Question No. 1 is compulsory. Answer any three questions from the rest.

1. (a) Identify the functional dependexies which hold for the following table :

Emp No. Name Address Dept Dept. Manager

E 1 N 1 A l D 1 E1 E2 N2 A2 D 1 E1 E3 N1 A2 D1 E 1

E4 N3 A3 D2 E9

E9 N4 A4 D2 E9

(b) Construct an E-R diagram for a training institute which imparts soft skills. The institute maintains records about instructors, students, classes, assignments, results (Theory as well as practicals) class timings for each student. The number of subjects, in which the candidate is enrolled and past performances in different subjects is recorded. Document all assumptions that you make about the mapping constraints.

5

8

MCS-043 1 P.T.O.


(c) How is the check pointing information used 7

in the recovery operation following a system crash in DBMS. Explain.

(d) Explain the concept of inheritance in object 5 oriented database system, with the help of an example.

(e) What are assertions ? Explain with an 5 example.

(f) How can you protect your database from 5 statistical query attacks ? Explain.

(g) Explain Clustering in data mining. 5

2. (a) Distinguish between the followings with 8 appropriate examples.

(i) Centralized two phase locking and Distributed two phase locking.

(ii) XML and HTML

(b) Consider the following database 6 employee (emp_name , street , city) , working (emp_name , factory , name_salary) factory (factory_name , city) Manager (emp_name ,manager_name) Write the relational algebra expressions for the following queries.

MCS-043 2


(i) Find the names , streets and cities of all factory employees who work for factories Fl and F5 and earn more than 25000.

(ii) Find all the factory employees who live in the same city as the factory where they are working.

(c) With the help of an example explain 6 insertion and deletion of aromatics.

3. (a) Describe the reference architecture of a distributed DBMS with the help of a block diagram.

(b) How does postgre SQL perform storage and 7 indexing of tables ? Briefly discuss the type of indexes involved in postgre SQL.

(c) What is semi structured data ? Explain with 6 an example.

4. (a) Define Hash join and explain the process 7 and cost calculation of Hash join with the help of an example.

(b) Describe two phase commit protocol in 8 distributed databases.

(c) List the features of semantic database. 5

MCS-043 3 P.T.O.


5. (a) Discuss the 5th normal form and domain 8

key normal form with a suitable example in

each.

(b) What do you mean by deadlock in DBMS ? 8

How can you detect a deadlock ? Suggest a technique that can be used to prevent it.

(c) What are challenges designing multimedia 4

databases ? Discuss.

MCS-043 4


mgs-043 : advanced database design - paraman.in · mgs-043 : advanced database design time : 3...

Documents