Model Question Paper
B.Sc.(Computer Science): III Year: Theory Paper-3
Database Management
Systems
Time:
3Hrs
Max. Marks: 100
_____________________________________________________________
Answer
the following questions. Each question carries 2 Marks.
Section – A (10 X
2 = 20M)
1.a) What is Meta
Data? Why it is important?
b) What is Candidate Key?
c) What is the degree of a relationship?
d) What is denormalization?
e) What is subquery? Give an example.
f) What is trigger?
g) What are the properties of a Transaction?
h) What are the advantages of the Distributed Databases?
i) What is cube?
j) What is Information System?
Section – B (5 X 16 = 80M)
Answer the following questions. Each question
carries 18 Marks.
2. a) Discuss about three schema architecture for
database development.
b) Discuss about the reasons brings you to choose the
database than the file system.
(or)
c) What is Data independence? Discuss about levels of Data
Independence.
d) What is meant by Table? Give the characteristics of a Table.
3. a) Discuss about the components of ER Model?
b) Discuss in detail about advantages and
disadvantages of DDBMS.
(or)
c) Discuss about BCNF. How does it differ from 3NF?
Why it is considered a strong form of
3NF.
d) What is Cursor? How can you access the cursor
in PL/SQL program.
4. a) Discuss about Arithmetic functions in SQL with
example?
b) What is a view in SQL? Discuss the limits of View
operations.
(or)
c) What is Index? Describe the properties of an Indexes.
d) Discuss about System
Development Life Cycle.
5. a) What is serializability? What is Transaction?
(or)
b) Discuss about Client
Server Architecture.
c) Discuss about Data Fragmentation?
6. a) What is Data warehouse? Discuss about the properties
of Data Warehouse.
b) Discuss about Star schema Architecture.
(or)
c) Discuss about concurrency control.
d) What is Data Analysis? How was OLAP operations are helpful in
analysis of Data.
* * * *
Answers :
1.a) What is Meta Data?
Data that describe the properties or
characteristics of end user data and the context of
that data.
b) What is Candidate Key?
An attribute or combination of
attributes that uniquely identifies a row in a relation.
c) What is the degree of a relationship?
The degree of a relationship is the
number of entity types that participate in that
relationship.
d) What is denormalization?
The process of transforming normalized
relations into unnormalized physical record specifications.
e) What is subquery?
A subquery is a query inside a query. A
subquery normally expressed inside parenthesis. The first
query in the SQL statement is known as the outer query. The
query inside the SQL statement is known as the inner query.
f) What is trigger?
A named set of SQL statements that is
considered when a data modification(INSERT, UPDATE,DELETE)
occurs. If a condition stated within the trigger is met then a prescribed
action is taken.
g) What are the properties of a Transaction?
A database transaction is a logical unit of database operations
which are executed as a whole to process user requests for retrieving data or
updating the database. Each transaction must display atomicity, consistency,
isolation, and durability. These properties are some time referred to as the
ACID test.
* Atomicity - All of the tasks (usually SQL requests) of a
database transaction must
be completed; If incomplete due to any
possible reasons, the database transaction must be aborted.
* Consistency - The database must be in a consistent or
legal state before and after the database transaction. It means that a database
transaction must not break the database integrity constraints.
* Isolation - Data used during the execution of a
database transaction must not be used by another database transaction
until the execution is completed. Therefore, the partial results of an
incomplete transaction must not be usable for other transactions until the
transaction is successfully committed. It also means that the execution of a
transaction is not affected by the database operations of other concurrent
transactions.
* Durability - All the database modifications of a
transaction will be made permanent even if a system failure occurs after the
transaction has been completed.
h) What are the advantages of the Distributed Databases?
Advantages of distributed databases:
a. Management of distributed data
with different levels of transparency.
b. Increase reliability and
availability.
c. Reflects organizational
structure.
d. Local autonomy.
e. Protection of valuable
data.
f. Economy.
g. Modularity.
h. Reliable transactions.
i) What is cube?
The database end users can visualize the
stored data as three dimensional cube
known as a data cube. The location of
each data value in the data cube is a
function of the x-,y- and z- axes in a
three dimensional space. The x-,y- and z-
axes represent the dimensions of the data
value. The hypercubes are the data cubes
with n number of dimensions.
j) What is Information System?
Systems designed to support
decision making based on historical point in time and
prediction data for complex queries
or data mining applications.
Section – B
(5
X 16 = 80M)
Answer the following questions. Each question carries 16 Marks.
2. a) Discuss about three schema architecture for
database development.
External schema : This is the views of managers and other
employees who are the database users. This is also represented as a combination
of the enterprise data model and a collection of detailed user views.
Conceptual schema : This schema combines the different
external views into a single, coherent definition of the enterprise’s
data. The conceptual schema represents the view of the data architect or
data administrator.
Internal schema : This schema consists of two separate schemas
: a logical schema and a physical schema. The logical schema is the
representation of data for a type of data management technology. The
physical schema describes how data are to be represented and stored in
secondary storage using a particular DBMS.
b) Discuss about the reasons brings you to choose the
database than the file system.
We have to choose the database than file
system because it has the following
advantages.
* Program data independence : With the database approach, data
descriptions are
stored in a central location called
repository. This property of database system
allows an organization's data to change
and evolve without changing the
application program that process the
data.
* Minimal data redundancy : The database approach does not
eliminate redundancy
completely, but it permits the designer
to carefully control the type and amount of
redundancy.
* Improved data sharing : A database is designed
as shared resource. Authorized
internal and external users are
permitted to use the database and each user is
provided one or more user views to
facilitates this use.
* Improved data consistency : By eliminating the data
redundancy we able to reduce
the data inconsistency.
* Improved data quality : The database approach enable us
for giving provision of
number of tools and processes to improve
data quality.
* Improved data accessibility
* Reduced program maintenance
* increased productivity of application development
(or)
c) What is Data independence? Discuss about levels of Data
Independence.
The disjointing of data descriptions from the application
programs that uses the data is called data independence. Data
independence is one of the main advantages of DBMS. The three-schema
architecture provides the concept of data independence, which means that
upper-levels are unaffected by changes to lower-levels. The three schemas
architecture makes it easier to achieve true data independence. There are two
kinds of data independence.
Physical data independence
* The ability to modify the physical schema without causing
application programs to
be rewritten.
* Modifications at this level are usually to improve
performance.
Logical data
independence
* The ability to modify the conceptual scheme without
causing application programs
to be rewritten.
* Usually done when logical structure of database is altered.
d) What is meant by Table? Give the characteristics of a
Table.
A table consists of a set of columns and an arbitrary number of
unnamed rows. Each row of a table corresponds to a record that contains data
values for a single entity.
Characteristics of a table (relation)
* A table is a two dimensional structure composed of
rows and columns.
* Each table row represents a single entity occurrence
within the entity set.
* Each table column represents an attribute and each column
has a distinct name.
* Each row and column intersection represents a single data
value.
* Each column has a specific range of values known as the
attribute domain.
* Each table must have a primary key.
3. a) Discuss about the components of ER Model?
An E-R model is a logical representation of the data for an
organization or for a business area. An E-R model is expressed in terms
of entities, relationship among those entities and the attributes of both the
entities and their relationships. An E-R model is normally expressed as
an E-R diagram which is the graphical representation of an E-R model.
Entity : A person, place, object, event or concept in the user
environment about which the organization wishes to maintain data.
Ex : person : EMPLOYEE, CUSTOMER,
STUDENT
Strong entity : An entity that exists with depending on other
entity type that means it exists on its own.
Weak entity type : The entity type on whose existence depends on
other entity type.
Attribute : A property or characteristic of an entity type that is
of interest to the organization.
Ex:
STUDENT : S_No, S_Name,
S_Course, S_Address
Required attribute : An attribute of an entity that must have a value
for each entity instance.
Optional attribute : An attribute of an entity that may not have a
value for every entity
instance.
Composite attribute : An attribute that can be broken down into
component parts.
Multivalued attribute : An attribute that may take on more than
one value for a given
entity instance.
Derived attribute : An attribute whose values can be calculated
from related attribute
values.
Relationships : A relationship is an association representing an
interaction among the instance of one or more entity types. That means a
meaningful association between entity types.
Associate entity : An entity type that associates the instance of
one or more entity types and contains attributes that are peculiar to the
relationship between those entity instances.
b) Discuss in detail about advantages and disadvantages of DDBMS
Advantages of DDBMS:
* Data are located near the greatest demand site
* Faster data access
* Faster data processing
* Growth facilitation
* Improved communication
* Reduced operating costs
* User friendly interface
* Less danger of a single point failure
* Processor independence
Disadvantages of DDBMS:
* Complexity of management and control
* Technological difficulty
* Security
* Lack of standards
* Increased training costs
* Increased storage and infrastructure facility.
(or)
c) Discuss about BCNF. How does it differ from 3NF? Why it is
considered a strong
form of 3NF.
Boyce-Codd normal form(BCNF): A relation in which every
determinant is a
candidate key.
3NF : A relation that is in second normal form
and has no transitive dependencies
present.
* It is always possible to obtain a 3NF design without
sacrificing lossless-join or
dependency-preservation.
* If we do not eliminate all transitive dependencies, we may
need to use null values
to represent some of the meaningful
relationships.
* Repetition of information occurs.
If we must choose between BCNF and dependency preservation, it is
generally better to opt for 3NF.
* If we cannot check for dependency preservation
efficiently, we either pay a high
price in system performance or risk the
integrity of the data.
* The limited amount of redundancy in 3NF.
To summarize, our goal for a relational database design is BCNF
* Lossless-join.
* Dependency-preservation.
If we cannot achieve this, we accept 3NF
* Lossless-join.
* Dependency-preservation.
When we decompose a relation, we have to use natural joins or
Cartesian products to put the pieces back together. This takes computational
time.
d) What is Cursor? How can you access the cursor in PL/SQL
program.
Explicit Cursors
An explicit cursor is defined in the declaration section of the PL/SQL
Block. It is created on a SELECT statement which returns more than one row. We
can provide a suitable name for the cursor.
The General Syntax for creating a cursor is as given below:
CURSOR cursor_name IS select_statement;
* cursor_name – A suitable name for the cursor.
* select_statement – A select query which returns multiple rows.
There are four steps in using an Explicit Cursor.
* DECLARE the cursor in the declaration section.
* OPEN the cursor in the Execution Section.
* FETCH the data from cursor into PL/SQL variables or records in
the Execution
Section.
* CLOSE the cursor in the Execution Section before you end the
PL/SQL Block.
DECLARE
CURSOR emp_cur IS SELECT * FROM emp
WHERE salary > 5000;
In the above example we are creating a cursor 'emp_cur' on a query
which returns the records of all the employees with salary greater than
5000. Here 'emp' in the table which contains records of all the
employees.
4. a) Discuss about Arithmetic functions in SQL with example?
Arithmetic functions in SQL :
ABS : returns absolute values of n
ABS(n)
Ex:
SELECT ABS(-26) “ABSOLUTE VALUE” FROM DUAL;
Output:
ABSOLUTE VALUE
26
POWER: returns m raised to the nth power.
Syntax
POWER(m,n)
SELECT POWER(3,2) “RAISED” FROM DUAL;
Output:
RAISED
9
ROUND : returns n, rounded to m places to the right of a decimal point.
Syntax
ROUND(n[,m])
SELECT ROUND(15.19,1) “ROUNDED” FROM DUAL;
Output:
ROUNDED
15.2
SQRT: returns square root of n
Syntax
SQRT(n)
SELECT SQRT(25) “SQUARE ROOT” FROM DUAL;
Output:
SQUARE ROOT
5
EXP : returns e raised to the nth power, where e = 2.71828183
Syntax
EXP(n)
SELECT EXP(5) “EXPO VALUE” FROM DUAL;
Output:
EXPO VALUE
148.413159
GREATEST : returns the greatest value in a list of expressions
Syntax
GREATEST(exp1,exp2……expn)
SELECT GREATEST (5,7,6) “LARGE” FROM DUAL;
Output:
LARGE
7
LEAST : returns least value in a list of expressions
Syntax
LEAST(exp1,exp2,……expn)
SELECT LEAST(5,7,6) “LOW ” FROM DUAL;
Output:
LOW
5
MOD : returns the remainder of a first number divided by second number
passed a parameter
Syntax
MOD(m,n)
SELECT MOD(15.7,7) “MODULUS” FROM DUAL;
Output:
MODULUS
1.7
TRUNC: returns a number truncated to a certain number of decimal
places. The decimal place value must be an integer.
Syntax
TRUNC(number,[decimal_places])
SELECT TRUNC(125.826,1) “TRUNCATION” FROM DUAL;
Output:
TRUNCATION
125.8
FLOOR: returns the largest integer value that is equal to or less
than a number.
Syntax
FLOOR(n)
SELECT FLOOR(24.9) “FLOOR VALUE” FROM DUAL;
Output:
FLOOR VALUE
24
CEIL : returns the smallest value that is greater than or equal to the
number.
Syntax
CEIL(n)
SELECT CEIL(24.9) “CEIL VALUE” FROM DUAL;
Output:
CEIL VALUE
25
b) What is a view in SQL? Discuss the limits of View operations.
To reduce redundant data to the minimum possible, SQL allows
the creation of an object called a view. A view is a mapped to a SELECT
sentence. The table on which the view is based is described in the FROM
clause of the SELECT statement. A view which is mapped to a table will in
effect have a subset of the actual columns of the table from which it is
built. This technique offers a simple effective way of hiding columns of
a table. If a view is used to only look at table data and nothing else
the view is called read-only view. A view that is used to look at table
data as well as insert, update and delete table data is called an updateable
view.
Creating view the following syntax is used.
CREATE VIEW < view name> AS
SELECT <column name
1><column name 2> FROM <table name>
WHERE <column
name> = <expression list>
GROUP BY
<grouping criteria> HAVING <predicate>
For the view to be updateable the view definition must not
include:
* Aggregate function
* DISTINCT, GROUP BY or HAVING clause
* Sub queries
* Constants, strings, or value expressions like book_price * 2.25
* UNION, INTERSECT or MINUS clause
* If a view is defined from another view, the second view should
be updateable.
(or)
c) What is Index? Describe the properties of an Indexes.
An index is an ordered list of contents of column or group of
columns of a table.
Indexing a table is an access strategy that is a way to sort and
search records in the table. Indexes are essential to improve the speed
with which records can be located and retrieved from a table. Indexing
involves forming a two dimensional matrix completely independent of the table
on which the index is being created. This two dimensional matrix will
have a single column, which will hold the sorted data, extracted from the table
columns on which the index is created.
Another column called the address field identifies the location of
the record in the oracle database.
When data is inserted in the table the oracle engine automatically
inserts the data values in the index. For every data value held in the
index the oracle engine inserts a unique ROWID value. This is done for
every data value inserted into the index, without exception. The ROWID
indicates exactly where the record is stored in the table. The records in the
index are sorted in the ascending order of the index columns.
d) Discuss about System Development Life Cycle.
System Development Life Cycle (SDLC) : The traditional methodology
used to develop, maintain and replace information system.
The traditional SDLC is divided into five phases : planning,
analysis, detailed systems design, implementation and maintenance. The
SDLC is an iterative rather than a sequential process.
Planning:
Initial assessment : The SDLC planning phase gives a general view
of an organization and it goals. An initial assessment of the information
flow must be made during this discovery portion of the SDLC. Such an
assessment should answer some important questions:
* should the existing system be continued?
* should the existing system be modified?
* should the existing system be replaced?
Feasibility study : The feasibility study must address the
following.
* The technical aspects of hardware and software requirements.
* The system cost
* The operational cost
Analysis:
Purpose : to analyze the business situation thoroughly to
determine requirements, to structure those requirements, and to select among
competing system features. That means a micro level analysis must be done for
both the individual needs as well as organizational needs. This phase may
address the following questions
* what are the requirements of the system’s end users?
* do those requirements fit into the overall information
requirements?
Detailed system design:
In this design the designer completes the design of the
system. The design includes all the necessary technical specification for
the screens, menus, reports, and other devices that might be used to help make
the system more efficient information generator.
Implementation:
During this phase, the hardware, applications programs and DBMS
software are installed and the database design is implemented. This means
that writing of programs, building data files, testing and installing new
system, training users and finalizing documentation have been taken place.
Maintenance:
The purpose of this phase is that to the operation and usefulness
of a system and to repair and enhance the system and the maintenance activities
are grouped into three types:
* corrective maintenance (system errors)
* Adaptive maintenance (changes in business environment)
* perfective maintenance (enhancing the system)
5. a) What is serializability? What is Transaction?
Serializability :
Concurrent transactions need to be processed in isolation so that
they do not interfere with each other. If one transaction were entirely
processed at a time before another transaction, no interference occur.
Procedures that process transactions so that the outcome is the same as this
are called serializable. Processing transactions using a serializable schedule
will give the same results as if the transactions had been processed one
after the other. Serializability is achieved by different means, but
locking mechanism are the most common type of concurrency control
mechanism.
Transaction:
A transaction is any action that reads from and /or writes to a
database. A transaction may consist simple SELECT statement, series of
INSERT and UPDATE statements. A transaction is a logical unit of work that
must entirely completed or entirely aborted; no intermediate states
are acceptable. All of the SQL statements in the transaction must be
completed successfully. If any of the SQL statements fail, the entire
transaction is rolled back to the original database state that existed before
the transaction started. A successful transaction changes the database
from one consistent state to another. A consistent database state is one
in which all data integrity constraints are satisfied.
(or)
d) Discuss about Client Server Architecture.
Client/server architecture : A LAN based environment in which
database software on a server (called database server or database engine)
performs database commands sent to it from client workstations and application
programs on each client concentrate on user interface function. The total
conceptual database can be distributed across the local PC workstation, an
intermediate server and a centralized server as one distributed database or as
separate but related physical databases.
e) Discuss about Data Fragmentation?
Data fragmentation allows us to break a single object into two or
more segments or fragments. The object is from user's database, a system
database or a table. Each fragment can be stored at any site over a computer
network.
* Horizontal fragmentation : refers to the division of a relation
into subsets(fragments)
of tuples(rows). Each fragment is stored at a different
node and each fragment has
unique rows.
* Vertical fragmentation : refers to the division of a
relation into attribute(column)
subsets. Each subset(fragment) is stored at a
different node and each fragment has
unique columns.
* Mixed fragmentation : refers to a combination of horizontal and
vertical strategies.
That means a table may be divided into several horizontal
subsets(rows), each one
having a subset of the attributes(columns).
6.a) What is Data warehouse? Discuss about the properties
of Data Warehouse.
Bill Inmon, father of data warehouse, defines the term as "an
integrated, subject-oriented, time-variant, nonvolatile collection of data that
provides support for decision making".
Charateristics of Data Warehouse
* Integrated : Provide a unified view of all data elements with a
common definition
and representation for all business units.
* Subject-oriented : Data are stored with a subject
orientation that facilitates multiple
views of the data and facilitates decision making.
* Time-variant : Data are recorded with a historical
perspective in mind. Hence, a
time dimension is added to facilitate data analysis
and various time comparisons.
* Nonvolatile : Data cannot be changed. Data are added
only periodically from
historical systems. Once the data are properly
stored, no changes are allowed.
b) Discuss about Star schema Architecture.
A star schema consists of fact tables and dimension tables. Fact
tables contain the quantitative or factual data about a business--the
information being queried. This information is often numerical, additive
measurements and can consist of many columns and millions or billions of rows.
Dimension tables are usually smaller and hold descriptive data that reflects
the dimensions, or attributes, of a business. SQL queries then use joins
between fact and dimension tables and constraints on the data to return
selected information.
Fact and dimension tables differ from each other only in their use
within a schema. Their physical structure and the SQL syntax used to create the
tables are the same. In a complex schema, a given table can act as a fact table
under some conditions and as a dimension table under others. The way in which a
table is referred to in a query determines whether a table behaves as a fact
table or a dimension table.
Even though they are physically the same type of table, it is important
to understand the difference between fact and dimension tables from a logical
point of view.
A well-designed schema provides dimension tables that allow a user to
browse a database to become familiar with the information in it and then to
write queries with constraints so that only the information that satisfies
those constraints is returned from the database.
Terminology
The terms fact table and dimension table represent the roles these
objects play in the logical schema. In terms of the physical database, a fact
table is a referencing table. That is, it has foreign key references to other
tables. A dimension table is a referenced table. That is, it has a primary key
that is a foreign key reference from one or more tables.
Simple star schemas
Any table that references or is referenced by another table must
have a primary key, which is a column or group of columns whose contents
uniquely identify each row. In a simple star schema, the primary key for the
fact table consists of one or more foreign keys. A foreign key is a column or
group of columns in one table whose values are defined by the primary key in
another table. When a database is created, the SQL statements used to create
the tables must designate the columns that are to form the primary and foreign
keys.
(or)
c) Discuss about concurrency control.
The coordination of the simultaneous execution of transactions in
a multi user database system is known as concurrency control. The
objective of concurrency control is to ensure serializability of transactions
in a multi user environment. During the concurrency control there is
possibility to get several data integrity and consistency problems. The
three main problems are lost updates, uncommitted data and inconsistent
retrievals.
Lost updates:
The lost update problem occurs when two concurrent transactions,
T1 and T2 are updating the same data element and one of the updates is lost.
Uncommitted data :
The problem of uncommitted data occurs when two transactions, T1
and T2 are executed concurrently and the first transaction(T1) is rolled back
after the second transaction (T2) has already accessed the uncommitted data –
thus violating the isolation property of transaction.
Inconsistent retrievals:
Inconsistent retrievals occur when a transaction accesses data
before and after another transactions finish working with such data.
d) How was OLAP operations are helpful in analysis of Data.
Online analytical processing(OLAP) : The use of a set of
graphical tools that provides users with multidimensional views of their data
and allows them to analyze the data using simple windowing techniques.
The OLAP creates an advanced data analysis environment that
supports decision making, business modeling and operations research. OLAP
systems share four main characteristics:
* They use multidimensional data analysis techniques.
* They provide advanced database support.
* They provide easy to use end user interfaces.
* They support client/server architecture.
Multidimensional data analysis techniques are augmented by the
following functions
* Advanced data presentation functions such as 3D
graphics, pivot tables, data rotation, and three dimensional cubes.
* Advanced data aggregation, consolidation and classification functions:
These allow the data analysts to create multiple data aggregation levels, slice
dice data and drill down and roll up data across different dimensions an
aggregation levels.
* Advanced computational functions : These include business
oriented variables, financial and accounting ratios and statistical and
forecasting functions.
Advanced database support:
OLAP tools have advanced data accessed features to get efficient
decision support. Some of such features are
* Access to many different kinds of DBMSs, flat files,
internal and external sources
* Access to aggregated data warehouse data.
* Advanced data navigation features such as drill-down and rolled
up.
No comments:
Post a Comment