solution for advanced database management system · solution for advanced database management...

Mumbai University I.T (Semester 5)

1 |www.brainheaters.in

Solution for Advanced Database

Management system

December 2015

Index

Q.1)

a) …………………………………………………………………………. 2-3

b) ………………………………………………………………………….3

c) ………………………………………………………………………….3-4

d) ………………………………………………………………………….4-5

Q.2)

a) …………………………………………………………………………. 5-7

b) ………………………………………………………………………….N.A

Q.3)

a) ………………………………………………………………………….8

b) ………………………………………………………………………….N.A

Q.4)

a) ………………………………………………………………………….N.A

b) …………………………………………………………………………. 9-11

Q.5)

a) ………………………………………………………………………….11-15

b) …………………………………………………………………………. N.A

Q.6)

a) …………………………………………………………………………. 16-17

b) 1) …………………………………………………………………….. 18-21

2)……………………………………………………………………….21-23

3)……………………………………………………………………… 23-24



Q1)

(a) Justify Write Ahead Login(WAL) Protocol for a recovery

algorithm that requires both UNDO and REDO .

Ans:

The recovery mechanism must ensure that the log data item is recorded in the

appropriate log entry and that the log entry Is deleted from disk before it is

overwritten with the database on disk .This process is generally known as write

– ahead logging

2 If all pages updated by a transaction are immediately written to disk when the

transaction commits ,this is called a force approach.

To permit recovery for in place updates ,the appropriate entries required for

recovery to record in disk before changes are applied to the database.

Write –ahead logging(WAL) protocol

1 The old image of data cannot be overwritten by its new image in the database

on disk until all UNDO –type log records for the updating transactions have

been written to disk

2 The commit operation of a transaction cannot be completed after all the

REDO –type and UNDO-type logs records for that transaction have been

written to disk

Working



DBMS recovery subsystem may need to maintain a number of lists related to

the transaction being processed .Which may include a list for active transaction

that have started but not committed it may also include lists of all committed

and aborted transaction from the last checkpoint. Maintaining these lists makes

the recovery process more efficient.

b) What is View ? Discuss the difference between the VIEW and Base

relation.

Ans:

A view is defined as a database object that allows us to create a virtual

table in the database whose contents are defined by a query or taken from

one or more tables.

View is defined to hide complexity of query from use.

Types of View

(a) Simple view

The view which are based on only one table called as Simple view.

Allow to perform DML operation with some restriction.

Query defining simple view can not have any join or grouping condition.

(b) Complex view

The views which are based on more than one table called as Complex

view .

Do not allow DML operation to be performed.

Query defining complex view can have join or grouping condition

c) Explain factless fact table

Ans:

A fact table consists of any facts of a business process or in other words if

fact table is empty then it is termed as Factless fact table.

A fact less table not consists of any facts but only consists of keys. It is

essentially an intersection of multiple dimensions.

A fact table is used only for recording some of the events. So, almost all

events tracking tables are Factless Fact tables.

A fact less tables not consists of any flexibility in data warehouse design.



For example ,Single record of student attendance in particular class. The

fact table would consist of 3 dimension tables: the student dimension ,the

time dimension ,and the class dimension.

Fact attendance

Student_id

Class_id

Time_id

If we are not using factless fact tables we will need to make use of two

separate fact tables to answer questions like,

(i) How many students attended a particular class on a specific date?

(ii) How many classes on average does a student attend on a specific date?

d) What is role of Metadata in data warehouse.

Ans:

Metadata in a data warehouse Is similar to the data dictionary in a

database management system ,where we can keep the information about

the data structure and the information about files ,folder and addresses.

Meta data components is the data about the data in the data warehouse

,Metadata In a data warehouse is much more than a data dictionary.

Types of metadata;

(i) Operational Metadata:

Data for the data warehouse comes from several heterogeneous

operational systems of the organization.

These source system contain different types of data structure .The data

elements may have various field lengths and data type.

While selecting data from the source database systems for the data

warehouse , we usually split record from different source files.

When we deliver information to the end-users, we must be able to give

that back to the original source data sets.



(ii) Extraction and Transformation Metadata:

Extraction and transformation metadata contain data about the extraction

of data from the heterogeneous source systems , extraction frequencies

,methods pf extraction and business rules for the data extraction.

Extraction and transformation metadata also contains all information

about the data transformation operations that takes place in the data

staging area of data warehouse.

(iii) End-User Metadata

The end-user metadata act like the roadmap of the data warehouse .

It makes possible to end-user to find all available information from the

data warehouse.

The end-user metadata allows the end-user to make three own business

strategy and look for information in their own ways.

Q2 )

a) What are the different types of SQL injection attacks? What risks

are associated with it. Explain any one in detail.

Ans: Sql injection is a type of security attack on an organization’s database .In

this attack a string is attached by the attacker to an SQL command that forwards

data from the database .This can be advantageous for the attacker as he/she may

get unauthorized access to the database or might get sensitive information .Sql

injection attacks are launched on database in many ways ,such as through

unauthorized database access and manipulation .The different categories of an

SQL injection attack are as follows.

1) SQL Manipulation : Web application allow data access from a database

by using SQL statements .If an application directly passes login

credentials to the database ,it Is prone to an SQL injection attack through

SQL manipulation. For example ,we can take the addition of certain

condition to the WHERE clause in an SQL query .This may skip the

authentication procedure ,and thus may give access to all the activities

that a user can perform .Consider the following statement.

Select* From user_db Where username = ‘Adam’ and password =

‘Adampass’



If an attacker has knowledge of the user name only he/she may change

the previous statement as follows:

Select* From user_db Where username = ‘Adam’ and( password =

‘Adampass’ or ‘a’ = ‘ a ’)

User authentication will be skipped and database access may be granted

who can do anything that an authorized user has permission for.

2) Code injection: Adding more SQL statement to an SQL statement in an

attempt to obtain access right or some sensitive information is termed as

code injection .This is another type of SQL injection attack .Code

injection attack takes advantage of some kind of bug that appears in the

computer system due to invalid data processing .SQL statement can be

added by an attacker with an intention of changing the course of query

execution .

3) Function call injection.

Function call injection is also kind of SQL injection attack in which an

attacker inserts a call to some database function or OS function in an SQL

statement. This may give an opportunity to the attacker to change or

manipulate data .The attacker may also get permission for making system

call through function call injection .Network communication related

aspects performed by a function can be exploited by the attacker by using

function call injection .In addition ,an attacker may take advantage of

dynamic SQL queries and some other functions contained in the custom

packages of a database.

Various kinds of risks are associated with SQL injection attacks.

Some of the risks are as follows:

1) Database fingerprint: Determining the type of database being used at

the backend may help attackers in launching database –specific

attacks. Through SQL injection ,the attackers may determine the

database type that an organization uses.

2) Denial of service: In an SQL injection attack ,the database server can

be flooded with requests by the attacker .Therefore ,it would start

rejecting the requests of legitimate users.



3) Authentication by pass: As explained earlier , manipulation of SQL

statement may result in bypassing the authentication process, thereby

providing the attacker with access to the either database.

4) Injectable parameter identification: SQL injection attack help the

attacker in identifying the type and structure of the database used at

the backend of a Web application .The identification of an injectable

parameters is mainly carried out from the default error page that

contains excessive description of an application.

5) Remote commands execution: SQL injection can be used to execute

the commands remotely .The attacker may execute the arbitrary

commands on a database .

6) Privilege escalation performance: A malicious person can take

advantage of the flaws present in a database by upgrading the access

level of an individual who is not authorized for higher –level roles.

b) Vaidehi Foods and Beverages is a new company which produces Dairy

,Bread and Cookies products with production unit located at Kharghar

,Navi Mumbai .These products are sold in selected regions of India .They

have sales unit at Mumbai ,Pune ,satara ,Sangli and Kholapur.The

president of the company wants sales information.

1) Find the dimension of data warehouse for the given problem statement

and justify your answer .

2) State and represent the concept hierarchy for each dimension.

3) If the president of company wants the report .the number of items sold

and income in each region for each product with time.

3.1 Explain at which concept hierarchy level the query will be?

3.2 Write ROLAP/MOLAP query.

4) Describe the Rollup or Drilldown OLAP operations.

Ans: N.A



Q3)

a) What are the jobs of the analysis, redo and undo phases in

ARIES? Explain with suitable example.

Ans:

(a) Analysis Phase:

It first finds the dirty pages (Data changes those are not committed to

database ) in the available buffer pool

It also identifies all active transactions at the time of the system crash

Identify Redo LSN from which redo should start.

(b) Redo:

In order to restore database system will repeat all actions performed on

database from start of log or from any selected point in log or from

RedoLSN

Then it restores the database state to state at which it was at the time of

the system crash.

RecLSN and RedoLSN avoid redo action already reflected on page.

(c) Undo:

It reverts or undoes all operations of transactions which are not

committed

So after above action now database only reflects actions which are

committed transactions

b) Consider the following database that has to be distributed.

PROS(PNO,PNAME,BUDG)

PAY(TITLE ,SALARY)

EMP(ENO,ENAME,TITLE)

ASG(ENO,PNO,REPONSIBILTY ,DURATION)

1) Show 2 examples of horizontal fragmentation.

2) Show 1 example of derived fragmentation.



3) Show 1 example of vertical fragmentation.

Ans: N.A

Q4 a) Design a schema for library system. Show 1 example each for

primary key and foreign key constraints. Create one suitable ECA

example to enforce the library constraint.

Ans: N.A

b) Explain different types of transparencies in distributed database in

detail.

Ans:



Q5)

(a) Explain with suitable example object identity, object structure

and type constructors in OODB’S.

Ans:



b) PERSON

SS

N

NAM

E

INCOM

E

ADDRE

SS

CIT

Y

STAT

E

ZI

P

SE

X

LAST_DEGR

EE

QUERY1 : SELECT COUNT(*) FROM PERSON WHERE<CONDITION>

QUERY 2: SELECT AVG (INCOME) (*) FROM PERSON

WHERE<CONDITION>



CONSIDER THE PERSON RELATION AND TWO QUERIE SAS

SHOWN:

1) Give <condition> so it is possible to infer the values of individual

tuples.

2) State clearly the remedies by which the possibility of inferring

individual information from statistical queries is reduced.

3) Explain in short Statistical Database Security with above relation

and queries.

Ans: N.A

Q6)

(a) Write suitable relational schema and give at least two queries of

1) Simple Query

2) Complex retreviel query using Group by

3) Recursive Query

4) Nested Queries

Ans: Consider the schema given below.



1) Simple query

Select * from student where name = ‘monali’.

2) Complex retreviel query using Groupgr by

Select * from student where fname = ‘monali’ and address = ‘Mumbai’ group

by ID;

OR

Select * from department were name = ‘I.T’ and I.D = ‘004’ group by Id.

3) Recursive Query (with recursive CTL In SQL)

CTL is common table expression in SQL which is temporary named as result

set, derived from a simple query and defined within the execution scope of a

select, insert update or delete statement

Select * 001 as employee_numberunion all select employee_number +1 from

recursion CTL where employee _number <009 ;

4) Nested query

Select * from course where course_no (select course_no from enrolment where

stud_no(select stud_no from student where stud_name = ‘monali’));



b) Attempt any two of the following.

1) What is meant by Granting and Revoking the privilege?

Ans: Granting Privileges:



2)Explain in short the concurrency control in database .

Ans:



Multiversion Two-phase Locking Using Certify Locks

3)Explain in detail the Information Delivery Component in data

warehouse architecture.

Ans:

solution for advanced database management system · solution for advanced database management...

Documents