Explore topic-wise InterviewSolutions in Current Affairs.

An OLE DB interface is specified by a set of objects, and the PROPERTIES and methods that they EXPOSE, and OLE DB defines standardized interfaces. An object NEED not expose all of its properties and methods in a GIVEN interface. An OLE DB implementation defines how the object SUPPORTS the interface. The implementation is completely hidden from the user. Thus developers of an object are free to change the implementation whenever they want, but they should not change the interface without consulting their users.

An OLE DB interface is specified by a set of objects, and the properties and methods that they expose, and OLE DB defines standardized interfaces. An object need not expose all of its properties and methods in a given interface. An OLE DB implementation defines how the object supports the interface. The implementation is completely hidden from the user. Thus developers of an object are free to change the implementation whenever they want, but they should not change the interface without consulting their users.

The major goals of OLE DB are to:

1. CREATE object interfaces for DBMS functionality PIECES;
2. Increase flexibility for DEVELOPERS and users;
3. provide an object interface over any TYPE of data; and
4. do not force data to be converted or moved from where it is.

The major goals of OLE DB are to:

By breaking the features and the functions of a DBMS into COM objects, OLE DB characteristic overcomes a major disadvantage of ODBC. With ODBC, a VENDOR must create an ODBC driver for almost all DBMS features and functions in order to participate in ODBC at all. This is a large task that REQUIRES a substantial initial investment. With OLE DB, however, a DBMS vendor can implement portions of their product.

By breaking the features and the functions of a DBMS into COM objects, OLE DB characteristic overcomes a major disadvantage of ODBC. With ODBC, a vendor must create an ODBC driver for almost all DBMS features and functions in order to participate in ODBC at all. This is a large task that requires a substantial initial investment. With OLE DB, however, a DBMS vendor can implement portions of their product.

An ODBC file DATA source is a file that can be SHARED among database users. A ODBC system data source is one that is local to a single computer. A ODBC user data source is only available to the user who created It.

An ODBC file data source is a file that can be shared among database users. A ODBC system data source is one that is local to a single computer. A ODBC user data source is only available to the user who created It.

Developed first, the ODBC standard is for relational databases; while the OLE DB standard provides FUNCTIONALITY for both relational and other databases. FINALLY, ADO was developed to PROVIDE easier access to OLE DB data for the non-object-oriented PROGRAMMER.

Developed first, the ODBC standard is for relational databases; while the OLE DB standard provides functionality for both relational and other databases. Finally, ADO was developed to provide easier access to OLE DB data for the non-object-oriented programmer.

SQL Server 2000 SUPPORTS INSTEAD OF and AFTER triggers only. There is no SQL Server support for BEFORE triggers. A table may have ONE or more AFTER triggers for INSERT, update and delete actions; AFTER triggers may not be assigned to views. A view or table may have at most one INSTEAD OF TRIGGER for each triggering action of insert, update or delete.

SQL Server 2000 supports INSTEAD OF and AFTER triggers only. There is no SQL Server support for BEFORE triggers. A table may have one or more AFTER triggers for insert, update and delete actions; AFTER triggers may not be assigned to views. A view or table may have at most one INSTEAD OF trigger for each triggering action of insert, update or delete.

With a clustered INDEX, the data are STORED in the bottom level of the index and in the same order as that index. With a nonclustered index, the bottom level of an index does not contain data; it contains pointers to the data. For data RETRIEVAL, clustered INDEXES are FASTER than nonclustered indexes.

With a clustered index, the data are stored in the bottom level of the index and in the same order as that index. With a nonclustered index, the bottom level of an index does not contain data; it contains pointers to the data. For data retrieval, clustered indexes are faster than nonclustered indexes.

With the simple RECOVERY model, no logging is done. The only WAY to recover a database is to RESTORE the database to the last backup. With full recovery, all database changes are logged. With bulk-logged database recovery, all changes are logged EXCEPT those that cause LARGE log entries.

With the simple recovery model, no logging is done. The only way to recover a database is to restore the database to the last backup. With full recovery, all database changes are logged. With bulk-logged database recovery, all changes are logged except those that cause large log entries.

The strictest isolation level is SERIALIZABLE. With it, SQL Server places a range lock on the rows that have been read. This level is the most expensive to use and should only be used when absolutely REQUIRED. The next most restrictive level is REPEATABLE READ, which means SQL Server places and holds locks on all rows that are read. It is possible to MAKE dirty READS by setting the isolation level to READ UNCOMMITTED, which is the least restrictive level. READ COMMITTED is the default isolation level.

The strictest isolation level is SERIALIZABLE. With it, SQL Server places a range lock on the rows that have been read. This level is the most expensive to use and should only be used when absolutely required. The next most restrictive level is REPEATABLE READ, which means SQL Server places and holds locks on all rows that are read. It is possible to make dirty reads by setting the isolation level to READ UNCOMMITTED, which is the least restrictive level. READ COMMITTED is the default isolation level.

A complete backup makes a copy of the ENTIRE database. A differential backup makes a copy of the changes that have been made to the database since the last complete backup. A complete backup MUST be made before the first differential backup. Because differential BACKUPS are faster, they can be taken more frequently and the chance of DATA loss is reduced. Complete backups take longer but are slightly simpler to use for recovery.

A complete backup makes a copy of the entire database. A differential backup makes a copy of the changes that have been made to the database since the last complete backup. A complete backup must be made before the first differential backup. Because differential backups are faster, they can be taken more frequently and the chance of data loss is reduced. Complete backups take longer but are slightly simpler to use for recovery.

Database redesign is necessary for two reasons. FIRST, redesign is necessary both to fix mistakes made during the initial database design. Second, redesign is necessary to adapt the database to CHANGES in SYSTEM REQUIREMENTS. Such changes are common because information systems and ORGANIZATIONS do not just influence each other they create each other. Thus, new information systems cause changes in systems requirements.

Database redesign is necessary for two reasons. First, redesign is necessary both to fix mistakes made during the initial database design. Second, redesign is necessary to adapt the database to changes in system requirements. Such changes are common because information systems and organizations do not just influence each other they create each other. Thus, new information systems cause changes in systems requirements.

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.

The advantages of stored procedures are:

A stored procedure is a program that is stored within the database and is compiled when USED. They can receive input parameters and they can RETURN results. Unlike TRIGGERS, their scope is database-wide; they can be used by any process that has permission to use the database stored procedure.

A stored procedure is a program that is stored within the database and is compiled when used. They can receive input parameters and they can return results. Unlike triggers, their scope is database-wide; they can be used by any process that has permission to use the database stored procedure.

1. providing default VALUES,
2. ENFORCING DATA CONSTRAINTS,
3. updating views and
4. enforcing REFERENTIAL integrity.

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.

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.

A SQL view is a virtual table built from other TABLES or views.

Views are USED to:

1. hide columns or rows,
2. the results of computed columns,
3. hide complicated SQL SYNTAX,
4. LAYER built-in functions,
5. provide a level of indirection between APPLICATION programs and tables,
6. assign different sets of processing permissions to tables, and
7. to assign different sets of triggers to the same table.

A SQL view is a virtual table built from other tables or views.

Views are used to:

Referential integrity constraints REQUIRE that foreign key values in one table correspond to primary key values in ANOTHER. If the value of the primary key is changed -- that is, updated -- the value of the foreign key must immediately be changed to match it. CASCADING updates will SET this CHANGE to be done automatically by the DBMS whenever necessary.

Referential integrity constraints require that foreign key values in one table correspond to primary key values in another. If the value of the primary key is changed -- that is, updated -- the value of the foreign key must immediately be changed to match it. Cascading updates will set this change to be done automatically by the DBMS whenever necessary.

A recursive relationship is a relationship among entities of the same CLASS, and is represented in the same way as other RELATIONSHIPS are. The rows of the tables can take two different roles, however. Some are parent rows, and others are child rows. Further, the table will contain both its own PRIMARY key and the foreign key that links back to the table itself. If a row has no parent, then the value of the foreign key column in that row will be NULL. If the row has a parent, then there must be a foreign key value in that row that corresponds to the primary key value of ANOTHER row in the table.

A recursive relationship is a relationship among entities of the same class, and is represented in the same way as other relationships are. The rows of the tables can take two different roles, however. Some are parent rows, and others are child rows. Further, the table will contain both its own primary key and the foreign key that links back to the table itself. If a row has no parent, then the value of the foreign key column in that row will be null. If the row has a parent, then there must be a foreign key value in that row that corresponds to the primary key value of another row in the table.

DATA constraints on a column are the LIMITS put on the VALUES the data can have.

There are four types of data constraints:

1. domain constraints, which DEFINE a limited set of values for the column,
2. range constraints, which specify that the values must fall within a CERTAIN range,
3. intrarelation constraints, which define what values the column can have based on values of other columns in the same table, and
4. interrelation constraints, which define values the column can have based on values of columns in other tables.

Data constraints on a column are the limits put on the values the data can have.

There are four types of data constraints:

The ideal PRIMARY key is short, NUMERIC and FIXED. A surrogate key is a unique, DBMS-supplied identifier intended to be used as the primary key of a table. Further, the DBMS will not allow the value of a surrogate key to be changed. The VALUES of a surrogate key have no meaning to the users and are usually hidden on forms and reports. By design, they are short, numeric and fixed and thus MEET the definition of the ideal primary key.

The ideal primary key is short, numeric and fixed. A surrogate key is a unique, DBMS-supplied identifier intended to be used as the primary key of a table. Further, the DBMS will not allow the value of a surrogate key to be changed. The values of a surrogate key have no meaning to the users and are usually hidden on forms and reports. By design, they are short, numeric and fixed and thus meet the definition of the ideal primary key.

The steps are:

1. SPECIFY the primary key,
2. specify candidate keys,
3. specify column properties including null status, DATA TYPE, default value (if any), and data CONSTRAINTS (if any), and
4. verifying normalization.

The steps are:

1:1 - a single entity INSTANCE of one type is related to a single-entity instance of ANOTHER type.
1:N - a single entity instance of one type is related to many-entity instances of another type.
M:N - many-entity instances of one type relate to many-entity instances of another type.

1:1 - a single entity instance of one type is related to a single-entity instance of another type.
1:N - a single entity instance of one type is related to many-entity instances of another type.
M:N - many-entity instances of one type relate to many-entity instances of another type.

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.

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.

An entity is SOMETHING that can be identified in the users’ WORK environment, something that the users want to TRACK. Entities of a given TYPE are grouped into entity classes. An entity instance is the REPRESENTATION of a particular entity.

An entity is something that can be identified in the users’ work environment, something that the users want to track. Entities of a given type are grouped into entity classes. An entity instance is the representation of a particular entity.

The inconsistent VALUES problem OCCURS when different users or DATA sources use slightly different forms of the same data value. One EXAMPLE is where automobiles are specified as “Ford, 2-door, Red” in one cell and “Red Ford 2-door’ in another.

The inconsistent values problem occurs when different users or data sources use slightly different forms of the same data value. One example is where automobiles are specified as “Ford, 2-door, Red” in one cell and “Red Ford 2-door’ in another.

Tables that are NORMALIZED contain DATA that has been distributed among the tables, but which may need to be recombined to answer queries from an application. To recombine the data, the programmer will have to use SUBQUERIES and/or JOINS. These SQL structures are more complex to write than a simple SELECT statement.

Tables that are normalized contain data that has been distributed among the tables, but which may need to be recombined to answer queries from an application. To recombine the data, the programmer will have to use subqueries and/or joins. These SQL structures are more complex to write than a simple SELECT statement.

1. Multivalued DEPENDENCIES,
2. FUNCTIONAL dependencies,
3. Candidate keys,
4. PRIMARY keys and
5. FOREIGN keys.

A deletion ANOMALY occurs when, by deleting the facts about one entity, we in advertently delete facts about another entity; with one deletion, we lose facts about two entities. For example, if we delete the tuple for Student 001289 from a table, we may lose not only the fact that Student 001289 is in Pierce Hall, but also the fact that he has $200 LEFT in his security deposit. An insertion anomaly happens when we encounter the restriction that we cannot insert a fact about one entity until we have an additional fact about another entity. For example, we WANT to store the fact that the security deposit for Pierce Hall is $300, but we cannot enter this data into the Student relation until a student registers for Pierce Hall.

A deletion anomaly occurs when, by deleting the facts about one entity, we in advertently delete facts about another entity; with one deletion, we lose facts about two entities. For example, if we delete the tuple for Student 001289 from a table, we may lose not only the fact that Student 001289 is in Pierce Hall, but also the fact that he has $200 left in his security deposit. An insertion anomaly happens when we encounter the restriction that we cannot insert a fact about one entity until we have an additional fact about another entity. For example, we want to store the fact that the security deposit for Pierce Hall is $300, but we cannot enter this data into the Student relation until a student registers for Pierce Hall.

A foreign key is used to establish relationships among relations in the relational model. TECHNICALLY, a foreign key is a column (or columns) appearing in one relation that is (are) the primary key of another table. Although there MAY be exceptions, the VALUES in the foreign key columns USUALLY must correspond to values existing in the SET of primary key values. This correspondence requirement is created in a database using a referential integrity constraint on the foreign key.

A foreign key is used to establish relationships among relations in the relational model. Technically, a foreign key is a column (or columns) appearing in one relation that is (are) the primary key of another table. Although there may be exceptions, the values in the foreign key columns usually must correspond to values existing in the set of primary key values. This correspondence requirement is created in a database using a referential integrity constraint on the foreign key.

Equations deal with numerical relationships. A functional DEPENDENCY deals with the existence of a DETERMINANT relationship between ATTRIBUTES, regardless of whether or not there is a numerical relationship between them. Thus, if we know that there is no hot WATER every Wednesday, No-Hot-Water is functionally DEPENDENT on Wednesday. So, if we know it is Wednesday, then we know we will have No-Hot-Water. This is a functional dependency, but not an equation.

Equations deal with numerical relationships. A functional dependency deals with the existence of a determinant relationship between attributes, regardless of whether or not there is a numerical relationship between them. Thus, if we know that there is no hot water every Wednesday, No-Hot-Water is functionally dependent on Wednesday. So, if we know it is Wednesday, then we know we will have No-Hot-Water. This is a functional dependency, but not an equation.

Relations are also called tables, and sometimes by the OLDER data processing TERM files. A row is known as a tuple in the relational model, but may also be referred to as a record. FINALLY, relational model attributes are known as table COLUMNS and sometimes as fields.

Relations are also called tables, and sometimes by the older data processing term files. A row is known as a tuple in the relational model, but may also be referred to as a record. Finally, relational model attributes are known as table columns and sometimes as fields.

An SQL subquery is a means of querying two or more tables at the same time. The subquery itself is an SQL SELECT STATEMENT CONTAINED within the WHERE CLAUSE of another SQL SELECT statement, and separated by being enclosed in parenthesis. Some subqueries have equivalent SQL join structures, but correlated subqueries cannot be DUPLICATED by a join..

An SQL subquery is a means of querying two or more tables at the same time. The subquery itself is an SQL SELECT statement contained within the WHERE clause of another SQL SELECT statement, and separated by being enclosed in parenthesis. Some subqueries have equivalent SQL join structures, but correlated subqueries cannot be duplicated by a join..

SELECT COUNT(*) AS NumStudents FROM STUDENT;

SELECT COUNT(*) AS NumStudents FROM STUDENT;

COUNT: COMPUTES the number of rows in a table. SUM: TOTALS numeric columns. AVG: computes the average value. MAX: obtains the MAXIMUM value of a column in a table. MIN: obtains the minimum value of a column in a table.

COUNT: computes the number of rows in a table. SUM: totals numeric columns. AVG: computes the average value. MAX: obtains the maximum value of a column in a table. MIN: obtains the minimum value of a column in a table.

SELECT * FROM STUDENT WHERE GRADE >= 90;

SELECT * FROM STUDENT WHERE Grade >= 90;

SQL stands for Structured Query Language, and is the most important data processing language in USE today. It is not a COMPLETE programming language LIKE Java or C#, but a data sublanguage used for creating and processing database data and METADATA. All DBMS products today use SQL.

SQL stands for Structured Query Language, and is the most important data processing language in use today. It is not a complete programming language like Java or C#, but a data sublanguage used for creating and processing database data and metadata. All DBMS products today use SQL.

While WORKING at IBM, E.F. Codd created the relational database model. A paper he PUBLISHED in 1970 presented his ideas to the world at large. His work is the foundation for most of the DBMSs currently in use, and thus forms the BASIS for database systems as we know and use them TODAY.

While working at IBM, E.F. Codd created the relational database model. A paper he published in 1970 presented his ideas to the world at large. His work is the foundation for most of the DBMSs currently in use, and thus forms the basis for database systems as we know and use them today.

In addition to the users’ DATA, a DATABASE contains a description of its own STRUCTURE. This descriptive data is CALLED “metadata.”

In addition to the users’ data, a database contains a description of its own structure. This descriptive data is called “metadata.”

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.

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.

DATA is recorded FACTS and FIGURES, and information is knowledge DERIVED from data. A database stores data in such a way that information can be CREATED.

Data is recorded facts and figures, and information is knowledge derived from data. A database stores data in such a way that information can be created.

1. 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 flies from the LATEST backup. All work performed on the database since the last backup is LOST.
2. 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. 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.

1. 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 flies from the latest backup. All work performed on the database since the last backup is lost.
2. 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. 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.

PL/SQL uses CURSORS for all database information ACCESSES statements. The language supports the use two types of cursors
1.) Implicit
2.) EXPLICIT

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

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.

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.

It is the PROGRAM MODULE, which tests for the SATISFACTION of integrity constraint and checks the authority of user to ACCESS DATA.

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

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.

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.

It is a PROGRAM MODULE, which ensures that database, remains in a consistent STATE despite system failures and concurrent transaction execution proceeds without conflicting.

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

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.

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.

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.

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.

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.

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.

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.

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.