Explore topic-wise InterviewSolutions in Current Affairs.

Data DEFINITION Language : A data base SCHEMA is SPECIFIES by a SET of definitions expressed by a special language CALLED DDL.

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

VDL (View DEFINITION LANGUAGE): It SPECIFIES user views and their mappings to the CONCEPTUAL SCHEMA.

VDL (View Definition Language): It specifies user views and their mappings to the conceptual schema.

Data independence tells about the independence of the data INSIDE the application. It usually deals with the STORAGE structure and represents the ability to modify the schema definition. It doesn’t affect the schema definition which is being written on the HIGHER level. There are TWO types of data independence:

1. Physical data independence: it ALLOW the modification to be done in physical level and doesn’t affect the logical level.
2. Logical data independence: it allow the modification to be done at logical level and affects the view level.

NOTE: Logical Data Independence is more difficult to achieve.

Data independence tells about the independence of the data inside the application. It usually deals with the storage structure and represents the ability to modify the schema definition. It doesn’t affect the schema definition which is being written on the higher level. There are two types of data independence:

NOTE: Logical Data Independence is more difficult to achieve.

ACID stands for Atomicity, Consistency, ISOLATION and durability and it plays an important role in the DATABASE. These properties allow the database to be more CONVENIENT to access and use. This allows data to be SHARED more safely in between the tables. If these properties are not being implemented then the data will become inconsistent and inaccurate. It helps in maintaining the ACCURACY of the data in the database.

ACID stands for Atomicity, Consistency, Isolation and durability and it plays an important role in the database. These properties allow the database to be more convenient to access and use. This allows data to be shared more safely in between the tables. If these properties are not being implemented then the data will become inconsistent and inaccurate. It helps in maintaining the accuracy of the data in the database.

Cardinality is important and USED to arrange the data inside the database. It is related to the design part and NEED to be properly used in database. It is used in E-R diagrams and used to show the relationship between entities/tables. It has many forms like the basic is ONE to one, which associate one entity with another.

Second is one to many: which relates one entity with many entities in a table.
Third is many to many M: N that ALLOWS many entities to be related to many more.
Last is many to one: that allows the many entities to be associated with one entity.

Cardinality is important and used to arrange the data inside the database. It is related to the design part and need to be properly used in database. It is used in E-R diagrams and used to show the relationship between entities/tables. It has many forms like the basic is one to one, which associate one entity with another.

Second is one to many: which relates one entity with many entities in a table.
Third is many to many M: N that allows many entities to be related to many more.
Last is many to one: that allows the many entities to be associated with one entity.

• DBMS is persistent and accessible when the data is CREATED or exists, but RDBMS tells about the relation between the table and other tables.
• RDBS SUPPORTS a TABULAR structure for data and relationship between them in the system whereas DBMS supports only the tabular structure.
• DBMS provide UNIFORM methods for application that has to be independently accessed, but RDBMS doesn’t provide methods like DBMS but provide relationship which link one entity with another.

This language is to SPECIFY the internal SCHEMA. This language MAY specify the mapping between TWO schemas.

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

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.

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.

There are three levels of data ABSTRACTION that is PRESENT in database MODEL and these are as follows:

• physical level: it is the lowest level that describes how data is stored inside the database.
• logical level: it is the next HIGHER level in the hierarchy that PROVIDES the abstraction. It describes what data are stored and the relationship between them.
• view level : it is the highest level in hierarch that describes part of the entire database. It allows user to view the database and do the query.

There are three levels of data abstraction that is present in database model and these are as follows:

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.

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.

Non-first normal form (NFA) – it describes the definition of the DATABASE design which is different from the first normal form. It KEEPS the values in structured and specialized types with their own domain specific LANGUAGES. The QUERY language used in this is extended to incorporate more support for relational domain values by adding more OPERATORS.

Non-first normal form (NFA) – it describes the definition of the database design which is different from the first normal form. It keeps the values in structured and specialized types with their own domain specific languages. The query language used in this is extended to incorporate more support for relational domain values by adding more operators.

Analytical processing databases are not very normalized. The operations which are used are read most databases. It is used to extract the data that are ancient and accumulated over long period of time. For this purpose de-normalization occurs that provide smart BUSINESS applications. Dimensional tables in star schema are good example of de-normalized data. The de-normalized form must be controlled while extracting, transforming, loading and processing. There should be constraint that user should not be allowed to view the STATE till it is CONSISTENT. It is used to INCREASE the performance on many SYSTEMS without RDBMS platform.

Analytical processing databases are not very normalized. The operations which are used are read most databases. It is used to extract the data that are ancient and accumulated over long period of time. For this purpose de-normalization occurs that provide smart business applications. Dimensional tables in star schema are good example of de-normalized data. The de-normalized form must be controlled while extracting, transforming, loading and processing. There should be constraint that user should not be allowed to view the state till it is consistent. It is used to increase the performance on many systems without RDBMS platform.

Normalization is very ESSENTIAL part of relational model. It consists of set of procedures that eliminates the domains that are non-atomic and redundancy of data that prevents data manipulation and loss of data INTEGRITY. Normal forms are the common form of normalization. It helps in reducing redundancy to increase the information overall. It has some DISADVANTAGES as it increases complexity and have some OVERHEAD of processing.

Normalization is very essential part of relational model. It consists of set of procedures that eliminates the domains that are non-atomic and redundancy of data that prevents data manipulation and loss of data integrity. Normal forms are the common form of normalization. It helps in reducing redundancy to increase the information overall. It has some disadvantages as it increases complexity and have some overhead of processing.

There are many relational operators that are used to perform ACTIONS on relational database. These operators are as FOLLOWS:

• union operator: that combines the rows of two relations and doesn’t include any duplicate. It also REMOVES the duplicates from the result.
• intersection operator: provides a set of rows that two relations have in common.
• difference operator: provide the output by TAKING two relations and producing the difference of rows from first that don’t exist in second.
• cartesian product: is done on two relations. It ACTS as a cross join operator.

There are many relational operators that are used to perform actions on relational database. These operators are as follows:

Index is a way to provide quick access to the data and STRUCTURE. It has indexes maintain and can be created to combine attributes on a relation. Index ALLOWS the QUERIES to FILTER out the searches faster and matching data can be found earlier with simplicity. For example it is same as the book where by using the index you can DIRECTLY jump to a defined section. In relational database there is a provision to give multiple indexing techniques to optimize the data distribution.

Index is a way to provide quick access to the data and structure. It has indexes maintain and can be created to combine attributes on a relation. Index allows the queries to filter out the searches faster and matching data can be found earlier with simplicity. For example it is same as the book where by using the index you can directly jump to a defined section. In relational database there is a provision to give multiple indexing techniques to optimize the data distribution.

Stored procedure stored inside the database. This also includes the executable code that usually COLLECTS and customizes the operations LIKE insert, encapsulation, etc. These stored procedures are used as APIs for simplicity and security purposes. The implementation of it allows the developers to have procedural EXTENSIONS to the STANDARD SQL syntax. Stored procedure doesn’t come as a part of RELATIONAL database model, but can be included in many implementations commercially.

Stored procedure stored inside the database. This also includes the executable code that usually collects and customizes the operations like insert, encapsulation, etc. These stored procedures are used as APIs for simplicity and security purposes. The implementation of it allows the developers to have procedural extensions to the standard SQL syntax. Stored procedure doesn’t come as a part of relational database model, but can be included in many implementations commercially.

• Primary key uniquely identify a relationship in a DATABASE, whereas FOREIGN key is the key that is in other relation and it has been referenced from the primary key from other table.
• Primary key remains one only for the table, whereas there can be more than one foreign key.
• Primary key is unique and won’t be SHARED between MANY tables, but foreign key will be shared between more than one table and will be used to tell the relationship between them.

There are two Integrity RULES.

1. Entity Integrity: States that Primary KEY cannot have NULL value
2. Referential Integrity: States that Foreign Key can be EITHER a NULL value or should be Primary Key value of other relation.

There are two Integrity rules.

The two principal rules for the relational model are as follows:

1. Entity integrity: this is used to maintain the integrity at entity level
2. REFERENTIAL integrity: it is used to maintain integrity on all the VALUES which have been referenced.

The differences between them are as follows:

• Entity integrity tells that in a database every entity should have a unique key; on the other hand referential integrity tells that in the database every table values for all foreign keys will remain valid.
• Referential integrity is based on entity integrity but it is not the other way around.

For example: if a table is PRESENT and there is a SET of column out of which one column has parent key set then to ensure that the table doesn’t contain any duplicate values, a unique index is defined on the column that CONTAINS the parent key.

The two principal rules for the relational model are as follows:

The differences between them are as follows:

For example: if a table is present and there is a set of column out of which one column has parent key set then to ensure that the table doesn’t contain any duplicate values, a unique index is defined on the column that contains the parent key.

Constraints are kind of restrictions that are applied to the database or on the domain of an attribute. For example an integer attribute is restricted from 1-10 and not more than that. They provide the way to implement the BUSINESS logic and the rules in database. In database it can be implemented in the form of check constraints that checks for the rules that haven’t been FOLLOWED by the programmer. Constraint ALSO used to restrict the data that can be STORED in the relations. Domain constraint can be applied to check the domain functionality and KEEP it safe..

Constraints are kind of restrictions that are applied to the database or on the domain of an attribute. For example an integer attribute is restricted from 1-10 and not more than that. They provide the way to implement the business logic and the rules in database. In database it can be implemented in the form of check constraints that checks for the rules that haven’t been followed by the programmer. Constraint also used to restrict the data that can be stored in the relations. Domain constraint can be applied to check the domain functionality and keep it safe..

Relational database MEANS the relationship between different databases. In relational database USER can store and access all the data through the tables which are related to each other. Relationship between the store data is called base relations and IMPLEMENTATION of it is called as tables. Whereas, relations which don’t store the data, but can be found out by applying relational operations on other relations are called as derived relations. When these are implemented they are termed as views or queries. Derived relations are more USEFUL then base RELATION, as they can have more information from many relations, but they act as a single relation.

Relational database means the relationship between different databases. In relational database user can store and access all the data through the tables which are related to each other. Relationship between the store data is called base relations and implementation of it is called as tables. Whereas, relations which don’t store the data, but can be found out by applying relational operations on other relations are called as derived relations. When these are implemented they are termed as views or queries. Derived relations are more useful then base relation, as they can have more information from many relations, but they act as a single relation.

Domain describes possible VALUES grouped together that can be given for an ATTRIBUTE. It is considered the same way as a constraint on the value of attribute. A domain can be attached to an attribute but only if the attribute is an ELEMENT of specified set. For EXAMPLE: XYZ doesn’t fulfill the domain constraint but the integer value as 899 fulfills the CRITERIA of domain constraint. Hence, domain is of high importance.

Domain describes possible values grouped together that can be given for an attribute. It is considered the same way as a constraint on the value of attribute. A domain can be attached to an attribute but only if the attribute is an element of specified set. For example: XYZ doesn’t fulfill the domain constraint but the integer value as 899 fulfills the criteria of domain constraint. Hence, domain is of high importance.

Relation in the relational database model is defined as the set of tuples that have the same attributes. TUPLE represents an object and also the information that the object contains. Objects are basically INSTANCES of classes and used to HOLD the larger picture. Relation is described as a table and is organized in ROWS and columns. The data referenced by the relation come in the same domain and have the same constraints as well. Relations in the relational database model can be modified using the commands like insert, DELETE etc.

Relation in the relational database model is defined as the set of tuples that have the same attributes. Tuple represents an object and also the information that the object contains. Objects are basically instances of classes and used to hold the larger picture. Relation is described as a table and is organized in rows and columns. The data referenced by the relation come in the same domain and have the same constraints as well. Relations in the relational database model can be modified using the commands like insert, delete etc.

Table CONSISTS of some properties that are known as ATTRIBUTES. These consist of the representation of ENTITY in the table. They are REPRESENTED by columns in the table. Entity is referred to the store data about any particular thing. It is the smallest unit inside the table.

Table consists of some properties that are known as attributes. These consist of the representation of entity in the table. They are represented by columns in the table. Entity is referred to the store data about any particular thing. It is the smallest unit inside the table.

A DATABASE MODEL REPRESENTS the relationship between one or more databases. The relationship is KNOWN as the relational database model. It is an extension of the normal databases WITHOUT relations. It provides flexibility and allows one database to be in relation with another database. It can access the data from many databases at one time over the network.

A database model represents the relationship between one or more databases. The relationship is known as the relational database model. It is an extension of the normal databases without relations. It provides flexibility and allows one database to be in relation with another database. It can access the data from many databases at one time over the network.

There are THREE MAJOR RELATIONSHIP MODELS:-

• One-to-one
• One-to-many
• Many-to-many

There are three major relationship models:-

INSERT STATEMENT is used to insert new rows in to table. Update to update existing data in the table. DELETE statement to delete a record from the table. Below CODE snippet for Insert, Update and Delete :-

INSERT INTO wbEmployee SET name='maxwell',age='22';
UPDATE wbEmployee SET age='22' where name='maxwell';
DELETE FROM wbEmployee WHERE name = 'david';

Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :-

INSERT INTO wbEmployee SET name='maxwell',age='22';
UPDATE wbEmployee SET age='22' where name='maxwell';
DELETE FROM wbEmployee WHERE name = 'david';

Functional Dependency is the STARTING point of NORMALIZATION. It exists when a relation between two ATTRIBUTES allows you to uniquely DETERMINE the corresponding attribute's value.

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

• Data REDUNDANCY and inconsistency.
• Difficult in accessing data.
• Data isolation.
• Data integrity.
• CONCURRENT access is not POSSIBLE.
• Security PROBLEMS.

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. So in short we can say 

RDBMS = DBMS + REFERENTIAL INTEGRITY

These relations are defined by using “Foreign Keys” in any RDBMS.Many DBMS companies claimed there DBMS PRODUCT was a RDBMS compliant, but according to industry rules and regulations if the DBMS fulfills the twelve CODD rules it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc) fulfills all the twelve CODD rules and are CONSIDERED as truly 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. So in short we can say 

RDBMS = DBMS + REFERENTIAL INTEGRITY

These relations are defined by using “Foreign Keys” in any RDBMS.Many DBMS companies claimed there DBMS product was a RDBMS compliant, but according to industry rules and regulations if the DBMS fulfills the twelve CODD rules it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc) fulfills all the twelve CODD rules and are considered as truly RDBMS.

ENTITY is a SET of ATTRIBUTES in a DATABASE.

Entity is a set of attributes in a database.

ORDER BY clause helps to SORT the data in EITHER ascending order to descending order.

• Ascending order sort query : SELECT NAME,AGE FROM pcdsEmployee ORDER BY age ASC.
• Descending order sort query : SELECT name FROM pcdsEmployee ORDER BY age DESC.

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

Denormalization is the process of BOOSTING up DATABASE PERFORMANCE and ADDING of REDUNDANT data which helps to get rid of complex data.

Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data.

• Redundancy is controlled.
• UNAUTHORISED access is restricted.
• PROVIDING multiple user INTERFACES.
• Enforcing integrity constraints.
• Providing backup and recovery.

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.

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.

SQL IN OPERATOR is used to see if the value exists in a GROUP of values. For instance the below

SQL checks if the Name is either 'David' or 'Craig' SELECT * FROM wbEmployee WHERE name IN ('David','Craig') Also you can specify a not CLAUSE with the same. SELECT * FROM wbEmployee WHERE AGE NOT IN (30,25)

SQL IN operator is used to see if the value exists in a group of values. For instance the below

SQL checks if the Name is either 'David' or 'Craig' SELECT * FROM wbEmployee WHERE name IN ('David','Craig') Also you can specify a not clause with the same. SELECT * FROM wbEmployee WHERE age NOT IN (30,25)

Main difference between a simple FILE and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and RETRIEVING part of a database but not the independent way of accessing data. MANY experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we LOOK at some old COBOL and C programs where file where the only means of storing data, we can SEE functionalities like locking, multi-user etc provided very efficiently. So it’s a matter of debate if some interviewers think this as a main difference between files and database accept it… going in to debate is probably loosing a job.

Main difference between a simple file and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and retrieving part of a database but not the independent way of accessing data. Many experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we look at some old COBOL and C programs where file where the only means of storing data, we can see functionalities like locking, multi-user etc provided very efficiently. So it’s a matter of debate if some interviewers think this as a main difference between files and database accept it… going in to debate is probably loosing a job.

A stored PROCEDURE is a named GROUP of SQL STATEMENTS that have been previously created and stored in the SERVER database.

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

Below SQL selects employees born between '01/01/1995' AND '01/01/1978' as per MYSQL

SELECT * FROM wbEmployee WHERE DOB BETWEEN '1995-01-01' AND '2011-09-28'

Below SQL selects employees born between '01/01/1995' AND '01/01/1978' as per mysql

SELECT * FROM wbEmployee WHERE DOB BETWEEN '1995-01-01' AND '2011-09-28'

There are four types of database languages:

• Data Definition LANGUAGE (DDL) e.g. CREATE, ALTER, DROP etc.
• Data Manipulation Language (DML) e.g. SELECT, UPDATE, INSERT etc.
• DATA Control Language (DCL) e.g. GRANT and REVOKE.
• TRANSACTION Control Language (TCL) e.g. COMMIT and ROLLBACK.

There are four types of database languages:

DATABASE provides a SYSTEMATIC and organized way of STORING, managing and retrieving from collection of logically related information. Secondly the information has to be persistent, that means even after the application is closed the information should be PERSISTED. Finally it should provide an independent way of ACCESSING data and should not be dependent on the application to access the information.

Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. Secondly the information has to be persistent, that means even after the application is closed the information should be persisted. Finally it should provide an independent way of accessing data and should not be dependent on the application to access the information.