technicalsymposium

Free Advertisement Procedure and Free Email /Whatsapp Updates

Free Email Alerts-Subscribe Below


Enter your Email ID here:

Note:Login & Check Your Email Inbox and Activate Confirmation Link

Technical Round MaterialsDBMS--Free Download

Technical Round MaterialsDBMS--Free Download

1. What is database?

A database is a collection of information that is organized. So that it can easily be accessed, managed, and updated.

2. What is DBMS?

DBMS stands for Database Management System. It is a collection of programs that enables user to create and maintain a database.

3. What is a Database system?

The database and DBMS software together is called as Database system.

4. What are the advantages of DBMS?

I. Redundancy is controlled.

II. Providing multiple user interfaces.

III. Providing backup and recovery

IV. Unauthorized access is restricted.

V. Enforcing integrity constraints.

5. What is normalization?

It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties
(1).Minimizing redundancy, (2). Minimizing insertion, deletion and update anomalies.

6. What is Data Model?
A collection of conceptual tools for describing data, data relationships data semantics and constraints.

7. What is E-R model?

This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

8. What is Object Oriented model?

This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

9. What is an Entity?
An entity is a thing or object of importance about which data must be captured.

10. What is DDL (Data Definition Language)?

A data base schema is specifies by a set of definitions expressed by a special language called DDL.

11. What is DML (Data Manipulation Language)?

This language that enable user to access or manipulate data as organised by appropriate data model. Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data. Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data

12. What is DML Compiler?

It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.

13. What is Query evaluation engine?

It executes low-level instruction generated by compiler.

14. What is Functional Dependency?

Functional Dependency is the starting point of normalization. Functional Dependency exists when a relation between two attributes allows you to uniquely determine the corresponding attribute?s value.

15. What is 1 NF (Normal Form)?

The first normal form or 1NF is the first and the simplest type of normalization that can be implemented in a database. The main aims of 1NF are to:

1. Eliminate duplicative columns from the same table.

2. Create separate tables for each group of related data and identify each row with a unique column (the primary key).

16. What is Fully Functional dependency?

A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.

17. What is 2NF?

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

18. What is 3NF?

A relation is in third normal form if it is in Second Normal Form and there are no functional (transitive) dependencies between two (or more) non-primary key attributes.

19. What is BCNF (Boyce-Codd Normal Form)?

A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a candidate key.

20. What is 4NF?

Fourth normal form requires that a table be BCNF and contain no multi-valued dependencies.

21. What is 5NF?

A table is in fifth normal form (5NF) or Project-Join Normal Form (PJNF) if it is in 4NF and it cannot have a lossless decomposition into any number of smaller tables.

22. What is a query?

A query with respect to DBMS relates to user commands that are used to interact with a data base.

23. What is meant by query optimization?

The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

24. What is an attribute?
It is a particular property, which describes the entity.

25. What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables.

26. What ? s difference between DBMS and RDBMS?

DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity.

27. What is SQL?

SQL stands for Structured Query Language. SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.

28. What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.

29. What is a view?

A view may be a subset of the database or it may contain virtual data that is derived from the database files but is not explicitly stored.

30. What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.

31. What is Index?

An index is a physical structure containing pointers to the data.

32. What is extension and intension?

Extension -It is the number of tuples present in a table at any instance. This is time dependent.

Intension -It is a constant value that gives the name, structure of table and the constraints laid on it.

33. What do you mean by atomicity and aggregation?

Atomicity-Atomicity states that database modifications must follow an ?all or nothing? rule. Each transaction is said to be ?atomic.? If one part of the transaction fails, the entire transaction fails.

Aggregation - A feature of the entity relationship model that allows a relationship set to participate in another relationship set. This is indicated on an ER diagram by drawing a dashed box around the aggregation.

34. What is RDBMS KERNEL?

Two important pieces of RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system- level data structures used by the kernel to manage the database.

35. Name the sub-systems of a RDBMS?

I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, Lock Management.

36. How do you communicate with an RDBMS?

You communicate with an RDBMS using Structured Query Language (SQL)

37. Disadvantage in File Processing System?

Data redundancy & inconsistency.

Difficult in accessing data.

  • Data isolation.
  • Data integrity.

Concurrent access is not possible.

  • Security Problems.

38. What is VDL (View Definition Language)?

It specifies user views and their mappings to the conceptual schema.

39. What is SDL (Storage Definition Language)?

This language is to specify the internal schema. This language may Specify the mapping between two schemas.

40. Describe concurrency control?

Concurrency control is the process managing simultaneous operations against a database so that database integrity is no compromised. There are two approaches to concurrency control.

The pessimistic approach involves locking and the optimistic approach involves versioning.

41. Describe the difference between homogeneous and heterogeneous distributed database?

A homogenous database is one that uses the same DBMS at each node. A heterogeneous database is one that may have a different DBMS at each node.

42. What is a distributed database?

A distributed database is a single logical database that is spread across more than one node or locations that are all connected via some communication link.

43. Explain the difference between two and three-tier architectures?

Three-tier architecture includes a client and two server layers.

The application code is stored on the application server and the database is stored on the database server. A two-tier architecture includes a client and one server layer. The database is stored on the database server.

44. Briefly describe the three types of SQL commands?

Data definition language commands are used to create, alter, and drop tables. Data manipulation commands are used to insert, modify, update, and query data in the database. Data control language commands help the DBA to control the database.

45. List some of the properties of a relation?

Relations in a database have a unique name and no multivalued attributes exist. Each row is unique and each attribute within a relation has a unique name. The sequence of both columns and rows is irrelevant.

46. Explain the differences between an intranet and an extranet?

An Internet database is accessible by everyone who has access to a Web site. An intranet database limits access to only people within a given organization.

47. What is SQL Deadlock?

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource.

48. What is a Catalog?

A catalog is a table that contains the information such as structure of each file, the type and storage format of each data item and various constraints on the data .The information stored in the catalog is called Metadata.

49. What is data ware housing & OLAP?

Data warehousing and OLAP (online analytical processing) systems are the techniques used in many companies to extract and analyze useful information from very large databases for decision making .

50. Describe the three levels of data abstraction?

Physical level: The lowest level of abstraction describes how data are stored.

Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.

View level: The highest level of abstraction describes only part of entire database.

51. What is Data Independence?

Data independence means that the application is independent of the storage structure and access strategy of data.

52. How many types of relationship exist in database designing?

There are three major relationship models:-

One-to-one

One-to-many

Many-to-many

53. What is order by clause?

ORDER BY clause helps to sort the data in either ascending order to descending

54. What is the use of DBCC commands?

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

55. What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared.

56. What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

57. What is Hashing technique?

This is a primary file organization technique that provides very fast access to records on certain search conditions.

58. What is a transaction?

A transaction is a logical unit of database processing that includes one or more database access operations.

59. What are the different phases of Transaction?

Analysis phase

Redo phase

Undo phase

60. What is ? transparent dbms ? ?

It is one, which keeps its physical structure hidden from user.

61. What are the primitive operations common to all record management System?

Addition, deletion and modification.

62. Explain the differences between structured data and unstructured data.

Structured data are facts concerning objects and events. The most important structured data are numeric, character, and dates.

Structured data are stored in tabular form. Unstructured data are multimedia data such as documents, photographs, maps, images, sound, and video clips. Unstructured data are most commonly found on Web servers and Web-enabled databases.

63. What are the major functions of the database administrator?

Managing database structure, controlling concurrent processing, managing processing rights and responsibilities, developing database security, providing for database recovery, managing the DBMS and maintaining the data repository.

64. What is a dependency graph?

A dependency graph is a diagram that is used to portray the connections between database elements.

65. Explain the difference between an exclusive lock and a shared lock?

An exclusive lock prohibits other users from reading the locked resource; a shared lock allows other users to read the locked resource, but they cannot update it.

66. Explain the "paradigm mismatch" between SQL and application programming languages.

SQL statements return a set of rows, while an application program works on one row at a time. To resolve this mismatch the results of SQL statements are processed as pseudofiles, using a cursor or pointer to specify which row is being processed.

67. Name four applications for triggers.

(1)Providing default values, (2) enforcing data constraints,

(3) Updating views and (4) enforcing referential integrity

68. What are the advantages of using stored procedures?

The advantages of stored procedures are (1) greater security, (2) decreased network traffic, (3) the fact that SQL can be optimized and (4) code sharing which leads to less work, standardized processing, and specialization among developers.

69. Explain the difference between attributes and identifiers.

Entities have attributes. Attributes are properties that describe the entity's characteristics. Entity instances have identifiers. Identifiers are attributes that name, or identify, entity instances.

70. What is Enterprise Resource Planning (ERP), and what kind of a database is used in an ERP application?

Enterprise Resource Planning (ERP) is an information system used in manufacturing companies and includes sales, inventory, production planning, purchasing and other business functions. An ERP system typically uses a multiuser database.

71. Describe the difference between embedded and dynamic SQL?

Embedded SQL is the process of including hard coded SQL statements. These statements do not change unless the source code is modified. Dynamic SQL is the process of generating SQL on the fly.The statements generated do not have to be the same each time.

72. Explain a join between tables

A join allows tables to be linked to other tables when a relationship between the tables exists. The relationships are established by using a common column in the tables and often uses the primary/foreign key relationship.

73. Describe a subquery .

A subquery is a query that is composed of two queries. The first query (inner query) is within the WHERE clause of the other query (outer query).

74. Compare a hierarchical and network database model?

The hierarchical model is a top-down structure where each parent may have many children but each child can have only one parent. This model supports one-to-one and one-to-many relationships.

The network model can be much more flexible than the hierarchical model since each parent can have multiple children but each child can also have multiple parents. This model supports one-to-one, one-to-many, and many-to-many relationships.

75. Explain the difference between a dynamic and materialized view.

A dynamic view may be created every time that a specific view is requested by a user. A materialized view is created and or updated infrequently and it must be synchronized with its associated base table(s).

76. Explain what needs to happen to convert a relation to third normal form.

First you must verify that a relation is in both first normal form and second normal form. If the relation is not, you must convert into second normal form. After a relation is in second normal form, you must remove all transitive dependencies.

77. Describe the four types of indexes?

A unique primary index is unique and is used to find and store a row. A nonunique primary index is not unique and is used to find a row but also where to store a row (based on its unique primary index). A unique secondary index is unique for each row and used to find table rows. A nonunique secondary index is not unique and used to find table rows.

78. Explain minimum and maximum cardinality?

Minimum cardinality is the minimum number of instances of an entity that can be associated with each instance of another entity. Maximum cardinality is the maximum number of instances of an entity that can be associated with each instance of another entity.

79. What is deadlock? How can it be avoided? How can it be resolved once it occurs?

Deadlock occurs when two transactions are each waiting on a resource that the other transaction holds. Deadlock can be prevented by requiring transactions to acquire all locks at the same time; once it occurs, the only way to cure it is to abort one of the transactions and back out of partially completed work.

80. Explain what we mean by an ACID transaction.

An ACID transaction is one that is atomic, consistent, isolated, and durable. Durable means that database changes are permanent. Consistency can mean either statement level or transaction level consistency. With transaction level consistency, a transaction may not see its own changes.Atomic means it is performed as a unit.

81. Under what conditions should indexes be used?

Indexes can be created to enforce uniqueness, to facilitate sorting, and to enable fast retrieval by column values. A good candidate for an index is a column that is frequently used with equal conditions in WHERE clauses.

82. What is difference between SQL and SQL SERVER?

SQL is a language that provides an interface to RDBMS, developed by IBM. SQL SERVER is a RDBMS just like Oracle, DB2.

83. What is Specialization?

It is the process of defining a set of subclasses of an entity type where each subclass contain all the attributes and relationships of the parent entity and may have additional attributes and relationships which are specific to itself.

84. What is generalization?

It is the process of finding common attributes and relations of a number of entities and defining a common super class for them.

85. What is meant by Proactive, Retroactive and Simultaneous Update?

Proactive Update: The updates that are applied to database before it becomes effective in real world.

Retroactive Update: The updates that are applied to database after it becomes effective in real world.

Simultaneous Update: The updates that are applied to database at the same time when it becomes effective in real world.

86. What is RAID Technology?


Redundant array of inexpensive (or independent) disks. The main goal of raid technology is to even out the widely different rates of performance improvement of disks against those in memory and microprocessor. Raid technology employs the technique of data striping to achieve higher transfer rates.

87. What are serial, non serial schedule?

A schedule S is serial if, for every transaction T participating in the schedule, all the operations of T is executed consecutively in the schedule, otherwise, the schedule is called non-serial schedule.

88. What are conflict serializable schedules?

A schedule S of n transactions is serializable if it is equivalent to some serial schedule of the same n transactions.

89. What is view serializable?

A schedule is said to be view serializable if it is view equivalent with some serial schedule.

90. What is a foreign key?

A key of a relation schema is called as a foreign key if it is the primary key of
some other relation to which it is related to.

91. What are the disadvantages of using a dbms?

1) High initial investments in h/w, s/w, and training.
2) Generality that a DBMS provides for defining and processing data.
3) Overhead for providing security, concurrency control, recovery, and integrity functions.

92. What is Lossless join property?
It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

93. What is a Phantom Deadlock?
In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.

94. What is a checkpoint and When does it occur?
A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

95. What is schema?
The description of a data base is called the database schema , which is specified during database design and is not expected to change frequently . A displayed schema is called schema diagram .We call each object in the schema as schema construct.

1. What is database?

A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.

2. What is DBMS?
It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.

3. What is a Database system?
The database and DBMS software together is called as Database system.

4. Advantages of DBMS?
Redundancy is controlled.
Unauthorised access is restricted.
Providing multiple user interfaces.
Enforcing integrity constraints.
Providing backup and recovery.

5. Disadvantage in File Processing System?
Data redundancy & inconsistency.
Difficult in accessing data.
Data isolation.
Data integrity.
Concurrent access is not possible.
Security Problems.

6. Describe the three levels of data abstraction?
The are three levels of abstraction:
Physical level: The lowest level of abstraction describes how data are stored.
Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
View level: The highest level of abstraction describes only part of entire database.

7. Define the "integrity rules"
There are two Integrity rules.
Entity Integrity: States that Primary key cannot have NULL value
Referential Integrity: States that Foreign Key can be either a NULL value or should be Primary Key value of other relation.

8. What is extension and intension?
Extension -
It is the number of tuples present in a table at any instance. This is time dependent.
Intension -
It is a constant value that gives the name, structure of table and the constraints laid on it.

9. What is System R? What are its two major subsystems?
System R was designed and developed over a period of 1974-79 at IBM San Jose Research Center. It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system.
Its two subsystems are
Research Storage
System Relational Data System.

10. How is the data structure of System R different from the relational structure?
Unlike Relational systems in System R
Domains are not supported
Enforcement of candidate key uniqueness is optional
Enforcement of entity integrity is optional
Referential integrity is not enforced

11. What is Data Independence?
Data independence means that the application is independent of the storage structure and access strategy of data. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.
Two types of Data Independence:
Physical Data Independence: Modification in physical level should not affect the logical level.
Logical Data Independence: Modification in logical level should affect the view level.
NOTE: Logical Data Independence is more difficult to achieve

12. What is a view? How it is related to data independence?
A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

13. What is Data Model?
A collection of conceptual tools for describing data, data relationships data semantics and constraints.

14. What is E-R model?
This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

15. What is Object Oriented model?
This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

16. What is an Entity?
It is a 'thing' in the real world with an independent existence.

17. What is an Entity type?
It is a collection (set) of entities that have same attributes.

18. What is an Entity set?
It is a collection of all entities of particular entity type in the database.

19. What is an Extension of entity type?
The collections of entities of a particular entity type are grouped together into an entity set.

20. What is Weak Entity set?
An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.

21. What is an attribute?
It is a particular property, which describes the entity.

22. What is a Relation Schema and a Relation?
A relation Schema denoted by R(A1, A2, , An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).

23. What is degree of a Relation?
It is the number of attribute of its relation schema.

24. What is Relationship?
It is an association among two or more entities.

25. What is Relationship set?
The collection (or set) of similar relationships.

26. What is Relationship type?
Relationship type defines a set of associations or a relationship set among a given set of entity types.

27. What is degree of Relationship type?
It is the number of entity type participating.

25. What is DDL (Data Definition Language)?
A data base schema is specifies by a set of definitions expressed by a special language called DDL.

26. What is VDL (View Definition Language)?
It specifies user views and their mappings to the conceptual schema.

27. What is SDL (Storage Definition Language)?
This language is to specify the internal schema. This language may specify the mapping between two schemas.

28. What is Data Storage - Definition Language?
The storage structures and access methods used by database system are specified by a set of definition in a special type of DDL called data storage-definition language.

29. What is DML (Data Manipulation Language)?
This language that enable user to access or manipulate data as organised by appropriate data model.
Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.
Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data.

31. What is DML Compiler?
It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.

32. What is Query evaluation engine?
It executes low-level instruction generated by compiler.

33. What is DDL Interpreter?
It interprets DDL statements and record them in tables containing metadata.

34. What is Record-at-a-time?
The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.

35. What is Set-at-a-time or Set-oriented?
The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.

36. What is Relational Algebra?
It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

37. What is Relational Calculus?
It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.

38. How does Tuple-oriented relational calculus differ from domain-oriented relational calculus
The tuple-oriented calculus uses a tuple variables i.e., variable whose only permitted values are tuples of that relation. E.g. QUEL
The domain-oriented calculus has domain variables i.e., variables that range over the underlying domains instead of over relation. E.g. ILL, DEDUCE.

39. What is normalization?
It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties
Minimizing redundancy
Minimizing insertion, deletion and update anomalies.

40. What is Functional Dependency?
A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.

41. When is a functional dependency F said to be minimal?
Every dependency in F has a single attribute for its right hand side.
We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset of X and still have a set of dependency that is equivalent to F.
We cannot remove any dependency from F and still have set of dependency that is equivalent to F.

42. What is Multivalued dependency?
Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following properties
t3[x] = t4[X] = t1[X] = t2[X]
t3[Y] = t1[Y] and t4[Y] = t2[Y]
t3[Z] = t2[Z] and t4[Z] = t1[Z]
where [Z = (R-(X U Y)) ]

43. What is Lossless join property?
It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

44. What is 1 NF (Normal Form)?
The domain of attribute must include only atomic (simple, indivisible) values.

45. What is Fully Functional dependency?
It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.

46. What is 2NF?
A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

47. What is 3NF?
A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true
X is a Super-key of R.
A is a prime attribute of R.
In other words, if every non prime attribute is non-transitively dependent on primary key.

48. What is BCNF (Boyce-Codd Normal Form)?
A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

49. What is 4NF?
A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true
X is subset or equal to (or) XY = R.
X is a super key.

50. What is 5NF?
A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true
Ri = R for some i.
The join dependency is implied by the set of FD, over R in which the left side is key of R.

51. What is Domain-Key Normal Form?
A relation is said to be in DKNF if all constraints and dependencies that should hold on the the constraint can be enforced by simply enforcing the domain constraint and key constraint on the relation.

52. What are partial, alternate,, artificial, compound and natural key?
Partial Key:
It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.
Alternate Key:
All Candidate Keys excluding the Primary Key are known as Alternate Keys.
Artificial Key:
If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.
Compound Key:
If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.
Natural Key:
When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.

53. What is indexing and what are the different kinds of indexing?
Indexing is a technique for determining how quickly specific data can be found.
Types:
Binary search style indexing
B-Tree indexing
Inverted list indexing
Memory resident table
Table indexing

54. What is system catalog or catalog relation? How is better known as?
A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of relations maintained by the system called metadata. It is also called data dictionary.

55. What is meant by query optimization?
The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

56. What is join dependency and inclusion dependency?
Join Dependency:
A Join dependency is generalization of Multivalued dependency.A JD {R1, R2, ..., Rn} is said to hold over a relation R if R1, R2, R3, ..., Rn is a lossless-join decomposition of R . There is no set of sound and complete inference rules for JD.
Inclusion Dependency:
An Inclusion Dependency is a statement of the form that some columns of a relation are contained in other columns. A foreign key constraint is an example of inclusion dependency.

57. What is durability in DBMS?
Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.

58. What do you mean by atomicity and aggregation?
Atomicity:
Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions.
Aggregation:
A concept which is used to model a relationship between a collection of entities and relationships. It is used when we need to express a relationship among relationships.

59. What is a Phantom Deadlock?
In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.

60. What is a checkpoint and When does it occur?
A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

61. What are the different phases of transaction?
Different phases are
Analysis phase
Redo Phase
Undo phase

62. What do you mean by flat file database?
It is a database in which there are no programs or user access languages. It has no cross-file capabilities but is user-friendly and provides user-interface management.

63. What is "transparent DBMS"?
It is one, which keeps its Physical Structure hidden from user.

64. Brief theory of Network, Hierarchical schemas and their properties
Network schema uses a graph data structure to organize records example for such a database management system is CTCG while a hierarchical schema uses a tree data structure example for such a system is IMS.

65. What is a query?
A query with respect to DBMS relates to user commands that are used to interact with a data base. The query language can be classified into data definition language and data manipulation language.

66. What do you mean by Correlated subquery?
Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.
E.g. Select * From CUST Where '10/03/1990' IN (Select ODATE From ORDER Where CUST.CNUM = ORDER.CNUM)

67. What are the primitive operations common to all record management systems?
Addition, deletion and modification.

68. Name the buffer in which all the commands that are typed in are stored
Edit Buffer

69. What are the unary operations in Relational Algebra?
PROJECTION and SELECTION.

70. Are the resulting relations of PRODUCT and JOIN operation the same?
No.
PRODUCT: Concatenation of every row in one relation with every row in another.
JOIN: Concatenation of rows from one relation and related rows from another.

71. What is RDBMS KERNEL?
Two important pieces of RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system-level data structures used by the kernel to manage the database
You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, retrieving, and securing data. An RDBMS maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrent resource usage; dispatches and schedules user requests; and manages space usage within its table-space structures
.
72. Name the sub-systems of a RDBMS
I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, Lock Management

73. Which part of the RDBMS takes care of the data dictionary? How
Data dictionary is a set of tables and database objects that is stored in a special area of the database and maintained exclusively by the kernel.

74. What is the job of the information stored in data-dictionary?
The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

75. Not only RDBMS takes care of locating data it also
determines an optimal access path to store or retrieve the data

76. How do you communicate with an RDBMS?
You communicate with an RDBMS using Structured Query Language (SQL)

77. Define SQL and state the differences between SQL and other conventional programming Languages
SQL is a nonprocedural language that is designed specifically for data access operations on normalized relational database structures. The primary difference between SQL and other conventional programming languages is that SQL statements specify what data operations should be performed rather than how to perform them.

78. Name the three major set of files on disk that compose a database in Oracle
There are three major sets of files on disk that compose a database. All the files are binary. These are
Database files
Control files
Redo logs
The most important of these are the database files where the actual data resides. The control files and the redo logs support the functioning of the architecture itself.
All three sets of files must be present, open, and available to Oracle for any data on the database to be useable. Without these files, you cannot access the database, and the database administrator might have to recover some or all of the database using a backup, if there is one.

79. What is an Oracle Instance?
The Oracle system processes, also known as Oracle background processes, provide functions for the user processesfunctions that would otherwise be done by the user processes themselves
Oracle database-wide system memory is known as the SGA, the system global area or shared global area. The data and control structures in the SGA are shareable, and all the Oracle background processes and user processes can use them.
The combination of the SGA and the Oracle background processes is known as an Oracle instance

80. What are the four Oracle system processes that must always be up and running for the database to be useable
The four Oracle system processes that must always be up and running for the database to be useable include DBWR (Database Writer), LGWR (Log Writer), SMON (System Monitor), and PMON (Process Monitor).

81. What are database files, control files and log files. How many of these files should a database have at least? Why?
Database Files
The database files hold the actual data and are typically the largest in size. Depending on their sizes, the tables (and other objects) for all the user accounts can go in one database filebut that's not an ideal situation because it does not make the database structure very flexible for controlling access to storage for different users, putting the database on different disk drives, or backing up and restoring just part of the database.
You must have at least one database file but usually, more than one files are used. In terms of accessing and using the data in the tables and other objects, the number (or location) of the files is immaterial.
The database files are fixed in size and never grow bigger than the size at which they were created
Control Files
The control files and redo logs support the rest of the architecture. Any database must have at least one control file, although you typically have more than one to guard against loss. The control file records the name of the database, the date and time it was created, the location of the database and redo logs, and the synchronization information to ensure that all three sets of files are always in step. Every time you add a new database or redo log file to the database, the information is recorded in the control files.
Redo Logs
Any database must have at least two redo logs. These are the journals for the database; the redo logs record all changes to the user objects or system objects. If any type of failure occurs, the changes recorded in the redo logs can be used to bring the database to a consistent state without losing any committed transactions. In the case of non-data loss failure, Oracle can apply the information in the redo logs automatically without intervention from the DBA.
The redo log files are fixed in size and never grow dynamically from the size at which they were created.

82. What is ROWID?
The ROWID is a unique database-wide physical address for every row on every table. Once assigned (when the row is first inserted into the database), it never changes until the row is deleted or the table is dropped.
The ROWID consists of the following three components, the combination of which uniquely identifies the physical storage location of the row.
Oracle database file number, which contains the block with the rows
Oracle block address, which contains the row
The row within the block (because each block can hold many rows)
The ROWID is used internally in indexes as a quick means of retrieving rows with a particular key value. Application developers also use it in SQL statements as a quick way to access a row once they know the ROWID

83. What is Oracle Block? Can two Oracle Blocks have the same address?
Oracle "formats" the database files into a number of Oracle blocks when they are first createdmaking it easier for the RDBMS software to manage the files and easier to read data into the memory areas.
The block size should be a multiple of the operating system block size. Regardless of the block size, the entire block is not available for holding data; Oracle takes up some space to manage the contents of the block. This block header has a minimum size, but it can grow.
These Oracle blocks are the smallest unit of storage. Increasing the Oracle block size can improve performance, but it should be done only when the database is first created.
Each Oracle block is numbered sequentially for each database file starting at 1. Two blocks can have the same block address if they are in different database files.

84. What is database Trigger?
A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. A database trigger can call database procedures that are also written in PL/SQL.

85. Name two utilities that Oracle provides, which are use for backup and recovery.
Along with the RDBMS software, Oracle provides two utilities that you can use to back up and restore the database. These utilities are Export and Import.
The Export utility dumps the definitions and data for the specified part of the database to an operating system binary file. The Import utility reads the file produced by an export, recreates the definitions of objects, and inserts the data
If Export and Import are used as a means of backing up and recovering the database, all the changes made to the database cannot be recovered since the export was performed. The best you can do is recover the database to the time when the export was last performed.

86. What are stored-procedures? And what are the advantages of using them.
Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.

87. How are exceptions handled in PL/SQL? Give some of the internal exceptions' name
PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination.
The exception handler must be defined within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.
User-Defined Exceptions
PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. User accomplishes this by naming an exception as in the following example:
ot_failure EXCEPTION;
In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:
EXCEPTION
when OT_FAILURE then
out_status_code := g_out_status_code;
out_msg := g_out_msg;
The following is an example of a subprogram exception:
EXCEPTION
when NO_DATA_FOUND then
g_out_status_code := 'FAIL';
RAISE ot_failure;
Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.
System-Defined Exceptions
Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND is a system-defined exception. Table below gives a complete list of internal exceptions.

PL/SQL internal exceptions.

PL/SQL internal exceptions.

Exception Name Oracle Error
CURSOR_ALREADY_OPEN ORA-06511
DUP_VAL_ON_INDEX ORA-00001
INVALID_CURSOR ORA-01001
INVALID_NUMBER ORA-01722
LOGIN_DENIED ORA-01017
NO_DATA_FOUND ORA-01403
NOT_LOGGED_ON ORA-01012
PROGRAM_ERROR ORA-06501
STORAGE_ERROR ORA-06500
TIMEOUT_ON_RESOURCE ORA-00051
TOO_MANY_ROWS ORA-01422
TRANSACTION_BACKED_OUT ORA-00061
VALUE_ERROR ORA-06502
ZERO_DIVIDE ORA-01476

In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established.

88. Does PL/SQL support "overloading"? Explain
The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call. The count and data types of formal parameters are also considered.
PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.

89. Tables derived from the ERD
a) Are totally unnormalised
b) Are always in 1NF
c) Can be further denormalised
d) May have multi-valued attributes

90. Spurious tuples may occur due to
i. Bad normalization
ii. Theta joins
iii. Updating tables from join
a) i & ii b) ii & iii
c) i & iii d) ii & iii

(a) i & iii because theta joins are joins made on keys that are not primary keys.

91. A B C is a set of attributes. The functional dependency is as follows
AB -> B
AC -> C
C -> B
a) is in 1NF
b) is in 2NF
c) is in 3NF
d) is in BCNF

(a) is in 1NF since (AC)+ = { A, B, C} hence AC is the primary key. Since C B is a FD given, where neither C is a Key nor B is a prime attribute, this it is not in 3NF. Further B is not functionally dependent on key AC thus it is not in 2NF. Thus the given FDs is in 1NF.

92. In mapping of ERD to DFD
a) entities in ERD should correspond to an existing entity/store in DFD
b) entity in DFD is converted to attributes of an entity in ERD
c) relations in ERD has 1 to 1 correspondence to processes in DFD
d) relationships in ERD has 1 to 1 correspondence to flows in DFD

(a) entities in ERD should correspond to an existing entity/store in DFD

93. A dominant entity is the entity
a) on the N side in a 1 : N relationship
b) on the 1 side in a 1 : N relationship
c) on either side in a 1 : 1 relationship
d) nothing to do with 1 : 1 or 1 : N relationship

(b) on the 1 side in a 1 : N relationship

94. Select 'NORTH', CUSTOMER From CUST_DTLS Where REGION = 'N' Order By
CUSTOMER Union Select 'EAST', CUSTOMER From CUST_DTLS Where REGION = 'E' Order By CUSTOMER
The above is
a) Not an error
b) Error - the string in single quotes 'NORTH' and 'SOUTH'
c) Error - the string should be in double quotes
d) Error - ORDER BY clause

(d) Error - the ORDER BY clause. Since ORDER BY clause cannot be used in UNIONS

95. What is Storage Manager?
It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

96. What is Buffer Manager?
It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cache in memory.

97. What is Transaction Manager?
It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

98. What is File Manager?
It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.

99. What is Authorization and Integrity manager?
It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.

100. What are stand-alone procedures?
Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run time, which slows execution.

101. What are cursors give different types of cursors.
PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors
Implicit
Explicit

102. What is cold backup and hot backup (in case of Oracle)?
Cold Backup:
It is copying the three sets of files (database files, redo logs, and control file) when the instance is shut down. This is a straight file copy, usually from the disk directly to tape. You must shut down the instance to guarantee a consistent copy.
If a cold backup is performed, the only option available in the event of data file loss is restoring all the files from the latest backup. All work performed on the database since the last backup is lost.
Hot Backup:
Some sites (such as worldwide airline reservations systems) cannot shut down the database while making a backup copy of the files. The cold backup is not an available option.
So different means of backing up database must be used the hot backup. Issue a SQL command to indicate to Oracle, on a tablespace-by-tablespace basis, that the files of the tablespace are to backed up. The users can continue to make full use of the files, including making changes to the data. Once the user has indicated that he/she wants to back up the tablespace files, he/she can use the operating system to copy those files to the desired backup destination.
The database must be running in ARCHIVELOG mode for the hot backup option.
If a data loss failure does occur, the lost database files can be restored using the hot backup and the online and offline redo logs created since the backup was done. The database is restored to the most consistent state without any loss of committed transactions.

103. What are Armstrong rules? How do we say that they are complete and/or sound
The well-known inference rules for FDs
Reflexive rule :
If Y is subset or equal to X then X Y.
Augmentation rule:
If X Y then XZ YZ.
Transitive rule:
If {X Y, Y Z} then X Z.
Decomposition rule :

If X YZ then X Y.
Union or Additive rule:
If {X Y, X Z} then X YZ.
Pseudo Transitive rule :
If {X Y, WY Z} then WX Z.
Of these the first three are known as Amstrong Rules. They are sound because it is enough if a set of FDs satisfy these three. They are called complete because using these three rules we can generate the rest all inference rules.

104. How can you find the minimal key of relational schema?
Minimal key is one which can identify each tuple of the given relation schema uniquely. For finding the minimal key it is required to find the closure that is the set of all attributes that are dependent on any given set of attributes under the given set of functional dependency.
Algo. I Determining X+, closure for X, given set of FDs F
1. Set X+ = X
2. Set Old X+ = X+
3. For each FD Y Z in F and if Y belongs to X+ then add Z to X+
4. Repeat steps 2 and 3 until Old X+ = X+

Algo.II Determining minimal K for relation schema R, given set of FDs F
1. Set K to R that is make K a set of all attributes in R
2. For each attribute A in K
a. Compute (K A)+ with respect to F
b. If (K A)+ = R then set K = (K A)+


105. What do you understand by dependency preservation?
Given a relation R and a set of FDs F, dependency preservation states that the closure of the union of the projection of F on each decomposed relation Ri is equal to the closure of F. i.e.,
((PR1(F)) U U (PRn(F)))+ = F+
if decomposition is not dependency preserving, then some dependency is lost in the decomposition.

106. What is meant by Proactive, Retroactive and Simultaneous Update.
Proactive Update:
The updates that are applied to database before it becomes effective in real world .
Retroactive Update:
The updates that are applied to database after it becomes effective in real world .
Simulatneous Update:
The updates that are applied to database at the same time when it becomes effective in real world .

107. What are the different types of JOIN operations?
Equi Join: This is the most common type of join which involves only equality comparisions. The disadvantage in this type of join is that there.

FAQ In SQL



1. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL)

2. What operator performs pattern matching?
LIKE operator

3. What operator tests column for the absence of data?
IS NULL operator

4. Which command executes the contents of a specified file?
START <filename> or @<filename>

5. What is the parameter substitution symbol used with INSERT INTO command?
&

6. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN

7. What are the wildcards used for pattern matching?
_ for single character substitution and % for multi-character substitution

8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True

9. State true or false. !=, <>, ^= all denote the same operation.
True

10. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all

11. What command is used to get back the privileges offered by the GRANT command?
REVOKE

12. Which system tables contain information on privileges granted and privileges obtained?
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

13. Which system table contains information on constraints on all the tables created?
USER_CONSTRAINTS

14. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.

15. What is the difference between TRUNCATE and DELETE commands?
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

16. What command is used to create a table by copying the structure of another table?
Answer :
CREATE TABLE .. AS SELECT command
Explanation :
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;
TROUBLETHETROUBLE

18. What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );
Answer :
NO
Explanation :
The query checks whether a given string is a numerical digit.

19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.


20. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN

21. Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME;
Variable names should start with an alphabet. Here the table name starts with an '&' symbol.

22. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

23. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.

24. What is the value of comm and sal after executing the following query if the initial value of sal is 10000?
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;
sal = 11000, comm = 100025. What is the use of DESC in SQL?
Answer :
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

26. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

27. Which function is used to find the largest integer less than or equal to a specific value?
FLOOR

28. What is the output of the following query?
SELECT TRUNC(1234.5678,-2) FROM DUAL;
1200





SQL QUERIES

I. SCHEMAS

Table 1 : STUDIES

PNAME (VARCHAR), SPLACE (VARCHAR), COURSE (VARCHAR), CCOST (NUMBER)

Table 2 : SOFTWARE

PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR), SCOST (NUMBER), DCOST (NUMBER), SOLD (NUMBER)

Table 3 : PROGRAMMER

PNAME (VARCHAR), DOB (DATE), DOJ (DATE), SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHAR), SAL (NUMBER) LEGEND :

PNAME Programmer Name, SPLACE Study Place, CCOST Course Cost, DEVIN Developed in, SCOST Software Cost, DCOST Development Cost, PROF1 Proficiency 1

QUERIES :

1. Find out the selling cost average for packages developed in Oracle.
2. Display the names, ages and experience of all programmers.
3. Display the names of those who have done the PGDCA course.
4. What is the highest number of copies sold by a package?
5. Display the names and date of birth of all programmers born in April.
6. Display the lowest course fee.
7. How many programmers have done the DCA course.
8. How much revenue has been earned through the sale of packages developed in C.
9. Display the details of software developed by Rakesh.
10. How many programmers studied at Pentafour.
11. Display the details of packages whose sales crossed the 5000 mark.
12. Find out the number of copies which should be sold in order to recover the development cost of each package.
13. Display the details of packages for which the development cost has been recovered.
14. What is the price of costliest software developed in VB?
15. How many packages were developed in Oracle ?
16. How many programmers studied at PRAGATHI?
17. How many programmers paid 10000 to 15000 for the course?
18. What is the average course fee?
19. Display the details of programmers knowing C.
20. How many programmers know either C or Pascal?
21. How many programmers dont know C and C++?
22. How old is the oldest male programmer?
23. What is the average age of female programmers?
24. Calculate the experience in years for each programmer and display along with their names in descending order.
25. Who are the programmers who celebrate their birthdays during the current month?

26. How many female programmers are there?
27. What are the languages known by the male programmers?
28. What is the average salary?
29. How many people draw 5000 to 7500?
30. Display the details of those who dont know C, C++ or Pascal.
31. Display the costliest package developed by each programmer.
32. Produce the following output for all the male programmers
Programmer
Mr. Arvind has 15 years of experience

KEYS:

1. SELECT AVG(SCOST) FROM SOFTWARE WHERE DEVIN = 'ORACLE';
2. SELECT PNAME,TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) "AGE", TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) "EXPERIENCE" FROM PROGRAMMER;
3. SELECT PNAME FROM STUDIES WHERE COURSE = 'PGDCA';
4. SELECT MAX(SOLD) FROM SOFTWARE;
5. SELECT PNAME, DOB FROM PROGRAMMER WHERE DOB LIKE '%APR%';
6. SELECT MIN(CCOST) FROM STUDIES;
7. SELECT COUNT(*) FROM STUDIES WHERE COURSE = 'DCA';
8. SELECT SUM(SCOST*SOLD-DCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'C';
9. SELECT * FROM SOFTWARE WHERE PNAME = 'RAKESH';
10. SELECT * FROM STUDIES WHERE SPLACE = 'PENTAFOUR';
11. SELECT * FROM SOFTWARE WHERE SCOST*SOLD-DCOST > 5000;
12. SELECT CEIL(DCOST/SCOST) FROM SOFTWARE;
13. SELECT * FROM SOFTWARE WHERE SCOST*SOLD >= DCOST;
14. SELECT MAX(SCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'VB';
15. SELECT COUNT(*) FROM SOFTWARE WHERE DEVIN = 'ORACLE';
16. SELECT COUNT(*) FROM STUDIES WHERE SPLACE = 'PRAGATHI';
17. SELECT COUNT(*) FROM STUDIES WHERE CCOST BETWEEN 10000 AND 15000;
18. SELECT AVG(CCOST) FROM STUDIES;
19. SELECT * FROM PROGRAMMER WHERE PROF1 = 'C' OR PROF2 = 'C';
20. SELECT * FROM PROGRAMMER WHERE PROF1 IN ('C','PASCAL') OR PROF2 IN ('C','PASCAL');
21. SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN ('C','C++') AND PROF2 NOT IN ('C','C++');

22. SELECT TRUNC(MAX(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHERE SEX = 'M';
23. SELECT TRUNC(AVG(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHERE SEX = 'F';
24. SELECT PNAME, TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) FROM PROGRAMMER ORDER BY PNAME DESC;
25. SELECT PNAME FROM PROGRAMMER WHERE TO_CHAR(DOB,'MON') = TO_CHAR(SYSDATE,'MON');
26. SELECT COUNT(*) FROM PROGRAMMER WHERE SEX = 'F';
27. SELECT DISTINCT(PROF1) FROM PROGRAMMER WHERE SEX = 'M';
28. SELECT AVG(SAL) FROM PROGRAMMER;
29. SELECT COUNT(*) FROM PROGRAMMER WHERE SAL BETWEEN 5000 AND 7500;
30. SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN ('C','C++','PASCAL') AND PROF2 NOT IN ('C','C++','PASCAL');
31. SELECT PNAME,TITLE,SCOST FROM SOFTWARE WHERE SCOST IN (SELECT MAX(SCOST) FROM SOFTWARE GROUP BY PNAME);
32.SELECT 'Mr.' || PNAME || ' - has ' || TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) || ' years of experience' Programmer FROM PROGRAMMER WHERE SEX = 'M' UNION SELECT 'Ms.' || PNAME || ' - has ' || TRUNC (MONTHS_BETWEEN (SYSDATE,DOJ)/12) || ' years of experience' Programmer FROM PROGRAMMER WHERE SEX = 'F';
II . SCHEMA :

Table 1 : DEPT

DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)

Table 2 : EMP

EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2))

MGR is the empno of the employee whom the employee reports to. DEPTNO is a foreign key.
QUERIES
1. List all the employees who have at least one person reporting to them.
2. List the employee details if and only if more than 10 employees are present in department no 10.
3. List the name of the employees with their immediate higher authority.
4. List all the employees who do not manage any one.
5. List the employee details whose salary is greater than the lowest salary of an employee belonging to deptno 20.

6. List the details of the employee earning more than the highest paid manager.
7. List the highest salary paid for each job.
8. Find the most recently hired employee in each department.
9. In which year did most people join the company? Display the year and the number of employees.
10. Which department has the highest annual remuneration bill?
11. Write a query to display a * against the row of the most recently hired employee.
12. Write a correlated sub-query to list out the employees who earn more than the average salary of their department.
13. Find the nth maximum salary.
14. Select the duplicate records (Records, which are inserted, that already exist) in the EMP table.
15. Write a query to list the length of service of the employees (of the form n years and m months).

KEYS:

1. SELECT DISTINCT(A.ENAME) FROM EMP A, EMP B WHERE A.EMPNO = B.MGR; or SELECT ENAME FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP);
2. SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO)>10 AND DEPTNO=10);
3. SELECT A.ENAME "EMPLOYEE", B.ENAME "REPORTS TO" FROM EMP A, EMP B WHERE A.MGR=B.EMPNO;
4. SELECT * FROM EMP WHERE EMPNO IN ( SELECT EMPNO FROM EMP MINUS SELECT MGR FROM EMP);
5. SELECT * FROM EMP WHERE SAL > ( SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=20);
6. SELECT * FROM EMP WHERE SAL > ( SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'MANAGER' );
7. SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;
8. SELECT * FROM EMP WHERE (DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROM EMP GROUP BY DEPTNO);
9. SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES" FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY') HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY'));
10. SELECT DEPTNO, LPAD(SUM(12*(SAL+NVL(COMM,0))),15) "COMPENSATION" FROM EMP GROUP BY DEPTNO HAVING SUM( 12*(SAL+NVL(COMM,0))) = (SELECT MAX(SUM(12*(SAL+NVL(COMM,0)))) FROM EMP GROUP BY DEPTNO);
11. SELECT ENAME, HIREDATE, LPAD('*', "RECENTLY HIRED" FROM EMP WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP) UNION SELECT ENAME NAME, HIREDATE, LPAD(' ',15) "RECENTLY HIRED" FROM EMP WHERE HIREDATE != (SELECT MAX(HIREDATE) FROM EMP);
12. SELECT ENAME,SAL FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP F WHERE E.DEPTNO = F.DEPTNO);
13. SELECT ENAME, SAL FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);
14. SELECT * FROM EMP A WHERE A.EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(EMPNO)>1) AND A.ROWID!=MIN (ROWID));
15. SELECT ENAME "EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE" FROM EMP;

1. What is database?

A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.

2. What is DBMS?

It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.

3. What is a Database system?

The database and DBMS software together is called as Database system.

4. Advantages of DBMS?

Redundancy is controlled.

Unauthorised access is restricted.

Providing multiple user interfaces.

Enforcing integrity constraints.

Providing backup and recovery.

5. Disadvantage in File Processing System?

Data redundancy & inconsistency.

Difficult in accessing data.

Data isolation.

Data integrity.

Concurrent access is not possible.

Security Problems.

6. Describe the three levels of data abstraction?

The are three levels of abstraction:

Physical level: The lowest level of abstraction describes how data are stored.

Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.

View level: The highest level of abstraction describes only part of entire database.

7. Define the "integrity rules"

There are two Integrity rules.

Entity Integrity: States that Primary key cannot have NULL value

Referential Integrity: States that Foreign Key can be either a NULL value or should be Primary Key value of other relation.

8. What is extension and intension?

Extension -

It is the number of tuples present in a table at any instance. This is time dependent.

Intension -

It is a constant value that gives the name, structure of table and the constraints laid on it.

9. What is System R? What are its two major subsystems?

System R was designed and developed over a period of 1974-79 at IBM San Jose Research Center. It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system.

Its two subsystems are

Research Storage

System Relational Data System.

10. How is the data structure of System R different from the relational structure?

Unlike Relational systems in System R

Domains are not supported

Enforcement of candidate key uniqueness is optional

Enforcement of entity integrity is optional

Referential integrity is not enforced

11. What is Data Independence?

Data independence means that the application is independent of the storage structure and access strategy of data. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.

Two types of Data Independence:

Physical Data Independence: Modification in physical level should not affect the logical level.

Logical Data Independence: Modification in logical level should affect the view level.

NOTE: Logical Data Independence is more difficult to achieve

12. What is a view? How it is related to data independence?

A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.

Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

13. What is Data Model?

A collection of conceptual tools for describing data, data relationships data semantics and constraints.

14. What is E-R model?

This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

15. What is Object Oriented model?

This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

16. What is an Entity?

It is a 'thing' in the real world with an independent existence.

17. What is an Entity type?

It is a collection (set) of entities that have same attributes.

18. What is an Entity set?

It is a collection of all entities of particular entity type in the database.

19. What is an Extension of entity type?

The collections of entities of a particular entity type are grouped together into an entity set.

20. What is Weak Entity set?

An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.

21. What is an attribute?

It is a particular property, which describes the entity.

22. What is a Relation Schema and a Relation?

A relation Schema denoted by R(A1, A2, , An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).

23. What is degree of a Relation?

It is the number of attribute of its relation schema.

24. What is Relationship?

It is an association among two or more entities.

25. What is Relationship set?

The collection (or set) of similar relationships.

26. What is Relationship type?

Relationship type defines a set of associations or a relationship set among a given set of entity types.

27. What is degree of Relationship type?

It is the number of entity type participating.

25. What is DDL (Data Definition Language)?

A data base schema is specifies by a set of definitions expressed by a special language called DDL.

26. What is VDL (View Definition Language)?

It specifies user views and their mappings to the conceptual schema.

27. What is SDL (Storage Definition Language)?

This language is to specify the internal schema. This language may specify the mapping between two schemas.

28. What is Data Storage - Definition Language?

The storage structures and access methods used by database system are specified by a set of definition in a special type of DDL called data storage-definition language.

29. What is DML (Data Manipulation Language)?

This language that enable user to access or manipulate data as organised by appropriate data model.

Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.

Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data.

31. What is DML Compiler?

It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.

32. What is Query evaluation engine?

It executes low-level instruction generated by compiler.

33. What is DDL Interpreter?

It interprets DDL statements and record them in tables containing metadata.

34. What is Record-at-a-time?

The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.

35. What is Set-at-a-time or Set-oriented?

The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.

36. What is Relational Algebra?

It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

37. What is Relational Calculus?

It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.

38. How does Tuple-oriented relational calculus differ from domain-oriented relational calculus

The tuple-oriented calculus uses a tuple variables i.e., variable whose only permitted values are tuples of that relation. E.g. QUEL

The domain-oriented calculus has domain variables i.e., variables that range over the underlying domains instead of over relation. E.g. ILL, DEDUCE.

39. What is normalization?

It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties

Minimizing redundancy

Minimizing insertion, deletion and update anomalies.

40. What is Functional Dependency?

A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.

41. When is a functional dependency F said to be minimal?

Every dependency in F has a single attribute for its right hand side.

We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset of X and still have a set of dependency that is equivalent to F.

We cannot remove any dependency from F and still have set of dependency that is equivalent to F.

42. What is Multivalued dependency?

Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following properties

t3[x] = t4[X] = t1[X] = t2[X]

t3[Y] = t1[Y] and t4[Y] = t2[Y]

t3[Z] = t2[Z] and t4[Z] = t1[Z]

where [Z = (R-(X U Y)) ]

43. What is Lossless join property?

It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

44. What is 1 NF (Normal Form)?

The domain of attribute must include only atomic (simple, indivisible) values.

45. What is Fully Functional dependency?

It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.

46. What is 2NF?

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

47. What is 3NF?

A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true

X is a Super-key of R.

A is a prime attribute of R.

In other words, if every non prime attribute is non-transitively dependent on primary key.

48. What is BCNF (Boyce-Codd Normal Form)?

A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

49. What is 4NF?

A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true

X is subset or equal to (or) XY = R.

X is a super key.

50. What is 5NF?

A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true

Ri = R for some i.

The join dependency is implied by the set of FD, over R in which the left side is key of R.

51. What is Domain-Key Normal Form?

A relation is said to be in DKNF if all constraints and dependencies that should hold on the the constraint can be enforced by simply enforcing the domain constraint and key constraint on the relation.

52. What are partial, alternate,, artificial, compound and natural key?

Partial Key:

It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.

Alternate Key:

All Candidate Keys excluding the Primary Key are known as Alternate Keys.

Artificial Key :

If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.

Compound Key:

If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.

Natural Key:

When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.

53. What is indexing and what are the different kinds of indexing?

Indexing is a technique for determining how quickly specific data can be found.

Types:

Binary search style indexing

B-Tree indexing

Inverted list indexing

Memory resident table

Table indexing

54. What is system catalog or catalog relation? How is better known as?

A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of relations maintained by the system called metadata. It is also called data dictionary.

55. What is meant by query optimization?

The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

56. What is join dependency and inclusion dependency?

Join Dependency:

A Join dependency is generalization of Multivalued dependency.A JD {R1, R2, ..., Rn} is said to hold over a relation R if R1, R2, R3, ..., Rn is a lossless-join decomposition of R . There is no set of sound and complete inference rules for JD.

Inclusion Dependency:

An Inclusion Dependency is a statement of the form that some columns of a relation are contained in other columns. A foreign key constraint is an example of inclusion dependency.

57. What is durability in DBMS?

Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.

58. What do you mean by atomicity and aggregation?

Atomicity:

Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions.

Aggregation:

A concept which is used to model a relationship between a collection of entities and relationships. It is used when we need to express a relationship among relationships.

59. What is a Phantom Deadlock?

In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.

60. What is a checkpoint and When does it occur?

A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

61. What are the different phases of transaction?

Different phases are

Analysis phase

Redo Phase

Undo phase

62. What do you mean by flat file database?

It is a database in which there are no programs or user access languages. It has no cross-file capabilities but is user-friendly and provides user-interface management.

63. What is "transparent DBMS"?

It is one, which keeps its Physical Structure hidden from user.

64. Brief theory of Network, Hierarchical schemas and their properties

Network schema uses a graph data structure to organize records example for such a database management system is CTCG while a hierarchical schema uses a tree data structure example for such a system is IMS.

65. What is a query?

A query with respect to DBMS relates to user commands that are used to interact with a data base. The query language can be classified into data definition language and data manipulation language.

66. What do you mean by Correlated subquery?

Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.

A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.

E.g. Select * From CUST Where '10/03/1990' IN (Select ODATE From ORDER Where CUST.CNUM = ORDER.CNUM)

67. What are the primitive operations common to all record management systems?

Addition, deletion and modification.

68. Name the buffer in which all the commands that are typed in are stored

Edit Buffer

69. What are the unary operations in Relational Algebra?

PROJECTION and SELECTION.

70. Are the resulting relations of PRODUCT and JOIN operation the same?

No.

PRODUCT : Concatenation of every row in one relation with every row in another.

JOIN : Concatenation of rows from one relation and related rows from another.

71. What is RDBMS KERNEL?

Two important pieces of RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system-level data structures used by the kernel to manage the database

You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, retrieving, and securing data. An RDBMS maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrent resource usage; dispatches and schedules user requests; and manages space usage within its table-space structures

.

72. Name the sub-systems of a RDBMS

I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, Lock Management

73. Which part of the RDBMS takes care of the data dictionary? How

Data dictionary is a set of tables and database objects that is stored in a special area of the database and maintained exclusively by the kernel.

74. What is the job of the information stored in data-dictionary?

The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

75. Not only RDBMS takes care of locating data it also

determines an optimal access path to store or retrieve the data

76. How do you communicate with an RDBMS?

You communicate with an RDBMS using Structured Query Language (SQL)

77. Define SQL and state the differences between SQL and other conventional programming Languages

SQL is a nonprocedural language that is designed specifically for data access operations on normalized relational database structures. The primary difference between SQL and other conventional programming languages is that SQL statements specify what data operations should be performed rather than how to perform them.

78. Name the three major set of files on disk that compose a database in Oracle

There are three major sets of files on disk that compose a database. All the files are binary. These are

Database files

Control files

Redo logs

The most important of these are the database files where the actual data resides. The control files and the redo logs support the functioning of the architecture itself.

All three sets of files must be present, open, and available to Oracle for any data on the database to be useable. Without these files, you cannot access the database, and the database administrator might have to recover some or all of the database using a backup, if there is one.

79. What is an Oracle Instance?

The Oracle system processes, also known as Oracle background processes, provide functions for the user processesfunctions that would otherwise be done by the user processes themselves

Oracle database-wide system memory is known as the SGA, the system global area or shared global area. The data and control structures in the SGA are shareable, and all the Oracle background processes and user processes can use them.

The combination of the SGA and the Oracle background processes is known as an Oracle instance

80. What are the four Oracle system processes that must always be up and running for the database to be useable

The four Oracle system processes that must always be up and running for the database to be useable include DBWR (Database Writer), LGWR (Log Writer), SMON (System Monitor), and PMON (Process Monitor).

81. What are database files, control files and log files. How many of these files should a database have at least? Why?

Database Files

The database files hold the actual data and are typically the largest in size. Depending on their sizes, the tables (and other objects) for all the user accounts can go in one database filebut that's not an ideal situation because it does not make the database structure very flexible for controlling access to storage for different users, putting the database on different disk drives, or backing up and restoring just part of the database.

You must have at least one database file but usually, more than one files are used. In terms of accessing and using the data in the tables and other objects, the number (or location) of the files is immaterial.

The database files are fixed in size and never grow bigger than the size at which they were created

Control Files

The control files and redo logs support the rest of the architecture. Any database must have at least one control file, although you typically have more than one to guard against loss. The control file records the name of the database, the date and time it was created, the location of the database and redo logs, and the synchronization information to ensure that all three sets of files are always in step. Every time you add a new database or redo log file to the database, the information is recorded in the control files.

Redo Logs

Any database must have at least two redo logs. These are the journals for the database; the redo logs record all changes to the user objects or system objects. If any type of failure occurs, the changes recorded in the redo logs can be used to bring the database to a consistent state without losing any committed transactions. In the case of non-data loss failure, Oracle can apply the information in the redo logs automatically without intervention from the DBA.

The redo log files are fixed in size and never grow dynamically from the size at which they were created.

82. What is ROWID?

The ROWID is a unique database-wide physical address for every row on every table. Once assigned (when the row is first inserted into the database), it never changes until the row is deleted or the table is dropped.

The ROWID consists of the following three components, the combination of which uniquely identifies the physical storage location of the row.

Oracle database file number, which contains the block with the rows

Oracle block address, which contains the row

The row within the block (because each block can hold many rows)

The ROWID is used internally in indexes as a quick means of retrieving rows with a particular key value. Application developers also use it in SQL statements as a quick way to access a row once they know the ROWID

83. What is Oracle Block? Can two Oracle Blocks have the same address?

Oracle "formats" the database files into a number of Oracle blocks when they are first createdmaking it easier for the RDBMS software to manage the files and easier to read data into the memory areas.

The block size should be a multiple of the operating system block size. Regardless of the block size, the entire block is not available for holding data; Oracle takes up some space to manage the contents of the block. This block header has a minimum size, but it can grow.

These Oracle blocks are the smallest unit of storage. Increasing the Oracle block size can improve performance, but it should be done only when the database is first created.

Each Oracle block is numbered sequentially for each database file starting at 1. Two blocks can have the same block address if they are in different database files.

84. What is database Trigger?

A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. A database trigger can call database procedures that are also written in PL/SQL.

85. Name two utilities that Oracle provides, which are use for backup and recovery.

Along with the RDBMS software, Oracle provides two utilities that you can use to back up and restore the database. These utilities are Export and Import.

The Export utility dumps the definitions and data for the specified part of the database to an operating system binary file. The Import utility reads the file produced by an export, recreates the definitions of objects, and inserts the data

If Export and Import are used as a means of backing up and recovering the database, all the changes made to the database cannot be recovered since the export was performed. The best you can do is recover the database to the time when the export was last performed.

86. What are stored-procedures? And what are the advantages of using them.

Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.

87. How are exceptions handled in PL/SQL? Give some of the internal exceptions' name

PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination.

The exception handler must be defined within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.

User-Defined Exceptions

PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. User accomplishes this by naming an exception as in the following example:

ot_failure EXCEPTION;

In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:

EXCEPTION

when OT_FAILURE then

out_status_code := g_out_status_code;

out_msg := g_out_msg;

The following is an example of a subprogram exception:

EXCEPTION

when NO_DATA_FOUND then

g_out_status_code := 'FAIL';

RAISE ot_failure;

Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.

System-Defined Exceptions

Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND is a system-defined exception. Table below gives a complete list of internal exceptions.

PL/SQL internal exceptions.

Exception Name

Oracle Error

CURSOR_ALREADY_OPEN

ORA-06511

DUP_VAL_ON_INDEX

ORA-00001

INVALID_CURSOR

ORA-01001

INVALID_NUMBER

ORA-01722

LOGIN_DENIED

ORA-01017

NO_DATA_FOUND

ORA-01403

NOT_LOGGED_ON

ORA-01012

PROGRAM_ERROR

ORA-06501

STORAGE_ERROR

ORA-06500

TIMEOUT_ON_RESOURCE

ORA-00051

TOO_MANY_ROWS

ORA-01422

TRANSACTION_BACKED_OUT

ORA-00061

VALUE_ERROR

ORA-06502

ZERO_DIVIDE

ORA-01476

In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established.

88. Does PL/SQL support "overloading"? Explain

The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call. The count and data types of formal parameters are also considered.

PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.

89. Tables derived from the ERD

a) Are totally unnormalised

b) Are always in 1NF

c) Can be further denormalised

d) May have multi-valued attributes

(b) Are always in 1NF

90. Spurious tuples may occur due to

i. Bad normalization

ii. Theta joins

iii. Updating tables from join

a) i & ii b) ii & iii

c) i & iii d) ii & iii

(a) i & iii because theta joins are joins made on keys that are not primary keys.

91. A B C is a set of attributes. The functional dependency is as follows

AB -> B

AC -> C

C -> B

a) is in 1NF

b) is in 2NF

c) is in 3NF

d) is in BCNF

(a) is in 1NF since (AC)+ = { A, B, C} hence AC is the primary key. Since C B is a FD given, where neither C is a Key nor B is a prime attribute, this it is not in 3NF. Further B is not functionally dependent on key AC thus it is not in 2NF. Thus the given FDs is in 1NF.

92. In mapping of ERD to DFD

a) entities in ERD should correspond to an existing entity/store in DFD

b) entity in DFD is converted to attributes of an entity in ERD

c) relations in ERD has 1 to 1 correspondence to processes in DFD

d) relationships in ERD has 1 to 1 correspondence to flows in DFD

(a) entities in ERD should correspond to an existing entity/store in DFD

93. A dominant entity is the entity

a) on the N side in a 1 : N relationship

b) on the 1 side in a 1 : N relationship

c) on either side in a 1 : 1 relationship

d) nothing to do with 1 : 1 or 1 : N relationship

(b) on the 1 side in a 1 : N relationship

94. Select 'NORTH', CUSTOMER From CUST_DTLS Where REGION = 'N' Order By

CUSTOMER Union Select 'EAST', CUSTOMER From CUST_DTLS Where REGION = 'E' Order By CUSTOMER

The above is

a) Not an error

b) Error - the string in single quotes 'NORTH' and 'SOUTH'

c) Error - the string should be in double quotes

d) Error - ORDER BY clause

(d) Error - the ORDER BY clause. Since ORDER BY clause cannot be used in UNIONS

95. What is Storage Manager?

It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

96. What is Buffer Manager?

It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cache in memory.

97. What is Transaction Manager?

It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

98. What is File Manager?

It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.

99. What is Authorization and Integrity manager?

It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.

100. What are stand-alone procedures?

Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run time, which slows execution.

101. What are cursors give different types of cursors.

PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors

Implicit

Explicit

102. What is cold backup and hot backup (in case of Oracle)?

Cold Backup:

It is copying the three sets of files (database files, redo logs, and control file) when the instance is shut down. This is a straight file copy, usually from the disk directly to tape. You must shut down the instance to guarantee a consistent copy.

If a cold backup is performed, the only option available in the event of data file loss is restoring all the files from the latest backup. All work performed on the database since the last backup is lost.

Hot Backup:

Some sites (such as worldwide airline reservations systems) cannot shut down the database while making a backup copy of the files. The cold backup is not an available option.

So different means of backing up database must be used the hot backup. Issue a SQL command to indicate to Oracle, on a tablespace-by-tablespace basis, that the files of the tablespace are to backed up. The users can continue to make full use of the files, including making changes to the data. Once the user has indicated that he/she wants to back up the tablespace files, he/she can use the operating system to copy those files to the desired backup destination.

The database must be running in ARCHIVELOG mode for the hot backup option.

If a data loss failure does occur, the lost database files can be restored using the hot backup and the online and offline redo logs created since the backup was done. The database is restored to the most consistent state without any loss of committed transactions.

103. What are Armstrong rules? How do we say that they are complete and/or sound

The well-known inference rules for FDs

Reflexive rule :

If Y is subset or equal to X then X Y.

Augmentation rule:

If X Y then XZ YZ.

Transitive rule:

If {X Y, Y Z} then X Z.

Decomposition rule :

If X YZ then X Y.

Union or Additive rule:

If {X Y, X Z} then X YZ.

Pseudo Transitive rule :

If {X Y, WY Z} then WX Z.

Of these the first three are known as Amstrong Rules. They are sound because it is enough if a set of FDs satisfy these three. They are called complete because using these three rules we can generate the rest all inference rules.

104. How can you find the minimal key of relational schema?

Minimal key is one which can identify each tuple of the given relation schema uniquely. For finding the minimal key it is required to find the closure that is the set of all attributes that are dependent on any given set of attributes under the given set of functional dependency.

Algo. I Determining X+, closure for X, given set of FDs F

1. Set X+ = X

2. Set Old X+ = X+

3. For each FD Y Z in F and if Y belongs to X+ then add Z to X +

4. Repeat steps 2 and 3 until Old X+ = X+

Algo.II Determining minimal K for relation schema R, given set of FDs F

1. Set K to R that is make K a set of all attributes in R

2. For each attribute A in K

a. Compute (K A)+ with respect to F

b. If (K A)+ = R then set K = (K A)+

105. What do you understand by dependency preservation?

Given a relation R and a set of FDs F, dependency preservation states that the closure of the union of the projection of F on each decomposed relation Ri is equal to the closure of F. i.e.,

((PR1(F)) U U (PRn(F)))+ = F+

if decomposition is not dependency preserving, then some dependency is lost in the decomposition.

106. What is meant by Proactive, Retroactive and Simultaneous Update.

Proactive Update:

The updates that are applied to database before it becomes effective in real world .

Retroactive Update:

The updates that are applied to database after it becomes effective in real world .

Simulatneous Update:

The updates that are applied to database at the same time when it becomes effective in real world .

107. What are the different types of JOIN operations?

Equi Join: This is the most common type of join which involves only equality comparisions. The disadvantage in this type of join is that there

Database Management Systems

Part A (Two mark questions)

1. List the purpose of Database System (or) List the drawback of normal File Processing System.

Problems with File Processing System:

1. Data redundancy and inconsistency

2. Difficulty in accessing data

3. Difficulty in data isolation

4. Integrity problems

5. Atomicity problems

6. Concurrent-access anomalies

7. Security problems

We can solve the above problems using Database System.

2. Define Data Abstraction and list the levels of Data Abstraction.

A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained. Since many database systems users are not computer trained, developers hide the complexity from users through several levels of abstraction, to simplify users interaction with the System: Physical level, Logical Level, View Level.

3. Define DBMS.

A Database-management system consists of a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the database, contains information about one particular enterprise. The primary goal of a DBMS is to provide an environment that is both convenient and efficient to use in retrieving and storing database information.

4. Define Data Independence.

The ability to modify a schema definition in one level without affecting a schema definition in the next higher level is called data independence. There are two levels of data independence: Physical data independence, and Logical data independence.

5. Define Data Models and list the types of Data Model.

Underlying the structure of a database is the data model: a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. The various data models that have been proposed fall into three different groups: object-based logical models, record-based logical models, and physical models.

6. Discuss about Object-Based Logical Models.

Object-based logical models are used in describing data at the logical and view levels. They provide fairly flexible structuring capabilities and allow data constraints to be specified explicitly. There are many different models: entity-relationship model, object-oriented model, semantic data model, and functional data model.

7. Define E-R model.

The entity-relationship data modal is based on perception of a real world that consists of a collection of basic objects, called entities, and of relationships among these objects. The overall logical structure of a database can be expressed graphically by an E-R diagram, which is built up from the following components:

Rectangles, which represent entity sets.

Ellipses, which represent attributes

Diamonds, which represent relationships among entity sets

Lines, which link attributes to entity sets and entity sets to relationships.

E.g.)

8. Define entity and entity set.

An entity is a thing or object in the real world that is distinguishable from other objects. For example, each person is an entity, and bank accounts can be considered to be entities. The set of all entities of the same type are termed an entity set.

Refer example in question no: 7

9. Define relationship and relationship set.

A relationship is an association among several entities. For example, a Depositor relationship associates a customer with each account that she has. The set of all relationships of the same type, are termed a relationship set.

Refer example in question no: 7

10. Define Object-Oriented Model.


The object-oriented model is based on a collection of objects. An object contains values stored in instance variables within the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain the same types of values and the same methods are grouped together into classes. The only way in which one object can access the data of another object is by invoking a method of that other object. This action is called sending a message to the object.

11. Define Record-Based Logical Models.

Record-based logical models are used in describing data at the logical and view levels. They are used both to specify the overall structure of the database and to provide a higher-level description of the implementation. Record-based models are so named because the database is structured in fixed-format records of several types. Each record type defines a fixed number of fields, or attributes, and each field is usually of fixed length. The three most widely accepted record-based data models are the relational, network, and hierarchical models.

12. Define Relational Model.

The relational model uses a collection of tables to represent both data and the relationships among those data. Each table has multiple columns, and each column has a unique name.

13. Define Network Model.

Data in the network model are represented by collections of records, and relationships among data are represented by links, which can be viewed as pointers. The records in the database are organized as collections of arbitrary graphs.

14. Define Hierarchical Model.

The hierarchical model is similar to the network model in the sense that data and relationships among data are represented by records and links, respectively. It differs from the network model in that the records are organized as collection of trees rather than arbitrary graphs.

15. Define DDL.

A database schema is specified by a set of definitions expressed by a special language called a data-definition language. The result of compilation of DDL statements is a set of tables that is stored in a special file called data dictionary. A data dictionary is a file that contains metadata-that is, data about data. The storage structure and access methods used by the database system are specified by a set of definitions in a special type of DDL called a data storage and definition language.

16. Define DML.

By data manipulation, we mean

The retrieval of information stored in the database.

The insertion of new information into the database

The deletion of information from the database

The modification of information stored in the database.

A DML is a language that enables users to access or manipulate data as organized by the appropriate data model. There are two types: Procedural DMLs and Nonprocedural DMLs.

17. Define Query and Query language.

A query is a statement requesting the retrieval of information. The portion of a DML that involves information retrieval is called query language.

18. List the role of DBA.

The person who has central control over the system is called the database administrator. The functions of the DBA include the following:

Schema definition

Storage structure and access-method definition

Schema and physical-organization modification

Granting of authorization for data access

Integrity-constraint specification

19. List the different types of database-system users.

There are four different types pf database-system users, differentiated by the way that they expect to interact with the system.

Application programmers

Sophisticated Users

Specialized users

Nave users.

20. Write about the role of Transaction manager.

TM is responsible for ensuring that the database remains in a consistent state despite system failures. The TM also ensures that concurrent transaction executions proceed without conflicting.

21. Write about the role of Storage manager.

A SM is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. The SM is responsible for interaction with the data stored on disk.

22. Define Attributes.

Entities are described in a database by a set of attributes. For example, the attributes account-number and balance describe one particular account in a bank.

An attribute, as used in the E-R model, can be characterized by the following attribute types.

Simple and composite attributes

Single-valued and multi valued attributes

Null attributes

Derived attributes.

23. Define Mapping Constraints.

An E-R enterprise schema may define certain constraints to which the contents of a database must conform. Two of the most important types of constraints are

Mapping Cardinalities: express the number of entities to which another entity can be associated via a relationship set.

Existence Dependencies: If the existence of entity x depends on the existence y, then x is said to be existence dependent on y.

24. Define Super key.

A super key is a set of one or more attributes that, taken collectively, allow us to identify uniquely an entity in the entity set. For example, the social-security attribute of the entity set customer is sufficient to distinguish one customer entity from another. Similarly, the combination of customer-name and social security is a super key for the entity set customer.

25. Define Primary key.

Superkeys for which no proper subset is a super key. Such minimal superkeys are called candidate keys or primary keys. For example, the social-security attribute of the entity set customer is sufficient to distinguish one customer entity from another.

25. Define Weak Entity Sets.

An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a Weak Entity Set. As an illustration, consider the entity set payment, which has the three attributes: payment-number, payment-date, and payment-amount. Although each payment entity is distinct, payments for different loans may share the same payment number. Thus, this entity set does not have a primary key; it is a weak entity set.

26. Define Strong Entity Set.

An entity set that has a primary key is termed Strong Entity set. E.g.) Customer entity set.

27. Define Relational Algebra.

A general expression in the relational algebra is constructed out of smaller sub expressions. Let E1 and E2 be relational algebra expressions. Then, the following are all relational algebra expressions:

  • E1 U E2
  • E1 E2
  • E1 * E2
  • (E1), where P is a predicate on attribute in E1.
  • (E1), where S is a list consisting of some of the attributes in E1
  • (E1), where x is the new name for the result of E1.

28. List the possible operations is Relational Algebra.

  • Select operation
  • Project operation
  • Union operation
  • Set Difference operation
  • Cartesian Product operation
  • Rename operation
  • Set-Intersection operation
  • Natural-join operation
  • Division
  • Assignment operation

29. Define Aggregate Functions.

Aggregate functions are functions that take a collection of values as input and return a single value. SQL offers five built-in aggregate functions:

  • Average: avg
  • Minimum: min
  • Maximum: max
  • Total: sum
  • Count: count

30. Define Null Values.

SQL allows the use of null values to indicate absence of information about the value of an attribute.

31. Define Nested Sub queries.

SQL provides a mechanism for the nesting of sub queries. A sub query is a select-from-where expression that is nested within another query. A common use of sub queries is to perform tests for set membership, set comparisons, and set cardinality.

32. Define Embedded SQL.

The SQL standard defines embeddings of SQL in a variety of programming languages, such as Pascal, PL/I, Fortran, C, and COBOL. A language in which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language constitute embedded SQL.

33. Define Integrity Constraints.

Integrity constraints provide a means of ensuring that changes made to the database by authorized users do not result in a loss of data consistency. Thus Integrity Constraints guard against accidental damage to the database. The constraints were in the following forms: Key declarations, and Form of a relationship.

34. Define Referential Integrity.

Often, we wish to ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. This condition is called referential integrity.

35. Define Assertions.

An assertion is a predicate expressing a condition that we wish the database always satisfied. E.g.) create assertion <assertion-name> check <predicate>

36. Define Triggers.

A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. To design a trigger mechanism, we must meet two requirements:

1. Specify the conditions under which the trigger is to be executed.

2. Specify the actions to be taken when the trigger executes.

37. Define Functional Dependency.

Functional dependencies are constraints on the set of legal relations. They allow us to express facts about the enterprise that we are modeling with our database.

Syntax: A -> B e.g.) account no -> balance for account table.

38. List the pitfalls in Relational Database Design.

1. Repetition of information

2. Inability to represent certain information

39. Define normalization.

By decomposition technique we can avoid the Pitfalls in Relational Database Design. This process is termed as normalization.

40. List the properties of decomposition.

1. Lossless join

2. Dependency Preservation

3. No repetition of information

41. Define First Normal Form.

If the Relation R contains only the atomic fields then that Relation R is in first normal form.

E.g.) R = (account no, balance) first normal form.

42. Define Second Normal Form.

A relation schema R is in 2 NF with respect to a set F of FDs if for all FDs of the form A -> B, where A is contained in R and B is contained in R, and A is a superkey for schema R.

43. Define BCNF.

A relation schema R is in BCNF with respect to a set F of FDs if for all FDs of the form A -> B, where A is contained in R and B is contained in R, at least one of the following holds:

1. A -> B is a trivial FD

2. A is a superkey for schema R.

44. Define 3 Normal Form.

A relation schema R is in 3 NF with respect to a set F of FDs if for all FDs of the form A -> B, where A is contained in R and B is contained in R, at least one of the following holds:

1. A -> B is a trivial FD

2. A is a superkey for schema R.

3. Each attribute in B A is contained in a candidate key for R.

45. Define Fourth Normal Form.

A relation schema R is in 4NF with respect to a set F of FDs if for all FDs of the form A ->> B (Multi valued Dependency), where A is contained in R and B is contained in R, at least one of the following holds:

1. A ->> B is a trivial MD

2. A is a superkey for schema R.

3. Define 5NF or Join Dependencies.

Let R be a relation schema and R1, R2, , Rn be a decomposition of R. The join dependency *(R1, R2, Rn) is used to restrict the set of legal relations to those for which R1,R2,Rn is a lossless-join decomposition of R. Formally, if R= R1 U R2U U Rn, we say that a relation r satisfies the join dependency *(R1, R2, Rn) if

R =

A join dependency is trivial if one of the Ri is R itself.

47. Define Cache?

The cache is the fastest and most costly form of storage. Cache memory is small; its use is managed by the operating system.

48. Explain Optical Storage Device?

The most popular form of optical storage is the compact disk read-only memory, can be read by a laser. Optical storage is the write-once, read-many disk, which allows data to be written once, but does not allow them to be erased and rewritten.

49. Define disk controller?

It is an interface between the computer system and the actual hardware of the disk drive. Accept high-level command to read or write a sector. It attaches checksums to each sector that is written. It also performs remapping of bad sectors.

50. Define RAID.

It is collectively called redundant arrays of inexpensive disk, have been proposed to address the performance and reliability issues. Raids are used for their higher reliability and higher data transfer rate. RAID stands for independent, instead of inexpensive.

51. Define file organization

A file is organized logically as a sequence of records. These records are mapped onto disk blocks. Files are provided as a basic construct in operating system.

52. Define Hash indices?

Indices are based on the values being distributed uniformly across a range of buckets. The bucket to which a value is assigned is determined by a function, called a hash function.

53. Define dense index?

An index record appears for every search-key value in the file. The index record contains the search-key value and pointer to the first data record with that search-key value.

54. Define sparse index?

An index record is created for only some of the values. Each index record contains a search-key value and a pointer to the first data record with that search-key value. To locate a record we find the index entry with the largest search-key value that is less than or equal to the search-key value.

55. Explain B+ -tree index structure?

The B+ -tree index structure is the most widely used of several index structures that maintain their efficiency despite insertion and deletion of data. A B+ -tree index takes the form of a balanced tree in which every path from the root of the tree to a leaf of

The tree is the same length.

56. Define Static Hashing?

File organization based on the technique of hashing allow us to avoid accessing an index structure. Hashing also provides a way of constructing indices.

57. Define Query processing?

Query processing refers to the range of activities involved in extracting data form a database. These activities include translation of queries expressed in high-level database language into expression that can be implemented at the physical level of the file system.

58. Define Merge-join?

The merge-join algorithm can be used to compute natural joins and equi-joins.

59. Explain Hybrid Hash-join?

The hybrid hash-join algorithm performs another optimization; it is useful when memory size is relatively large, but not all the build relation fits in memory. The partitioning phase of the hash-join algorithm needs one block of memory as a buffer for each partition that is created, and one block of memory as an input buffer.

60. Define hash-table overflow?

Hash-table overflow occurs in partition i of the build relation s if the hash index on H is larger than main memory. Hash-table overflow can occur if there are many tuples in the build relation with the same values for the join attributes.

61. What is transaction?

A transaction is a unit of program execution that accesses and possibly updates various data items. A transaction usually results from the execution of a user program written in a high-level data-manipulation language or programming language, and is delimited by statements of the form begin transaction and end transaction. The transaction consists of all operations executed between the begin and end of the transaction.

62. List the properties of transaction.

1. Atomicity

2. Consistency

3. Isolation

4. Durability

63. List the possible transaction states.

1. Active

2. Partially committed

3. Aborted

4. Committed

13. What is database?

A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.

14. What is DBMS?

It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.

15. What is a Database system?

The database and DBMS software together is called as Database system.

16. Advantages of DBMS?

Redundancy is controlled.

Unauthorised access is restricted.

Providing multiple user interfaces.

Enforcing integrity constraints.

Providing backup and recovery.

17. Disadvantage in File Processing System?

Data redundancy & inconsistency.

Difficult in accessing data.

Data isolation.

Data integrity.

Concurrent access is not possible.

Security Problems.

18. Describe the three levels of data abstraction?

The are three levels of abstraction:

Physical level: The lowest level of abstraction describes how data are stored.

Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.

View level: The highest level of abstraction describes only part of entire database.

19. Define the "integrity rules"

There are two Integrity rules.

Entity Integrity: States that Primary key cannot have NULL value

Referential Integrity: States that Foreign Key can be either a NULL value or should be Primary Key value of other relation.

20. What is extension and intension?

Extension -

It is the number of tuples present in a table at any instance. This is time dependent.

Intension -

It is a constant value that gives the name, structure of table and the constraints laid on it.

21. What is System R? What are its two major subsystems?

System R was designed and developed over a period of 1974-79 at IBM San Jose Research Center. It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system.

Its two subsystems are

Research Storage

System Relational Data System.

22. How is the data structure of System R different from the relational structure?

Unlike Relational systems in System R

Domains are not supported

Enforcement of candidate key uniqueness is optional

Enforcement of entity integrity is optional

Referential integrity is not enforced

23. What is Data Independence?

Data independence means that the application is independent of the storage structure and access strategy of data. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.

Two types of Data Independence:

Physical Data Independence: Modification in physical level should not affect the logical level.

Logical Data Independence: Modification in logical level should affect the view level.

NOTE: Logical Data Independence is more difficult to achieve

24. What is a view? How it is related to data independence?

A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.

Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

14. What is Data Model?

A collection of conceptual tools for describing data, data relationships data semantics and constraints.

16. What is E-R model?

This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

17. What is Object Oriented model?

This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

28. What is an Entity?

It is a 'thing' in the real world with an independent existence.

29. What is an Entity type?

It is a collection (set) of entities that have same attributes.

30. What is an Entity set?

It is a collection of all entities of particular entity type in the database.

31. What is an Extension of entity type?

The collections of entities of a particular entity type are grouped together into an entity set.

32. What is Weak Entity set?

An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.

33. What is an attribute?

It is a particular property, which describes the entity.

34. What is a Relation Schema and a Relation?

A relation Schema denoted by R(A1, A2, , An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).

35. What is degree of a Relation?

It is the number of attribute of its relation schema.

36. What is Relationship?

It is an association among two or more entities.

37. What is Relationship set?

The collection (or set) of similar relationships.

38. What is Relationship type?

Relationship type defines a set of associations or a relationship set among a given set of entity types.

39. What is degree of Relationship type?

It is the number of entity type participating.

30. What is DDL (Data Definition Language)?

A data base schema is specifies by a set of definitions expressed by a special language called DDL.

31. What is VDL (View Definition Language)?

It specifies user views and their mappings to the conceptual schema.

32. What is SDL (Storage Definition Language)?

This language is to specify the internal schema. This language may specify the mapping between two schemas.

33. What is Data Storage - Definition Language?

The storage structures and access methods used by database system are specified by a set of definition in a special type of DDL called data storage-definition language.

34. What is DML (Data Manipulation Language)?

This language that enable user to access or manipulate data as organised by appropriate data model.

Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.

Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data.

34. What is DML Compiler?

It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.

35. What is Query evaluation engine?

It executes low-level instruction generated by compiler.

36. What is DDL Interpreter?

It interprets DDL statements and record them in tables containing metadata.

51. What is Record-at-a-time?

The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.

52. What is Set-at-a-time or Set-oriented?

The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.

53. What is Relational Algebra?

It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

54. What is Relational Calculus?

It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.

55. How does Tuple-oriented relational calculus differ from domain-oriented relational calculus

The tuple-oriented calculus uses a tuple variables i.e., variable whose only permitted values are tuples of that relation. E.g. QUEL

The domain-oriented calculus has domain variables i.e., variables that range over the underlying domains instead of over relation. E.g. ILL, DEDUCE.

56. What is normalization?

It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties

Minimizing redundancy

Minimizing insertion, deletion and update anomalies.

57. What is Functional Dependency?

A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.

58. When is a functional dependency F said to be minimal?

Every dependency in F has a single attribute for its right hand side.

We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset of X and still have a set of dependency that is equivalent to F.

We cannot remove any dependency from F and still have set of dependency that is equivalent to F.

59. What is Multivalued dependency?

Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following properties

t3[x] = t4[X] = t1[X] = t2[X]

t3[Y] = t1[Y] and t4[Y] = t2[Y]

t3[Z] = t2[Z] and t4[Z] = t1[Z]

where [Z = (R-(X U Y)) ]

60. What is Lossless join property?

It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

61. What is 1 NF (Normal Form)?

The domain of attribute must include only atomic (simple, indivisible) values.

62. What is Fully Functional dependency?

It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.

63. What is 2NF?

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

64. What is 3NF?

A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true

X is a Super-key of R.

A is a prime attribute of R.

In other words, if every non prime attribute is non-transitively dependent on primary key.

65. What is BCNF (Boyce-Codd Normal Form)?

A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

66. What is 4NF?

A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true

X is subset or equal to (or) XY = R.

X is a super key.

67. What is 5NF?

A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true

Ri = R for some i.

The join dependency is implied by the set of FD, over R in which the left side is key of R.

52. What is Domain-Key Normal Form?

A relation is said to be in DKNF if all constraints and dependencies that should hold on the the constraint can be enforced by simply enforcing the domain constraint and key constraint on the relation.

108. What are partial, alternate,, artificial, compound and natural key?

Partial Key:

It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.

Alternate Key:

All Candidate Keys excluding the Primary Key are known as Alternate Keys.

Artificial Key :

If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.

Compound Key:

If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.

Natural Key:

When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.

109. What is indexing and what are the different kinds of indexing?

Indexing is a technique for determining how quickly specific data can be found.

Types:

Binary search style indexing

B-Tree indexing

Inverted list indexing

Memory resident table

Table indexing

110. What is system catalog or catalog relation? How is better known as?

A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of relations maintained by the system called metadata. It is also called data dictionary.

111. What is meant by query optimization?

The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

112. What is join dependency and inclusion dependency?

Join Dependency:

A Join dependency is generalization of Multivalued dependency.A JD {R1, R2, ..., Rn} is said to hold over a relation R if R1, R2, R3, ..., Rn is a lossless-join decomposition of R . There is no set of sound and complete inference rules for JD.

Inclusion Dependency:

An Inclusion Dependency is a statement of the form that some columns of a relation are contained in other columns. A foreign key constraint is an example of inclusion dependency.

113. What is durability in DBMS?

Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.

114. What do you mean by atomicity and aggregation?

Atomicity:

Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions.

Aggregation:

A concept which is used to model a relationship between a collection of entities and relationships. It is used when we need to express a relationship among relationships.

115. What is a Phantom Deadlock?

In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.

116. What is a checkpoint and When does it occur?

A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

117. What are the different phases of transaction?

Different phases are

Analysis phase

Redo Phase

Undo phase

118. What do you mean by flat file database?

It is a database in which there are no programs or user access languages. It has no cross-file capabilities but is user-friendly and provides user-interface management.

119. What is "transparent DBMS"?

It is one, which keeps its Physical Structure hidden from user.

120. Brief theory of Network, Hierarchical schemas and their properties

Network schema uses a graph data structure to organize records example for such a database management system is CTCG while a hierarchical schema uses a tree data structure example for such a system is IMS.

121. What is a query?

A query with respect to DBMS relates to user commands that are used to interact with a data base. The query language can be classified into data definition language and data manipulation language.

122. What do you mean by Correlated subquery?

Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.

A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.

E.g. Select * From CUST Where '10/03/1990' IN (Select ODATE From ORDER Where CUST.CNUM = ORDER.CNUM)

123. What are the primitive operations common to all record management systems?

Addition, deletion and modification.

124. Name the buffer in which all the commands that are typed in are stored

Edit Buffer

125. What are the unary operations in Relational Algebra?

PROJECTION and SELECTION.

126. Are the resulting relations of PRODUCT and JOIN operation the same?

No.

PRODUCT : Concatenation of every row in one relation with every row in another.

JOIN : Concatenation of rows from one relation and related rows from another.

127. What is RDBMS KERNEL?

Two important pieces of RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system-level data structures used by the kernel to manage the database

You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, retrieving, and securing data. An RDBMS maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrent resource usage; dispatches and schedules user requests; and manages space usage within its table-space structures

.

128. Name the sub-systems of a RDBMS

I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, Lock Management

129. Which part of the RDBMS takes care of the data dictionary? How

Data dictionary is a set of tables and database objects that is stored in a special area of the database and maintained exclusively by the kernel.

130. What is the job of the information stored in data-dictionary?

The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

131. Not only RDBMS takes care of locating data it also

determines an optimal access path to store or retrieve the data

132. How do you communicate with an RDBMS?

You communicate with an RDBMS using Structured Query Language (SQL)

133. Define SQL and state the differences between SQL and other conventional programming Languages

SQL is a nonprocedural language that is designed specifically for data access operations on normalized relational database structures. The primary difference between SQL and other conventional programming languages is that SQL statements specify what data operations should be performed rather than how to perform them.

134. Name the three major set of files on disk that compose a database in Oracle

There are three major sets of files on disk that compose a database. All the files are binary. These are

Database files

Control files

Redo logs

The most important of these are the database files where the actual data resides. The control files and the redo logs support the functioning of the architecture itself.

All three sets of files must be present, open, and available to Oracle for any data on the database to be useable. Without these files, you cannot access the database, and the database administrator might have to recover some or all of the database using a backup, if there is one.

135. What is an Oracle Instance?

The Oracle system processes, also known as Oracle background processes, provide functions for the user processesfunctions that would otherwise be done by the user processes themselves

Oracle database-wide system memory is known as the SGA, the system global area or shared global area. The data and control structures in the SGA are shareable, and all the Oracle background processes and user processes can use them.

The combination of the SGA and the Oracle background processes is known as an Oracle instance

136. What are the four Oracle system processes that must always be up and running for the database to be useable

The four Oracle system processes that must always be up and running for the database to be useable include DBWR (Database Writer), LGWR (Log Writer), SMON (System Monitor), and PMON (Process Monitor).

137. What are database files, control files and log files. How many of these files should a database have at least? Why?

Database Files

The database files hold the actual data and are typically the largest in size. Depending on their sizes, the tables (and other objects) for all the user accounts can go in one database filebut that's not an ideal situation because it does not make the database structure very flexible for controlling access to storage for different users, putting the database on different disk drives, or backing up and restoring just part of the database.

You must have at least one database file but usually, more than one files are used. In terms of accessing and using the data in the tables and other objects, the number (or location) of the files is immaterial.

The database files are fixed in size and never grow bigger than the size at which they were created

Control Files

The control files and redo logs support the rest of the architecture. Any database must have at least one control file, although you typically have more than one to guard against loss. The control file records the name of the database, the date and time it was created, the location of the database and redo logs, and the synchronization information to ensure that all three sets of files are always in step. Every time you add a new database or redo log file to the database, the information is recorded in the control files.

Redo Logs

Any database must have at least two redo logs. These are the journals for the database; the redo logs record all changes to the user objects or system objects. If any type of failure occurs, the changes recorded in the redo logs can be used to bring the database to a consistent state without losing any committed transactions. In the case of non-data loss failure, Oracle can apply the information in the redo logs automatically without intervention from the DBA.

The redo log files are fixed in size and never grow dynamically from the size at which they were created.

138. What is ROWID?

The ROWID is a unique database-wide physical address for every row on every table. Once assigned (when the row is first inserted into the database), it never changes until the row is deleted or the table is dropped.

The ROWID consists of the following three components, the combination of which uniquely identifies the physical storage location of the row.

Oracle database file number, which contains the block with the rows

Oracle block address, which contains the row

The row within the block (because each block can hold many rows)

The ROWID is used internally in indexes as a quick means of retrieving rows with a particular key value. Application developers also use it in SQL statements as a quick way to access a row once they know the ROWID

139. What is Oracle Block? Can two Oracle Blocks have the same address?

Oracle "formats" the database files into a number of Oracle blocks when they are first createdmaking it easier for the RDBMS software to manage the files and easier to read data into the memory areas.

The block size should be a multiple of the operating system block size. Regardless of the block size, the entire block is not available for holding data; Oracle takes up some space to manage the contents of the block. This block header has a minimum size, but it can grow.

These Oracle blocks are the smallest unit of storage. Increasing the Oracle block size can improve performance, but it should be done only when the database is first created.

Each Oracle block is numbered sequentially for each database file starting at 1. Two blocks can have the same block address if they are in different database files.

140. What is database Trigger?

A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. A database trigger can call database procedures that are also written in PL/SQL.

141. Name two utilities that Oracle provides, which are use for backup and recovery.

Along with the RDBMS software, Oracle provides two utilities that you can use to back up and restore the database. These utilities are Export and Import.

The Export utility dumps the definitions and data for the specified part of the database to an operating system binary file. The Import utility reads the file produced by an export, recreates the definitions of objects, and inserts the data

If Export and Import are used as a means of backing up and recovering the database, all the changes made to the database cannot be recovered since the export was performed. The best you can do is recover the database to the time when the export was last performed.

142. What are stored-procedures? And what are the advantages of using them.

Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.

143. How are exceptions handled in PL/SQL? Give some of the internal exceptions' name

PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination.

The exception handler must be defined within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.

User-Defined Exceptions

PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. User accomplishes this by naming an exception as in the following example:

ot_failure EXCEPTION;

In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:

EXCEPTION

when OT_FAILURE then

out_status_code := g_out_status_code;

out_msg := g_out_msg;

The following is an example of a subprogram exception:

EXCEPTION

when NO_DATA_FOUND then

g_out_status_code := 'FAIL';

RAISE ot_failure;

Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.

System-Defined Exceptions

Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND is a system-defined exception. Table below gives a complete list of internal exceptions.

PL/SQL internal exceptions.

Exception Name

Oracle Error

CURSOR_ALREADY_OPEN

ORA-06511

DUP_VAL_ON_INDEX

ORA-00001

INVALID_CURSOR

ORA-01001

INVALID_NUMBER

ORA-01722

LOGIN_DENIED

ORA-01017

NO_DATA_FOUND

ORA-01403

NOT_LOGGED_ON

ORA-01012

PROGRAM_ERROR

ORA-06501

STORAGE_ERROR

ORA-06500

TIMEOUT_ON_RESOURCE

ORA-00051

TOO_MANY_ROWS

ORA-01422

TRANSACTION_BACKED_OUT

ORA-00061

VALUE_ERROR

ORA-06502

ZERO_DIVIDE

ORA-01476

In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established.

144. Does PL/SQL support "overloading"? Explain

The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call. The count and data types of formal parameters are also considered.

PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.

145. Tables derived from the ERD

a) Are totally unnormalised

b) Are always in 1NF

c) Can be further denormalised

d) May have multi-valued attributes

(b) Are always in 1NF

146. Spurious tuples may occur due to

i. Bad normalization

ii. Theta joins

iii. Updating tables from join

a) i & ii b) ii & iii

c) i & iii d) ii & iii

(a) i & iii because theta joins are joins made on keys that are not primary keys.

147. A B C is a set of attributes. The functional dependency is as follows

AB -> B

AC -> C

C -> B

a) is in 1NF

b) is in 2NF

c) is in 3NF

d) is in BCNF

(a) is in 1NF since (AC)+ = { A, B, C} hence AC is the primary key. Since C B is a FD given, where neither C is a Key nor B is a prime attribute, this it is not in 3NF. Further B is not functionally dependent on key AC thus it is not in 2NF. Thus the given FDs is in 1NF.

148. In mapping of ERD to DFD

a) entities in ERD should correspond to an existing entity/store in DFD

b) entity in DFD is converted to attributes of an entity in ERD

c) relations in ERD has 1 to 1 correspondence to processes in DFD

d) relationships in ERD has 1 to 1 correspondence to flows in DFD

(a) entities in ERD should correspond to an existing entity/store in DFD

149. A dominant entity is the entity

a) on the N side in a 1 : N relationship

b) on the 1 side in a 1 : N relationship

c) on either side in a 1 : 1 relationship

d) nothing to do with 1 : 1 or 1 : N relationship

(b) on the 1 side in a 1 : N relationship

150. Select 'NORTH', CUSTOMER From CUST_DTLS Where REGION = 'N' Order By

CUSTOMER Union Select 'EAST', CUSTOMER From CUST_DTLS Where REGION = 'E' Order By CUSTOMER

The above is

a) Not an error

b) Error - the string in single quotes 'NORTH' and 'SOUTH'

c) Error - the string should be in double quotes

d) Error - ORDER BY clause

(d) Error - the ORDER BY clause. Since ORDER BY clause cannot be used in UNIONS

151. What is Storage Manager?

It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

152. What is Buffer Manager?

It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cache in memory.

153. What is Transaction Manager?

It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

154. What is File Manager?

It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.

155. What is Authorization and Integrity manager?

It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.

156. What are stand-alone procedures?

Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run time, which slows execution.

157. What are cursors give different types of cursors.

PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors

Implicit

Explicit

158. What is cold backup and hot backup (in case of Oracle)?

Cold Backup:

It is copying the three sets of files (database files, redo logs, and control file) when the instance is shut down. This is a straight file copy, usually from the disk directly to tape. You must shut down the instance to guarantee a consistent copy.

If a cold backup is performed, the only option available in the event of data file loss is restoring all the files from the latest backup. All work performed on the database since the last backup is lost.

Hot Backup:

Some sites (such as worldwide airline reservations systems) cannot shut down the database while making a backup copy of the files. The cold backup is not an available option.

So different means of backing up database must be used the hot backup. Issue a SQL command to indicate to Oracle, on a tablespace-by-tablespace basis, that the files of the tablespace are to backed up. The users can continue to make full use of the files, including making changes to the data. Once the user has indicated that he/she wants to back up the tablespace files, he/she can use the operating system to copy those files to the desired backup destination.

The database must be running in ARCHIVELOG mode for the hot backup option.

If a data loss failure does occur, the lost database files can be restored using the hot backup and the online and offline redo logs created since the backup was done. The database is restored to the most consistent state without any loss of committed transactions.

159. What are Armstrong rules? How do we say that they are complete and/or sound

The well-known inference rules for FDs

Reflexive rule :

If Y is subset or equal to X then X Y.

Augmentation rule:

If X Y then XZ YZ.

Transitive rule:

If {X Y, Y Z} then X Z.

Decomposition rule :

If X YZ then X Y.

Union or Additive rule:

If {X Y, X Z} then X YZ.

Pseudo Transitive rule :

If {X Y, WY Z} then WX Z.

Of these the first three are known as Amstrong Rules. They are sound because it is enough if a set of FDs satisfy these three. They are called complete because using these three rules we can generate the rest all inference rules.

160. How can you find the minimal key of relational schema?

Minimal key is one which can identify each tuple of the given relation schema uniquely. For finding the minimal key it is required to find the closure that is the set of all attributes that are dependent on any given set of attributes under the given set of functional dependency.

Algo. I Determining X+, closure for X, given set of FDs F

5. Set X+ = X

6. Set Old X+ = X+

7. For each FD Y Z in F and if Y belongs to X+ then add Z to X +

8. Repeat steps 2 and 3 until Old X+ = X+

Algo.II Determining minimal K for relation schema R, given set of FDs F

3. Set K to R that is make K a set of all attributes in R

4. For each attribute A in K

a. Compute (K A)+ with respect to F

b. If (K A)+ = R then set K = (K A)+

161. What do you understand by dependency preservation?

Given a relation R and a set of FDs F, dependency preservation states that the closure of the union of the projection of F on each decomposed relation Ri is equal to the closure of F. i.e.,

((PR1(F)) U U (PRn(F)))+ = F+

if decomposition is not dependency preserving, then some dependency is lost in the decomposition.

162. What is meant by Proactive, Retroactive and Simultaneous Update.

Proactive Update:

The updates that are applied to database before it becomes effective in real world .

Retroactive Update:

The updates that are applied to database after it becomes effective in real world .

Simulatneous Update:

The updates that are applied to database at the same time when it becomes effective in real world .

163. What are the different types of JOIN operations?

Equi Join: This is the most common type of join which involves only equality comparisions. The disadvantage in this type of join is that there

Source: Contents are provided by Technicalsymposium Google Group Members.
Disclaimer: All the above contents are provided by technicalsymposium.com Google Group members.
Further, this content is not intended to be used for commercial purpose. Technicalsymposium.com is not liable/responsible for any copyright issues.

Technical Symposium.Com All Jobs & Certifications
Lecture Notes and Scholarships & Project Details

Fresher Jobs Details
job detailsAll Fresher Software Jobs Details
job detailsAll Government Jobs Details
job detailsAll Internships Details
job detailsOff-Campus Interviews/Walk-in Details
job detailsAll Competitive Exam Details
job detailsBank Clerk/Officers Jobs Details
job detailsB.Sc/M.Sc Graduate Fresher Jobs
job detailsAll Technical Jobs Details
job detailsAll State/Central Government Jobs Details
Conference/Symposium Details
job detailsAll International Conference Details
job detailsAll National Conference Details
job detailsAll Symposium/Workshop Details
job detailsUniversity Conferences Details
job detailsIIT/NIT/IIM Conference Details
job detailsComputer Science Conference Details
job detailsElectronics Conferences Details
job detailsMechanical Conferences Details
job detailsAll Latest Conferences Details
Calendar of Events Details
job detailsJanuary List of All Event Details
job detailsFebruary List of All Event Details
job detailsMarch List of All Event Details
job detailsApril List of All Event Details
job detailsMay List of All Event Details
job detailsJune List of All Event Details
job detailsJuly List of All Event Details
job detailsAugust List of All Event Details
job detailsAll Months - List of All Events Details
GATE Question Papers & Syllabus
job detailsAll GATE Previous Year Question Papers
job detailsGATE Syllabus for All Branches
job detailsGATE-Computer Science Question Papers
job detailsGATE Mechanical Question Papers
job detailsGATE ECE Question Papers
job detailsGATE- EEE Question Papers
job detailsGATE Civil Engineering Question Papers
job detailsGATE All Branches Question Papers
job detailsGATE Exam Pattern Details
IES Question Papers & Syllabus
job detailsIES Complete Information
job detailsIES Previous Year Question Papers
job detailsIES Syllabus for All Branches
job detailsIES Mechanical Question Papers
job detailsIES - E & T Question Papers
job detailsIES - EEE Question Papers
job detailsIES -Civil Engineering Question Papers
job detailsIES - All Branches Question Papers
job detailsIES - Exam Pattern Details
All Companies Placement Materials
job detailsTCS Placement Papers
job detailsWIPRO Placement Papers
job detailsZOHO Placement Papers
job detailsCTS Placement Papers
job detailsINFOSYS Placement Papers
job detailsARICENT Placement Papers
job detailsSBI Placement Papers
job detailsRRB Placement Papers
job detailsBANK/IBPS Placement Papers
Aptitude Round Materials
job details Problem on Numbers
job detailsProblem on Trains
job detailsTime and Work
job detailsSimple Interest
job detailsAptitude Short- Cut Formulas
job detailsAptitude Tricks
job detailsOdd Man Out Series
job detailsSimplification Problems
job detailsProfit & Loss
Technical Round Materials
job detailsJAVA Technical Round Q & A
job detailsC Technical Round Q & A
job detailsC++ Technical Round Q & A
job detailsASP.NET Technical Round Q & A
job detailsData Structure Technical Round Q & A
job detailsSQL Technical Round Q & A
job detailsC# Technical Round Q & A
job detailsPHP Technical Round Q & A
job detailsJ2EE Technical Round Q & A
Software Programs and Lab Manuals
job detailsSurvey Lab Manuals
job detailsCAD/CAM Lab Manuals
job detailsOOPS/C++ Lab Manuals
job detailsJAVA/Internet Programming Manuals
job detailsList of All C Programs with Source Code
job detailsList of All Data Structure Programs
job detailsC Aptitude Programs
job detailsMicroprocessor & Micro Controller Manuals
job detailsAll Engineering Branches Lab Manuals
Anna University Syllabus & Question Papers
job detailsB.E/B.Tech Previous Year Question Papers
job detailsBE-Computer Science Syllabus
job detailsBE-Mechanical Engineering Syllabus
job detailsBE-Civil Engineering Syllabus
job detailsBE-ECE Syllabus
job detailsBE-EEE Syllabus
job detailsMBA/MCA Syllabus
job detailsBE-Aeronautical Engineering Syllabus
job detailsBE-Biomedical Syllabus
IBPS/Bank Syllabus & Question Papers
job detailsIBPS Clerk Exam Syllabus
job detailsIBPS Clerk Question Papers
job detailsIBPS Probationary Officers Syllabus
job detailsIBPS PO Question Papers
job detailsIBPS RRB Exam Syllabus
job detailsIBPS Special Officers Exam Syllabus
job detailsSBI Clerk/PO Syllabus
job detailsAll Private Banks PO/Clerk Syllabuses
job detailsAll Nationalized Bank Po/Clerk Syllabuses
Civil Engineering Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
Mechanical Engineering Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
Computer Science Engineering Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
MCA Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
MBA Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
ECE Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
EEE Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
Biotechnology Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
Chemical Engineering Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
Biomedical Engineering Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
Aeronautical Engineering Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsSeventh Semester Lecture Notes
job detailsEighth Semester Lecture Notes
job detailsLab Manuals
B.Sc (All Major Branches) Lecture Notes
job detailsMaths / Statistics Lecture Notes
job detailsPhysics Lecture Notes
job detailsChemistry Lecture Notes
job detailsElectronics Lecture Notes
job detailsComputer Science/IT Lecture Notes
job detailsAgriculture Lecture Notes
job detailsBiotechnology/Biomedical Lecture Notes
job detailsNursing Lecture Notes
job detailsFashion Technology Lecture Notes
M.Sc (All Major Branches) Lecture Notes
job detailsComputer Science/IT Lecture Notes
job detailsAgriculture Lecture Notes
job detailsBiotechnology/Biomedical Lecture Notes
job detailsNursing Lecture Notes
job detailsFashion Technology Lecture Notes
job detailsMaths / Statistics Lecture Notes
job detailsPhysics Lecture Notes
job detailsChemistry Lecture Notes
job detailsElectronics Lecture Notes
B.Com/M.Com Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsLab Manuals
job detailsCommerce Placement Materials
job detailsCommerce Placement Materials
BBA Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsLab Manuals
job detailsPlacement MaterialsEighth Semester Lecture Notes
job detailsFresher Jobs Details
BCA Lecture Notes
job detailsFirst Semester Lecture Notes
job detailsSecond Semester Lecture Notes
job detailsThird Semester Lecture Notes
job detailsFourth Semester Lecture Notes
job detailsFifth Semester Lecture Notes
job detailsSixth Semester Lecture Notes
job detailsLab Manuals
job detailsPlacement Materials
job detailsFresher Jobs Details

Hosting by Yahoo!

About-Us    Contact-Us    Site-map

copyright All rights are reserved to technicalsymposium.com