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

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

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

Ans:
I. Redundancy is controlled.
II. Providing multiple user interfaces.
III. Providing backup and recovery
IV. Unauthorized access is restricted.
V. Enforcing integrity constraints.

Ans: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.

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

Ans: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.

Ans: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.

Ans:An entity is a thing or object of importance about which data must be captured.

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

Ans: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

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

Ans:It executes low-level instruction generated by compiler.

Ans: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.

Ans: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).

Ans: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.

Ans: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.

Ans: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.

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

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

Ans: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.

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

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

Ans:It is a particular property, which describes the entity.

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

Ans: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.

Ans: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.

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

Ans: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.

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

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

Ans: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.

Ans: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.

Ans: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.

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

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

Ans:•
Data redundancy & inconsistency.
• Difficult in accessing data.
• Data isolation.
• Data integrity.
• Concurrent access is not possible.
• Security Problems.

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

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

Ans: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.

Ans: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.

Ans: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.

Ans: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.

Ans: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.

Ans: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.

Ans: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.

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

Ans: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.

Ans: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 .

Ans: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.

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

Ans:
There are three major relationship models:-
One-to-one
One-to-many
Many-to-many

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

Ans: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.

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

Ans: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.

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

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

Ans:
Analysis phase
Redo phase
Undo phase

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

Ans:Addition, deletion and modification.

Ans: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.

Ans: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.

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

Ans: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.

Ans: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.

Ans:(1)Providing default values, (2) enforcing data constraints, (3) Updating views and (4) enforcing referential integrity

Ans: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.

Ans: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.

Ans: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.

Ans: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.

Ans: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.

Ans: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).

Ans: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.

Ans: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).

Ans: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.

Ans: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.

Ans: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.

Ans: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.

Ans: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.

Ans: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.

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

Ans: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.

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

Ans: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.

Ans: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.

Ans: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.

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

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

Ans: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.

Ans: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.

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

Ans: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.

Ans: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.

Ans: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.

Ans:
A. UNDO is a table in database B. UNDO is used to restore the database to the point before the last operation
C. Both (A) & (B)
D. None of the above
Answer: A
UNDO is a table in database where all the DML transactions are stored in opposite way. For example if user does an ‘ INSERT’ transaction on any table, an opposite transaction i.e., ‘DELETE’ transaction is entered into UNDO table. This is done because if user decides to undo a transaction, whatever is stored in UNDO is fired. In this case, ‘ DELETE’ is fired on the same table. So that the row which was inserted before gets deleted now leaving table unaltered.
This may look like typical undo in our text editors but in Database, this is also a DML transaction which gets executed on the table. E.g., User Transaction : insert into table employee (, “Joe”, ‘M’, ‘California’); This transaction will insert the row into table and will return a row id of that row. Now the following transaction is added to UNDO table. delete from employee where rowid = <row_id_returned_by_insert>;

Ans:
A. Tables
B. Binary Data
C. Files
D. Data in Tables & Metadata in Files
Answer: C

Ans:
A. Client
B. Server
C. Both Client and Server
D. Client or Server
Answer:D If the client is database-intelligent client, Syntax checking is done at client side or else syntax checking is done at server side.

A. To store data.
B. To store metadata
C. To store system information
D. None of the above
Answer: A

A. Functions & Procedures are one and the same.
B. Package is a collection of Functions and Procedures
C. Function returns a value while a Procedure doesn’t.
Answer: All of the above
Iostat reports on terminal, disk and tape I/O activity. Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity. Netstat reports on the contents of network data structures.

A. Opposite of UNDO
B. Re-does the previous operation on database again.
C. REDO is used for ROLLBACK.
D. None of the above.
Answer: C
The most important point to remember is REDO is not the opposite of UNDO. Whenever a DML transaction happens in database, the data to be updated goes to the DATABASE BUFFER CACHE. From here the data is written to REDO BUFFER and then to REDO Logs. These logs are saved for future use. Future ROLLBACK and DATA RECOVERY operations require these logs. Without these logs it is impossible to do DATA RECOVERY. If ARCHIVING is enabled then these logs are bundled or archived and stored.

A. True
B. False
Answer: B
COMMIT simply confirms the transaction and writes the committed data to disk and clears UNDO file. While ROLLBACK does the opposite transaction. ROLLBACK also clears UNDO file. ROLLBACK takes much longer time because it has to execute one full transaction (opposite) and COMMIT it. Hence COMMIT is faster than ROLLBACK.

A. ORDERBY performs sorting while GROUPBY AGGREGATES Data
B. GROUPBY sorts data while ORDERBY puts data in order
C. Both perform sorting.
D. None of the above
Answer: A
The ORDER BY performs a sort operation. So think of a telephone phone directory. SELECT NAME FROM DIRECTORY ORDER BY NAME This would ensure that the result set would be sorted in (by default) ascending order. The GROUP BY operation aggregates data in your result set. Continuing the example of the telephone directory SELECT CITY, COUNT(CITY) FROM DIRECTORY GROUP BY CITY This would ensure that the result set would be grouped according to the city where the individual lives. The COUNT and GROUP BY works in conjunction.

A. UNDO file
B. Alert Log file
C. Archive file
D. Both A & B
Answer: C
Alert log file records all modifications to the database but modifications to data alone is recorded by Archive files. UNDO file stores UNDO tables which have opposite transactions recorded. Archive files also help in recovery of data.

A. SQL
B. Procedures
Answer: SQL
• SQL is often much shorter to write - you can do an update or summary procedure in one line of code that would take you several lines of procedural.
• For set-based problems - SQL is much faster processor-wise and IO wise too because all the underlining looping iteration is delegated to a database server process that does it in a very low level way and uses IO/processor more efficiently and knows the current state of the data - e.g. what other processes are asking for the data
If you were to update say a sales person of all customers in a particular region - your procedural way would look something like this do until eof
if rs("state") = "NH" then
rs("salesperson") = "Mike"
end if
rs.next
loop
The SQL way would be: UPDATE customers SET salesperson = "Mike" WHERE state = "NH"
If you had, say 2 or 3 tables you need to check, your procedural quickly becomes difficult to manage as you pile on nested loop after loop.