Q.1 What is a database? Describe the advantages
and disadvantages of using of DBMS.
Ans: Database – A database is a collection of
related data and/or information stored so that it is available to many users
for different purposes.
Advantages Of DBMS:-
1. database system is that the organization can
exert, via the DBA, centralized unnecessary duplication of data and effectively
reduces the total amount of data
2. Reduction of Redundancies and Inconsistencies
- Centralized control avoids storage required. Removing redundancy eliminates
inconsistencies.
3 Data
Integrity - Data integrity means that the data contained in the database is
both
4 Data
Sharing - A database allows the sharing of data under its control by any number
of application programs or users. accurate and consistent. Centralized control
can also ensure that adequate checks. The DBA who has the ultimate
responsibility for the data in the DBMS can ensure incorporated in the DBMS to
provide data integrity.
5. Data Security - Data is of vital importance
to an organization and may be confidential. Such confidential data must not be
accessed by unauthorized persons. one level of a database system without having
to change the schema at the next that proper access procedures are followed.
Different levels of security could be implemented for various types of data and
operations. independence is the capacity to change the conceptual schema
without having to
6. Data Independence - Data independence is the
capacity to change the schema at level.
It is usually considered from two points of view: physical data independence the
internal schema without having to change conceptual schema. Logical data and
logical data independence. Physical data independence is the capacity to change
change external schemas or application programs. hardware and/or software
failures.
7. Providing Storage Structures for Efficient
Query Processing - Database systems
provide capabilities for efficiently executing
queries and updates. Auxiliary files
called indexes are used for this purpose.
Disadvantages Of DBMS
1. Cost of Software/Hardware and Migration - A
significant disadvantage of the DBMS system is cost.
2. Reduced Response and Throughput - The
processing overhead introduced by the DBMS to implement security, integrity,
and sharing of the data causes a degradation of the response and throughput
times.
from a single source namely the database. This
increases the potential of security breaches and disruption of the operation of
the organization because of downtimes and failures.
Q.2 Explain five duties of Database
Administrator.
Ans:
1. DBA administers the three levels of the
database and, in consultation with the overall user community, sets up the
definition of the global view or conceptual level
2. Mappings between the internal and the
conceptual levels, as well as between the of the database.
conceptual and external levels, are also defined
by the DBA. database and that the database is not accessible to unauthorized
users.
3. DBA ensures that appropriate measures are in
place to maintain the integrity of the
4. DBA is responsible for granting permission to
the users of the database and stores the profile of each user in the database with
minimal loss of data.
5. DBA is responsible for defining procedures to
recover the database from failures
Q.3 Explain the terms primary key, candidate key
and foreign key.
Ans: Primary Key – Primary key is one of the
candidate keys that uniquely identifies each row in the relation.
Candidate Key – A candidate key of an entity set
is a minimal superkey, that uniquely identifies each row in the relation.
Foreign Key – Let there are two relations
(tables) R and S. Any candidate key of the relation R which is referred in the
relation S is called the foreign key in the relation S and referenced key in
the relation R. The relation R is also called as parent table and relation S is
also called as child table.
Q.4 Differentiate between logical database
design and physical database design. Show how this separation leads to data
independence.
Ans: Basis Logical Database Design Physical
Database Design Task Maps or transforms the conceptual schema (or an ER schema)
from the high-level data model into a relational database schema. The
specifications for the stored database in terms of physical storage structures,
record placement, and indexes are designed. Choice of criteria The mapping
can proceed in two stages:
-
System-independent mapping but data
model-dependent
-
Tailoring the schemas to a specific DBMS
The following criteria are often used to guide
the choice of physical database design options:
-
Response Time
- Space Utilization
-
Transaction Throughput
Result DDL statements in the language of the
chosen DBMS that specify the conceptual and external level schemas of the
database system. But if the DDL statements include some physical design
parameters, a complete DDL specification must wait until after the physical database design phase is completed. An
initial determination of storage structures and the access paths for the
database files. This corresponds to defining the internal schema in terms of
Data Storage Definition Language. The database design is divided into several
phases. The logical database design and physical database design are two of
them. This separation is generally based on the system which is independent
from the output of the physical database design that is concept of three-level
architecture of DBMS, which provides the data independence.
Therefore,
we can say that this separation leads to data independence because the output of the logical database design is the conceptual
and external level schemas of the database internal schema.
Q.6 What is ODBC? How does Oracle act as ODBC
and give examples of front end uses with
ODBC.
Ans:
ODBC – Open DataBase Connectivity (ODBC) enable the integration of SQL with a general-purpose programming language. ODBC expose database capabilities in a standardized way to the application programmer through an application programming interface (API). Using ODBC, an application can access not just one DBMS but several different ones simultaneously. ODBC achieve portability at the level of the executable by introducing an extra level of indirection. All direct interaction with a specific DBMS happens through a DBMS specific driver. A driver is a software program that
translates the ODBC calls into DBMS specific calls. Drivers are loaded dynamically on demand
since the DBMSs the application is going to access are known only at run-time.
Available drivers are registered with a driver manager. The Oracle database
driver translates the SQL commands from the application into equivalent
commands that the Oracle DBMS understands and takes the result from the DBMS
and translate into equivalent form for the application. Example: Let there be a
DSN named EMPLOYEE through, which we want to access the Oracle database in
Visual Basic.
Dim CN As New ADODB. Connection Dim RS As New
ADODB.
Recordset
CN.Open “DSN=employee”, “scott”, “tiger”
RS.Open “Select * From Emp”, CN
Q. 7 Differentiate between the following:
(i) Theta Join. (ii) Equi Join. (iii) Natural
Join (iv) Outer Join.
Ans:(i) Theta Join – The theta join operation is
an extension to the natural-join operation that allows us to combine selection
and a Cartesian product into a single operation. Consider relations r(R) and
s(S), and let _ be a predicate on attributes in the schema R ??S. The theta
join operation r __ ??s is defined as follows: r __ ??s = ???(r x s)
(ii) Equi Join – It produces all the
combinations of tuples from two relations that satisfy a join condition with
only equality comparison (=).
(iii) Natural Join - Same as equi-join except
that the join attributes (having same names) are not included in the resulting
relation. Only one sets of domain compatible attributes involved in the natural
join are present.
(iv) Outer Join - If there are any values in one
table that do not have corresponding value(s) in the other, in an equi-join
that will not be selected. Such rows can be forcefully selected by using the
outer join. The corresponding columns for that row will have NULLs. There are
actually three forms of the outer-join operation: left outer join ( X), right
outer join (X ) and full outer join ( X ).
Q.8 Draw and explain the three level architecture
of the database system.
Ans:
A DBMS provides three levels of data is said to
follow three-level architecture. The goal of the three-schema architecture is
to separate the user applications and the physical database. The view at each
of these levels is described by a schema. The processes of transforming
requests and results between levels are called mappings. In this architecture, schemas
can be defined at the following three levels:
External Level or Subschema – It is the highest level of database
abstraction where
only those portions of the database of concern
to a user or application program are
included. Any number of user views (some of
which may be identical) may exist for a
given global or conceptual view. Each external
view is described by means of a schema
called an external schema or subschema.
Conceptual Level or Conceptual Schema - At this level of database abstraction all the database
entities and the relationships among them are included. One conceptual view represents
the entire database. This conceptual view is defined by the conceptual schema.
There is only one conceptual schema per database. The description of data at this
level is in a format independent of its physical representation. It also
includes features that specify the checks to retain data consistency and
integrity.
Internal Level or Physical Schema – It is closest to the physical storage method
used. It indicates how the data will be stored and describes the data
structures and access methods to be used by the database. The internal view is
expressed by the internal schema.
Q.9 Explain (a) Heap file (b) Sorted file. Also
discuss their advantages and disadvantages.
Ans:
Heap File is an unordered set of records, stored on a set of pages. This
class provides basic support for inserting, selecting, updating, and deleting
records. Temporary heap files are used for external sorting and in other
relational operators. A sequential scan of a heap file (via the Scan class) is
the most basic access method. Sorted file The sort utility shall perform one of
the following functions:
Sort lines of all the named files together and write the result to the
specified output.
Merge lines of all the named (presorted) files together and write the result
to the specified
output.
Check
that a single input file is correctly presorted. Comparisons shall be based on
one or more sort keys extracted from each line of input (or, if no sort keys
are specified, the entire line up to, but not including, the terminating
<newline>), and shall be performed using the collating sequence of the
current locale.
Q.10 Explain the integrity constraints: Not
Null, Unique, Primary Key with an example each. Is the combination ‘Not Null,
Primary Key’ a valid combination. Justify.
Ans:
Not Null – Should contain valid values and cannot be NULL.
Unique – An attribute or a combination of two or more attributes must
have a unique value in each row. The unique key can have NULL values.
Primary Key – It is same as unique key but cannot have NULL values. A table
can have at most one primary key in it.
For example:
STUDENT
Roll No Name
City Mobile
17 Ankit Vats Delhi 9891663808
16 Vivek Rajput Meerut 9891468487
6 Vanita Punjab NULL
75 Bhavya Delhi 9810618396
_ Roll No is a primary key.
_ Name is defined with NOT NULL, means each
student must have a name.
_ Mobile is unique.
‘Not Null, Primary Key’ is a valid combination.
Primary key constraint already includes
‘Not Null’ constraint in it but we can also add
‘Not Null’ constraint with it. The use of
‘Not Null’ with ‘Primary Key’ will not have any
effect. It is same as if we are using just
‘Primary Key’.
Q.11 Explain the followings :
(i) Nested Queries.
(ii) Cursors in SQL.
(iii) RDBMS.
(iv) View
(v) Application Programming Interface (14)
Ans:
(i) Nested Queries – A SELECT query can
have subquery(s) in it. When a SELECT
query having another SELECT query in it, is called as nested query. Some operations
cannot be performed with single SELECT command or with join operation. There
are some operations which can be performed with the help of nested queries
(also referred to as subqueries). For example, we want to compute the second
highest salary:
SELECT MAX(SAL) FROM EMP WHERE SAL < (SELECT
MAX(SAL) FROM EMP)
Some operations can be performed both by Join
and subqueries. The Join operation is costlier in terms of time and space.
Therefore, the solution based on subqueries is preferred.
(ii) Cursors in SQL – An object used to
store the output of a query for row-by-row processing by the application
programs. Cursors are constructs that enable the user to name a private memory
area to hold a specific statement for access at a later time. Cursors are used
to process multi-row result sets one row at a time. Additionally, cursors keep
track of which row is currently being accessed, which allows for interactive processing
of the active set.
(iii) RDBMS – RDBMS is a database
management system (DBMS) that stores data in the form of relations. Relational
databases are powerful because they require few assumptions about how data is
related or how it will be extracted from the database. As a result, the same
database can be viewed in many different ways. An important feature of relational
system is that a single database can be spread across several tables. This
differs from flat-file databases, in which each database is self-contained in a
single table.
(iv) View – A view is a relation (virtual
rather than base) and can be used in query expressions, that is, queries can be
written using the view as a relation. In other words, a view is a named table
that is represented, not by its own physically separate stored data, but by its
definition in terms of other named tables (base tables or views). The base relations
on which a view is based are sometimes called the existing relations. The definition
of a view in a create view statement is stored in the system catalog. The
syntax to create a view is:
CREATE [OR REPLACE] VIEW <view_name>
[(<aliases>)] AS
<query> WITH {READ ONLY|CHECK OPTION
[CONSTRAINT
<constraint_name>]};
(v) Application Programming Interface –
Commercial SQL implementations take one of the two basic techniques for
including SQL in a programming language – embedded SQL and application program
interface (API). In the application program interface approach, the program communicates
with the RDBMS using a set of functions called the Application Program
Interface (API). The program passes the SQL statements to the RDBMS using API
calls and uses API calls to retrieve the results. In this method, the precompiler is not required.
Q.12What is data independence? Explain the
difference between physical and logical data independence.
Ans:
Data independence is the capacity to change the
schema at one level of a database system without having to change the schema at
the next level. The three-schema architecture allows the feature of data
independence. Data independence occurs because when the schema is changed at
some level, the schema at the next level remains unchanged; only the mapping
between the two levels is changed.
Types of data independence are:
Physical Data Independence – It is capacity to change the internal schema
without having to change conceptual schema. Hence, the external schemas need
not be changed as well. Changes to the internal schema may be needed because some
physical files had to be reorganized to improve the performance of retrieval or
update. If the same data as before remains in the database, the conceptual
schema needs not be changed.
Logical Data Independence - It is the capacity to change the conceptual
schema without having to change external schemas or application programs. The
conceptual schema may be changed to expand the database (by adding a record
type or data item), to change constraints, or to reduce the database (by
removing a record type or data item). Only the view definition and the mappings
need be changed in a DBMS that supports logical data independence. Changes to
constraints can be applied to the
conceptual schema without affecting the external
schemas or application programs.
Q.13 Write short notes on :
(i) Weak and strong entity sets.
(ii) Types of attributes.
(iii) Oracle Instance.
(iv) Mid square method of hashing. (4 x 4 = 16 )
Ans:
(i) Weak and Strong entity sets: A strong
entity set has a primary key. All tuples in the set are distinguishable by that
key. A weak entity set has no primary key unless attributes of the strong
entity set on which it depends are included. Tuples in a weak entity set are
partitioned according to their relationship with tuples in a strong entity set.
Tuples within each partition are distinguishable by a discriminator, which is a
set of attributes. A strong entity set has a primary key. All tuples in the set
are distinguishable by that key. A weak entity set has no primary key unless
attributes of the strong entity set
on which it depends are included. Tuples in a
weak entity set are partitioned according to their relationship with tuples in
a strong entity set. Tuples within each partition are distinguishable by a
discriminator, which is a set of attributes.
(ii) Types of attributes: An attribute's
type determines the kind of values that are allowed in the attribute. For
example, the value version 1 is not valid for an attribute defined as an integer,
but the value 1 is valid. Numeric types (such as integer or real) can also be limited
to a predefined range by their attribute definition.
Choice :
An attribute with a list of predefined values.
ID Reference: An attribute with a value that is
a Unique ID value from another element.
It is typically used for element-based
cross-references.
ID References: An attribute with a value of one
or more Unique ID values from another element.
Integer: An attribute with a whole number value
(no decimal parts). Examples of valid
integers are 22, -22, and +322. An integer can
be defined to fall within a range.
Integers: An attribute with a value of one or
more integers. Enter each number on a separate line in the Attribute Value text
box.
Real An attribute with a real number value, with
or without a decimal part (the value can also be expressed in scientific
notation). Examples of valid real numbers are 2, 22.4, - 0.22, and 2.3e-1. A
real number can be defined to fall within a range.
Reals: An attribute with a value of one or more
real numbers. Enter each number on a
separate line in the Attribute Value text box.
String: An attribute with a value of a series of
characters (text).
Strings: An attribute with a value of one or
more strings. Enter each string on a
separate line in the Attribute Value text box.
Unique ID: An attribute with a value of a unique
text string. An element can have only
one ID attribute (which can be of type Unique ID
or Unique IDs). All ID values must be
unique in the document or book. An element with
a Unique ID attribute can be the source
for an element-based cross-reference.
Unique IDs: An attribute with a value of one or
more unique text strings. Enter each
string on a separate line in the Attribute Value
text box.
(iii) Oracle Instances: An instance is
the (executed) Oracle software and the memory
they use. It is the instance that manipulates
the data stored in the database. It can be
started independent of any database. It consists
of:
1) A shared memory area that provides the
communication between various processes.
2) Upto five background processes which handled
various tasks Whenever an oracle instance starts, the file ‘INIT.ORA’ is
executed.
(iv) Mid
square method of hashing: In mid square hashing, the key is squared and the
address selected from the middle of the squared
number.
Mid square method
* Square K.
* Strip predetermined digits from front and
rear.
* e.g., use thousands and ten thousands places.
Q.14 Discuss the correspondence between the E-R
model construct and the relation model construct.
Show how each E-R model construct can be mapped
to the relational model using the
suitable example?
Ans: An entity-relationship model (ERM): An
entity-relationship model (ERM) is an abstract conceptual representation of
structured data. Entity-relationship modeling is a relational schema database
modeling method, used in software engineering to produce a type of conceptual
data model (or semantic data model) of a system, often a relational database,
and its requirements in a top-down fashion. Diagrams created using this process
are called entity-relationship diagrams, or ER diagrams or ERDs for short.
ER-to-Relational Mapping Algorithm:
1) Step 1: Mapping of regular entity types: For
each strong entity type E, create a relation T that includes all the simple
attributes of a composite attribute.
2) Step2: Mapping of weak entity types: For each
weak entity type W with owner entity type E, create relation R and include all
simple attributes (or simple components of composite attributes) of W as
attributed of R. In addition, include as foreign key attributes of R, the
primary key attribute (s) of relation(s) that correspond to the owner(s) and
the partial key of the weak entity type W, if any.
3) Mapping of relationship types: form a
relation R, for relationship with primary keys of participating relations A and
B as foreign keys in R. In addition to this, any attributes of relationship
become an attribute of R also.
4) Mapping of multivalued attributes: For each
multilvalued attribute A, create a new relation R. This relation R will include
an attribute corresponding to A, plus primary key attribute K-as a foreign key
in R-of the relation that represents the entity type or relationship type that
has A as an attribute.
Q.15 Explain the concept of a data model. What
data models are used in database management systems?
Ans:
Data Model – Model is an abstraction process
that hides irrelevant details while highlighting details relevant to the
applications at hand. Similarly, a data model is a collection of concepts that
can be used to describe structure of a database and provides the necessary
means to achieve this abstraction. Structure of database means the data types,
relationships, and constraints that should hold for the data. In general a data
model consists of two elements:
1. mathematical notation for expressing data and
relationships.
2.perations on the data that serve to express
queries and other manipulations of the data.
Data Models used in DBMSs:
Hierarchical Model - It was developed to model many types of
hierarchical organizations that exist in the real world. It uses tree
structures to represent relationship among records. In hierarchical model, no
dependent record can occur without its parent record occurrence and no
dependent record occurrence may be connected to more than one parent record
occurrence.
Network Model - It was formalised in the late 1960s by the Database Task Group
of the Conference on Data System Language (DBTG/CODASYL). It uses two different
data structures to represent the database entities and relationships between the
entities, namely record type and set type. In the network model, the
relationships as well as the navigation through the database are predefined at
database creation time.
Relational Model - The relational model was first introduced by
E.F. Codd of the IBM Research in 1970. The model uses the concept of a
mathematical relation (like a table of values) as its basic building block, and
has its theoretical basis in set theory and first-order predicate logic. The
relational model represents the database as a collection of relations.
Object Oriented Model – This model is based on the object-oriented
programming language paradigm. It includes the features of OOP like
inheritance, object-identity, encapsulation, etc. It also supports a rich type
system, including structured and collection types.
Object Relational Model – This model combines the features of both
relational model and object oriented model. It extends the traditional
relational model with a variety of features such as structured and collection
types.
